SQLite User Forum

Bug: Incorrect results caused by nested JOIN and BETWEEN
Login

Bug: Incorrect results caused by nested JOIN and BETWEEN

(1) By Zuming Jiang (zuming_jiang) on 2023-11-10 19:38:25 [source]

Set up the database

create table t1 (c6 TEXT, primary key(c6)); create table t2 (vkey INTEGER, c12 TEXT); insert into t1 values (''); insert into t2 values (88, '');

Test Case 1

select 1 from ((t2 as ref_0 left outer join t2 as ref_1 on true) right outer join t1 as ref_2 on (ref_1.c12 = ref_2.c6)) where case when true then (ref_1.vkey between null and ref_0.vkey) else (ref_1.vkey between null and ref_0.vkey) end; --- return 0 row: {}

I simplify case when true then (ref_1.vkey between null and ref_0.vkey) else (ref_1.vkey between null and ref_0.vkey) end to ref_1.vkey between null and ref_0.vkey. The results should not change. The test case becomes:

Test Case 2

select 1 from ((t2 as ref_0 left outer join t2 as ref_1 on true) right outer join t1 as ref_2 on (ref_1.c12 = ref_2.c6)) where ref_1.vkey between null and ref_0.vkey; --- return 1 row: {1}

Test Case 1 returns 0 row, while Test Case 2 returns 1 row. They are not consistent.

SQLite version

3.45.0 2023-11-10 15:03:18 530d10e93a5f63b71aaa94a2b89102d012a2cda815997066beb0f585fe823536 (64-bit)

(2) By Richard Hipp (drh) on 2023-11-10 21:11:34 in reply to 1 [link] [source]

Thanks for the bug report. Fixed here: f1eae192315335d7.