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 <[email protected]> Diagnosed-by: Tom Lane <[email protected]> Author: Richard Guo <[email protected]> Discussion: https://postgr.es/m/[email protected] 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(-)
