SQLite

View Ticket
Login
Ticket Hash: d3e7f2ba5b3ba4fb3c55082f7e248f2761a413ff
Title: Nested boolean formula with IN operator computes an incorrect result
Status: Fixed Type: Code_Defect
Severity: Important Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2019-05-20 12:35:18
Version Found In: 3.28
User Comments:
mrigger added on 2019-05-19 18:23:34:

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.


dan added on 2019-05-20 10:23:36:

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".


mrigger added on 2019-05-20 11:07:05:

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.


drh added on 2019-05-20 12:35:18:

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