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
