3.46.1 collation/primary key/in-operator

(1) By anonymous on 2025-08-04 20:03:58 [source]

Hey,

There seems to be a bug in version 3.46.1 (and 3.45.1). I don't know if it has been fixed downstream. I am using the sqlite3 library in Python 3.13 and 3.12 respectively, on Ubuntu Linux.

Context:

CREATE TABLE t (c1 TEXT COLLATE NOCASE, c2 INTEGER, PRIMARY KEY (c1, c2));
INSERT INTO t (c1, c2) VALUES ('a', 1);

The following statement:

SELECT ('a', 1) IN (SELECT c1, c2 from t);

returns 1, as I suppose it ought to, but:

SELECT (1, 'a') IN (SELECT c2, c1 from t);

returns 0.

Have I made a mistake here?

(2.1) By Richard Hipp (drh) on 2025-08-05 02:06:18 edited from 2.0 in reply to 1 [link] [source]

This problem appears to go back to the first introduction of support for row-value comparisons by check-in 2016-09-07T19:54z, 8 years, 10 months, and 28 days ago. In all the time that SQLite has supported row-value comparisons using the IN operator, as shown in the test case above, it has never gotten the correct answer when the columns are out of order from the index, and one column is of type integer, and the other is of type text.

Good find. Thanks for the bug report.

Fixed on Fiddle and by check-in 2025-08-04T23:05z.