View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0006617 | SymmetricDS | Bug | public | 2024-10-11 16:26 | 2024-10-14 21:03 |
| Reporter | pbelov | Assigned To | pbelov | ||
| Priority | normal | ||||
| Status | assigned | Resolution | open | ||
| Product Version | 3.15.0 | ||||
| Summary | 0006617: Update trigger to accommodate NULLs for unique index with NULLable columns | ||||
| Description | Unique index with NULL-able columns presents a challenge because it is very similar to the regular primary key, yet comparing column values fails when values are NULL due to SQL specification that NULL <> NULL. And update is needed to detect NULLable columns in the index and change where clause to: (source.column = target.column OR source.column IS NULL AND target.column IS NULL). | ||||
| Steps To Reproduce | Update trigger is constructed in such a way that it is not working for the case: - Table does not have a PrimaryKey - Table has a Unique index instead - Column which is part of Unique index is NULLable - Update is performed on a row : * Updated column is not part of the unique index; * There was already a NULL in the column, which is part unique index (this value is unaffected by the update) Observe: Row change is not captured by the SymmetricDS trigger. | ||||
| Additional Information | Related to a more complex issue brought up in [#JNJ-86978-524] 3.15.8 converting NULL value to empty String. CREATE TABLE test6617( col1key INTEGER NOT NULL ,col2nullablekey CHAR(3) NULL ,col3 INTEGER NULL ); CREATE UNIQUE INDEX test6617_udx on test6617(col1key, col2nullablekey); INSERT INTO test6617 VALUES ( 1, NULL, 1); UPDATE test6617 SET col3 = 2 WHERE col2nullablekey IS NULL; --// Notice that change is NOT captured and target table on the client node is NOT updated! SELECT * FROM test6617; | ||||
| Tags | trigger | ||||
| Date Modified | Username | Field | Change |
|---|---|---|---|
| 2024-10-11 16:26 | pbelov | New Issue | |
| 2024-10-11 16:26 | pbelov | Status | new => assigned |
| 2024-10-11 16:26 | pbelov | Assigned To | => pbelov |
| 2024-10-11 16:26 | pbelov | Tag Attached: trigger | |
| 2024-10-11 20:11 | pbelov | Steps to Reproduce Updated | View Revisions |
| 2024-10-11 20:11 | pbelov | Additional Information Updated | View Revisions |
| 2024-10-14 21:03 | pbelov | Summary | Update trigger to accomodate NULLs for unique index with NULLable columns => Update trigger to accommodate NULLs for unique index with NULLable columns |
| 2024-10-14 21:03 | pbelov | Additional Information Updated | View Revisions |