Re: Alias of VALUES RTE in explain plan

On 11/3/24 00:09, Tom Lane wrote:
> Ashutosh Bapat <ashutosh(dot)bapat(dot)oss(at)gmail(dot)com> writes:
>> On Tue, Oct 29, 2024 at 10:49 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> regression=# SELECT x
> regression-# FROM ( VALUES (4), (2), (3), (1)
> regression(# ORDER BY t1_1.x
> regression(# LIMIT 2) t1(x);
> ERROR: missing FROM-clause entry for table "t1_1"
> LINE 3: ORDER BY t1_1.x
> ^
>
> Now maybe we could teach ruleutils that these table aliases don't have
> to be distinct. But that feels fragile, and it's work that we'd be
> expending only so that we can break any existing SQL code that's
> using this construct. That's enough to put me firmly on the side of
> "let's not change that behavior".
Thanks. I also see the issue now. Of course, it is doable to teach
set_rtable_names about 'VALUES inside a trivial subquery' statement, but
I agree that it seems overcomplicated and fragile.
>
> It seems sufficient to avoid alias pushdown when there's an ORDER BY
> inside the VALUES subquery. We disallow a locking clause, and
> while there can be LIMIT/OFFSET, those aren't allowed to reference the
> VALUES output anyway. I added some test cases to show that this is
> enough to make view-dumping behave sanely.
I spent some time trying to find another possible way to reference
values aliases except the ORDER-BY clause. And could invent only a
subquery inside a value:
SELECT * FROM (VALUES (1 IN (SELECT t1.x FROM generate_series(1,t1.x))))
AS t1(x);

But it can't refer to t1.x because, at the moment of parsing, this alias
still doesn't exist. So, the code looks good enough to let it find other
corner cases in action.

--
regards, Andrei Lepikhov