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>
In-Reply-To: <CAPpHfdv6gzSTXHJxYSgB8sULadXM4wvhgoQODaOxYCJfagKNPw@mail.gmail.com>
From: Tender Wang <tndrwang@gmail.com>
Date: Wed, 18 Mar 2026 09:12:57 +0800
Message-ID: <CAHewXN=7kDJjUcgEm+6qhaKOXuqzvhRqAAKdafNCRgn0yH7BGg@mail.gmail.com>
Subject: Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by
 Complex Join with Self-Referencing Tables
To: Alexander Korotkov <aekorotkov@gmail.com>
Cc: Kirill Reshke <reshkekirill@gmail.com>, Fujii Masao <masao.fujii@gmail.com>, ammmkilo@163.com, 
	pgsql-bugs@lists.postgresql.org
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CAHewXN%3D7kDJjUcgEm%2B6qhaKOXuqzvhRqAAKdafNCRgn0yH7BGg%40mail.gmail.com>
Precedence: bulk

Alexander Korotkov <aekorotkov@gmail.com> =E4=BA=8E2026=E5=B9=B43=E6=9C=881=
8=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.com> =
wrote:
> > Kirill Reshke <reshkekirill@gmail.com> =E4=BA=8E2026=E5=B9=B43=E6=9C=88=
17=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> wrote:
> > > 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.
The cause of this bug is doing the following statement:
...
ChangeVarNodesWalkExpression((Node *) rinfo->clause, context);
...
in replace_relid_callback().

The rinfo->clause is only Var(tom1.col_bool), sorry I typoed it to "
ON tom1.col_bool IS NOT NULL", in my first reply email.
See expression_tree_walker_impl(), it does nothing if the node is just
a Var node.
So I think whether we can add logic in ChangeVarNodesWalkExpression() as be=
low:

...
if (node && IsA(node, Var))
    {
        Var        *var =3D (Var *) node;

        if (var->varlevelsup =3D=3D context->sublevels_up)
        {
            if (var->varno =3D=3D context->rt_index)
                var->varno =3D context->new_index;
            var->varnullingrels =3D adjust_relid_set(var->varnullingrels,
                                                   context->rt_index,
                                                   context->new_index);
            if (var->varnosyn =3D=3D context->rt_index)
                var->varnosyn =3D context->new_index;
        }
        return false;
    }
    else
        return expression_tree_walker(node,
                                      ChangeVarNodes_walker,
                                      (void *) context);
...

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 width=
=3D4)
   ->  Seq Scan on pg_table_a tom3  (cost=3D0.00..34.70 rows=3D2470 width=
=3D0)
   ->  Materialize  (cost=3D0.00..99509.82 rows=3D3019575 width=3D0)
         ->  Nested Loop Left Join  (cost=3D0.00..75564.95 rows=3D3019575 w=
idth=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 NOT N=
ULL))
               ->  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.


--=20
Thanks,
Tender Wang