Wrong results due to missing quals

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

Responses

Browse pgsql-hackers by date

  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~?