Hi everyone,
I think that I found a bug that occurs when setting legacy_file_format to
true and calling REINDEX, which then results in "Error: UNIQUE constraint
failed: index 'index_0'" in the specific example below:
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 OR IGNORE INTO test(c0, c1) VALUES ('a', TRUE);
INSERT OR IGNORE INTO test(c0, c1) VALUES ('a', FALSE);
PRAGMA legacy_file_format=true;
REINDEX;
I would not expect the UNIQUE constraint to fail, since there is only one
value that equals to FALSE in c1:
sqlite> SELECT * FROM test WHERE c1 == FALSE;
a|0
I could not reduce the example further. I originally found this issue with
a slightly more complex example and VACUUM instead of REINDEX:
CREATE TABLE test (c0, c1 TEXT);
CREATE UNIQUE INDEX IF NOT EXISTS index_0 ON test(FALSE >= c1 DESC);
CREATE UNIQUE INDEX IF NOT EXISTS index_1 ON test((c0 || FALSE) ASC) WHERE
c1;
INSERT OR IGNORE INTO test(c0, c1) VALUES ('', '1');
INSERT OR IGNORE INTO test(c0, c1) VALUES ('', FALSE);
SELECT * FROM test;
PRAGMA legacy_file_format=true;
VACUUM;
Best,
Manuel
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
