MIME-Version: 1.0
From: Richard Guo <guofenglinux@gmail.com>
Date: Wed, 24 May 2023 19:19:16 +0800
Message-ID: <CAMbWs48EYi_9-pSd0ORes1kTmTeAjT4Q3gu49hJtYCbSn2JyeA@mail.gmail.com>
Subject: Wrong results due to missing quals
To: PostgreSQL-development <pgsql-hackers@postgresql.org>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Content-Type: multipart/alternative; boundary="0000000000005087e305fc6ead53"
Archived-At: <https://www.postgresql.org/message-id/CAMbWs48EYi_9-pSd0ORes1kTmTeAjT4Q3gu49hJtYCbSn2JyeA%40mail.gmail.com>
Precedence: bulk

--0000000000005087e305fc6ead53
Content-Type: text/plain; charset="UTF-8"

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

--0000000000005087e305fc6ead53
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Testing with SQLancer reports a wrong results issue on mas=
ter and I<br>reduced it to the repro query below.<br><br>create table t (a =
int, b int);<br><br>explain (costs off)<br>select * from t t1 left join<br>=
=C2=A0 =C2=A0 (t t2 left join t t3 full join t t4 on false on false)<br>=C2=
=A0 =C2=A0 left join t t5 on t2.a =3D t5.a<br>on t2.b =3D 1;<br>=C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 QUERY PLAN<br>-=
-------------------------------------------------<br>=C2=A0Nested Loop Left=
 Join<br>=C2=A0 =C2=A0-&gt; =C2=A0Seq Scan on t t1<br>=C2=A0 =C2=A0-&gt; =
=C2=A0Materialize<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-&gt; =C2=A0Nested L=
oop Left Join<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-&g=
t; =C2=A0Nested Loop Left Join<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=
 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Join Filter: false<br>=C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-&gt; =C2=A0Seq =
Scan on t t2<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Filter: (b =3D 1)<br>=C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-&gt; =C2=A0R=
esult<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0One-Time Filter: false<br>=C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-&gt; =C2=A0Materialize<br>=C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-&gt; =C2=
=A0Seq Scan on t t5<br>(12 rows)<br><br>So the qual &#39;t2.a =3D t5.a&#39;=
 is missing.<br><br>I looked into it and found that both clones of this joi=
nqual are<br>rejected by clause_is_computable_at, because their required_re=
lids do<br>not include the outer join of t2/(t3/t4), and meanwhile include =
nullable<br>rels of this outer join.<br><br>I think the root cause is that,=
 as Tom pointed out in [1], we&#39;re not<br>maintaining required_relids ve=
ry accurately.=C2=A0 In b9c755a2, we make<br>clause_is_computable_at test r=
equired_relids for clone clauses.=C2=A0 I think<br>this is how this issue s=
neaks in.<br><br>To fix it, it seems to me that the ideal way would be to a=
lways compute<br>accurate required_relids.=C2=A0 But I&#39;m not sure how d=
ifficult it is.<br><br>[1] <a href=3D"https://www.postgresql.org/message-id=
/395264.1684698283%40sss.pgh.pa.us">https://www.postgresql.org/message-id/3=
95264.1684698283%40sss.pgh.pa.us</a><br><br>Thanks<br>Richard<br></div>

--0000000000005087e305fc6ead53--