View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0006617 | SymmetricDS | Bug | public | 2024-10-11 16:26 | 2024-11-13 20:31 |
| Reporter | pbelov | Assigned To | pbelov | ||
| Priority | normal | ||||
| Status | closed | Resolution | fixed | ||
| Product Version | 3.15.0 | ||||
| Target Version | 3.15.10 | Fixed in Version | 3.15.10 | ||
| 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 | ||||
|
|
Issued a patch https://downloads.jumpmind.com/symmetricds/patches/3.15/patch-0006617.jar |
|
|
Merged the fix to 3.15 PR= https://github.com/JumpMind/symmetric-ds/pull/199 |
| 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 |
| 2024-10-21 15:02 | pbelov | Note Added: 0002508 | |
| 2024-10-21 15:04 | pbelov | Status | assigned => resolved |
| 2024-10-21 15:04 | pbelov | Resolution | open => fixed |
| 2024-10-21 15:04 | pbelov | Fixed in Version | => 3.15.10 |
| 2024-10-21 15:04 | pbelov | Note Added: 0002509 | |
| 2024-11-05 20:35 | pbelov | Note Edited: 0002509 | View Revisions |
| 2024-11-13 20:31 | pbelov | Status | resolved => closed |