Incorrect results caused by view and join
(1) By Zuming Jiang (zuming_jiang) on 2023-03-01 19:10:05 [source]
Set up the database
CREATE TABLE t0 (
wkey INTEGER
);
INSERT INTO t0 VALUES(1);
CREATE TABLE t1 (
pkey INTEGER PRIMARY KEY,
c1 INTEGER
);
INSERT INTO t1 VALUES(2,3);
CREATE VIEW v0 as select 1;
Test case
select *
from
t0 as ref_0
where exists (
select
4 as c0
from
(t1 as ref_1
left outer join (
select
ref_0.wkey as c1
from
v0 as ref_4
) as subq_0
on (ref_1.c1 = subq_0.c1 ))
where not (
(not (subq_0.c1 <> (ref_0.wkey + ref_1.pkey)))
or false
)); --- {1}
It returns results {1}.
Then I delete or false in the second to last line. The results should not change because anything with or false is equal to itself. The test case becomes:
select *
from
t0 as ref_0
where exists (
select
4 as c0
from
(t1 as ref_1
left outer join (
select
ref_0.wkey as c1
from
v0 as ref_4
) as subq_0
on (ref_1.c1 = subq_0.c1 ))
where not (
(not (subq_0.c1 <> (ref_0.wkey + ref_1.pkey)))
)); --- {}
It returns empty {} and is not consistent with the previous one.
(2) By Richard Hipp (drh) on 2023-03-01 20:28:47 in reply to 1 [link] [source]
Fixed by check-in 198b3e33dcfd74c7.
(3) By Zuming Jiang (zuming_jiang) on 2023-03-02 07:29:45 in reply to 2 [link] [source]
Thanks for fixing!