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

On Thu, May 12, 2022 at 7:11 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

> We may have more to do here, though, because with this patch I get
>
> explain verbose
> SELECT
>     grouping(res.cnt)
> FROM Card
> CROSS JOIN LATERAL
> (SELECT
>     (SELECT Card.id) AS cnt
> ) AS res
> GROUP BY
>     res.cnt;
>
>  HashAggregate  (cost=67.38..71.88 rows=200 width=8)
>    Output: GROUPING((SubPlan 1)), ((SubPlan 2))
>    Group Key: (SubPlan 2)
>    ->  Seq Scan on public.card  (cost=0.00..61.00 rows=2550 width=8)
>          Output: (SubPlan 2), card.id
>          SubPlan 2
>            ->  Result  (cost=0.00..0.01 rows=1 width=4)
>                  Output: card.id
>
> What became of SubPlan 1?  Maybe this is fine but it looks a little
> shaky.  We should at least run down why that's happening and make
> sure we're not leaving dangling pointers anywhere.
>

I did some debug on this. The 'SubPlan 1' is not explained because its
SubPlanState is not created and added to AggState->subPlan.

When we compile each tlist column for the agg node, the GroupingFunc
expressions would not be collected to AggState->args, because only
Aggref nodes are expected there. As a result, the 'SubPlan 1', as the
arg of the GroupingFunc node, does not have a chance to be initialized
by ExecInitSubPlan() and added to AggState->subPlan when evaluating
arguments to aggregate function in ExecBuildAggTrans().

Not sure if this is fine or not.

Thanks
Richard