Partial index uniqueness violation

[sim1984]

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.

[hvlad]

Should be fixed in master now, please check next build

[sim1984]

The case given in the ticket works correctly. But the opposite case, when the column in the filtering condition is changed so that the record no longer falls into the index key, does not work.

If you do everything the same as before and then this

SQL> ALTER INDEX IDX_T1_A ACTIVE;
SQL> select * from t1;

                   ID                     A       B
===================== ===================== =======
                    1                     1       1
                    2                     2       1
                    3                     1       0

SQL> update t1 set b=0;
SQL> commit;
SQL> insert into t1(id, a, b) values(5, 2, 1); <-- 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" = 2)
SQL> rollback;
SQL> select * from t1;

                   ID                     A       B
===================== ===================== =======
                    1                     1       0
                    2                     2       0
                    3                     1       0

[sim1984]

Simplified version of the second case

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);
INSERT INTO T1(ID, A, B) VALUES (3, 1, 0);

COMMIT;

UPDATE T1 SET B=0;

COMMIT;

INSERT INTO T1(ID, A, B) VALUES (4, 2, 1); /* Error */

[sim1984]

Pavel the bug is not completely fixed. Only one special case has been fixed. Perhaps you need to change the description of the error. It is not about a violation of uniqueness, but about the fact that changes in the columns participating in the partial index filtering condition are not tracked. It's just easy to see with unique indexes.

[hvlad]

The case given in the ticket works correctly. But the opposite case, when the column in the filtering condition is changed so that the record no longer falls into the index key, does not work.

I see, thanks. Working on fixing it.

[sim1984]

After the latest fix (version 6.0.0.325), partial indexes work without errors. Please make a fix for 5.0 as well. Thanks

[hvlad]

Done, thanks for testing.