Unexpected Execution Result

(1) By axelteo on 2026-01-16 11:35:28 [source]

Dear SQLite Developers,

I found a test case where two semantically equivalent queries produce different outputs. I can reproduce it on the latest checkout [a5918835e172fd4eda6511d297b61368952d617e]. Thank you for your efforts in bug-fixing and analysis.

CREATE TABLE t0 (c1 COLLATE NOCASE);
CREATE INDEX i0 ON t0 (c1);
CREATE TABLE t1 (c1);
INSERT INTO t0 VALUES ('a');
INSERT INTO t1 VALUES ('A\n');

-- Query 1: Returns 1 row
SELECT * FROM t0, t1;

-- Query 2: Returns 0 rows
SELECT * FROM t0, t1 WHERE
      (t0.c1 >= t1.c1) AND (t0.c1) OR (t1.c1 BETWEEN True AND t0.c1)
UNION ALL
SELECT * FROM t0, t1 WHERE NOT (
      (t0.c1 >= t1.c1) AND (t0.c1) OR (t1.c1 BETWEEN True AND t0.c1)
)
UNION ALL
SELECT * FROM t0, t1 WHERE (
      (t0.c1 >= t1.c1) AND (t0.c1) OR (t1.c1 BETWEEN True AND t0.c1)
) IS NULL;

(2) By Richard Hipp (drh) on 2026-01-16 12:28:40 in reply to 1 [link] [source]

Consider the expression (t0.c1 >= t1.c1). Because the definition of t0.c1 contains COLLATE NOCASE the NOCASE collating sequence is used for comparison. (See the rules for this at https://sqlite.org/datatype3.html#collation.)

The expression ('a' >= 'A\n' COLLATE NOCASE) is always false. Hence the WHERE clause on each element of the second query is false. So no rows are returned.

That all seems correct to me. Where do you see an error?

(3) By Bo Lindbergh (_blgl_) on 2026-01-16 13:14:13 in reply to 2 [link] [source]

You missed the OR operator.

Using the same tables, this simplified example returns no rows:

SELECT * FROM t0, t1 WHERE (t0.c1 >= t1.c1) AND (t0.c1) OR (t1.c1 BETWEEN True AND t0.c1);

But if you swap the operands of the OR operator, you get one row:

SELECT * FROM t0, t1 WHERE (t1.c1 BETWEEN True AND t0.c1) OR (t0.c1 >= t1.c1) AND (t0.c1);

That can't be right.

(4) By Richard Hipp (drh) on 2026-01-16 13:51:08 in reply to 3 [link] [source]

Yeah, that does seem wrong. So the simplified problem report is that the last two SELECT statements in the following script get different answers:

CREATE TABLE t0(x COLLATE NOCASE);
CREATE INDEX t0x ON t0(x);
CREATE TABLE t1(y);
INSERT INTO t0 VALUES('a');
INSERT INTO t1 VALUES('ABC');
SELECT * FROM t0, t1 WHERE (x>=y AND x) OR (y BETWEEN 1 AND x);
SELECT * FROM t0, t1 WHERE (y BETWEEN 1 AND x) OR (x>=y AND x);

(7) By Simon Slavin (slavin) on 2026-01-17 22:19:37 in reply to 3 [link] [source]

What are the priority rules for the OR and AND sequences in the above ? Ignoring the bracketed sections, are the rules for

a AND b OR c

and

a OR b AND c

clearly stated in the documentation ?

(8) By TripeHound on 2026-01-17 22:51:29 in reply to 7 [link] [source]

Yes... SQL Language Expressions, section 2: Operators, and Parse-Affecting Attributes.

(5) By Richard Hipp (drh) on 2026-01-16 15:56:35 in reply to 1 [link] [source]

Good find. This problem goes back to 2015-03-16, SQLite version 3.8.9. Now fixed on trunk.

(6) By axelteo on 2026-01-17 05:03:34 in reply to 5 [link] [source]

Thank you for fixing so quickly!