Bug report: equivalence transfer results in incorrect output
(1) By Wang Ke (krking) on 2021-05-04 09:01:04 [link]
Hello, Consider the following example: ``` CREATE TABLE t0(c0 INT, c1 INT UNIQUE); CREATE TABLE t1(c0 INT); INSERT INTO t0(c0, c1) VALUES (0, 1); INSERT INTO t1(c0) VALUES (1); SELECT ALL * FROM t1 NATURAL JOIN t0 WHERE (t1.c0=t0.c1); SELECT ALL * FROM t1 NATURAL JOIN t0 WHERE (likely(t1.c0=t0.c1)); SELECT ALL * FROM t1,t0 WHERE (likely(t1.c0=t0.c1) AND t1.c0=t0.c0); ``` The resultset of the SELECT statements should be empty since there are no rows satisfing "t0.c0=t1.c0", but we got non-empty resultset from the second and the third statement. It looks like a bug caused by some kind of equivalence transfer optimization described [here](https://sqlite.org/forum/forumpost/1c0afcbc0d). I wonder whether it's an undiscovered bug. Looking forward to your reply! Thanks, Wang Ke
(2) By anonymous on 2021-05-04 17:08:08 in reply to 1
Here are some behaviors fyr: ``` CREATE TABLE t0(c0, c1 UNIQUE); CREATE TABLE t1(c0); INSERT INTO t0(c0, c1) VALUES (0, 1); INSERT INTO t1(c0) VALUES (1); SELECT * FROM t0, t1 WHERE t1.c0=t0.c1 INTERSECT SELECT * FROM t0, t1 WHERE t1.c0=t0.c0; -- empty SELECT * FROM t0, t1 WHERE LIKELY(t1.c0=t0.c1) INTERSECT SELECT * FROM t0, t1 WHERE t1.c0=t0.c0; -- empty SELECT * FROM t0, t1 WHERE t1.c0=t0.c1 AND t1.c0=t0.c0; -- empty SELECT * FROM t0, t1 WHERE LIKELY(t1.c0=t0.c1) AND t1.c0=t0.c0; -- 0|1|1 SELECT * FROM t1 NATURAL JOIN t0 WHERE t1.c0=t0.c1; -- empty SELECT * FROM t1 NATURAL JOIN t0 WHERE LIKELY(t1.c0=t0.c1); -- 1|1 ``` Presumably, the likely() function changes the estimated cost so that the query planner generates a different plan from the origin one. In other words, there may be incorrect query plans discarded because of cost rather than correctness. Overall, I think it's a bug that is less lilely to appear in the real world.
(3) By Richard Hipp (drh) on 2021-05-04 18:31:06 in reply to 1 [link]
Yes, this is a bug. It has been in the code for a long time. It is due to an adverse interaction between the likely() function and the equivalence transfer optimization, as you conjecture. We are working on a solution. You may have noticed recent check-ins that do not fix the problem, but do provide additional diagnostic output when using ".wheretrace" in the CLI. Those check-ins are related to our efforts to fix the problem.
(4) By Wang Ke (krking) on 2021-05-04 19:50:42 in reply to 3 [link]
Thank you for the confirmation!
(5) By Richard Hipp (drh) on 2021-05-04 23:22:37 in reply to 1 [link]
I think the problem is now fixed on trunk. Please try again and report if you find additional problems.
(6) By SeverKetor on 2021-05-05 01:10:15 in reply to 5 [link]
Just to let you know, the link to the post included with the changelog on the timeline is incorrect. It has "wiki?name=" in it which causes it to point to an empty page