|Title:||Nested boolean formula with IN operator computes an incorrect result|
|Last Modified:||2019-05-20 12:35:18|
|Version Found In:||3.28|
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
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).