unexpected results when adding keyword ”where“
(1) By syang (ysy111) on 2023-11-09 15:49:18 [link] [source]
I ran these sql statements by SQLite CLI on Windows 11 and version 3.44.0, 3.43.2，they have the same problem.
The expected output of the first select query should be no less than the second select query. Besides,the second and thrid select query should have the same output.However,i dont know what's the root cause why they happened.
CREATE TABLE rt0 (c0 INTEGER, c1 INTEGER, c2 INTEGER, c3 INTEGER, c4 INTEGER);
CREATE TABLE rt3 (c0 INTEGER, c1 INTEGER, c2 INTEGER,c3 INTEGER);
INSERT OR IGNORE INTO rt0(c3, c1) VALUES (x'', '1'), ('-1', -1e500), (1, x'');
CREATE VIEW v6(c0, c1, c2) AS SELECT 0, 0, 0;
SELECT COUNT(*) FROM rt0 LEFT OUTER JOIN rt3 ON NULL RIGHT OUTER JOIN v6 ON ((CASE v6.c0 WHEN rt0.c4 THEN rt3.c3 END) NOT BETWEEN (rt0.c4) AND (NULL)); -- 1
SELECT COUNT(*) FROM rt0 LEFT OUTER JOIN rt3 ON NULL RIGHT OUTER JOIN v6 ON ((CASE v6.c0 WHEN rt0.c4 THEN rt3.c3 END) NOT BETWEEN (rt0.c4) AND (NULL)) WHERE (rt0.c1); -- 2
SELECT COUNT(*) FROM rt0 LEFT OUTER JOIN rt3 ON NULL RIGHT OUTER JOIN v6 ON ((CASE v6.c0 WHEN rt0.c4 THEN rt3.c3 END) NOT BETWEEN (rt0.c4) AND (NULL)) WHERE ((rt0.c1) IS TRUE); -- 0
(2) By David Raymond (dvdraymond) on 2023-11-09 16:16:44 in reply to 1 [link] [source]
There's a problem, but it's with the second query, not the first or third.
from rt0 left outer join rt3 on null
so you're left with just the original contents of rt0
...
right outer join v6 on ((case 0 when null then <doesn't matter> end)<so always null> not between something and null) <so always null>
so you're left with only the records in v6, which there's 1 of.
So query 1 is correct.
rt0 is always nulls, so query 3 is also correct.
But it looks like there's a problem with the planning for query 2.
sqlite> SELECT * FROM rt0 LEFT OUTER JOIN rt3 ON NULL RIGHT OUTER JOIN v6 ON ((CASE v6.c0 WHEN rt0.c4 THEN rt3.c3 END) NOT BETWEEN (rt0.c4) AND (NULL));
QUERY PLAN
|--MATERIALIZE v6
| `--SCAN CONSTANT ROW
|--SCAN rt0
|--SCAN rt3 LEFT-JOIN
|--SCAN v6
`--RIGHT-JOIN v6
`--SCAN v6
c0 c1 c2 c3 c4 c0 c1 c2 c3 c0 c1 c2
-- -- -- -- -- -- -- -- -- -- -- --
0 0 0
sqlite> SELECT * FROM rt0 LEFT OUTER JOIN rt3 ON NULL RIGHT OUTER JOIN v6 ON ((CASE v6.c0 WHEN rt0.c4 THEN rt3.c3 END) NOT BETWEEN (rt0.c4) AND (NULL)) WHERE (rt0.c1);
QUERY PLAN
|--MATERIALIZE v6
| `--SCAN CONSTANT ROW
|--SCAN v6
|--SCAN rt0
`--SCAN rt3 LEFT-JOIN
c0 c1 c2 c3 c4 c0 c1 c2 c3 c0 c1 c2
-- ---- -- -- -- -- -- -- -- -- -- --
1 0 0 0
-Inf -1 0 0 0
sqlite> SELECT * FROM rt0 LEFT OUTER JOIN rt3 ON NULL RIGHT OUTER JOIN v6 ON ((CASE v6.c0 WHEN rt0.c4 THEN rt3.c3 END) NOT BETWEEN (rt0.c4) AND (NULL)) WHERE ((rt0.c1) IS TRUE);
QUERY PLAN
|--MATERIALIZE v6
| `--SCAN CONSTANT ROW
|--SCAN rt0
|--SCAN rt3 LEFT-JOIN
|--SCAN v6
`--RIGHT-JOIN v6
`--SCAN v6
(3) By Richard Hipp (drh) on 2023-11-09 22:56:39 in reply to 1 [source]
Simplified test case that you can run in Fiddle:
.mode qbox CREATE TABLE t1(a INT,b INT,c INT); INSERT INTO t1(a,b) VALUES(1,0),(-500,-1); CREATE TABLE t2(m INT); CREATE VIEW t3(x) AS SELECT 0; .echo on SELECT * FROM t1 LEFT JOIN t2 ON NULL RIGHT JOIN t3 ON (CASE x WHEN c THEN m END)=0; SELECT * FROM t1 LEFT JOIN t2 ON NULL RIGHT JOIN t3 ON (CASE x WHEN c THEN m END)=0 WHERE a!=0; SELECT * FROM t1 LEFT JOIN t2 ON NULL RIGHT JOIN t3 ON (CASE x WHEN c THEN m END)=0 WHERE (a!=0) IS TRUE;
The middle SELECT is wrong. The first and last get the same answer as PostgreSQL. All three SELECTs get the same result as PostgreSQL if you change the t3 view into a real table:
DROP VIEW t3; CREATE TABLE t3(x INT); INSERT INTO t3(x) VALUES(0);
(4) By Richard Hipp (drh) on 2023-11-10 16:03:55 in reply to 1 [link] [source]
This was a problem in the RIGHT-JOIN strength reduction optimization that was introduced with version 3.43 (see item 5a on the change log). The problem is resolved by check-in 530d10e93a5f63b7.
The Incorrect results cause dby nested JOIN table issue repored by Zuming Jiang appears to be the same problem. Both are fixed by the same check-in.