Reproduce in Firebird 5.0.1.1381
RECREATE TABLE T1 (
ID BIGINT NOT NULL,
A BIGINT NOT NULL,
B SMALLINT NOT NULL,
CONSTRAINT PK_T1 PRIMARY KEY(ID)
);
CREATE UNIQUE INDEX IDX_T1_A ON T1(A) WHERE (B = 1);
INSERT INTO T1(ID, A, B) VALUES (1, 1, 0);
INSERT INTO T1(ID, A, B) VALUES (2, 2, 1);
COMMIT;
Now let's try to break the uniqueness.
SQL> INSERT INTO T1(ID, A, B) VALUES (3, 1, 0); <-- Good
SQL> COMMIT;
SQL> INSERT INTO T1(ID, A, B) VALUES (4, 2, 1); <-- Good
Statement failed, SQLSTATE = 23000
attempt to store duplicate value (visible to active transactions) in unique index "IDX_T1_A"
-Problematic key value is ("A" = 2)
SQL> ROLLBACK;
SQL> UPDATE T1 SET B = 1 WHERE ID = 1; <-- Good
SQL> COMMIT;
SQL> UPDATE T1 SET B = 1 WHERE ID = 3; <-- Not Good
SQL> COMMIT;
SQL> ALTER INDEX IDX_T1_A ACTIVE; <-- Boom!
Statement failed, SQLSTATE = 23000
attempt to store duplicate value (visible to active transactions) in unique index "IDX_T1_A"
-Problematic key value is ("A" = 1)
Thus, if the filtering field of a partial index is not included in the index key, then its uniqueness can be easily violated by updating the filtering field. This breaks backup/restore and index rebuilding.
Reproduce in Firebird 5.0.1.1381
Now let's try to break the uniqueness.
Thus, if the filtering field of a partial index is not included in the index key, then its uniqueness can be easily violated by updating the filtering field. This breaks backup/restore and index rebuilding.