From: | Andrei Lepikhov <lepihov(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Ashutosh Bapat <ashutosh(dot)bapat(dot)oss(at)gmail(dot)com>, Yasir <yasir(dot)hussain(dot)shah(at)gmail(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Alias of VALUES RTE in explain plan |
Date: | 2024-10-29 09:24:18 |
Message-ID: | 8ceca773-b463-4e93-8bce-2e524f94bc84@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On 10/28/24 22:05, Tom Lane wrote:
> Andrei Lepikhov <lepihov(at)gmail(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Peter Eisentraut | 2024-10-29 09:34:09 | Re: Make all Perl warnings fatal |
Previous Message | Peter Eisentraut | 2024-10-29 09:23:37 | Re: define pg_structiszero(addr, s, r) |