Addressed by https://www.sqlite.org/src/info/713caa382cf7ddef
On 5/1/19, Manuel Rigger <[email protected]> wrote: > 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 > [email protected] > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp [email protected] _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
