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