|Title:||REINDEX causes "UNIQUE constraint failed" error for generated column|
|Last Modified:||2019-10-26 23:52:24|
|Version Found In:||3.30.0|
mrigger added on 2019-10-26 20:05:39:
Consider the following test case: <pre> CREATE TABLE t0(c0, c1 TEXT GENERATED ALWAYS AS (1) UNIQUE); INSERT INTO t0(c0) VALUES (1); REINDEX; INSERT INTO t0(c0) VALUES (0); REINDEX; -- unexpected: UNIQUE constraint failed </pre> Unexpectedly, the REINDEX results in an error "UNIQUE constraint failed".
drh added on 2019-10-26 20:24:58: (text/x-fossil-wiki)
Are you sure? I get the "UNIQUE constraint failed" error on the second INSERT, which is exactly what one would expect. The second REINDEX seems to work fine for me. What specific check-in of SQLite are you testing this against?
mrigger added on 2019-10-26 21:26:49: (text/x-fossil-wiki)
I just reproduced this issue using a newly configured and built SQLite3 based on the latest commit [713fe86b8c] and using no configuration options. <pre> SQLite version 3.31.0 2019-10-26 18:56:12 Enter ".help" for usage hints. sqlite> CREATE TABLE t0(c0, c1 TEXT GENERATED ALWAYS AS (1) UNIQUE); sqlite> INSERT INTO t0(c0) VALUES (1); sqlite> REINDEX; sqlite> INSERT INTO t0(c0) VALUES (0); sqlite> REINDEX; -- unexpected: UNIQUE constraint failed Error: UNIQUE constraint failed: t0.c1 </pre>
drh added on 2019-10-26 23:52:24: (text/x-fossil-wiki)
OK. I managed to get it to fail. Fixed now.