SQLite Forum

Unexpected result in a equivalence transfer query
Login

Unexpected result in a equivalence transfer query

(1.1) By Wang Ke (krking) on 2021-08-30 16:22:02 edited from 1.0 [source]

Hello developers,

We found a testcase making SQLite outputs the wrong result as follows:

CREATE TABLE t1(a INT PRIMARY KEY);
INSERT INTO t1(a) VALUES(1),(2),(3);
CREATE TABLE t2(x INTEGER PRIMARY KEY,y INT);
INSERT INTO t2(y) VALUES(2),(3);
SELECT * FROM t2,t1 WHERE t2.y=t1.a AND t1.a=t2.x; -- expected: empty
SELECT * FROM t2,t1 WHERE likely(t2.y=t1.a) AND unlikely(t1.a=t2.x); -- unexpected: two lines

Presumably, this is a legacy issue related to the recent fix (check-in f1f9b5de) for the equivalence transfer problem, you probably forgot to consider the condition using PRIMARY KEY when dealing with equivalent transfer optimization.

Looking forward to your reply, further details will be appreciated!

(2) By Dan Kennedy (dan) on 2021-08-30 17:11:23 in reply to 1.1 [link] [source]

Thanks for this. It was as you suggest - the equivalence transfer problem was still lurking for "INTEGER PRIMARY KEY" indexes. Now fixed here:

https://sqlite.org/src/ci/46e28cbc

Cheers,

Dan.