Incorrect results caused by nested JOIN table
(1) By Zuming Jiang (zuming_jiang) on 2023-11-09 12:33:28 [source]
Set up the database
create table t0 (c1 int);
insert into t0 values (0);
Test Case 1
select 1
from
(t0 as ref_0
right outer join (t0 as ref_3
left outer join t0 as ref_4
on false)
on false)
where ((case when true then ref_0.c1 else ref_0.c1 end)
<= (case when false then ref_0.c1 else ref_0.c1 end))
group by ref_0.c1;
--- return 0 row: {}
I simplify (case when true then ref_0.c1 else ref_0.c1 end) to ref_0.c1. The results should not change. The test case becomes:
Test Case 2
select 1
from
(t0 as ref_0
right outer join (t0 as ref_3
left outer join t0 as ref_4
on false)
on false)
where (ref_0.c1
<= (case when false then ref_0.c1 else ref_0.c1 end))
group by ref_0.c1;
--- return 1 row: {1}
Test Case 1 returns 0 row, while Test Case 2 returns 1 row. They are not consistent.
SQLite version
SQLite 3.45.0 2023-11-09 01:54:26 1c98d46d60ef1494bd8b7561c7d0cd5aafc178201a6f1f0da25dea6140b91cd0 gcc-9.4.0 (64-bit)