SQLite

Consider the example below:

CREATE TABLE t0(c0);
INSERT INTO t0(c0) VALUES ('val');
SELECT * FROM t0 WHERE (
 (
 		(0 IS NOT FALSE) -- 0
	OR -- 0 
		NOT (0 IS FALSE OR (t0.c0 IN (-1))) -- 0

 ) -- should be 0 (but is 1)
 IS 0
); -- fetches no row

I expect the query to fetch 'val', but it is not fetched. This is due to an incorrect result being computed by the expression. This can be verified by replacing subexpressions by their results. For example, consider the left side of the OR expression:

SELECT (0 IS NOT FALSE); -- 0

When replacing this subexpression above by 0, which should be equivalent, the query fetches the row.

Same problem demonstrated here:

CREATE TABLE t1(x, y);
INSERT INTO t1 VALUES(1, 1);
INSERT INTO t1 VALUES(0, 0);
.print -- before index
SELECT * FROM t1 WHERE y IS TRUE ORDER BY x;

.print -- after index
CREATE INDEX i1 ON t1(x) WHERE y IS NOT TRUE;
SELECT * FROM t1 WHERE y IS TRUE ORDER BY x;

First query above works, second query returns the wrong row.

The problem is that the compiler sometimes thinks the expressions "x IS TRUE/FALSE" is the same as "x IS NOT TRUE/FALSE".

Thanks for the explanation and the fix! I've started assigning a severity level of "cosmetic" to new bug reports, so feel free to adjust it.

This problem goes back to the introduction of the IS TRUE and IS FALSE operators in SQLite version 3.23.0 (2018-04-02).