On 10/28/24 22:05, Tom Lane wrote:
Andrei Lepikhov <lepi...@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
