Re: BUG #17479: "plan should not reference subplan's variable" when calling `grouping` on result of subquery - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      17479
Logged by:          Michael J. Sullivan
Email address:      sully@msully.net
PostgreSQL version: 14.2
Operating system:   Linux
Description:       

The following query produces "plan should not reference subplan's
variable"

create table Card (id uuid);

SELECT
    -- This line causes "variable not found in subplan target list"
    -- grouping(res.cnt)
    -- This line causes "plan should not reference subplan's variable"
   (SELECT grouping(res.cnt))
FROM Card
CROSS JOIN LATERAL
(SELECT
    (SELECT Card.id) AS cnt
) AS res
GROUP BY
    res.cnt

As the comment says, a slight change instead errors with "variable not found
in subplan target list".

# explain (verbose, costs off)
SELECT grouping(res.cnt) FROM Card CROSS JOIN LATERAL (SELECT (SELECT Card.id) AS cnt) AS res GROUP BY res.cnt;
ERROR:  variable not found in subplan target list

For this query, initially it has two TargetEntrys and both referencing
the RangeTblEntry of the subquery.

   {TARGETENTRY
   :expr
      {GROUPINGFUNC
      :args (
         {VAR
         :varno 2
         :varattno 1


AND

   {TARGETENTRY
   :expr
      {VAR
      :varno 2
      :varattno 1

More specifically, they are both referencing the first TargetEntry from
the subquery. And the first TargetEntry of the subquery is of type
EXPR SubLink. So after we pull up this subquery, the two TargetEntrys
become:

   {TARGETENTRY
   :expr
      {GROUPINGFUNC
      :args (
         {SUBLINK
         :subLinkType 4
         :subLinkId 0

AND

   {TARGETENTRY
   :expr
      {SUBLINK
      :subLinkType 4
      :subLinkId 0


Actually the two SubLink expressions are totally the same. But we did
not check that and proceeded to expand them to two SubPlans.

  {TARGETENTRY
   :expr
      {GROUPINGFUNC
      :args (
         {SUBPLAN
         :subLinkType 4
         :testexpr <>
         :paramIds <>
         :plan_id 1
         :plan_name SubPlan\ 1

AND

   {TARGETENTRY
   :expr
      {SUBPLAN
      :subLinkType 4
      :testexpr <>
      :paramIds <>
      :plan_id 2
      :plan_name SubPlan\ 2

The two SubPlans are assigned with different plan_ids/plan_names.
That's why when we fix up the GROUPINGFUNC target entry we failed to
match the whole SubPlan expression, i.e. we failed to match 'SubPlan 1'
against 'SubPlan 2'.

Thanks
Richard