View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0004142 | SymmetricDS | Bug | public | 2019-11-10 11:48 | 2019-11-10 11:48 |
| Reporter | ei99045 | Assigned To | |||
| Priority | normal | ||||
| Status | new | Resolution | open | ||
| Product Version | 3.10.5 | ||||
| Summary | 0004142: DataExtractorService level queries don't handle initial_load_select conditions properly | ||||
| Description | DataExtractorService attempts to handle self-referencing tables by performing multi level queries. These queries attempt to navigate the acyclical graph of references by starting with those rows that don't reference any other rows, then moving on to the rows that reference those "level 0" rows, and so on. This is implemented by adding an "AND" clause to whichever WHERE clause is already being used in the extraction for the given table. In my scenario, we have set the initial_load_select in the sym_trigger_router table to be "column_a = ${externalId} OR column_b = ${externalId}, and this results in a level 0 query that looks like this: column_a='$(externalId)' OR column_b='$(externalId)' and reference_column is null or reference_column = uuid This won't work as intended, because the AND operator takes precedence over the initial OR operator. The query will be interpreted as if it had been written: column_a='$(externalId)' OR (column_b='$(externalId)' and reference_column is null) OR reference_column = uuid This is clearly not what was intended and results is unwanted records being extracted. The initial_load_select clause should be included in parentheses to both sides of that final OR operator, something like this: ((column_a='$(externalId)' OR column_b='$(externalId)') AND reference_column is null) OR ((column_a='$(externalId)' OR column_b='$(externalId)') AND reference_column = uuid) The queries that are produced for levels 1 and beyond also have the same problem. | ||||
| Steps To Reproduce | Try to do an initial load for a table that has a reload enabled trigger configured with an initial_load_select that includes an OR expression. The initial load will never finish because of duplicate key errors at the target node. | ||||
| Additional Information | This happens irrespective of the initial.load.use.extract.job.enabled value. | ||||
| Tags | dialect: postgresql, extract, initial/partial load | ||||