Re: Alias of VALUES RTE in explain plan

On Tue, Oct 29, 2024 at 10:49 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> Andrei Lepikhov <lepihov(at)gmail(dot)com> writes:
> > -- 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);
>
> After taking a closer look at that, yeah it's new behavior, and
> I'm not sure we want to change it. (The existing behavior is that
> you'd have to write 'column1' or '"*VALUES*".column1' in the
> subquery's ORDER BY.)
>
> This example also violates my argument that the user thinks they
> are attaching the alias directly to VALUES.
>
> So what I now think
> is that we ought to tweak the patch so that the parent alias is
> pushed down only when the subquery contains just VALUES, no other
> clauses. Per a look at the grammar, ORDER BY, LIMIT, and FOR
> UPDATE could conceivably appear alongside VALUES; although
> FOR UPDATE would draw "FOR UPDATE cannot be applied to VALUES",
> so maybe we needn't worry about it.
>
> Thoughts?

If the user writes it in this manner, I think they intend to attach
the alias to VALUES() since there's no other way to do it. What is
weird is that they can use the alias before it's declared. For the
sake of eliminating this weirdness, your proposed tweak sounds fine to
me.

Even if we don't add that tweak, it's not easy for users to find out
that they can write the query this way. But it's better to plug the
hole before somebody starts exploiting it.

--
Best Wishes,
Ashutosh Bapat