MIME-Version: 1.0
References: <19435-3cc1a87f291129f1@postgresql.org> <CAHGQGwEEHFnH8DsZbwxdZeiHozm2LCRTLSgL8Qn=5MoN4450ZA@mail.gmail.com>
 <CAPpHfdsDQhDqvqi+-UxvyYRP72ASkdWzCn43e2Hj6WsY15opvA@mail.gmail.com>
 <CAHewXNmU3E0oXLgvoOtXw7kfTiAZsKu8La_hQ=pyScgxvP45iw@mail.gmail.com>
 <CAHewXN=LjuWz3PcyhjdbJAyo+Zs9MisPDRYnSZBUy4PMeKi+zA@mail.gmail.com>
 <CALdSSPj1kTTQvmV3H3HMf5P3um8ybxoH3DaTPm+XgdYAur1Q4A@mail.gmail.com>
 <CAHewXNndByMu3S+_h4LLDkXA5qrO1s=s-CE8HqUtc9vTA9yrjg@mail.gmail.com>
 <CAPpHfdv6gzSTXHJxYSgB8sULadXM4wvhgoQODaOxYCJfagKNPw@mail.gmail.com>
 <CAHewXN=7kDJjUcgEm+6qhaKOXuqzvhRqAAKdafNCRgn0yH7BGg@mail.gmail.com> <CAHewXNm5OOREJ8wZ1cLJdQz7O1aQ0E1RBB55S6O138K8vBdc9g@mail.gmail.com>
In-Reply-To: <CAHewXNm5OOREJ8wZ1cLJdQz7O1aQ0E1RBB55S6O138K8vBdc9g@mail.gmail.com>
From: Alexander Korotkov <aekorotkov@gmail.com>
Date: Wed, 18 Mar 2026 10:40:17 +0200
Message-ID: <CAPpHfducqLJ=o3LkoPKGfZJVQuuei+P=2oUF6hX6rzHTZSxoyA@mail.gmail.com>
Subject: Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by
 Complex Join with Self-Referencing Tables
To: Tender Wang <tndrwang@gmail.com>
Cc: Kirill Reshke <reshkekirill@gmail.com>, Fujii Masao <masao.fujii@gmail.com>, ammmkilo@163.com, 
	pgsql-bugs@lists.postgresql.org
Content-Type: multipart/mixed; boundary="000000000000809b27064d486732"
Archived-At: <https://www.postgresql.org/message-id/CAPpHfducqLJ%3Do3LkoPKGfZJVQuuei%2BP%3D2oUF6hX6rzHTZSxoyA%40mail.gmail.com>
Precedence: bulk

--000000000000809b27064d486732
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Hi, Tender!

On Wed, Mar 18, 2026 at 7:44=E2=80=AFAM Tender Wang <tndrwang@gmail.com> wr=
ote:
>
> Tender Wang <tndrwang@gmail.com> =E4=BA=8E2026=E5=B9=B43=E6=9C=8818=E6=97=
=A5=E5=91=A8=E4=B8=89 09:12=E5=86=99=E9=81=93=EF=BC=9A
> >
> > Alexander Korotkov <aekorotkov@gmail.com> =E4=BA=8E2026=E5=B9=B43=E6=9C=
=8818=E6=97=A5=E5=91=A8=E4=B8=89 01:46=E5=86=99=E9=81=93=EF=BC=9A
> > >
> > > On Tue, Mar 17, 2026 at 3:30=E2=80=AFPM Tender Wang <tndrwang@gmail.c=
om> wrote:
> > > > Kirill Reshke <reshkekirill@gmail.com> =E4=BA=8E2026=E5=B9=B43=E6=
=9C=8817=E6=97=A5=E5=91=A8=E4=BA=8C 21:24=E5=86=99=E9=81=93=EF=BC=9A
> > > > >
> > > > > On Tue, 17 Mar 2026 at 18:20, Tender Wang <tndrwang@gmail.com> wr=
ote:
> > > > > Yes, it looks like your analysis is valid. Will you share a patch=
 for
> > > > > updating `clause` ?
> > > > >
> > > > Sorry, it's already the middle of the night here, so I'm afraid I
> > > > don't have much time to work on this right now.
> > >
> > > Thank you for your research.  I've written a simple draft patch.  It
> > > fixes the reported case, but I doubt it is correct in general.  I'll
> > > continue the investigation.
> > I tried the above fix, no error again.  But I got a plan like this:
> >                                         QUERY PLAN
> > -----------------------------------------------------------------------=
--------------------
> >  Nested Loop Left Join  (cost=3D0.00..115164616.71 rows=3D7458350250 wi=
dth=3D4)
> >    ->  Seq Scan on pg_table_a tom3  (cost=3D0.00..34.70 rows=3D2470 wid=
th=3D0)
> >    ->  Materialize  (cost=3D0.00..99509.82 rows=3D3019575 width=3D0)
> >          ->  Nested Loop Left Join  (cost=3D0.00..75564.95 rows=3D30195=
75 width=3D0)
> >                Join Filter: tom2.col_bool
> >                ->  Seq Scan on pg_table_a tom2  (cost=3D0.00..34.70
> > rows=3D2445 width=3D5)
> >                      Filter: ((col_bool IS NOT NULL) AND (col_bool IS N=
OT NULL))
> >                ->  Materialize  (cost=3D0.00..47.05 rows=3D2470 width=
=3D0)
> >                      ->  Seq Scan on pg_table_a tom0
> > (cost=3D0.00..34.70 rows=3D2470 width=3D0)
> > (9 rows)
> >
> > Filter: ((col_bool IS NOT NULL) AND (col_bool IS NOT NULL))
> > This makes me unhappy.
> > Your patch gets the same plan.
> >
> In replace_relid_callback(), we add NullTest to rinfo, but it is not a
> logical equal check by restrict_infos_logically_equal().
> I think for baserestrictinfo, we can just use rinfo->clause, no need
> to check the equality of RestrictInfo.
>
> I tried this way, the plan looks as follows:
>                                         QUERY PLAN
> -------------------------------------------------------------------------=
------------------
>  Nested Loop Left Join  (cost=3D0.00..115776846.35 rows=3D7498006100 widt=
h=3D4)
>    ->  Seq Scan on pg_table_a tom3  (cost=3D0.00..34.70 rows=3D2470 width=
=3D0)
>    ->  Materialize  (cost=3D0.00..100038.47 rows=3D3035630 width=3D0)
>          ->  Nested Loop Left Join  (cost=3D0.00..75966.32 rows=3D3035630=
 width=3D0)
>                Join Filter: tom2.col_bool
>                ->  Seq Scan on pg_table_a tom2  (cost=3D0.00..34.70
> rows=3D2458 width=3D5)
>                      Filter: (col_bool IS NOT NULL)
>                ->  Materialize  (cost=3D0.00..47.05 rows=3D2470 width=3D0=
)
>                      ->  Seq Scan on pg_table_a tom0
> (cost=3D0.00..34.70 rows=3D2470 width=3D0)
> (9 rows)
>
> No redundant filter anymore.
>
> Please see the attached patch.

What about being more generic and call ChangeVarNodes_walker() for the
node in ChangeVarNodesWalkExpression()?  It also works with out case
and avoids code duplication.

Changes in restrict_infos_logically_equal() makes me a bit uneasy.  I
see, restictinfo's are different by their outer_relids.  Why
outer_relids doesn't matter when required_relids is singleton?  More
general when do outer_relids matter for add_non_redundant_clauses() if
we're putting restictinfo's into a single list anyway?

------
Regards,
Alexander Korotkov
Supabase

--000000000000809b27064d486732
Content-Type: application/octet-stream; name="0001-Fix-sje-bug-v2.patch"
Content-Disposition: attachment; filename="0001-Fix-sje-bug-v2.patch"
Content-Transfer-Encoding: base64
Content-ID: <f_mmvs0w6t0>
X-Attachment-Id: f_mmvs0w6t0

ZGlmZiAtLWdpdCBhL3NyYy9iYWNrZW5kL29wdGltaXplci9wbGFuL2FuYWx5emVqb2lucy5jIGIv
c3JjL2JhY2tlbmQvb3B0aW1pemVyL3BsYW4vYW5hbHl6ZWpvaW5zLmMKaW5kZXggMTJlOWVkMGQw
YzcuLjRiOWI1ZDU2M2Y2IDEwMDY0NAotLS0gYS9zcmMvYmFja2VuZC9vcHRpbWl6ZXIvcGxhbi9h
bmFseXplam9pbnMuYworKysgYi9zcmMvYmFja2VuZC9vcHRpbWl6ZXIvcGxhbi9hbmFseXplam9p
bnMuYwpAQCAtMTYzNSw5ICsxNjM1LDE0IEBAIHJlc3RyaWN0X2luZm9zX2xvZ2ljYWxseV9lcXVh
bChSZXN0cmljdEluZm8gKmEsIFJlc3RyaWN0SW5mbyAqYikKIAlpbnQJCQlzYXZlZF9yaW5mb19z
ZXJpYWwgPSBhLT5yaW5mb19zZXJpYWw7CiAJYm9vbAkJcmVzdWx0OwogCi0JYS0+cmluZm9fc2Vy
aWFsID0gYi0+cmluZm9fc2VyaWFsOwotCXJlc3VsdCA9IGVxdWFsKGEsIGIpOwotCWEtPnJpbmZv
X3NlcmlhbCA9IHNhdmVkX3JpbmZvX3NlcmlhbDsKKwlpZiAoYm1zX21lbWJlcnNoaXAoYS0+cmVx
dWlyZWRfcmVsaWRzKSA9PSBCTVNfU0lOR0xFVE9OKQorCQlyZXN1bHQgPSBlcXVhbChhLT5jbGF1
c2UsIGItPmNsYXVzZSk7CisJZWxzZQorCXsKKwkJYS0+cmluZm9fc2VyaWFsID0gYi0+cmluZm9f
c2VyaWFsOworCQlyZXN1bHQgPSBlcXVhbChhLCBiKTsKKwkJYS0+cmluZm9fc2VyaWFsID0gc2F2
ZWRfcmluZm9fc2VyaWFsOworCX0KIAogCXJldHVybiByZXN1bHQ7CiB9CmRpZmYgLS1naXQgYS9z
cmMvYmFja2VuZC9yZXdyaXRlL3Jld3JpdGVNYW5pcC5jIGIvc3JjL2JhY2tlbmQvcmV3cml0ZS9y
ZXdyaXRlTWFuaXAuYwppbmRleCBmZTg5NzU0YTczYy4uMmFkNjYzYzk1MmEgMTAwNjQ0Ci0tLSBh
L3NyYy9iYWNrZW5kL3Jld3JpdGUvcmV3cml0ZU1hbmlwLmMKKysrIGIvc3JjL2JhY2tlbmQvcmV3
cml0ZS9yZXdyaXRlTWFuaXAuYwpAQCAtNzQ0LDYgKzc0NCwxMiBAQCBDaGFuZ2VWYXJOb2RlcyhO
b2RlICpub2RlLCBpbnQgcnRfaW5kZXgsIGludCBuZXdfaW5kZXgsIGludCBzdWJsZXZlbHNfdXAp
CiBib29sCiBDaGFuZ2VWYXJOb2Rlc1dhbGtFeHByZXNzaW9uKE5vZGUgKm5vZGUsIENoYW5nZVZh
ck5vZGVzX2NvbnRleHQgKmNvbnRleHQpCiB7CisJaWYgKG5vZGUgPT0gTlVMTCkKKwkJcmV0dXJu
IGZhbHNlOworCisJaWYgKENoYW5nZVZhck5vZGVzX3dhbGtlcihub2RlLCBjb250ZXh0KSkKKwkJ
cmV0dXJuIHRydWU7CisKIAlyZXR1cm4gZXhwcmVzc2lvbl90cmVlX3dhbGtlcihub2RlLAogCQkJ
CQkJCQkgIENoYW5nZVZhck5vZGVzX3dhbGtlciwKIAkJCQkJCQkJICAodm9pZCAqKSBjb250ZXh0
KTsK
--000000000000809b27064d486732--