SQLite

View Ticket
Login
Plaintext
2019-05-01
15:32 • Fixed ticket [3be1295b]: Inconsistent behavior of a unique constraint on a boolean expression. plus 5 other changes (artifact: 1100ca34 user: drh)
15:32
Avoid unwelcomed side effects on the input operands in the OP_Concat operator. Fix for ticket [3be1295b264be2fac49b681] (check-in: 713caa38 user: drh tags: trunk)
14:28 • New ticket [3be1295b] Inconsistent behavior of a unique constraint on a boolean expression.. (artifact: f434cc99 user: drh)
Ticket Hash: 3be1295b264be2fac49b68189a98c82061c17904
Title: Inconsistent behavior of a unique constraint on a boolean expression.
Status: Fixed Type: Code_Defect
Severity: Important Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2019-05-01 15:32:51
Version Found In: 3.28.0
User Comments:
drh added on 2019-05-01 14:28:03:

In the SQL below, the first INSERT statement fails due to a uniqueness constraint failure. But the next two inserts actually work, even though they collectively insert the same content. 

CREATE TABLE test (c0, c1 TEXT);
CREATE UNIQUE INDEX IF NOT EXISTS index_0 ON test(c1 == FALSE);
CREATE INDEX IF NOT EXISTS index_1 ON test(c0 || FALSE) WHERE c1;
INSERT INTO test(c0, c1) VALUES('a',TRUE),('a',FALSE); -- fails
INSERT INTO test(c0, c1) VALUES('a',TRUE);
INSERT INTO test(c0, c1) VALUES('a',FALSE); -- works

This issue arose from a mailing list report by Manual Rigger. As the anomalous behavior depends on having a boolean expression as a term in a UNIQUE constraint, and since that is a unlikely construct to occur in practice, this ticket is given a low priority.