Unexpected result with UNIQUE constraint

(1) By axelteo on 2026-02-26 05:37:07 [source]

Dear SQLite Developers,

I found a test case with an unexpected result on the latest checkout [21f9abe6c9d1e2ce99ed75c2984103dcd2356b5d].

Adding a UNIQUE constraint to column c1 in table t0 changes the output of the same query.

Case 1: Without `UNIQUE` constraint

CREATE TABLE t0 (c0 PRIMARY KEY COLLATE NOCASE, c1) WITHOUT ROWID;
INSERT INTO t0 VALUES ('X', 'a');

CREATE TABLE t1 (c0);
INSERT INTO t1 VALUES ('a');
INSERT INTO t1 VALUES ('aa');

SELECT ALL *
FROM t0, t1
WHERE (t1.c0, t1.c0) > (t0.c1, t0.c0);

-- outputs 2 rows

Case 2: With `UNIQUE` constraint on `c1`

CREATE TABLE t0 (c0 PRIMARY KEY COLLATE NOCASE, c1 UNIQUE) WITHOUT ROWID;
INSERT INTO t0 VALUES ('X', 'a');

CREATE TABLE t1 (c0);
INSERT INTO t1 VALUES ('a');
INSERT INTO t1 VALUES ('aa');

SELECT ALL *
FROM t0, t1
WHERE (t1.c0, t1.c0) > (t0.c1, t0.c0);

-- outputs 1 row

Thank you for your efforts in bug-fixing and analysis.

(2) By Bo Lindbergh (_blgl_) on 2026-02-26 07:23:19 in reply to 1 [link] [source]

If you rewrite the query to remove the row value comparison, you get the same result in both cases. Interesting…

SELECT ALL *
FROM t0, t1
WHERE t1.c0 > t0.c1 or (t1.c0 = t0.c1 and t1.c0 > t0.c0);

(3) By Dan Kennedy (dan) on 2026-02-26 11:04:27 in reply to 1 [link] [source]

Thanks for reporting this. Should now be fixed here:

https://sqlite.org/src/info/212c68249c

Dan.

(4) By Richard Hipp (drh) on 2026-05-18 10:38:47 in reply to 3 [link] [source]

That bug fix appears to create a new problem described at Bug report 2026-05-17T11:59:19Z.