vishalmore90 opened a new issue, #41431:
URL: https://github.com/apache/beam/issues/41431

   
   ### What happened?
   
   In `apache_beam.transforms.enrichment_handlers.cloudsql` 
(`sdks/python/apache_beam/transforms/enrichment_handlers/cloudsql.py`, lines 
350–377), `CloudSQLEnrichmentHandler._process_batch_request` contains multiple 
critical flaws in batch response correlation:
   
   #### 1. Silent Data Loss on Unmatched / Missing Database Rows
   In single-request mode (`_process_single_request`, lines 337–349), when an 
input record has no matching row in the SQL database, `result.first()` returns 
`None`, yielding `(request, beam.Row())`. `Enrichment`'s `join_fn` (e.g. 
`cross_join`) then preserves the original request in the output `PCollection`.
   
   However, in batch mode (`_process_batch_request`, lines 350–377):
   ```python
       result: Union[list[dict[str, Any]], dict[str, Any]] = 
self._execute_query(
           raw_query, params=param_dict, is_batch=True)
       for response in result:
         response_row = beam.Row(**response)  # type: ignore[arg-type]
         response_key = self.create_row_key(response_row)
         if response_key in requests_map:
           responses.append((requests_map[response_key], response_row))
       return responses
   ```
   The handler only iterates over rows returned by the SQL query. Unmatched 
requests in `requests_map` are never tracked or yielded. As a result, any input 
record whose join key does not exist in the database is **silently dropped** 
from the pipeline output without warning or error.
   
   #### 2. Silent Data Loss on Duplicate Join Keys in the Same Batch
   In `_process_batch_request` (lines 361–364):
   ```python
       for req in requests:
         current_values = self._extract_values_from_request(req)
         values.extend(current_values)
         requests_map[self.create_row_key(req)] = req
   ```
   `requests_map` is a standard `dict` mapping each key to a single `req`. When 
a batch contains multiple records sharing the same join key (common in 
event-stream processing, e.g., multiple clicks from `user_id=101`), each 
subsequent record overwrites the earlier one in `requests_map`. When the query 
returns the row for that key, only the last request is emitted; all preceding 
records are **permanently lost**.
   
   *(In contrast, `BigQueryEnrichmentHandler` in 
`sdks/python/apache_beam/transforms/enrichment_handlers/bigquery.py`, lines 
201–242, uses `requests_map: dict[Any, list[beam.Row]] = defaultdict(list)` and 
emits `(req, beam.Row())` for all unmatched requests.)*
   
   #### 3. `KeyError` Crash When `column_names` Omits Join Column or Column 
Names Differ
   Line 374 invokes:
   ```python
   response_key = self.create_row_key(response_row)
   ```
   Where `create_row_key` (lines 601–611) extracts fields using 
`self._query_config.where_clause_fields` (or `where_clause_value_fn`):
   ```python
     def create_row_key(self, row: beam.Row):
       if isinstance(self._query_config, TableFunctionQueryConfig):
         return tuple(self._query_config.where_clause_value_fn(row))
       if isinstance(self._query_config, TableFieldsQueryConfig):
         row_dict = row._asdict()
         return (
             tuple(
                 row_dict[where_clause_field]
                 for where_clause_field in 
self._query_config.where_clause_fields))
   ```
   - If a user configures `column_names` to fetch specific enriched fields 
(e.g., `column_names=['email', 'tier']`) without explicitly including the join 
column (`id`), `response_row` will only contain `email` and `tier`. 
`create_row_key(response_row)` fails with `KeyError: 'id'`.
   - If the input record uses a different field name than the database table 
column (e.g. input record has `customer_id` while the table column is `id`), 
`create_row_key(response_row)` looks for `customer_id` in the database row and 
raises `KeyError`.
   - If `TableFunctionQueryConfig` is used, `where_clause_value_fn` expects an 
input `Row` and may reference fields that do not exist in the database table 
schema.
   
   #### 4. Cache Key Mismatch in `get_cache_key`
   In `get_cache_key` (lines 632 and 649–650):
   - Batch mode formats keys with `repr`: `key = ';'.join(map(repr, 
current_values))` -> `'alice'`
   - Single mode formats keys with `%s` / `str`: `key = ";".join(["%s"] * 
len(current_values)) % tuple(current_values)` -> `alice`
   This causes cache divergence between batched and unbatched executions.
   
   ---
   
   ### Code Pointers / Steps to Reproduce
   
   The flaw can be verified directly with a mocked `_execute_query` or against 
an external SQL instance:
   
   ```python
   import apache_beam as beam
   from unittest.mock import MagicMock
   from apache_beam.transforms.enrichment_handlers.cloudsql import (
       CloudSQLEnrichmentHandler,
       ExternalSQLDBConnectionConfig,
       DatabaseTypeAdapter,
       TableFieldsQueryConfig,
   )
   
   config = ExternalSQLDBConnectionConfig(
       db_adapter=DatabaseTypeAdapter.POSTGRESQL,
       host='localhost', port=5432, user='u', password='p', db_id='db'
   )
   query_config = TableFieldsQueryConfig(
       table_id='users', where_clause_template='id = :id', 
where_clause_fields=['id']
   )
   handler = CloudSQLEnrichmentHandler(
       connection_config=config, query_config=query_config, min_batch_size=2, 
max_batch_size=10
   )
   
   # Reproduction 1: Missing DB row -> silent data loss
   handler._execute_query = MagicMock(return_value=[{'id': 1, 'name': 'Alice'}])
   reqs = [beam.Row(id=1, val='A'), beam.Row(id=2, val='B')]  # id=2 is missing 
from DB
   responses = handler._process_batch_request(reqs)
   assert len(responses) == 2, f"Expected 2 responses, got {len(responses)}"
   # AssertionError: Expected 2 responses, got 1 (id=2 was dropped!)
   
   # Reproduction 2: Duplicate join keys -> silent data loss
   handler._execute_query = MagicMock(return_value=[{'id': 1, 'name': 'Alice'}])
   reqs = [beam.Row(id=1, event='click'), beam.Row(id=1, event='buy')]
   responses = handler._process_batch_request(reqs)
   assert len(responses) == 2, f"Expected 2 responses, got {len(responses)}"
   # AssertionError: Expected 2 responses, got 1 (reqs[0] was overwritten in 
requests_map and dropped!)
   
   # Reproduction 3: column_names omits join column -> KeyError
   handler_cols = CloudSQLEnrichmentHandler(
       connection_config=config, query_config=query_config, 
column_names=['name'],
       min_batch_size=2, max_batch_size=10
   )
   handler_cols._execute_query = MagicMock(return_value=[{'name': 'Alice'}])
   handler_cols._process_batch_request([beam.Row(id=1), beam.Row(id=2)])
   # KeyError: 'id'
   ```
   
   ---
   
   ### Impact
   
   - **Silent Data Loss:** When batching is enabled (`min_batch_size > 1`), any 
input rows lacking a matching record in the database table are dropped from the 
pipeline. Furthermore, multiple input rows sharing the same join key within a 
batch are collapsed into a single row, permanently discarding data.
   - **Pipeline Crashes:** Restricting fetched fields via `column_names` or 
using different field names between the input schema and SQL table causes 
unhandled `KeyError` crashes in batch mode.
   
   ---
   
   ### Proposed Solution
   
   1. In `_process_batch_request` 
(`sdks/python/apache_beam/transforms/enrichment_handlers/cloudsql.py`, lines 
350–377):
      - Use `requests_map: dict[Any, list[beam.Row]] = 
collections.defaultdict(list)` so duplicate keys map to all matching input 
requests.
      - Maintain an `unmatched_requests` set or copy of `requests_map`.
      - When iterating over `result`, pop matched keys from 
`unmatched_requests` and append `(req, response_row)` for each request in the 
bucket.
      - For remaining entries in `unmatched_requests`, append `(req, 
beam.Row())` (or optionally respect `throw_exception_on_empty_results` if 
added), preserving standard outer/left join semantics.
   2. Ensure join fields are always included in the `SELECT` projection (or 
create row keys using the SQL table's join column definitions rather than 
relying on input field names).
   3. Align `get_cache_key` between single and batch modes to use consistent 
string formatting.
   
   ---
   
   ### Issue Priority
   
   Priority: 1 (data loss / total loss of function)
   
   ---
   
   ### Issue Components
   
   - [x] Component: Python SDK
   - [ ] Component: Java SDK
   - [ ] Component: Go SDK
   - [ ] Component: Typescript SDK
   - [x] Component: IO connector
   - [ ] Component: Beam YAML
   - [ ] Component: Beam ML
   - [ ] Component: Beam examples
   - [ ] Component: Beam playground
   - [ ] Component: Beam katas
   - [ ] Component: Website
   - [ ] Component: Infrastructure
   - [ ] Component: Spark Runner
   - [ ] Component: Flink Runner
   - [ ] Component: Prism Runner
   - [ ] Component: Hazelcast Jet Runner
   - [ ] Component: Google Cloud Dataflow Runner
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: [email protected]

For queries about this service, please contact Infrastructure at:
[email protected]

Reply via email to