pgsql: Fix unsafe pushdown of quals referencing grouping Vars

Fix unsafe pushdown of quals referencing grouping Vars

When checking a subquery's output expressions to see if it's safe to
push down an upper-level qual, check_output_expressions() previously
treated grouping Vars as opaque Vars. This implicitly assumed they
were stable and scalar.

However, a grouping Var's underlying expression corresponds to the
grouping clause, which may be volatile or set-returning. If an
upper-level qual references such an output column, pushing it down
into the subquery is unsafe. This can cause strange results due to
multiple evaluation of a volatile function, or introduce SRFs into
the subquery's WHERE/HAVING quals.

This patch teaches check_output_expressions() to look through grouping
Vars to their underlying expressions. This ensures that any
volatility or set-returning properties in the grouping clause are
detected, preventing the unsafe pushdown.

We do not need to recursively examine the Vars contained in these
underlying expressions. Even if they reference outputs from
lower-level subqueries (at any depth), those references are guaranteed
not to expand to volatile or set-returning functions, because
subqueries containing such functions in their targetlists are never
pulled up.

Backpatch to v18, where this issue was introduced.

Reported-by: Eric Ridge <eebbrr(at)gmail(dot)com>
Diagnosed-by: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Author: Richard Guo <guofenglinux(at)gmail(dot)com>
Discussion: https://postgr.es/m/7900964C-F99E-481E-BEE5-4338774CEB9F@gmail.com
Backpatch-through: 18

Branch
------
REL_18_STABLE

Details
-------
https://git.postgresql.org/pg/commitdiff/7650eabb662f2f3708042c0b713c46aa042db94f

Modified Files
--------------
src/backend/optimizer/path/allpaths.c | 26 +++++++-
src/backend/optimizer/util/var.c | 8 ++-
src/test/regress/expected/subselect.out | 104 ++++++++++++++++++++++++++++++++
src/test/regress/sql/subselect.sql | 40 ++++++++++++
4 files changed, 175 insertions(+), 3 deletions(-)