Re: Alias of VALUES RTE in explain plan

On 10/28/24 22:05, Tom Lane wrote:
> Andrei Lepikhov <lepihov@gmail.com> writes:
>> My goal is to understand why the implementation follows this pattern. As
>> I see, previously, we had consistent behaviour, according to which we
>> removed the pulling-up subquery's alias as well. And I want to know, is
>> it really the only way to break this behavior? Maybe it is possible to
>> add the VALUES alias to the grammar. Or is it causing much worse code?
> So IMO, making use of an alias that's attached to a VALUES clause
> in this way is a natural thing to do from a user's viewpoint.
> You have a good point that we should be wary of using subquery
> aliases in other ways --- but the proposed patch is specific to
> this case.
Thanks for the detailed explanation. I agree it make sense.
Also, after skimming the code, I propose some extra tests:

-- Just to cover the ERROR:
EXPLAIN (COSTS OFF, VERBOSE)
SELECT * FROM (VALUES (1),(2),(3),(4)) AS t1(x,y);
ERROR:  VALUES lists "t1" have 1 columns available but 2 columns specified

-- New behavior
EXPLAIN (COSTS OFF, VERBOSE)
SELECT * FROM (VALUES (4),(2),(3),(1) ORDER BY t1.x LIMIT 2) AS t1(x);
SELECT * FROM (VALUES (4),(2),(3),(1) ORDER BY t1.x LIMIT 2) AS t1(x);

-- Not mentioned column is assigned with default name
EXPLAIN (COSTS OFF, VERBOSE)
SELECT * FROM (VALUES (4,1),(2,1),(3,1),(1,1) ORDER BY t1.column2,t1.x 
LIMIT 2 OFFSET 1) AS t1(x);
SELECT * FROM (VALUES (4,1),(2,1),(3,1),(1,1) ORDER BY t1.column2,t1.x 
LIMIT 2 OFFSET 1) AS t1(x);

-- Here it isn't allowed to sort with full reference 't2.x2', but in the 
EXPLAIN we see exactly 'Sort Key: t2.x2, t2.y':
EXPLAIN (COSTS OFF, VERBOSE)
SELECT * FROM (VALUES (3,3),(4,4)) AS t2(x2,y)
UNION ALL
SELECT * FROM (VALUES (1,1),(2,2)) AS t1(x1)
ORDER BY x2,y;

The code looks good.

-- 
regards, Andrei Lepikhov