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)