(text/x-fossil-wiki)
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.
<blockquote><verbatim>
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
</verbatim></blockquote>
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.
|