MIME-Version: 1.0
References: <7900964C-F99E-481E-BEE5-4338774CEB9F@gmail.com>
 <C7FCAD3A-9859-4CCB-95DE-62ECF1368331@gmail.com> <1607957.1767725532@sss.pgh.pa.us>
 <CAMbWs49jxSN3AQ0xc9=Cyx8OgLbzu9su6QK68zKgZCsQxTk68w@mail.gmail.com>
 <CAMbWs49jgcUS1yowhmDrsY-yDurniYSQWL_RpD8gPjKsah4fpw@mail.gmail.com>
 <1654153.1767753474@sss.pgh.pa.us> <CAMbWs48nfVij2BuU=3cJhRLVpvEc6krW5erG-hTcTRk673i7TQ@mail.gmail.com>
 <1752324.1767805242@sss.pgh.pa.us>
In-Reply-To: <1752324.1767805242@sss.pgh.pa.us>
From: Richard Guo <guofenglinux@gmail.com>
Date: Thu, 8 Jan 2026 12:23:36 +0900
Message-ID: <CAMbWs49i=H-q_QZ6J+zkFQyR-EWuZvZGq8Oa40EykaB=0O0DrQ@mail.gmail.com>
Subject: Re: Fwd: pg18 bug? SELECT query doesn't work
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Eric Ridge <eebbrr@gmail.com>, Pg Hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CAMbWs49i%3DH-q_QZ6J%2BzkFQyR-EWuZvZGq8Oa40EykaB%3D0O0DrQ%40mail.gmail.com>
Precedence: bulk

On Thu, Jan 8, 2026 at 2:00=E2=80=AFAM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> The main problem, as you say, is that allpaths.c is coming to
> conclusions about the contents of output expressions of the
> subquery without having done any of this.  The only really
> simple answer I can see is to make a copy of the subquery's
> tlist and apply these transformations to it before we run
> the subquery_is_pushdown_safe logic.  That's ... ugly.

It seems that allpaths.c checks the subquery's output only for two
specific cases: to determine if it contains SRFs or volatile
functions.  Because of this, it seems that we don't need to apply the
full set of transformations to it.  We only need to account for join
alias Vars and grouping Vars, as these can hide underlying
expressions.

The underlying expression of a join alias Var can only be a Var
(potentially coerced) from one of the join's input rels, or a COALESCE
expression containing the two input Vars.  Therefore, it should not be
able to contain SRFs or volatile functions, and thus we do not need to
expand it beforehand.  (This also answers my previous question about
whether the current bug exists for join alias Vars.)

Therefore, it seems to me that we only need to expand the grouping
Vars beforehand when checking the subquery's output, as in the changes
I proposed earlier.  It's still ugly, but less so I think.

> Perhaps another idea could be to shove the responsibility for this
> down into subquery_planner (or make it call a callback at the right
> point), and handle transferring of parent restriction clauses into
> HAVING only after we've finished preprocessing the subquery's tlist.
> That's an uncomfortably big change to be making in a released branch,
> but it might still be a better way than duplicating preprocessing.

Agreed.  I think this is the theoretically correct way to handle the
push-down of a subquery's restriction clauses.  However, it seems like
a non-trivial project, and it seems to require changing the signature
of subquery_planner(), as we'd need to pass the subquery's RTE and
RelOptInfo into it.  So this looks too risky for stable branches.  But
maybe we can do that in dev branch.

- Richard