Re: Alias of VALUES RTE in explain plan

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Andrei Lepikhov <lepihov(at)gmail(dot)com>
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 17:19:22
Message-ID: 251197.1730222362@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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?

regards, tom lane

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Joe Conway 2024-10-29 17:34:08 Re: Replace current implementations in crypt() and gen_salt() to OpenSSL
Previous Message Paul Ramsey 2024-10-29 17:16:54 Re: RFC: Extension Packaging & Lookup