Bug report: equivalence transfer results in incorrect output

(1) By Wang Ke (krking) on 2021-05-04 09:01:04 [source]

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.

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 [link] [source]

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] [source]

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] [source]

Thank you for the confirmation!

(5) By Richard Hipp (drh) on 2021-05-04 23:22:37 in reply to 1 [link] [source]

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] [source]

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