Hi everyone,
It think that I found a bug where I could circumvent a UNIQUE check of an
index. Consider the example below, which causes "Error: UNIQUE constraint
failed" when invoking REINDEX:
CREATE TABLE test (c0, c1 REAL);
CREATE UNIQUE INDEX index_1 ON test(c0 GLOB c1);
INSERT INTO test(c0, c1) VALUES ('1', '1');
INSERT INTO test(c0, c1) VALUES ('0', '1');
REINDEX;
The '1's stored to c1 are converted to REAL due to the column's affinity,
and supposedly because SQLite determines that the conversion is lossless.
However, while '1' GLOB '1' would yield true and '0' GLOB '1' would yield
false, which would be valid, after the conversion both GLOB expressions
yield false, violating the UNIQUE property of the index, which is not
detected until the REINDEX:
sqlite> SELECT *, typeof(c0), typeof(c1), c0 GLOB c1, c0 GLOB '1' from test;
1|1.0|text|real|0|1
0|1.0|text|real|0|0
Probably, the index check is performed before the conversion?
Best,
Manuel
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
