Re: Alias of VALUES RTE in explain plan

Hi Ashutosh & PG Hackers,

I have fixed the code to produce desired output by adding a few lines in
pull_up_simple_subquery().
Attached patch is divided in 2 files:
- 001-Fix-Alias-VALUES-RTE.patch contains the actual fix.
- 002-Fix-Alias-VALUES-RTE.patch contains the expected output changes
against the actual fix.

I also have verified regression tests, all seems good.

Respected hackers please have a look.

Thanks and regards...

Yasir

On Thu, Aug 15, 2024 at 7:13 PM Yasir <yasir(dot)hussain(dot)shah(at)gmail(dot)com> wrote:

>
>
> 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
>>
>