Re: Alias of VALUES RTE in explain plan

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?

The problem is standards compliance. Per SQL, to put VALUES into FROM
with an alias you have to write

select * from (values (1,1), (2,2)) as t(a,b);

You can't omit the "extra" parentheses, and you can't put the AS
inside the parentheses.

Under the hood, those extra parentheses are making the VALUES into
a sub-select --- but I seriously doubt that any ordinary users
understand the construct that way. It's just a weird requirement to
put parentheses there. So IMO, when a user writes something like
this, they think they *are* putting an alias on the VALUES clause
itself.

As to your point that subquery aliases aren't generally used by
EXPLAIN, that's true, but consider this variant of your example:

regression=# EXPLAIN VERBOSE SELECT x,y FROM (
SELECT oidx,rname FROM pg_class p(oidx, rname) WHERE rname = 'pg_index') AS
c(x,y) WHERE c.y = 'pg_index';
QUERY PLAN
---------------------------------------------------------------------------------------------------------
Index Scan using pg_class_relname_nsp_index on pg_catalog.pg_class p (cost=0.28..8.29 rows=1 width=68)
Output: p.oidx, p.rname
Index Cond: (p.rname = 'pg_index'::name)
(3 rows)

So aliases attached directly to a relation *are* used by EXPLAIN,
table and column aliases both.

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.

regards, tom lane