Inconsistant output when likely/unlikely and join keyword is applied
(1) By Yu Liang (LY1598773890) on 2021-04-15 17:08:26 [link]
For query: ```SQL: CREATE TABLE v0 ( v1 PRIMARY KEY , v2 , v3 ) ; INSERT INTO v0 VALUES ( '111' , '222' , '333' ) ; CREATE TABLE v4 ( v5 PRIMARY KEY ) ; INSERT INTO v4 VALUES ( '0' ) ; SELECT * FROM v4 JOIN v0 ON v0.v3 = v0.v1 AND v0.v3 = '111'; SELECT * FROM v4 JOIN v0 ON likely( v0.v3 = v0.v1 ) AND v0.v3 = '111'; SELECT * FROM v4 JOIN v0 ON unlikely( v0.v3 = v0.v1 ) AND v0.v3 = '111'; ``` The expected result should be: (EMPTY) (EMPTY) (EMPTY) However, the actual output is: (EMPTY) 0\|111\|222\|333 0\|111\|222\|333 This inconsistent behavior is reproducible with the latest development version: **FossilOrigin-Name: b5dc7aba036cfd6d09c68dd17608328063634ca99ff341f97bab2dc2a1f59b11**; and one of the latest released version: **SQLite version 3.31.1** This inconsistent behavior seems to be introduced in: **FossilOrigin-Name: 82c67efb723dba387964f690cd459b420e59e3367d9589016597a76531596391**
(2) By Yu Liang (LY1598773890) on 2021-04-17 21:02:19 in reply to 1 [link]
It appears that the bug has been fixed by: https://sqlite.org/src/info/2363a14ca723c034. Appreciate with the fix.
(3.1) By Yu Liang (LY1598773890) on 2021-05-29 18:25:16 edited from 3.0 in reply to 2
It appears that another query form also triggers this bug. ```SQL CREATE TABLE v0 ( v1, v2 PRIMARY KEY); CREATE INDEX v3 ON v0 ( v2, v2 ); INSERT INTO v0 ( v1, v2 ) VALUES ( 10, 'x' ); SELECT COUNT ( * ) FROM v0 AS a13, v0 AS a14, v0 AS a15, v0 AS a16, v0 AS a17 WHERE a13.v1 = a13.v2 AND a13.v1 = 'x'; /* 1 */ SELECT COUNT ( * ) FROM v0 WHERE v0.v1 = v0.v2 AND v0.v1 = 'x'; /* 0 */ ``` Since v0.v1 is not equals to v0.v2, both SELECT statements are expected to output 0 or empty results. Fix <https://sqlite.org/src/info/2363a14ca723c034> also fix the inconsistent outputs from this query.