Re: Alias of VALUES RTE in explain plan

On Mon, Jul 1, 2024 at 3:17 PM Ashutosh Bapat <ashutosh(dot)bapat(dot)oss(at)gmail(dot)com>
wrote:

> Hi All,
> While reviewing Richard's patch for grouping sets, I stumbled upon
> following explain output
>
> explain (costs off)
> select distinct on (a, b) a, b
> from (values (1, 1), (2, 2)) as t (a, b) where a = b
> group by grouping sets((a, b), (a))
> order by a, b;
> QUERY PLAN
> ----------------------------------------------------------------
> Unique
> -> Sort
> Sort Key: "*VALUES*".column1, "*VALUES*".column2
> -> HashAggregate
> Hash Key: "*VALUES*".column1, "*VALUES*".column2
> Hash Key: "*VALUES*".column1
> -> Values Scan on "*VALUES*"
> Filter: (column1 = column2)
> (8 rows)
>
> There is no VALUES.column1 and VALUES.column2 in the query. The alias t.a
> and t.b do not appear anywhere in the explain output. I think explain
> output should look like
> explain (costs off)
> select distinct on (a, b) a, b
> from (values (1, 1), (2, 2)) as t (a, b) where a = b
> group by grouping sets((a, b), (a))
> order by a, b;
> QUERY PLAN
> ----------------------------------------------------------------
> Unique
> -> Sort
> Sort Key: t.a, t.b
> -> HashAggregate
> Hash Key: t.a, t.b
> Hash Key: t.a
> -> Values Scan on "*VALUES*" t
> Filter: (a = b)
> (8 rows)
>
> I didn't get time to figure out the reason behind this, nor the history.
> But I thought I would report it nonetheless.
>

I have looked into the issue and found that when subqueries are pulled up,
a modifiable copy of the subquery is created for modification in the
pull_up_simple_subquery() function. During this process,
flatten_join_alias_vars() is called to flatten any join alias variables in
the subquery's target list. However at this point, we lose subquery's alias.
If you/hackers agree with my findings, I can provide a working patch soon.

> --
> Best Wishes,
> Ashutosh Bapat
>