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(at)sss(dot)pgh(dot)pa(dot)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