Subject: pgsql: Fix incorrect handling of subquery pullup

From: Richard Guo <[email-masked]>
Date: Thu, 13 Mar 2025 07:36:21 +0000

Fix incorrect handling of subquery pullup

When pulling up a subquery, if the subquery's target list items are
used in grouping set columns, we need to wrap them in PlaceHolderVars.
This ensures that expressions retain their separate identity so that
they will match grouping set columns when appropriate.

In 90947674f, we decided to wrap subquery outputs that are non-var
expressions in PlaceHolderVars.  This prevents const-simplification
from merging them into the surrounding expressions after subquery
pullup, which could otherwise lead to failing to match those
subexpressions to grouping set columns, with the effect that they'd
not go to null when expected.

However, that left some loose ends.  If the subquery's target list
contains two or more identical Var expressions, we can still fail to
match the Var expression to the expected grouping set expression.
This is not related to const-simplification, but rather to how we
match expressions to lower target items in setrefs.c.

For sort/group expressions, we use ressortgroupref matching, which
works well.  For other expressions, we primarily rely on comparing the
expressions to determine if they are the same.  Therefore, we need a
way to prevent setrefs.c from matching the expression to some other
identical ones.

To fix, wrap all subquery outputs in PlaceHolderVars if the parent
query uses grouping sets, ensuring that they preserve their separate
identity throughout the whole planning process.

Reported-by: Dean Rasheed <[email-masked]>
Author: Richard Guo <[email-masked]>
Reviewed-by: Dean Rasheed <[email-masked]>
Discussion: https://postgr.es/m/[email-masked]

Branch
------
master

Details
-------
https://git.postgresql.org/pg/commitdiff/cc5d98525d43c22b98f360ef0f2c8d7dc57f04dc

Modified Files
--------------
src/backend/optimizer/prep/prepjointree.c  | 93 ++++++++++++++++--------------
src/backend/rewrite/rewriteManip.c         |  2 +-
src/test/regress/expected/groupingsets.out | 29 ++++++++++
src/test/regress/sql/groupingsets.sql      | 11 ++++
src/tools/pgindent/typedefs.list           |  1 +
5 files changed, 93 insertions(+), 43 deletions(-)