Re: Alias of VALUES RTE in explain plan

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> To be honest, that pushdown feels really uncomfortable to me. To me,
> the natural syntax for associating an alias with a VALUES clause would
> be something like "VALUES (...) myalias" or, if you also wanted column
> aliasing, "VALUES (...) myalias(a,b,c)". That would feel just like
> what we allow when aliasing a table or a function reference. But to
> not allow that syntax but then allow the outer alias to propagate
> inward in this one case seems quite strange to me.

I agree that the syntax is weird, but AFAICS this is what the SQL spec
requires. The only grammar path that permits VALUES within FROM is
(as of SQL:2021)

<from clause> ::=
FROM <table reference list>

<table reference list> ::=
<table reference> [ { <comma> <table reference> }... ]

<table reference> ::=
<table factor>

<table factor> ::=
<table primary> [ <sample clause> ]

<table primary> ::=
| <derived table> <correlation or recognition>

<derived table> ::= <table subquery>

<correlation or recognition> ::=
[ AS ] <correlation name> [ <parenthesized derived column list> ]

<table subquery> ::= <subquery>

<subquery> ::=
<left paren> <query expression> <right paren>

<query expression> ::=
[ <with clause> ] <query expression body>
[ <order by clause> ] [ <result offset clause> ] [ <fetch first clause> ]

<query expression body> ::=
<query term>

<query term> ::=
<query primary>

<query primary> ::=
<simple table>

<simple table> ::=
| <table value constructor>

<table value constructor> ::=
VALUES <row value expression list>

(For brevity, I've omitted irrelevant alternatives in each of these
steps. Note that simple table names and functions-in-FROM are
permitted by other alternatives of <table primary> -- but there is
no other path to reach VALUES.)

So parentheses are required by the <subquery> production, and an
alias (<correlation name>) can only be introduced outside the parens,
and yet ORDER BY and OFFSET/LIMIT can be inside the parens. I wonder
a little bit whether the ability to write ORDER BY after VALUES was
even intentional on the spec authors' part --- there was no such
possibility in SQL99. But that's what we've got to work with.

You could argue perhaps that we should extend the spec's syntax
somewhere in here, but I'm fairly hesitant to do so for fear of
painting ourselves into a corner vis-a-vis some future spec
extension. Also, doing so would end with pg_dump producing
non-spec-compliant rendering of views containing such syntax,
which we've generally tried hard to avoid.

regards, tom lane