SQLite User Forum

unexpected results when adding keyword ”where“
Login
Chronological

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.

(5) By anonymous on 2024-03-14 16:22:53 in reply to 4 [link] [source]

Hello,

I'm sorry to bother you. I already know that this bug has been fixed. Here I just want to confirm if my tool is working properly. The version of the sqlite is 3.44.0.

Below is the simplified query result that still triggers the bug. The aim of this simplification is to assist developers in identifying the root cause of the issue.

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);

CREATE VIEW v6(c0, c1, c2) AS SELECT 0, 0, 0;


SELECT COUNT(*) FROM rt0 LEFT OUTER JOIN rt3 RIGHT OUTER JOIN v6 ON ((rt3.c3 ) ); --1

SELECT COUNT(*) FROM rt0 LEFT OUTER JOIN rt3 RIGHT OUTER JOIN v6 ON (( rt3.c3 ) ) WHERE (rt0.c1); --2
  • We've distilled the steps and SQL statements to the bare essentials needed to reproduce the issue. This ensures clarity and focuses on the core problem.
  • The logic behind this simplification process was to eliminate any elements of the query that did not directly contribute to the observed bug, streamlining the diagnostic process.

Could you please confirm whether this simplification process has been beneficial for developers in diagnosing and addressing the issue?

(6) By Stephan Beal (stephan) on 2024-03-14 16:28:06 in reply to 5 [link] [source]

The version of the sqlite is 3.44.0.

The fix linked to in the post you responded to was applied about a week after 3.44.0 was released.

(7) By anonymous on 2024-03-14 19:19:13 in reply to 6 [link] [source]

Yes, I know it has already been fixed. I just want to confirm if my tool is helpful for developers to diagnose bugs.

I would greatly appreciate it if you could confirm this for me.

(8) By jose isaias cabrera (jicman) on 2024-03-14 19:46:07 in reply to 7 [link] [source]

I am not speaking for the developers, but I don't think so. The SQLite developers have their own diagnosing tools. But, if you keep finding problems, please keep placing them in this forum and it will be addressed.

(9) By Stephan Beal (stephan) on 2024-03-14 19:53:02 in reply to 7 [link] [source]

I just want to confirm if my tool is helpful for developers to diagnose bugs.

That's difficult to say: the bug was reported by syang and we don't know what tool, if any, syang was using.