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:

Consider the following test case:

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

Unexpectedly, the REINDEX results in an error "UNIQUE constraint failed".


drh added on 2019-10-26 20:24:58:

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:

I just reproduced this issue using a newly configured and built SQLite3 based on the latest commit [713fe86b8c] and using no configuration options.

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


drh added on 2019-10-26 23:52:24:

OK. I managed to get it to fail. Fixed now.