Re: Alias of VALUES RTE in explain plan

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Thu, Jan 2, 2025 at 4:41 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> (Or we could decide to simplify
>> things at the cost of breaking such SQL code, since there probably
>> is none in the field. It's still not clear to me which choice is
>> better.)

> This part I don't understand.

Sorry, not enough context there. The two alternatives that I think
are on the table now are the v2 patch [1] and the v3 patch [2].
(v2 lacks some test cases that are in v3, but here I'm just talking
about the code differences.) The difference between them is that
given

SELECT ... FROM (VALUES (...)) v(a,b,c)

v2 always pushes the outer query's alias "v(a,b,c)" down to the
implicit subquery's VALUES RTE. But v3 does so only if the
implicit subquery contains no ORDER BY, that is a behavioral
difference appears for

SELECT ... FROM (VALUES (...) ORDER BY column1) v(a,b,c)

With v3, if you write the latter then EXPLAIN will still talk about
"*VALUES*" and denote its columns as column1 etc, which is
inconsistent with what happens without an ORDER BY. If we use v2 then
queries like this example will start to fail because they will use
the wrong names for the VALUES columns. Now, you could adapt such a
query easily enough:

SELECT ... FROM (VALUES (...) ORDER BY a) v(a,b,c)

If it'd been like that all along, nobody would blink at it I think,
even though you could argue that it's action-at-a-distance to let
an outer alias affect what happens inside the implicit subquery.

I'm fairly sure that the SQL spec says that such unspecified column
aliases are implementation-defined or maybe even
implementation-dependent, so that there's no issue of standards
compliance here: we can do what we please as far as the spec is
concerned.

So the question is: are there enough people using this sort of
query to justify our worrying about preserving compatibility
for it? It's a mighty weird construct, but AFAICS it's legal
per spec, even though the spec doesn't tell you how you can
name the VALUES' columns in that ORDER BY.

regards, tom lane

[1] https://www.postgresql.org/message-id/3002521.1729877700%40sss.pgh.pa.us
[2] https://www.postgresql.org/message-id/2192705.1730567369%40sss.pgh.pa.us