Inconsistant output when likely/unlikely and join keyword is applied

(1) By Yu Liang (LY1598773890) on 2021-04-15 17:08:26 [source]

For query:

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)

However, the actual output is:

(EMPTY)

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

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

It appears that another query form also triggers this bug.

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.