SQLite User Forum

Bug report: equivalence transfer results in incorrect output
Login

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

Status Moderators and the post's owner may change the status of this thread unless it is still. pending moderation. See /help/forum-statuses

(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