SQLite

View Ticket
Login
Ticket Hash: 3ea175512444b0d166af9d674757d83c946d9ce6
Title: REINDEX causes "UNIQUE constraint failed" error for generated column
Status: Fixed Type: Code_Defect
Severity: Important Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2019-10-26 23:52:24
Version Found In: 3.30.0
User Comments:
mrigger added on 2019-10-26 20:05:39: (text/x-fossil-wiki)
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.