View Revisions: Issue #6617

Back to Issue
Summary 0006617: Update trigger to accommodate NULLs for unique index with NULLable columns
Revision 2024-10-11 16:26 by pbelov
Steps To Reproduce
Revision 2024-10-11 20:11 by pbelov
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.
Revision 2024-10-11 16:26 by pbelov
Additional Information Related to a more complex issue brought up in [#JNJ-86978-524] 3.15.8 converting NULL value to empty String
Revision 2024-10-11 20:11 by pbelov
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;

SELECT * FROM test6617;
Revision 2024-10-14 21:03 by pbelov
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;