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.