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]
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: