From: | Richard Guo <guofenglinux(at)gmail(dot)com> |
---|---|
To: | PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Subject: | Wrong results due to missing quals |
Date: | 2023-05-24 11:19:16 |
Message-ID: | CAMbWs48EYi_9-pSd0ORes1kTmTeAjT4Q3gu49hJtYCbSn2JyeA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Testing with SQLancer reports a wrong results issue on master and I
reduced it to the repro query below.
create table t (a int, b int);
explain (costs off)
select * from t t1 left join
(t t2 left join t t3 full join t t4 on false on false)
left join t t5 on t2.a = t5.a
on t2.b = 1;
QUERY PLAN
--------------------------------------------------
Nested Loop Left Join
-> Seq Scan on t t1
-> Materialize
-> Nested Loop Left Join
-> Nested Loop Left Join
Join Filter: false
-> Seq Scan on t t2
Filter: (b = 1)
-> Result
One-Time Filter: false
-> Materialize
-> Seq Scan on t t5
(12 rows)
So the qual 't2.a = t5.a' is missing.
I looked into it and found that both clones of this joinqual are
rejected by clause_is_computable_at, because their required_relids do
not include the outer join of t2/(t3/t4), and meanwhile include nullable
rels of this outer join.
I think the root cause is that, as Tom pointed out in [1], we're not
maintaining required_relids very accurately. In b9c755a2, we make
clause_is_computable_at test required_relids for clone clauses. I think
this is how this issue sneaks in.
To fix it, it seems to me that the ideal way would be to always compute
accurate required_relids. But I'm not sure how difficult it is.
[1] https://www.postgresql.org/message-id/395264.1684698283%40sss.pgh.pa.us
Thanks
Richard
From | Date | Subject | |
---|---|---|---|
Next Message | Drouvot, Bertrand | 2023-05-24 11:58:54 | Re: pgsql: TAP test for logical decoding on standby |
Previous Message | Daniel Gustafsson | 2023-05-24 11:03:04 | Re: Cutting support for OpenSSL 1.0.1 and 1.0.2 in 17~? |