Home > mailing lists

Thread: Alias of VALUES RTE in explain plan

Alias of VALUES RTE in explain plan

From

Ashutosh Bapat

Date:

01 July 2024, 13:17:03

Hi All,

While reviewing Richard's patch for grouping sets, I stumbled upon following explain output

explain (costs off)
select distinct on (a, b) a, b
from (values (1, 1), (2, 2)) as t (a, b) where a = b
group by grouping sets((a, b), (a))
order by a, b;
QUERY PLAN
----------------------------------------------------------------
Unique
-> Sort
Sort Key: "*VALUES*".column1, "*VALUES*".column2
-> HashAggregate
Hash Key: "*VALUES*".column1, "*VALUES*".column2
Hash Key: "*VALUES*".column1
-> Values Scan on "*VALUES*"
Filter: (column1 = column2)
(8 rows)

There is no VALUES.column1 and VALUES.column2 in the query. The alias t.a and t.b do not appear anywhere in the explain output. I think explain output should look like

explain (costs off)
select distinct on (a, b) a, b
from (values (1, 1), (2, 2)) as t (a, b) where a = b
group by grouping sets((a, b), (a))
order by a, b;
QUERY PLAN
----------------------------------------------------------------
Unique
-> Sort
Sort Key: t.a, t.b
-> HashAggregate
Hash Key: t.a, t.b
Hash Key: t.a
-> Values Scan on "*VALUES*" t
Filter: (a = b)
(8 rows)

I didn't get time to figure out the reason behind this, nor the history. But I thought I would report it nonetheless.

--

Best Wishes,

Ashutosh Bapat

Re: Alias of VALUES RTE in explain plan

From

Yasir

Date:

15 August 2024, 20:13:29

On Mon, Jul 1, 2024 at 3:17 PM Ashutosh Bapat <ashutosh.bapat.oss@gmail.com> wrote:

Hi All,
While reviewing Richard's patch for grouping sets, I stumbled upon following explain output

explain (costs off)
select distinct on (a, b) a, b
from (values (1, 1), (2, 2)) as t (a, b) where a = b
group by grouping sets((a, b), (a))
order by a, b;
QUERY PLAN
----------------------------------------------------------------
Unique
-> Sort
Sort Key: "*VALUES*".column1, "*VALUES*".column2
-> HashAggregate
Hash Key: "*VALUES*".column1, "*VALUES*".column2
Hash Key: "*VALUES*".column1
-> Values Scan on "*VALUES*"
Filter: (column1 = column2)
(8 rows)

There is no VALUES.column1 and VALUES.column2 in the query. The alias t.a and t.b do not appear anywhere in the explain output. I think explain output should look like
explain (costs off)
select distinct on (a, b) a, b
from (values (1, 1), (2, 2)) as t (a, b) where a = b
group by grouping sets((a, b), (a))
order by a, b;
QUERY PLAN
----------------------------------------------------------------
Unique
-> Sort
Sort Key: t.a, t.b
-> HashAggregate
Hash Key: t.a, t.b
Hash Key: t.a
-> Values Scan on "*VALUES*" t
Filter: (a = b)
(8 rows)

I didn't get time to figure out the reason behind this, nor the history. But I thought I would report it nonetheless.

I have looked into the issue and found that when subqueries are pulled up, a modifiable copy of the subquery is created for modification in the pull_up_simple_subquery() function. During this process, flatten_join_alias_vars() is called to flatten any join alias variables in the subquery's target list. However at this point, we lose subquery's alias.

If you/hackers agree with my findings, I can provide a working patch soon.

--
Best Wishes,
Ashutosh Bapat

Re: Alias of VALUES RTE in explain plan

From

Tom Lane

Date:

25 October 2024, 23:35:00

Yasir <yasir.hussain.shah@gmail.com> writes:
> I have fixed the code to produce desired output by adding a few lines in
> pull_up_simple_subquery().
> Attached patch is divided in 2 files:
>  - 001-Fix-Alias-VALUES-RTE.patch contains the actual fix.
>  - 002-Fix-Alias-VALUES-RTE.patch contains the expected output changes
> against the actual fix.

I was initially skeptical about this, because we've been printing
"*VALUES*" for a decade or more and there have been few complaints.
So I wondered if the change would annoy more people than liked it.
However, after looking at the output for awhile, it is nice that the
columns of the VALUES are referenced with their user-given names
instead of "columnN".  I think that's enough of an improvement that
it'll win people over.

However ... I don't like this implementation, not even a little
bit.  Table/column aliases are assigned by the parser, and the
planner has no business overriding them.  Quite aside from being
a violation of system structural principles, there are practical
reasons not to do it like that:

1. We'd see different results when considering plan trees than
unplanned query trees.

2. At the place where you put this, some planning transformations have
already been done, and that affects the results.  That means that
future extensions or restructuring of the planner might change the
results, which seems undesirable.

I think the right way to make this happen is for the parser to
do it, which it can do by passing down the outer query level's
Alias to addRangeTableEntryForValues.  There's a few layers of
subroutine calls between, but we can minimize the pain by adding
a ParseState field to carry the Alias.  See attached.

My point 2 is illustrated by the fact that my patch produces
different results in a few cases than yours does --- look at
groupingsets.out in particular.  I think that's fine, and
the changes that yours makes and mine doesn't look a little
unprincipled.  For example, in the tests involving the "gstest1"
view, if somebody wants nice labels on that view's VALUES columns
then the right place to apply those labels is within the view.
Letting a subsequent call of the view inject labels seems pretty
action-at-a-distance-y.

            regards, tom lane

diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index f2bcd6aa98..73dd1d80c8 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -8992,16 +8992,16 @@ insert into utrtest values (2, 'qux');
 -- with a non-direct modification plan
 explain (verbose, costs off)
 update utrtest set a = 1 from (values (1), (2)) s(x) where a = s.x returning *;
-                                           QUERY PLAN
-------------------------------------------------------------------------------------------------
+                                        QUERY PLAN
+-------------------------------------------------------------------------------------------
  Update on public.utrtest
-   Output: utrtest_1.a, utrtest_1.b, "*VALUES*".column1
+   Output: utrtest_1.a, utrtest_1.b, s.x
    Foreign Update on public.remp utrtest_1
      Remote SQL: UPDATE public.loct SET a = $2 WHERE ctid = $1 RETURNING a, b
    Update on public.locp utrtest_2
    ->  Hash Join
-         Output: 1, "*VALUES*".*, "*VALUES*".column1, utrtest.tableoid, utrtest.ctid, utrtest.*
-         Hash Cond: (utrtest.a = "*VALUES*".column1)
+         Output: 1, s.*, s.x, utrtest.tableoid, utrtest.ctid, utrtest.*
+         Hash Cond: (utrtest.a = s.x)
          ->  Append
                ->  Foreign Scan on public.remp utrtest_1
                      Output: utrtest_1.a, utrtest_1.tableoid, utrtest_1.ctid, utrtest_1.*
@@ -9009,9 +9009,9 @@ update utrtest set a = 1 from (values (1), (2)) s(x) where a = s.x returning *;
                ->  Seq Scan on public.locp utrtest_2
                      Output: utrtest_2.a, utrtest_2.tableoid, utrtest_2.ctid, NULL::record
          ->  Hash
-               Output: "*VALUES*".*, "*VALUES*".column1
-               ->  Values Scan on "*VALUES*"
-                     Output: "*VALUES*".*, "*VALUES*".column1
+               Output: s.*, s.x
+               ->  Values Scan on s
+                     Output: s.*, s.x
 (18 rows)

 update utrtest set a = 1 from (values (1), (2)) s(x) where a = s.x returning *;
@@ -9049,16 +9049,16 @@ ERROR:  cannot route tuples into foreign table to be updated "remp"
 -- with a non-direct modification plan
 explain (verbose, costs off)
 update utrtest set a = 3 from (values (2), (3)) s(x) where a = s.x returning *;
-                                             QUERY PLAN
------------------------------------------------------------------------------------------------------
+                                        QUERY PLAN
+-------------------------------------------------------------------------------------------
  Update on public.utrtest
-   Output: utrtest_1.a, utrtest_1.b, "*VALUES*".column1
+   Output: utrtest_1.a, utrtest_1.b, s.x
    Update on public.locp utrtest_1
    Foreign Update on public.remp utrtest_2
      Remote SQL: UPDATE public.loct SET a = $2 WHERE ctid = $1 RETURNING a, b
    ->  Hash Join
-         Output: 3, "*VALUES*".*, "*VALUES*".column1, utrtest.tableoid, utrtest.ctid, (NULL::record)
-         Hash Cond: (utrtest.a = "*VALUES*".column1)
+         Output: 3, s.*, s.x, utrtest.tableoid, utrtest.ctid, (NULL::record)
+         Hash Cond: (utrtest.a = s.x)
          ->  Append
                ->  Seq Scan on public.locp utrtest_1
                      Output: utrtest_1.a, utrtest_1.tableoid, utrtest_1.ctid, NULL::record
@@ -9066,9 +9066,9 @@ update utrtest set a = 3 from (values (2), (3)) s(x) where a = s.x returning *;
                      Output: utrtest_2.a, utrtest_2.tableoid, utrtest_2.ctid, utrtest_2.*
                      Remote SQL: SELECT a, b, ctid FROM public.loct FOR UPDATE
          ->  Hash
-               Output: "*VALUES*".*, "*VALUES*".column1
-               ->  Values Scan on "*VALUES*"
-                     Output: "*VALUES*".*, "*VALUES*".column1
+               Output: s.*, s.x
+               ->  Values Scan on s
+                     Output: s.*, s.x
 (18 rows)

 update utrtest set a = 3 from (values (2), (3)) s(x) where a = s.x returning *; -- ERROR
diff --git a/contrib/tsm_system_rows/expected/tsm_system_rows.out
b/contrib/tsm_system_rows/expected/tsm_system_rows.out
index 87b4a8fc64..cd472d2605 100644
--- a/contrib/tsm_system_rows/expected/tsm_system_rows.out
+++ b/contrib/tsm_system_rows/expected/tsm_system_rows.out
@@ -49,13 +49,13 @@ SELECT * FROM
   (VALUES (0),(10),(100)) v(nrows),
   LATERAL (SELECT count(*) FROM test_tablesample
            TABLESAMPLE system_rows (nrows)) ss;
-                        QUERY PLAN
-----------------------------------------------------------
+                  QUERY PLAN
+-----------------------------------------------
  Nested Loop
-   ->  Values Scan on "*VALUES*"
+   ->  Values Scan on v
    ->  Aggregate
          ->  Sample Scan on test_tablesample
-               Sampling: system_rows ("*VALUES*".column1)
+               Sampling: system_rows (v.nrows)
 (5 rows)

 SELECT * FROM
diff --git a/contrib/tsm_system_time/expected/tsm_system_time.out
b/contrib/tsm_system_time/expected/tsm_system_time.out
index ac44f30be9..6c5aac3709 100644
--- a/contrib/tsm_system_time/expected/tsm_system_time.out
+++ b/contrib/tsm_system_time/expected/tsm_system_time.out
@@ -47,7 +47,7 @@ SELECT * FROM
          ->  Materialize
                ->  Sample Scan on test_tablesample
                      Sampling: system_time ('100000'::double precision)
-   ->  Values Scan on "*VALUES*"
+   ->  Values Scan on v
 (6 rows)

 SELECT * FROM
@@ -65,14 +65,14 @@ SELECT * FROM
   (VALUES (0),(100000)) v(time),
   LATERAL (SELECT COUNT(*) FROM test_tablesample
            TABLESAMPLE system_time (time)) ss;
-                           QUERY PLAN
-----------------------------------------------------------------
+                      QUERY PLAN
+------------------------------------------------------
  Nested Loop
-   ->  Values Scan on "*VALUES*"
+   ->  Values Scan on v
    ->  Aggregate
          ->  Materialize
                ->  Sample Scan on test_tablesample
-                     Sampling: system_time ("*VALUES*".column1)
+                     Sampling: system_time (v."time")
 (6 rows)

 SELECT * FROM
diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c
index 506e063161..8133f61485 100644
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.c
@@ -221,6 +221,7 @@ parse_analyze_withcb(RawStmt *parseTree, const char *sourceText,
 Query *
 parse_sub_analyze(Node *parseTree, ParseState *parentParseState,
                   CommonTableExpr *parentCTE,
+                  Alias *parentAlias,
                   bool locked_from_parent,
                   bool resolve_unknowns)
 {
@@ -228,6 +229,7 @@ parse_sub_analyze(Node *parseTree, ParseState *parentParseState,
     Query       *query;

     pstate->p_parent_cte = parentCTE;
+    pstate->p_parent_alias = parentAlias;
     pstate->p_locked_from_parent = locked_from_parent;
     pstate->p_resolve_unknowns = resolve_unknowns;

@@ -1725,11 +1727,13 @@ transformValuesClause(ParseState *pstate, SelectStmt *stmt)
         lateral = true;

     /*
-     * Generate the VALUES RTE
+     * Generate the VALUES RTE.  If we're in a RangeSubselect of an outer
+     * query level, and that had an Alias, use that rather than *VALUES*.
      */
     nsitem = addRangeTableEntryForValues(pstate, exprsLists,
                                          coltypes, coltypmods, colcollations,
-                                         NULL, lateral, true);
+                                         copyObject(pstate->p_parent_alias),
+                                         lateral, true);
     addNSItemToQuery(pstate, nsitem, true, true, true);

     /*
@@ -2167,7 +2171,7 @@ transformSetOperationTree(ParseState *pstate, SelectStmt *stmt,
          * namespace list.
          */
         selectQuery = parse_sub_analyze((Node *) stmt, pstate,
-                                        NULL, false, false);
+                                        NULL, NULL, false, false);

         /*
          * Check for bogus references to Vars on the current query level (but
diff --git a/src/backend/parser/parse_clause.c b/src/backend/parser/parse_clause.c
index 4c97690908..26ebb0aa1e 100644
--- a/src/backend/parser/parse_clause.c
+++ b/src/backend/parser/parse_clause.c
@@ -430,6 +430,7 @@ transformRangeSubselect(ParseState *pstate, RangeSubselect *r)
      * might still be required (if there is an all-tables locking clause).
      */
     query = parse_sub_analyze(r->subquery, pstate, NULL,
+                              r->alias,
                               isLockedRefname(pstate,
                                               r->alias == NULL ? NULL :
                                               r->alias->aliasname),
diff --git a/src/backend/parser/parse_cte.c b/src/backend/parser/parse_cte.c
index de9ae9b483..9070f834b5 100644
--- a/src/backend/parser/parse_cte.c
+++ b/src/backend/parser/parse_cte.c
@@ -312,7 +312,7 @@ analyzeCTE(ParseState *pstate, CommonTableExpr *cte)
     }

     /* Now we can get on with analyzing the CTE's query */
-    query = parse_sub_analyze(cte->ctequery, pstate, cte, false, true);
+    query = parse_sub_analyze(cte->ctequery, pstate, cte, NULL, false, true);
     cte->ctequery = (Node *) query;

     /*
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index ef0b560f5e..1888f363df 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -1880,7 +1880,8 @@ transformSubLink(ParseState *pstate, SubLink *sublink)
     /*
      * OK, let's transform the sub-SELECT.
      */
-    qtree = parse_sub_analyze(sublink->subselect, pstate, NULL, false, true);
+    qtree = parse_sub_analyze(sublink->subselect, pstate, NULL, NULL,
+                              false, true);

     /*
      * Check that we got a SELECT.  Anything else should be impossible given
diff --git a/src/include/parser/analyze.h b/src/include/parser/analyze.h
index 28b66fccb4..f63c6ed25f 100644
--- a/src/include/parser/analyze.h
+++ b/src/include/parser/analyze.h
@@ -36,6 +36,7 @@ extern Query *parse_analyze_withcb(RawStmt *parseTree, const char *sourceText,

 extern Query *parse_sub_analyze(Node *parseTree, ParseState *parentParseState,
                                 CommonTableExpr *parentCTE,
+                                Alias *parentAlias,
                                 bool locked_from_parent,
                                 bool resolve_unknowns);

diff --git a/src/include/parser/parse_node.h b/src/include/parser/parse_node.h
index 2375e95c10..a051fbef9c 100644
--- a/src/include/parser/parse_node.h
+++ b/src/include/parser/parse_node.h
@@ -161,6 +161,9 @@ typedef Node *(*CoerceParamHook) (ParseState *pstate, Param *param,
  * p_parent_cte: CommonTableExpr that immediately contains the current query,
  * if any.
  *
+ * p_parent_alias: Alias attached to the current sub-SELECT in the parent
+ * query level, if any.
+ *
  * p_target_relation: target relation, if query is INSERT/UPDATE/DELETE/MERGE
  *
  * p_target_nsitem: target relation's ParseNamespaceItem.
@@ -222,6 +225,7 @@ struct ParseState
     List       *p_ctenamespace; /* current namespace for common table exprs */
     List       *p_future_ctes;    /* common table exprs not yet in namespace */
     CommonTableExpr *p_parent_cte;    /* this query's containing CTE */
+    Alias       *p_parent_alias; /* parent's alias for this query */
     Relation    p_target_relation;    /* INSERT/UPDATE/DELETE/MERGE target rel */
     ParseNamespaceItem *p_target_nsitem;    /* target rel's NSItem, or NULL */
     ParseNamespaceItem *p_grouping_nsitem;    /* NSItem for grouping, or NULL */
diff --git a/src/test/regress/expected/create_view.out b/src/test/regress/expected/create_view.out
index f551624afb..e1daa0b793 100644
--- a/src/test/regress/expected/create_view.out
+++ b/src/test/regress/expected/create_view.out
@@ -2189,34 +2189,34 @@ select pg_get_viewdef('tt25v', true);
 -- also check cases seen only in EXPLAIN
 explain (verbose, costs off)
 select * from tt24v;
-                                        QUERY PLAN
-------------------------------------------------------------------------------------------
+                                QUERY PLAN
+--------------------------------------------------------------------------
  Hash Join
-   Output: (cte.r).column2, ((ROW("*VALUES*".column1, "*VALUES*".column2))).column2
-   Hash Cond: ((cte.r).column1 = ((ROW("*VALUES*".column1, "*VALUES*".column2))).column1)
+   Output: (cte.r).column2, ((ROW(rr.column1, rr.column2))).column2
+   Hash Cond: ((cte.r).column1 = ((ROW(rr.column1, rr.column2))).column1)
    CTE cte
-     ->  Values Scan on "*VALUES*_1"
-           Output: ROW("*VALUES*_1".column1, "*VALUES*_1".column2)
+     ->  Values Scan on r
+           Output: ROW(r.column1, r.column2)
    ->  CTE Scan on cte
          Output: cte.r
    ->  Hash
-         Output: (ROW("*VALUES*".column1, "*VALUES*".column2))
+         Output: (ROW(rr.column1, rr.column2))
          ->  Limit
-               Output: (ROW("*VALUES*".column1, "*VALUES*".column2))
-               ->  Values Scan on "*VALUES*"
-                     Output: ROW("*VALUES*".column1, "*VALUES*".column2)
+               Output: (ROW(rr.column1, rr.column2))
+               ->  Values Scan on rr
+                     Output: ROW(rr.column1, rr.column2)
 (14 rows)

 explain (verbose, costs off)
 select (r).column2 from (select r from (values(1,2),(3,4)) r limit 1) ss;
-                            QUERY PLAN
--------------------------------------------------------------------
+                   QUERY PLAN
+-------------------------------------------------
  Subquery Scan on ss
    Output: (ss.r).column2
    ->  Limit
-         Output: (ROW("*VALUES*".column1, "*VALUES*".column2))
-         ->  Values Scan on "*VALUES*"
-               Output: ROW("*VALUES*".column1, "*VALUES*".column2)
+         Output: (ROW(r.column1, r.column2))
+         ->  Values Scan on r
+               Output: ROW(r.column1, r.column2)
 (6 rows)

 -- test pretty-print parenthesization rules, and SubLink deparsing
diff --git a/src/test/regress/expected/gist.out b/src/test/regress/expected/gist.out
index c75bbb23b6..af85945ea4 100644
--- a/src/test/regress/expected/gist.out
+++ b/src/test/regress/expected/gist.out
@@ -141,11 +141,11 @@ cross join lateral
                              QUERY PLAN
 --------------------------------------------------------------------
  Nested Loop
-   ->  Values Scan on "*VALUES*"
+   ->  Values Scan on v
    ->  Limit
          ->  Index Only Scan using gist_tbl_point_index on gist_tbl
-               Index Cond: (p <@ "*VALUES*".column1)
-               Order By: (p <-> ("*VALUES*".column1)[0])
+               Index Cond: (p <@ v.bb)
+               Order By: (p <-> (v.bb)[0])
 (6 rows)

 select p from
diff --git a/src/test/regress/expected/groupingsets.out b/src/test/regress/expected/groupingsets.out
index d7c9b44605..bed1174c13 100644
--- a/src/test/regress/expected/groupingsets.out
+++ b/src/test/regress/expected/groupingsets.out
@@ -846,17 +846,17 @@ select v.c, (select count(*) from gstest2 group by () having v.c)
 explain (costs off)
   select v.c, (select count(*) from gstest2 group by () having v.c)
     from (values (false),(true)) v(c) order by v.c;
-                        QUERY PLAN
------------------------------------------------------------
+                  QUERY PLAN
+-----------------------------------------------
  Sort
-   Sort Key: "*VALUES*".column1
-   ->  Values Scan on "*VALUES*"
+   Sort Key: v.c
+   ->  Values Scan on v
          SubPlan 1
            ->  Aggregate
                  Group Key: ()
-                 Filter: "*VALUES*".column1
+                 Filter: v.c
                  ->  Result
-                       One-Time Filter: "*VALUES*".column1
+                       One-Time Filter: v.c
                        ->  Seq Scan on gstest2
 (10 rows)

@@ -1349,15 +1349,15 @@ explain (costs off)
     from (values (1),(2)) v(x), gstest_data(v.x)
    group by grouping sets (a,b)
    order by 3, 1, 2;
-                             QUERY PLAN
----------------------------------------------------------------------
+                      QUERY PLAN
+------------------------------------------------------
  Sort
-   Sort Key: (sum("*VALUES*".column1)), gstest_data.a, gstest_data.b
+   Sort Key: (sum(v.x)), gstest_data.a, gstest_data.b
    ->  HashAggregate
          Hash Key: gstest_data.a
          Hash Key: gstest_data.b
          ->  Nested Loop
-               ->  Values Scan on "*VALUES*"
+               ->  Values Scan on v
                ->  Function Scan on gstest_data
 (8 rows)

@@ -1481,7 +1481,7 @@ explain (costs off)
          Hash Key: gstest_data.b
          Group Key: ()
          ->  Nested Loop
-               ->  Values Scan on "*VALUES*"
+               ->  Values Scan on v
                ->  Function Scan on gstest_data
 (10 rows)

@@ -2323,16 +2323,16 @@ select distinct on (a, b) a, b
 from (values (1, 1), (2, 2)) as t (a, b) where a = b
 group by grouping sets((a, b), (a))
 order by a, b;
-                           QUERY PLAN
-----------------------------------------------------------------
+             QUERY PLAN
+-------------------------------------
  Unique
    ->  Sort
-         Sort Key: "*VALUES*".column1, "*VALUES*".column2
+         Sort Key: t.a, t.b
          ->  HashAggregate
-               Hash Key: "*VALUES*".column1, "*VALUES*".column2
-               Hash Key: "*VALUES*".column1
-               ->  Values Scan on "*VALUES*"
-                     Filter: (column1 = column2)
+               Hash Key: t.a, t.b
+               Hash Key: t.a
+               ->  Values Scan on t
+                     Filter: (a = b)
 (8 rows)

 select distinct on (a, b) a, b
@@ -2352,16 +2352,16 @@ select distinct on (a, b+1) a, b+1
 from (values (1, 0), (2, 1)) as t (a, b) where a = b+1
 group by grouping sets((a, b+1), (a))
 order by a, b+1;
-                              QUERY PLAN
-----------------------------------------------------------------------
+                QUERY PLAN
+-------------------------------------------
  Unique
    ->  Sort
-         Sort Key: "*VALUES*".column1, (("*VALUES*".column2 + 1))
+         Sort Key: t.a, ((t.b + 1))
          ->  HashAggregate
-               Hash Key: "*VALUES*".column1, ("*VALUES*".column2 + 1)
-               Hash Key: "*VALUES*".column1
-               ->  Values Scan on "*VALUES*"
-                     Filter: (column1 = (column2 + 1))
+               Hash Key: t.a, (t.b + 1)
+               Hash Key: t.a
+               ->  Values Scan on t
+                     Filter: (a = (b + 1))
 (8 rows)

 select distinct on (a, b+1) a, b+1
@@ -2381,15 +2381,15 @@ select a, b
 from (values (1, 1), (2, 2)) as t (a, b) where a = b
 group by grouping sets((a, b), (a))
 order by a, b nulls first;
-                           QUERY PLAN
-----------------------------------------------------------------
+            QUERY PLAN
+----------------------------------
  Sort
-   Sort Key: "*VALUES*".column1, "*VALUES*".column2 NULLS FIRST
+   Sort Key: t.a, t.b NULLS FIRST
    ->  HashAggregate
-         Hash Key: "*VALUES*".column1, "*VALUES*".column2
-         Hash Key: "*VALUES*".column1
-         ->  Values Scan on "*VALUES*"
-               Filter: (column1 = column2)
+         Hash Key: t.a, t.b
+         Hash Key: t.a
+         ->  Values Scan on t
+               Filter: (a = b)
 (7 rows)

 select a, b
@@ -2427,16 +2427,16 @@ explain (costs off)
 select a, b, row_number() over (order by a, b nulls first)
 from (values (1, 1), (2, 2)) as t (a, b) where a = b
 group by grouping sets((a, b), (a));
-                              QUERY PLAN
-----------------------------------------------------------------------
+               QUERY PLAN
+----------------------------------------
  WindowAgg
    ->  Sort
-         Sort Key: "*VALUES*".column1, "*VALUES*".column2 NULLS FIRST
+         Sort Key: t.a, t.b NULLS FIRST
          ->  HashAggregate
-               Hash Key: "*VALUES*".column1, "*VALUES*".column2
-               Hash Key: "*VALUES*".column1
-               ->  Values Scan on "*VALUES*"
-                     Filter: (column1 = column2)
+               Hash Key: t.a, t.b
+               Hash Key: t.a
+               ->  Values Scan on t
+                     Filter: (a = b)
 (8 rows)

 select a, b, row_number() over (order by a, b nulls first)
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index 9b2973694f..3bd75074ba 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -4508,16 +4508,16 @@ select * from
 (values (1, array[10,20]), (2, array[20,30])) as v1(v1x,v1ys)
 left join (values (1, 10), (2, 20)) as v2(v2x,v2y) on v2x = v1x
 left join unnest(v1ys) as u1(u1y) on u1y = v2y;
-                         QUERY PLAN
--------------------------------------------------------------
+               QUERY PLAN
+----------------------------------------
  Nested Loop Left Join
-   ->  Values Scan on "*VALUES*"
+   ->  Values Scan on v1
    ->  Hash Right Join
-         Hash Cond: (u1.u1y = "*VALUES*_1".column2)
-         Filter: ("*VALUES*_1".column1 = "*VALUES*".column1)
+         Hash Cond: (u1.u1y = v2.v2y)
+         Filter: (v2.v2x = v1.v1x)
          ->  Function Scan on unnest u1
          ->  Hash
-               ->  Values Scan on "*VALUES*_1"
+               ->  Values Scan on v2
 (8 rows)

 select * from
@@ -4654,10 +4654,10 @@ using (join_key);
                                 QUERY PLAN
 --------------------------------------------------------------------------
  Nested Loop Left Join
-   Output: "*VALUES*".column1, i1.f1, (666)
-   Join Filter: ("*VALUES*".column1 = i1.f1)
-   ->  Values Scan on "*VALUES*"
-         Output: "*VALUES*".column1
+   Output: foo1.join_key, i1.f1, (666)
+   Join Filter: (foo1.join_key = i1.f1)
+   ->  Values Scan on foo1
+         Output: foo1.join_key
    ->  Materialize
          Output: i1.f1, (666)
          ->  Nested Loop Left Join
@@ -6541,12 +6541,12 @@ explain (costs off)
    ->  Nested Loop
          ->  Nested Loop
                ->  Index Only Scan using tenk1_unique1 on tenk1 a
-               ->  Values Scan on "*VALUES*"
+               ->  Values Scan on ss
          ->  Memoize
-               Cache Key: "*VALUES*".column1
+               Cache Key: ss.x
                Cache Mode: logical
                ->  Index Only Scan using tenk1_unique2 on tenk1 b
-                     Index Cond: (unique2 = "*VALUES*".column1)
+                     Index Cond: (unique2 = ss.x)
 (10 rows)

 select count(*) from tenk1 a,
@@ -7326,12 +7326,12 @@ select * from
   lateral (select f1 from int4_tbl
            where f1 = any (select unique1 from tenk1
                            where unique2 = v.x offset 0)) ss;
-                              QUERY PLAN
-----------------------------------------------------------------------
+                            QUERY PLAN
+------------------------------------------------------------------
  Nested Loop
-   Output: "*VALUES*".column1, "*VALUES*".column2, int4_tbl.f1
-   ->  Values Scan on "*VALUES*"
-         Output: "*VALUES*".column1, "*VALUES*".column2
+   Output: v.id, v.x, int4_tbl.f1
+   ->  Values Scan on v
+         Output: v.id, v.x
    ->  Nested Loop Semi Join
          Output: int4_tbl.f1
          Join Filter: (int4_tbl.f1 = tenk1.unique1)
@@ -7341,7 +7341,7 @@ select * from
                Output: tenk1.unique1
                ->  Index Scan using tenk1_unique2 on public.tenk1
                      Output: tenk1.unique1
-                     Index Cond: (tenk1.unique2 = "*VALUES*".column2)
+                     Index Cond: (tenk1.unique2 = v.x)
 (14 rows)

 select * from
@@ -7368,13 +7368,13 @@ lateral (select * from int8_tbl t1,
                                                          QUERY PLAN
     

----------------------------------------------------------------------------------------------------------------------------
  Nested Loop
-   Output: "*VALUES*".column1, t1.q1, t1.q2, ss2.q1, ss2.q2
+   Output: v.id, t1.q1, t1.q2, ss2.q1, ss2.q2
    ->  Seq Scan on public.int8_tbl t1
          Output: t1.q1, t1.q2
    ->  Nested Loop
-         Output: "*VALUES*".column1, ss2.q1, ss2.q2
-         ->  Values Scan on "*VALUES*"
-               Output: "*VALUES*".column1
+         Output: v.id, ss2.q1, ss2.q2
+         ->  Values Scan on v
+               Output: v.id
          ->  Subquery Scan on ss2
                Output: ss2.q1, ss2.q2
                Filter: (t1.q1 = ss2.q2)
@@ -7390,7 +7390,7 @@ lateral (select * from int8_tbl t1,
                                      Output: GREATEST(t1.q1, t2.q2)
                              InitPlan 2
                                ->  Result
-                                     Output: ("*VALUES*".column1 = 0)
+                                     Output: (v.id = 0)
                              ->  Seq Scan on public.int8_tbl t3
                                    Output: t3.q1, t3.q2
                                    Filter: (t3.q2 = (InitPlan 1).col1)
diff --git a/src/test/regress/expected/plpgsql.out b/src/test/regress/expected/plpgsql.out
index 0a6945581b..ac0bafe21f 100644
--- a/src/test/regress/expected/plpgsql.out
+++ b/src/test/regress/expected/plpgsql.out
@@ -5180,10 +5180,10 @@ select consumes_rw_array(a), a from returns_rw_array(1) a;
 explain (verbose, costs off)
 select consumes_rw_array(a), a from
   (values (returns_rw_array(1)), (returns_rw_array(2))) v(a);
-                             QUERY PLAN
----------------------------------------------------------------------
- Values Scan on "*VALUES*"
-   Output: consumes_rw_array("*VALUES*".column1), "*VALUES*".column1
+              QUERY PLAN
+---------------------------------------
+ Values Scan on v
+   Output: consumes_rw_array(v.a), v.a
 (2 rows)

 select consumes_rw_array(a), a from
diff --git a/src/test/regress/expected/rowtypes.out b/src/test/regress/expected/rowtypes.out
index 9168979a62..efe8bf1ef6 100644
--- a/src/test/regress/expected/rowtypes.out
+++ b/src/test/regress/expected/rowtypes.out
@@ -1193,10 +1193,10 @@ explain (verbose, costs off)
 select r, r is null as isnull, r is not null as isnotnull
 from (values (1,row(1,2)), (1,row(null,null)), (1,null),
              (null,row(1,2)), (null,row(null,null)), (null,null) ) r(a,b);
-                                                                                                         QUERY PLAN
                                                                                                      

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- Values Scan on "*VALUES*"
-   Output: ROW("*VALUES*".column1, "*VALUES*".column2), (("*VALUES*".column1 IS NULL) AND ("*VALUES*".column2 IS NOT
DISTINCTFROM NULL)), (("*VALUES*".column1 IS NOT NULL) AND ("*VALUES*".column2 IS DISTINCT FROM NULL)) 
+                                                            QUERY PLAN
            

+-----------------------------------------------------------------------------------------------------------------------------------
+ Values Scan on r
+   Output: ROW(r.a, r.b), ((r.a IS NULL) AND (r.b IS NOT DISTINCT FROM NULL)), ((r.a IS NOT NULL) AND (r.b IS DISTINCT
FROMNULL)) 
 (2 rows)

 select r, r is null as isnull, r is not null as isnotnull
diff --git a/src/test/regress/expected/select_parallel.out b/src/test/regress/expected/select_parallel.out
index d17ade278b..84c70efda3 100644
--- a/src/test/regress/expected/select_parallel.out
+++ b/src/test/regress/expected/select_parallel.out
@@ -432,7 +432,7 @@ select * from
                                 QUERY PLAN
 --------------------------------------------------------------------------
  Nested Loop Left Join
-   ->  Values Scan on "*VALUES*"
+   ->  Values Scan on v
    ->  Finalize Aggregate
          ->  Gather
                Workers Planned: 4
@@ -458,7 +458,7 @@ select * from
                                       QUERY PLAN
 --------------------------------------------------------------------------------------
  Nested Loop Left Join
-   ->  Values Scan on "*VALUES*"
+   ->  Values Scan on v
    ->  Finalize Aggregate
          ->  Gather
                Workers Planned: 4
@@ -609,7 +609,7 @@ select * from explain_parallel_sort_stats();
                        explain_parallel_sort_stats
 --------------------------------------------------------------------------
  Nested Loop Left Join (actual rows=30000 loops=1)
-   ->  Values Scan on "*VALUES*" (actual rows=3 loops=1)
+   ->  Values Scan on v (actual rows=3 loops=1)
    ->  Gather Merge (actual rows=10000 loops=3)
          Workers Planned: 4
          Workers Launched: 4
@@ -873,7 +873,7 @@ select * from
                         QUERY PLAN
 ----------------------------------------------------------
  Nested Loop Left Join
-   ->  Values Scan on "*VALUES*"
+   ->  Values Scan on v
    ->  Finalize GroupAggregate
          Group Key: tenk1.string4
          ->  Gather Merge
diff --git a/src/test/regress/expected/sqljson.out b/src/test/regress/expected/sqljson.out
index 70721c9a5a..e80beb3e6b 100644
--- a/src/test/regress/expected/sqljson.out
+++ b/src/test/regress/expected/sqljson.out
@@ -1066,15 +1066,15 @@ DROP VIEW json_arrayagg_view;
 -- Test JSON_ARRAY(subquery) deparsing
 EXPLAIN (VERBOSE, COSTS OFF)
 SELECT JSON_ARRAY(SELECT i FROM (VALUES (1), (2), (NULL), (4)) foo(i) RETURNING jsonb);
-                             QUERY PLAN
----------------------------------------------------------------------
+                       QUERY PLAN
+--------------------------------------------------------
  Result
    Output: (InitPlan 1).col1
    InitPlan 1
      ->  Aggregate
-           Output: JSON_ARRAYAGG("*VALUES*".column1 RETURNING jsonb)
-           ->  Values Scan on "*VALUES*"
-                 Output: "*VALUES*".column1
+           Output: JSON_ARRAYAGG(foo.i RETURNING jsonb)
+           ->  Values Scan on foo
+                 Output: foo.i
 (7 rows)

 CREATE VIEW json_array_subquery_view AS
diff --git a/src/test/regress/expected/subselect.out b/src/test/regress/expected/subselect.out
index 2d35de3fad..95cc0545ae 100644
--- a/src/test/regress/expected/subselect.out
+++ b/src/test/regress/expected/subselect.out
@@ -1124,7 +1124,7 @@ explain (verbose, costs off)
     (select (select now()) as x from (values(1),(2)) v(y)) ss;
                    QUERY PLAN
 ------------------------------------------------
- Values Scan on "*VALUES*"
+ Values Scan on v
    Output: (InitPlan 1).col1, (InitPlan 2).col1
    InitPlan 1
      ->  Result
@@ -1141,7 +1141,7 @@ explain (verbose, costs off)
 -----------------------------------
  Subquery Scan on ss
    Output: ss.x, ss.x
-   ->  Values Scan on "*VALUES*"
+   ->  Values Scan on v
          Output: (InitPlan 1).col1
          InitPlan 1
            ->  Result
@@ -1151,33 +1151,33 @@ explain (verbose, costs off)
 explain (verbose, costs off)
   select x, x from
     (select (select now() where y=y) as x from (values(1),(2)) v(y)) ss;
-                              QUERY PLAN
-----------------------------------------------------------------------
- Values Scan on "*VALUES*"
+               QUERY PLAN
+----------------------------------------
+ Values Scan on v
    Output: (SubPlan 1), (SubPlan 2)
    SubPlan 1
      ->  Result
            Output: now()
-           One-Time Filter: ("*VALUES*".column1 = "*VALUES*".column1)
+           One-Time Filter: (v.y = v.y)
    SubPlan 2
      ->  Result
            Output: now()
-           One-Time Filter: ("*VALUES*".column1 = "*VALUES*".column1)
+           One-Time Filter: (v.y = v.y)
 (10 rows)

 explain (verbose, costs off)
   select x, x from
     (select (select random() where y=y) as x from (values(1),(2)) v(y)) ss;
-                                 QUERY PLAN
-----------------------------------------------------------------------------
+                  QUERY PLAN
+----------------------------------------------
  Subquery Scan on ss
    Output: ss.x, ss.x
-   ->  Values Scan on "*VALUES*"
+   ->  Values Scan on v
          Output: (SubPlan 1)
          SubPlan 1
            ->  Result
                  Output: random()
-                 One-Time Filter: ("*VALUES*".column1 = "*VALUES*".column1)
+                 One-Time Filter: (v.y = v.y)
 (8 rows)

 --
@@ -1366,13 +1366,13 @@ select * from
   (3 not in (select * from (values (1), (2)) ss1)),
   (false)
 ) ss;
-               QUERY PLAN
-----------------------------------------
- Values Scan on "*VALUES*"
-   Output: "*VALUES*".column1
+          QUERY PLAN
+-------------------------------
+ Values Scan on ss
+   Output: ss.column1
    SubPlan 1
-     ->  Values Scan on "*VALUES*_1"
-           Output: "*VALUES*_1".column1
+     ->  Values Scan on ss1
+           Output: ss1.column1
 (5 rows)

 select * from
diff --git a/src/test/regress/expected/tablesample.out b/src/test/regress/expected/tablesample.out
index 9ff4611640..34b39a7153 100644
--- a/src/test/regress/expected/tablesample.out
+++ b/src/test/regress/expected/tablesample.out
@@ -251,14 +251,14 @@ select pct, count(unique1) from
   (values (0),(100)) v(pct),
   lateral (select * from tenk1 tablesample bernoulli (pct)) ss
   group by pct;
-                       QUERY PLAN
---------------------------------------------------------
+                QUERY PLAN
+-------------------------------------------
  HashAggregate
-   Group Key: "*VALUES*".column1
+   Group Key: v.pct
    ->  Nested Loop
-         ->  Values Scan on "*VALUES*"
+         ->  Values Scan on v
          ->  Sample Scan on tenk1
-               Sampling: bernoulli ("*VALUES*".column1)
+               Sampling: bernoulli (v.pct)
 (6 rows)

 select pct, count(unique1) from
diff --git a/src/test/regress/expected/union.out b/src/test/regress/expected/union.out
index c73631a9a1..19cb0cb7d5 100644
--- a/src/test/regress/expected/union.out
+++ b/src/test/regress/expected/union.out
@@ -481,27 +481,27 @@ reset enable_hashagg;
 set enable_hashagg to on;
 explain (costs off)
 select x from (values ('11'::varbit), ('10'::varbit)) _(x) union select x from (values ('11'::varbit), ('10'::varbit))
_(x);
-                  QUERY PLAN
------------------------------------------------
+              QUERY PLAN
+--------------------------------------
  Unique
    ->  Sort
-         Sort Key: "*VALUES*".column1
+         Sort Key: _.x
          ->  Append
-               ->  Values Scan on "*VALUES*"
-               ->  Values Scan on "*VALUES*_1"
+               ->  Values Scan on _
+               ->  Values Scan on __1
 (6 rows)

 set enable_hashagg to off;
 explain (costs off)
 select x from (values ('11'::varbit), ('10'::varbit)) _(x) union select x from (values ('11'::varbit), ('10'::varbit))
_(x);
-                  QUERY PLAN
------------------------------------------------
+              QUERY PLAN
+--------------------------------------
  Unique
    ->  Sort
-         Sort Key: "*VALUES*".column1
+         Sort Key: _.x
          ->  Append
-               ->  Values Scan on "*VALUES*"
-               ->  Values Scan on "*VALUES*_1"
+               ->  Values Scan on _
+               ->  Values Scan on __1
 (6 rows)

 reset enable_hashagg;
@@ -509,13 +509,13 @@ reset enable_hashagg;
 set enable_hashagg to on;
 explain (costs off)
 select x from (values (array[1, 2]), (array[1, 3])) _(x) union select x from (values (array[1, 2]), (array[1, 4]))
_(x);
-               QUERY PLAN
------------------------------------------
+           QUERY PLAN
+--------------------------------
  HashAggregate
-   Group Key: "*VALUES*".column1
+   Group Key: _.x
    ->  Append
-         ->  Values Scan on "*VALUES*"
-         ->  Values Scan on "*VALUES*_1"
+         ->  Values Scan on _
+         ->  Values Scan on __1
 (5 rows)

 select x from (values (array[1, 2]), (array[1, 3])) _(x) union select x from (values (array[1, 2]), (array[1, 4]))
_(x);
@@ -528,14 +528,14 @@ select x from (values (array[1, 2]), (array[1, 3])) _(x) union select x from (va

 explain (costs off)
 select x from (values (array[1, 2]), (array[1, 3])) _(x) intersect select x from (values (array[1, 2]), (array[1, 4]))
_(x);
-                  QUERY PLAN
------------------------------------------------
+                QUERY PLAN
+-------------------------------------------
  HashSetOp Intersect
    ->  Append
          ->  Subquery Scan on "*SELECT* 1"
-               ->  Values Scan on "*VALUES*"
+               ->  Values Scan on _
          ->  Subquery Scan on "*SELECT* 2"
-               ->  Values Scan on "*VALUES*_1"
+               ->  Values Scan on __1
 (6 rows)

 select x from (values (array[1, 2]), (array[1, 3])) _(x) intersect select x from (values (array[1, 2]), (array[1, 4]))
_(x);
@@ -546,14 +546,14 @@ select x from (values (array[1, 2]), (array[1, 3])) _(x) intersect select x from

 explain (costs off)
 select x from (values (array[1, 2]), (array[1, 3])) _(x) except select x from (values (array[1, 2]), (array[1, 4]))
_(x);
-                  QUERY PLAN
------------------------------------------------
+                QUERY PLAN
+-------------------------------------------
  HashSetOp Except
    ->  Append
          ->  Subquery Scan on "*SELECT* 1"
-               ->  Values Scan on "*VALUES*"
+               ->  Values Scan on _
          ->  Subquery Scan on "*SELECT* 2"
-               ->  Values Scan on "*VALUES*_1"
+               ->  Values Scan on __1
 (6 rows)

 select x from (values (array[1, 2]), (array[1, 3])) _(x) except select x from (values (array[1, 2]), (array[1, 4]))
_(x);
@@ -565,14 +565,14 @@ select x from (values (array[1, 2]), (array[1, 3])) _(x) except select x from (v
 -- non-hashable type
 explain (costs off)
 select x from (values (array['10'::varbit]), (array['11'::varbit])) _(x) union select x from (values
(array['10'::varbit]),(array['01'::varbit])) _(x); 
-                  QUERY PLAN
------------------------------------------------
+              QUERY PLAN
+--------------------------------------
  Unique
    ->  Sort
-         Sort Key: "*VALUES*".column1
+         Sort Key: _.x
          ->  Append
-               ->  Values Scan on "*VALUES*"
-               ->  Values Scan on "*VALUES*_1"
+               ->  Values Scan on _
+               ->  Values Scan on __1
 (6 rows)

 select x from (values (array['10'::varbit]), (array['11'::varbit])) _(x) union select x from (values
(array['10'::varbit]),(array['01'::varbit])) _(x); 
@@ -586,14 +586,14 @@ select x from (values (array['10'::varbit]), (array['11'::varbit])) _(x) union s
 set enable_hashagg to off;
 explain (costs off)
 select x from (values (array[1, 2]), (array[1, 3])) _(x) union select x from (values (array[1, 2]), (array[1, 4]))
_(x);
-                  QUERY PLAN
------------------------------------------------
+              QUERY PLAN
+--------------------------------------
  Unique
    ->  Sort
-         Sort Key: "*VALUES*".column1
+         Sort Key: _.x
          ->  Append
-               ->  Values Scan on "*VALUES*"
-               ->  Values Scan on "*VALUES*_1"
+               ->  Values Scan on _
+               ->  Values Scan on __1
 (6 rows)

 select x from (values (array[1, 2]), (array[1, 3])) _(x) union select x from (values (array[1, 2]), (array[1, 4]))
_(x);
@@ -606,16 +606,16 @@ select x from (values (array[1, 2]), (array[1, 3])) _(x) union select x from (va

 explain (costs off)
 select x from (values (array[1, 2]), (array[1, 3])) _(x) intersect select x from (values (array[1, 2]), (array[1, 4]))
_(x);
-                     QUERY PLAN
------------------------------------------------------
+                   QUERY PLAN
+-------------------------------------------------
  SetOp Intersect
    ->  Sort
          Sort Key: "*SELECT* 1".x
          ->  Append
                ->  Subquery Scan on "*SELECT* 1"
-                     ->  Values Scan on "*VALUES*"
+                     ->  Values Scan on _
                ->  Subquery Scan on "*SELECT* 2"
-                     ->  Values Scan on "*VALUES*_1"
+                     ->  Values Scan on __1
 (8 rows)

 select x from (values (array[1, 2]), (array[1, 3])) _(x) intersect select x from (values (array[1, 2]), (array[1, 4]))
_(x);
@@ -626,16 +626,16 @@ select x from (values (array[1, 2]), (array[1, 3])) _(x) intersect select x from

 explain (costs off)
 select x from (values (array[1, 2]), (array[1, 3])) _(x) except select x from (values (array[1, 2]), (array[1, 4]))
_(x);
-                     QUERY PLAN
------------------------------------------------------
+                   QUERY PLAN
+-------------------------------------------------
  SetOp Except
    ->  Sort
          Sort Key: "*SELECT* 1".x
          ->  Append
                ->  Subquery Scan on "*SELECT* 1"
-                     ->  Values Scan on "*VALUES*"
+                     ->  Values Scan on _
                ->  Subquery Scan on "*SELECT* 2"
-                     ->  Values Scan on "*VALUES*_1"
+                     ->  Values Scan on __1
 (8 rows)

 select x from (values (array[1, 2]), (array[1, 3])) _(x) except select x from (values (array[1, 2]), (array[1, 4]))
_(x);
@@ -649,14 +649,14 @@ reset enable_hashagg;
 set enable_hashagg to on;
 explain (costs off)
 select x from (values (row(1, 2)), (row(1, 3))) _(x) union select x from (values (row(1, 2)), (row(1, 4))) _(x);
-                  QUERY PLAN
------------------------------------------------
+              QUERY PLAN
+--------------------------------------
  Unique
    ->  Sort
-         Sort Key: "*VALUES*".column1
+         Sort Key: _.x
          ->  Append
-               ->  Values Scan on "*VALUES*"
-               ->  Values Scan on "*VALUES*_1"
+               ->  Values Scan on _
+               ->  Values Scan on __1
 (6 rows)

 select x from (values (row(1, 2)), (row(1, 3))) _(x) union select x from (values (row(1, 2)), (row(1, 4))) _(x);
@@ -669,16 +669,16 @@ select x from (values (row(1, 2)), (row(1, 3))) _(x) union select x from (values

 explain (costs off)
 select x from (values (row(1, 2)), (row(1, 3))) _(x) intersect select x from (values (row(1, 2)), (row(1, 4))) _(x);
-                     QUERY PLAN
------------------------------------------------------
+                   QUERY PLAN
+-------------------------------------------------
  SetOp Intersect
    ->  Sort
          Sort Key: "*SELECT* 1".x
          ->  Append
                ->  Subquery Scan on "*SELECT* 1"
-                     ->  Values Scan on "*VALUES*"
+                     ->  Values Scan on _
                ->  Subquery Scan on "*SELECT* 2"
-                     ->  Values Scan on "*VALUES*_1"
+                     ->  Values Scan on __1
 (8 rows)

 select x from (values (row(1, 2)), (row(1, 3))) _(x) intersect select x from (values (row(1, 2)), (row(1, 4))) _(x);
@@ -689,16 +689,16 @@ select x from (values (row(1, 2)), (row(1, 3))) _(x) intersect select x from (va

 explain (costs off)
 select x from (values (row(1, 2)), (row(1, 3))) _(x) except select x from (values (row(1, 2)), (row(1, 4))) _(x);
-                     QUERY PLAN
------------------------------------------------------
+                   QUERY PLAN
+-------------------------------------------------
  SetOp Except
    ->  Sort
          Sort Key: "*SELECT* 1".x
          ->  Append
                ->  Subquery Scan on "*SELECT* 1"
-                     ->  Values Scan on "*VALUES*"
+                     ->  Values Scan on _
                ->  Subquery Scan on "*SELECT* 2"
-                     ->  Values Scan on "*VALUES*_1"
+                     ->  Values Scan on __1
 (8 rows)

 select x from (values (row(1, 2)), (row(1, 3))) _(x) except select x from (values (row(1, 2)), (row(1, 4))) _(x);
@@ -712,14 +712,14 @@ select x from (values (row(1, 2)), (row(1, 3))) _(x) except select x from (value
 -- type is hashable.  (Otherwise, this would fail at execution time.)
 explain (costs off)
 select x from (values (row('10'::varbit)), (row('11'::varbit))) _(x) union select x from (values (row('10'::varbit)),
(row('01'::varbit)))_(x); 
-                  QUERY PLAN
------------------------------------------------
+              QUERY PLAN
+--------------------------------------
  Unique
    ->  Sort
-         Sort Key: "*VALUES*".column1
+         Sort Key: _.x
          ->  Append
-               ->  Values Scan on "*VALUES*"
-               ->  Values Scan on "*VALUES*_1"
+               ->  Values Scan on _
+               ->  Values Scan on __1
 (6 rows)

 select x from (values (row('10'::varbit)), (row('11'::varbit))) _(x) union select x from (values (row('10'::varbit)),
(row('01'::varbit)))_(x); 
@@ -735,14 +735,14 @@ select x from (values (row('10'::varbit)), (row('11'::varbit))) _(x) union selec
 create type ct1 as (f1 varbit);
 explain (costs off)
 select x from (values (row('10'::varbit)::ct1), (row('11'::varbit)::ct1)) _(x) union select x from (values
(row('10'::varbit)::ct1),(row('01'::varbit)::ct1)) _(x); 
-                  QUERY PLAN
------------------------------------------------
+              QUERY PLAN
+--------------------------------------
  Unique
    ->  Sort
-         Sort Key: "*VALUES*".column1
+         Sort Key: _.x
          ->  Append
-               ->  Values Scan on "*VALUES*"
-               ->  Values Scan on "*VALUES*_1"
+               ->  Values Scan on _
+               ->  Values Scan on __1
 (6 rows)

 select x from (values (row('10'::varbit)::ct1), (row('11'::varbit)::ct1)) _(x) union select x from (values
(row('10'::varbit)::ct1),(row('01'::varbit)::ct1)) _(x); 
@@ -757,14 +757,14 @@ drop type ct1;
 set enable_hashagg to off;
 explain (costs off)
 select x from (values (row(1, 2)), (row(1, 3))) _(x) union select x from (values (row(1, 2)), (row(1, 4))) _(x);
-                  QUERY PLAN
------------------------------------------------
+              QUERY PLAN
+--------------------------------------
  Unique
    ->  Sort
-         Sort Key: "*VALUES*".column1
+         Sort Key: _.x
          ->  Append
-               ->  Values Scan on "*VALUES*"
-               ->  Values Scan on "*VALUES*_1"
+               ->  Values Scan on _
+               ->  Values Scan on __1
 (6 rows)

 select x from (values (row(1, 2)), (row(1, 3))) _(x) union select x from (values (row(1, 2)), (row(1, 4))) _(x);
@@ -777,16 +777,16 @@ select x from (values (row(1, 2)), (row(1, 3))) _(x) union select x from (values

 explain (costs off)
 select x from (values (row(1, 2)), (row(1, 3))) _(x) intersect select x from (values (row(1, 2)), (row(1, 4))) _(x);
-                     QUERY PLAN
------------------------------------------------------
+                   QUERY PLAN
+-------------------------------------------------
  SetOp Intersect
    ->  Sort
          Sort Key: "*SELECT* 1".x
          ->  Append
                ->  Subquery Scan on "*SELECT* 1"
-                     ->  Values Scan on "*VALUES*"
+                     ->  Values Scan on _
                ->  Subquery Scan on "*SELECT* 2"
-                     ->  Values Scan on "*VALUES*_1"
+                     ->  Values Scan on __1
 (8 rows)

 select x from (values (row(1, 2)), (row(1, 3))) _(x) intersect select x from (values (row(1, 2)), (row(1, 4))) _(x);
@@ -797,16 +797,16 @@ select x from (values (row(1, 2)), (row(1, 3))) _(x) intersect select x from (va

 explain (costs off)
 select x from (values (row(1, 2)), (row(1, 3))) _(x) except select x from (values (row(1, 2)), (row(1, 4))) _(x);
-                     QUERY PLAN
------------------------------------------------------
+                   QUERY PLAN
+-------------------------------------------------
  SetOp Except
    ->  Sort
          Sort Key: "*SELECT* 1".x
          ->  Append
                ->  Subquery Scan on "*SELECT* 1"
-                     ->  Values Scan on "*VALUES*"
+                     ->  Values Scan on _
                ->  Subquery Scan on "*SELECT* 2"
-                     ->  Values Scan on "*VALUES*_1"
+                     ->  Values Scan on __1
 (8 rows)

 select x from (values (row(1, 2)), (row(1, 3))) _(x) except select x from (values (row(1, 2)), (row(1, 4))) _(x);
diff --git a/src/test/regress/expected/updatable_views.out b/src/test/regress/expected/updatable_views.out
index 8786058ed0..7ce7cddb5b 100644
--- a/src/test/regress/expected/updatable_views.out
+++ b/src/test/regress/expected/updatable_views.out
@@ -2988,15 +2988,15 @@ EXPLAIN (costs off)
 MERGE INTO rw_view1 t
   USING (VALUES ('Tom'), ('Dick'), ('Harry')) AS v(person) ON t.person = v.person
   WHEN MATCHED AND snoop(t.person) THEN UPDATE SET person = v.person;
-                         QUERY PLAN
--------------------------------------------------------------
+                     QUERY PLAN
+-----------------------------------------------------
  Merge on base_tbl
    ->  Nested Loop
-         Join Filter: (base_tbl.person = "*VALUES*".column1)
+         Join Filter: (base_tbl.person = v.person)
          ->  Seq Scan on base_tbl
                Filter: (visibility = 'public'::text)
          ->  Materialize
-               ->  Values Scan on "*VALUES*"
+               ->  Values Scan on v
 (7 rows)

 -- security barrier view on top of security barrier view
@@ -3090,10 +3090,10 @@ MERGE INTO rw_view2 t
 -------------------------------------------------------------------------
  Merge on base_tbl
    ->  Nested Loop
-         Join Filter: (base_tbl.person = "*VALUES*".column1)
+         Join Filter: (base_tbl.person = v.person)
          ->  Seq Scan on base_tbl
                Filter: ((visibility = 'public'::text) AND snoop(person))
-         ->  Values Scan on "*VALUES*"
+         ->  Values Scan on v
 (6 rows)

 DROP TABLE base_tbl CASCADE;

Re: Alias of VALUES RTE in explain plan

From

Tom Lane

Date:

26 October 2024, 01:21:54

I wrote:
> However ... I don't like this implementation, not even a little
> bit.

I forgot to mention a third problem, which is that reassigning the
alias during subquery pullup means it doesn't happen if subquery
pullup doesn't happen.  As an example, with your patch:

regression=# explain verbose select * from (values (1), (2)) v(x);
                     QUERY PLAN                     
----------------------------------------------------
 Values Scan on v  (cost=0.00..0.03 rows=2 width=4)
   Output: v.x
(2 rows)

regression=# explain verbose select * from (values (1), (random())) v(x);
                         QUERY PLAN                          
-------------------------------------------------------------
 Values Scan on "*VALUES*"  (cost=0.00..0.03 rows=2 width=8)
   Output: "*VALUES*".column1
(2 rows)

That's because the volatile function prevents subquery flattening.

            regards, tom lane

Re: Alias of VALUES RTE in explain plan

From

Yasir

Date:

28 October 2024, 02:03:14

On Sat, Oct 26, 2024 at 12:21 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

I wrote:
> However ... I don't like this implementation, not even a little
> bit.

I forgot to mention a third problem, which is that reassigning the
alias during subquery pullup means it doesn't happen if subquery
pullup doesn't happen. As an example, with your patch:

regression=# explain verbose select * from (values (1), (2)) v(x);
QUERY PLAN
----------------------------------------------------
Values Scan on v (cost=0.00..0.03 rows=2 width=4)
Output: v.x
(2 rows)

regression=# explain verbose select * from (values (1), (random())) v(x);
QUERY PLAN
-------------------------------------------------------------
Values Scan on "*VALUES*" (cost=0.00..0.03 rows=2 width=8)
Output: "*VALUES*".column1
(2 rows)

That's because the volatile function prevents subquery flattening.

Yes, that is by design. As I used is_simple_values() so if the values list is not a simple one, which is not in this case, the alias won't be reassigned.

regards, tom lane

Re: Alias of VALUES RTE in explain plan

From

Tom Lane

Date:

28 October 2024, 02:07:37

Yasir <yasir.hussain.shah@gmail.com> writes:
> On Sat, Oct 26, 2024 at 12:21 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> I forgot to mention a third problem, which is that reassigning the
>> alias during subquery pullup means it doesn't happen if subquery
>> pullup doesn't happen.

> Yes, that is by design.

By design?  How can you claim that's not a bug?  The alias(es)
associated with a VALUES clause surely should not vary depending
on whether the clause includes a volatile function --- not to
mention other unobvious reasons for flattening to happen or not.

            regards, tom lane

Re: Alias of VALUES RTE in explain plan

From

Yasir

Date:

28 October 2024, 02:15:19

On Mon, Oct 28, 2024 at 1:07 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

Yasir <yasir.hussain.shah@gmail.com> writes:
> On Sat, Oct 26, 2024 at 12:21 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> I forgot to mention a third problem, which is that reassigning the
>> alias during subquery pullup means it doesn't happen if subquery
>> pullup doesn't happen.

> Yes, that is by design.

By design? How can you claim that's not a bug? The alias(es)
associated with a VALUES clause surely should not vary depending
on whether the clause includes a volatile function --- not to
mention other unobvious reasons for flattening to happen or not.

By design of my solution, I was not taking it as a bug. But now, I agree with your opinion.

regards, tom lane

Re: Alias of VALUES RTE in explain plan

From

Andrei Lepikhov

Date:

28 October 2024, 10:47:30

On 10/28/24 03:15, Yasir wrote:
> By design of my solution, I was not taking it as a bug. But now, I agree 
> with your opinion.
I think the case provided by Ashutosh was initially correct, and nothing 
needs to change. Look at the similar case:

EXPLAIN SELECT x,y FROM (
   SELECT oid,relname FROM pg_class WHERE relname = 'pg_index') AS 
c(x,y) WHERE c.y = 'pg_index';

                                          QUERY PLAN 

--------------------------------------------------------------------------------------------
  Index Scan using pg_class_relname_nsp_index on pg_class 
(cost=0.27..8.29 rows=1 width=68)
    Index Cond: (relname = 'pg_index'::name)
(2 rows)

I don't see any reference to the alias c(x,y) in this explain. 
Similarly, the flattened VALUES clause shouldn't be referenced under the 
alias t(a,b).

-- 
regards, Andrei Lepikhov

Re: Alias of VALUES RTE in explain plan

From

Ashutosh Bapat

Date:

28 October 2024, 19:19:11

On Mon, Oct 28, 2024 at 10:17 AM Andrei Lepikhov <lepihov@gmail.com> wrote:
>
> On 10/28/24 03:15, Yasir wrote:
> > By design of my solution, I was not taking it as a bug. But now, I agree
> > with your opinion.
> I think the case provided by Ashutosh was initially correct, and nothing
> needs to change. Look at the similar case:
>
> EXPLAIN SELECT x,y FROM (
>    SELECT oid,relname FROM pg_class WHERE relname = 'pg_index') AS
> c(x,y) WHERE c.y = 'pg_index';
>
>                                           QUERY PLAN
>
> --------------------------------------------------------------------------------------------
>   Index Scan using pg_class_relname_nsp_index on pg_class
> (cost=0.27..8.29 rows=1 width=68)
>     Index Cond: (relname = 'pg_index'::name)
> (2 rows)
>
> I don't see any reference to the alias c(x,y) in this explain.
> Similarly, the flattened VALUES clause shouldn't be referenced under the
> alias t(a,b).

The reason you don't see c(x, y) is because the subquery gets pulled
up and the subquery with c(x, y) no longer exists. If the subquery
doesn't get pulled, you would see c(x, y) in the EXPLAIN plan.

Our syntax doesn't allow an alias to be attached to VALUES(). E.g.
select * from values (1), (2) x(a) is not allowed. Instead we allow
(values (1), (2)) x(a) where values (1), (2) is treated as a subquery.
Since there is no way to attach an alias to VALUES() itself, I think
it's fair to consider the outer alias as the alias of the VALUES
relation. That's what Tom's patch does. The result is useful as well.

The patch looks good to me, except the name of the new member.

  CommonTableExpr *p_parent_cte; /* this query's containing CTE */
+ Alias    *p_parent_alias; /* parent's alias for this query */

the two "parent"s here mean different things and that might lead one
to assume that the p_parent_alias refers to alias of CTE. The comment
adds to the confusion since it mentions parent. How about renaming it
as p_outer_alias? or something which indicates alias of the outer
query?

--
Best Wishes,
Ashutosh Bapat

Re: Alias of VALUES RTE in explain plan

From

Andrei Lepikhov

Date:

28 October 2024, 19:42:05

On 28/10/2024 20:19, Ashutosh Bapat wrote:
> On Mon, Oct 28, 2024 at 10:17 AM Andrei Lepikhov <lepihov@gmail.com> wrote:
>>
>> On 10/28/24 03:15, Yasir wrote:
>>> By design of my solution, I was not taking it as a bug. But now, I agree
>>> with your opinion.
>> I think the case provided by Ashutosh was initially correct, and nothing
>> needs to change. Look at the similar case:
>>
>> EXPLAIN SELECT x,y FROM (
>>     SELECT oid,relname FROM pg_class WHERE relname = 'pg_index') AS
>> c(x,y) WHERE c.y = 'pg_index';
>>
>>                                            QUERY PLAN
>>
>> --------------------------------------------------------------------------------------------
>>    Index Scan using pg_class_relname_nsp_index on pg_class
>> (cost=0.27..8.29 rows=1 width=68)
>>      Index Cond: (relname = 'pg_index'::name)
>> (2 rows)
>>
>> I don't see any reference to the alias c(x,y) in this explain.
>> Similarly, the flattened VALUES clause shouldn't be referenced under the
>> alias t(a,b).
> 
> The reason you don't see c(x, y) is because the subquery gets pulled
> up and the subquery with c(x, y) no longer exists. If the subquery
> doesn't get pulled, you would see c(x, y) in the EXPLAIN plan.
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?

-- 
regards, Andrei Lepikhov

Re: Alias of VALUES RTE in explain plan

From

Tom Lane

Date:

28 October 2024, 21:05:38

Andrei Lepikhov <lepihov@gmail.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

Re: Alias of VALUES RTE in explain plan

From

Tom Lane

Date:

28 October 2024, 21:16:54

Ashutosh Bapat <ashutosh.bapat.oss@gmail.com> writes:
> The patch looks good to me, except the name of the new member.

>   CommonTableExpr *p_parent_cte; /* this query's containing CTE */
> + Alias    *p_parent_alias; /* parent's alias for this query */

> the two "parent"s here mean different things and that might lead one
> to assume that the p_parent_alias refers to alias of CTE. The comment
> adds to the confusion since it mentions parent. How about renaming it
> as p_outer_alias? or something which indicates alias of the outer
> query?

Hmm, I figured the two "parent" references do mean the same thing,
ie the immediately surrounding syntactic construct.  While I won't
fight hard about it, I don't see an advantage in naming the new
field differently.  We could make the comment be

        /* outer level's alias for this query */

if that helps any.

            regards, tom lane

Re: Alias of VALUES RTE in explain plan

From

Yasir

Date:

29 October 2024, 11:32:02

On Mon, Oct 28, 2024 at 8:16 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

Ashutosh Bapat <ashutosh.bapat.oss@gmail.com> writes:
> The patch looks good to me, except the name of the new member.

> CommonTableExpr *p_parent_cte; /* this query's containing CTE */
> + Alias *p_parent_alias; /* parent's alias for this query */

> the two "parent"s here mean different things and that might lead one
> to assume that the p_parent_alias refers to alias of CTE. The comment
> adds to the confusion since it mentions parent. How about renaming it
> as p_outer_alias? or something which indicates alias of the outer
> query?

Hmm, I figured the two "parent" references do mean the same thing,
ie the immediately surrounding syntactic construct. While I won't
fight hard about it, I don't see an advantage in naming the new
field differently. We could make the comment be

/* outer level's alias for this query */

This seems ok to me.

if that helps any.

regards, tom lane

Re: Alias of VALUES RTE in explain plan

From

Andrei Lepikhov

Date:

29 October 2024, 15:24:18

On 10/28/24 22:05, Tom Lane wrote:
> Andrei Lepikhov <lepihov@gmail.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?
> 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.
Thanks for the detailed explanation. I agree it make sense.
Also, after skimming the code, I propose some extra tests:

-- Just to cover the ERROR:
EXPLAIN (COSTS OFF, VERBOSE)
SELECT * FROM (VALUES (1),(2),(3),(4)) AS t1(x,y);
ERROR:  VALUES lists "t1" have 1 columns available but 2 columns specified

-- 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);

-- Not mentioned column is assigned with default name
EXPLAIN (COSTS OFF, VERBOSE)
SELECT * FROM (VALUES (4,1),(2,1),(3,1),(1,1) ORDER BY t1.column2,t1.x 
LIMIT 2 OFFSET 1) AS t1(x);
SELECT * FROM (VALUES (4,1),(2,1),(3,1),(1,1) ORDER BY t1.column2,t1.x 
LIMIT 2 OFFSET 1) AS t1(x);

-- Here it isn't allowed to sort with full reference 't2.x2', but in the 
EXPLAIN we see exactly 'Sort Key: t2.x2, t2.y':
EXPLAIN (COSTS OFF, VERBOSE)
SELECT * FROM (VALUES (3,3),(4,4)) AS t2(x2,y)
UNION ALL
SELECT * FROM (VALUES (1,1),(2,2)) AS t1(x1)
ORDER BY x2,y;

The code looks good.

-- 
regards, Andrei Lepikhov

Re: Alias of VALUES RTE in explain plan

From

Ashutosh Bapat

Date:

29 October 2024, 19:38:27

On Mon, Oct 28, 2024 at 8:46 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Ashutosh Bapat <ashutosh.bapat.oss@gmail.com> writes:
> > The patch looks good to me, except the name of the new member.
>
> >   CommonTableExpr *p_parent_cte; /* this query's containing CTE */
> > + Alias    *p_parent_alias; /* parent's alias for this query */
>
> > the two "parent"s here mean different things and that might lead one
> > to assume that the p_parent_alias refers to alias of CTE. The comment
> > adds to the confusion since it mentions parent. How about renaming it
> > as p_outer_alias? or something which indicates alias of the outer
> > query?
>
> Hmm, I figured the two "parent" references do mean the same thing,
> ie the immediately surrounding syntactic construct.  While I won't
> fight hard about it, I don't see an advantage in naming the new
> field differently.  We could make the comment be
>
>         /* outer level's alias for this query */
WFM.



--
Best Wishes,
Ashutosh Bapat

Re: Alias of VALUES RTE in explain plan

From

Tom Lane

Date:

29 October 2024, 20:02:01

Andrei Lepikhov <lepihov@gmail.com> writes:
> Thanks for the detailed explanation. I agree it make sense.

Cool, I think we're all agreed then.

> Also, after skimming the code, I propose some extra tests:

Most of these are covered well enough by existing tests, aren't
they?

            regards, tom lane

Re: Alias of VALUES RTE in explain plan

From

Tom Lane

Date:

29 October 2024, 23:19:22

Andrei Lepikhov <lepihov@gmail.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

Re: Alias of VALUES RTE in explain plan

From

Andrei Lepikhov

Date:

30 October 2024, 07:54:48

On 10/30/24 00:19, Tom Lane wrote:
> Andrei Lepikhov <lepihov@gmail.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?
You have written before that a VALUES alias should be a special case 
because it's a 'natural thing'. And I buy it. So, it looks natural to 
use this alias everywhere in the query without restrictions. That's why 
I provided examples in my previous email to check that it is a full 
replacement for the '"*VALUES*".columnN'.

-- 
regards, Andrei Lepikhov

Re: Alias of VALUES RTE in explain plan

From

Ashutosh Bapat

Date:

30 October 2024, 18:46:12

On Tue, Oct 29, 2024 at 10:49 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Andrei Lepikhov <lepihov@gmail.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

Re: Alias of VALUES RTE in explain plan

From

Tom Lane

Date:

02 November 2024, 23:09:29

Ashutosh Bapat <ashutosh.bapat.oss@gmail.com> writes:
> On Tue, Oct 29, 2024 at 10:49 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> 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.

> 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.

I was starting to come around to Andrei's position that changing this
behavior is fine, until I realized that it creates a problem for
ruleutils.c.  With the v2 patch, dumping a view that contains a
construct like this doesn't work:

regression=# create view vv as SELECT * FROM (VALUES (4),(2),(3),(1) ORDER BY t1.x LIMIT 2) AS t1(x);
      
CREATE VIEW
regression=# \d+ vv
                             View "public.vv"
 Column |  Type   | Collation | Nullable | Default | Storage | Description
--------+---------+-----------+----------+---------+---------+-------------
 x      | integer |           |          |         | plain   |
View definition:
 SELECT x
   FROM ( VALUES (4), (2), (3), (1)
          ORDER BY t1_1.x
         LIMIT 2) t1(x);

ruleutils has decided that it needs to make the two "t1" table
aliases distinct.  But of course that will fail on reload:

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".

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.

            regards, tom lane

diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index f2bcd6aa98..73dd1d80c8 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -8992,16 +8992,16 @@ insert into utrtest values (2, 'qux');
 -- with a non-direct modification plan
 explain (verbose, costs off)
 update utrtest set a = 1 from (values (1), (2)) s(x) where a = s.x returning *;
-                                           QUERY PLAN
-------------------------------------------------------------------------------------------------
+                                        QUERY PLAN
+-------------------------------------------------------------------------------------------
  Update on public.utrtest
-   Output: utrtest_1.a, utrtest_1.b, "*VALUES*".column1
+   Output: utrtest_1.a, utrtest_1.b, s.x
    Foreign Update on public.remp utrtest_1
      Remote SQL: UPDATE public.loct SET a = $2 WHERE ctid = $1 RETURNING a, b
    Update on public.locp utrtest_2
    ->  Hash Join
-         Output: 1, "*VALUES*".*, "*VALUES*".column1, utrtest.tableoid, utrtest.ctid, utrtest.*
-         Hash Cond: (utrtest.a = "*VALUES*".column1)
+         Output: 1, s.*, s.x, utrtest.tableoid, utrtest.ctid, utrtest.*
+         Hash Cond: (utrtest.a = s.x)
          ->  Append
                ->  Foreign Scan on public.remp utrtest_1
                      Output: utrtest_1.a, utrtest_1.tableoid, utrtest_1.ctid, utrtest_1.*
@@ -9009,9 +9009,9 @@ update utrtest set a = 1 from (values (1), (2)) s(x) where a = s.x returning *;
                ->  Seq Scan on public.locp utrtest_2
                      Output: utrtest_2.a, utrtest_2.tableoid, utrtest_2.ctid, NULL::record
          ->  Hash
-               Output: "*VALUES*".*, "*VALUES*".column1
-               ->  Values Scan on "*VALUES*"
-                     Output: "*VALUES*".*, "*VALUES*".column1
+               Output: s.*, s.x
+               ->  Values Scan on s
+                     Output: s.*, s.x
 (18 rows)

 update utrtest set a = 1 from (values (1), (2)) s(x) where a = s.x returning *;
@@ -9049,16 +9049,16 @@ ERROR:  cannot route tuples into foreign table to be updated "remp"
 -- with a non-direct modification plan
 explain (verbose, costs off)
 update utrtest set a = 3 from (values (2), (3)) s(x) where a = s.x returning *;
-                                             QUERY PLAN
------------------------------------------------------------------------------------------------------
+                                        QUERY PLAN
+-------------------------------------------------------------------------------------------
  Update on public.utrtest
-   Output: utrtest_1.a, utrtest_1.b, "*VALUES*".column1
+   Output: utrtest_1.a, utrtest_1.b, s.x
    Update on public.locp utrtest_1
    Foreign Update on public.remp utrtest_2
      Remote SQL: UPDATE public.loct SET a = $2 WHERE ctid = $1 RETURNING a, b
    ->  Hash Join
-         Output: 3, "*VALUES*".*, "*VALUES*".column1, utrtest.tableoid, utrtest.ctid, (NULL::record)
-         Hash Cond: (utrtest.a = "*VALUES*".column1)
+         Output: 3, s.*, s.x, utrtest.tableoid, utrtest.ctid, (NULL::record)
+         Hash Cond: (utrtest.a = s.x)
          ->  Append
                ->  Seq Scan on public.locp utrtest_1
                      Output: utrtest_1.a, utrtest_1.tableoid, utrtest_1.ctid, NULL::record
@@ -9066,9 +9066,9 @@ update utrtest set a = 3 from (values (2), (3)) s(x) where a = s.x returning *;
                      Output: utrtest_2.a, utrtest_2.tableoid, utrtest_2.ctid, utrtest_2.*
                      Remote SQL: SELECT a, b, ctid FROM public.loct FOR UPDATE
          ->  Hash
-               Output: "*VALUES*".*, "*VALUES*".column1
-               ->  Values Scan on "*VALUES*"
-                     Output: "*VALUES*".*, "*VALUES*".column1
+               Output: s.*, s.x
+               ->  Values Scan on s
+                     Output: s.*, s.x
 (18 rows)

 update utrtest set a = 3 from (values (2), (3)) s(x) where a = s.x returning *; -- ERROR
diff --git a/contrib/tsm_system_rows/expected/tsm_system_rows.out
b/contrib/tsm_system_rows/expected/tsm_system_rows.out
index 87b4a8fc64..cd472d2605 100644
--- a/contrib/tsm_system_rows/expected/tsm_system_rows.out
+++ b/contrib/tsm_system_rows/expected/tsm_system_rows.out
@@ -49,13 +49,13 @@ SELECT * FROM
   (VALUES (0),(10),(100)) v(nrows),
   LATERAL (SELECT count(*) FROM test_tablesample
            TABLESAMPLE system_rows (nrows)) ss;
-                        QUERY PLAN
-----------------------------------------------------------
+                  QUERY PLAN
+-----------------------------------------------
  Nested Loop
-   ->  Values Scan on "*VALUES*"
+   ->  Values Scan on v
    ->  Aggregate
          ->  Sample Scan on test_tablesample
-               Sampling: system_rows ("*VALUES*".column1)
+               Sampling: system_rows (v.nrows)
 (5 rows)

 SELECT * FROM
diff --git a/contrib/tsm_system_time/expected/tsm_system_time.out
b/contrib/tsm_system_time/expected/tsm_system_time.out
index ac44f30be9..6c5aac3709 100644
--- a/contrib/tsm_system_time/expected/tsm_system_time.out
+++ b/contrib/tsm_system_time/expected/tsm_system_time.out
@@ -47,7 +47,7 @@ SELECT * FROM
          ->  Materialize
                ->  Sample Scan on test_tablesample
                      Sampling: system_time ('100000'::double precision)
-   ->  Values Scan on "*VALUES*"
+   ->  Values Scan on v
 (6 rows)

 SELECT * FROM
@@ -65,14 +65,14 @@ SELECT * FROM
   (VALUES (0),(100000)) v(time),
   LATERAL (SELECT COUNT(*) FROM test_tablesample
            TABLESAMPLE system_time (time)) ss;
-                           QUERY PLAN
-----------------------------------------------------------------
+                      QUERY PLAN
+------------------------------------------------------
  Nested Loop
-   ->  Values Scan on "*VALUES*"
+   ->  Values Scan on v
    ->  Aggregate
          ->  Materialize
                ->  Sample Scan on test_tablesample
-                     Sampling: system_time ("*VALUES*".column1)
+                     Sampling: system_time (v."time")
 (6 rows)

 SELECT * FROM
diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c
index 506e063161..fb8f433a2a 100644
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.c
@@ -221,6 +221,7 @@ parse_analyze_withcb(RawStmt *parseTree, const char *sourceText,
 Query *
 parse_sub_analyze(Node *parseTree, ParseState *parentParseState,
                   CommonTableExpr *parentCTE,
+                  Alias *parentAlias,
                   bool locked_from_parent,
                   bool resolve_unknowns)
 {
@@ -228,6 +229,7 @@ parse_sub_analyze(Node *parseTree, ParseState *parentParseState,
     Query       *query;

     pstate->p_parent_cte = parentCTE;
+    pstate->p_parent_alias = parentAlias;
     pstate->p_locked_from_parent = locked_from_parent;
     pstate->p_resolve_unknowns = resolve_unknowns;

@@ -1578,6 +1580,7 @@ transformValuesClause(ParseState *pstate, SelectStmt *stmt)
     List      **colexprs = NULL;
     int            sublist_length = -1;
     bool        lateral = false;
+    Alias       *valias;
     ParseNamespaceItem *nsitem;
     ListCell   *lc;
     ListCell   *lc2;
@@ -1725,11 +1728,20 @@ transformValuesClause(ParseState *pstate, SelectStmt *stmt)
         lateral = true;

     /*
-     * Generate the VALUES RTE
+     * Generate the VALUES RTE.  If we're in a RangeSubselect of an outer
+     * query level, and that had an Alias, we prefer to use that alias rather
+     * than "*VALUES*".columnN.  But stick with "*VALUES*" if there is a
+     * sortClause, because that could contain references to the "*VALUES*"
+     * names.  (If we supported a lockingClause, that could too; but we
+     * don't.)
      */
+    if (pstate->p_parent_alias && stmt->sortClause == NIL)
+        valias = copyObject(pstate->p_parent_alias);
+    else
+        valias = NULL;
     nsitem = addRangeTableEntryForValues(pstate, exprsLists,
                                          coltypes, coltypmods, colcollations,
-                                         NULL, lateral, true);
+                                         valias, lateral, true);
     addNSItemToQuery(pstate, nsitem, true, true, true);

     /*
@@ -2167,7 +2179,7 @@ transformSetOperationTree(ParseState *pstate, SelectStmt *stmt,
          * namespace list.
          */
         selectQuery = parse_sub_analyze((Node *) stmt, pstate,
-                                        NULL, false, false);
+                                        NULL, NULL, false, false);

         /*
          * Check for bogus references to Vars on the current query level (but
diff --git a/src/backend/parser/parse_clause.c b/src/backend/parser/parse_clause.c
index 979926b605..5040b64274 100644
--- a/src/backend/parser/parse_clause.c
+++ b/src/backend/parser/parse_clause.c
@@ -428,6 +428,7 @@ transformRangeSubselect(ParseState *pstate, RangeSubselect *r)
      * might still be required (if there is an all-tables locking clause).
      */
     query = parse_sub_analyze(r->subquery, pstate, NULL,
+                              r->alias,
                               isLockedRefname(pstate,
                                               r->alias == NULL ? NULL :
                                               r->alias->aliasname),
diff --git a/src/backend/parser/parse_cte.c b/src/backend/parser/parse_cte.c
index de9ae9b483..9070f834b5 100644
--- a/src/backend/parser/parse_cte.c
+++ b/src/backend/parser/parse_cte.c
@@ -312,7 +312,7 @@ analyzeCTE(ParseState *pstate, CommonTableExpr *cte)
     }

     /* Now we can get on with analyzing the CTE's query */
-    query = parse_sub_analyze(cte->ctequery, pstate, cte, false, true);
+    query = parse_sub_analyze(cte->ctequery, pstate, cte, NULL, false, true);
     cte->ctequery = (Node *) query;

     /*
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index c2806297aa..8e8aff0c35 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -1878,7 +1878,8 @@ transformSubLink(ParseState *pstate, SubLink *sublink)
     /*
      * OK, let's transform the sub-SELECT.
      */
-    qtree = parse_sub_analyze(sublink->subselect, pstate, NULL, false, true);
+    qtree = parse_sub_analyze(sublink->subselect, pstate, NULL, NULL,
+                              false, true);

     /*
      * Check that we got a SELECT.  Anything else should be impossible given
diff --git a/src/include/parser/analyze.h b/src/include/parser/analyze.h
index 28b66fccb4..f63c6ed25f 100644
--- a/src/include/parser/analyze.h
+++ b/src/include/parser/analyze.h
@@ -36,6 +36,7 @@ extern Query *parse_analyze_withcb(RawStmt *parseTree, const char *sourceText,

 extern Query *parse_sub_analyze(Node *parseTree, ParseState *parentParseState,
                                 CommonTableExpr *parentCTE,
+                                Alias *parentAlias,
                                 bool locked_from_parent,
                                 bool resolve_unknowns);

diff --git a/src/include/parser/parse_node.h b/src/include/parser/parse_node.h
index 2375e95c10..87aec73827 100644
--- a/src/include/parser/parse_node.h
+++ b/src/include/parser/parse_node.h
@@ -161,6 +161,9 @@ typedef Node *(*CoerceParamHook) (ParseState *pstate, Param *param,
  * p_parent_cte: CommonTableExpr that immediately contains the current query,
  * if any.
  *
+ * p_parent_alias: Alias attached to the current sub-SELECT in the parent
+ * query level, if any.
+ *
  * p_target_relation: target relation, if query is INSERT/UPDATE/DELETE/MERGE
  *
  * p_target_nsitem: target relation's ParseNamespaceItem.
@@ -222,6 +225,7 @@ struct ParseState
     List       *p_ctenamespace; /* current namespace for common table exprs */
     List       *p_future_ctes;    /* common table exprs not yet in namespace */
     CommonTableExpr *p_parent_cte;    /* this query's containing CTE */
+    Alias       *p_parent_alias; /* outer level's alias for this query */
     Relation    p_target_relation;    /* INSERT/UPDATE/DELETE/MERGE target rel */
     ParseNamespaceItem *p_target_nsitem;    /* target rel's NSItem, or NULL */
     ParseNamespaceItem *p_grouping_nsitem;    /* NSItem for grouping, or NULL */
diff --git a/src/test/regress/expected/create_view.out b/src/test/regress/expected/create_view.out
index f551624afb..5c18230b27 100644
--- a/src/test/regress/expected/create_view.out
+++ b/src/test/regress/expected/create_view.out
@@ -2189,34 +2189,34 @@ select pg_get_viewdef('tt25v', true);
 -- also check cases seen only in EXPLAIN
 explain (verbose, costs off)
 select * from tt24v;
-                                        QUERY PLAN
-------------------------------------------------------------------------------------------
+                                QUERY PLAN
+--------------------------------------------------------------------------
  Hash Join
-   Output: (cte.r).column2, ((ROW("*VALUES*".column1, "*VALUES*".column2))).column2
-   Hash Cond: ((cte.r).column1 = ((ROW("*VALUES*".column1, "*VALUES*".column2))).column1)
+   Output: (cte.r).column2, ((ROW(rr.column1, rr.column2))).column2
+   Hash Cond: ((cte.r).column1 = ((ROW(rr.column1, rr.column2))).column1)
    CTE cte
-     ->  Values Scan on "*VALUES*_1"
-           Output: ROW("*VALUES*_1".column1, "*VALUES*_1".column2)
+     ->  Values Scan on r
+           Output: ROW(r.column1, r.column2)
    ->  CTE Scan on cte
          Output: cte.r
    ->  Hash
-         Output: (ROW("*VALUES*".column1, "*VALUES*".column2))
+         Output: (ROW(rr.column1, rr.column2))
          ->  Limit
-               Output: (ROW("*VALUES*".column1, "*VALUES*".column2))
-               ->  Values Scan on "*VALUES*"
-                     Output: ROW("*VALUES*".column1, "*VALUES*".column2)
+               Output: (ROW(rr.column1, rr.column2))
+               ->  Values Scan on rr
+                     Output: ROW(rr.column1, rr.column2)
 (14 rows)

 explain (verbose, costs off)
 select (r).column2 from (select r from (values(1,2),(3,4)) r limit 1) ss;
-                            QUERY PLAN
--------------------------------------------------------------------
+                   QUERY PLAN
+-------------------------------------------------
  Subquery Scan on ss
    Output: (ss.r).column2
    ->  Limit
-         Output: (ROW("*VALUES*".column1, "*VALUES*".column2))
-         ->  Values Scan on "*VALUES*"
-               Output: ROW("*VALUES*".column1, "*VALUES*".column2)
+         Output: (ROW(r.column1, r.column2))
+         ->  Values Scan on r
+               Output: ROW(r.column1, r.column2)
 (6 rows)

 -- test pretty-print parenthesization rules, and SubLink deparsing
@@ -2265,6 +2265,67 @@ select viewname from pg_views where viewname = 'tt27v'; -- Ok to access a system
 (1 row)

 reset restrict_nonsystem_relation_kind;
+-- test assignment of aliases to VALUES clauses
+create view tt28v as
+select *
+from (values (4),(2),(3),(1) order by "*VALUES*".column1 limit 2) as t1(x);
+select pg_get_viewdef('tt28v', true);
+            pg_get_viewdef
+---------------------------------------
+  SELECT x                            +
+    FROM ( VALUES (4), (2), (3), (1)  +
+           ORDER BY "*VALUES*".column1+
+          LIMIT 2) t1(x);
+(1 row)
+
+explain (verbose, costs off) select * from tt28v where x > 0;
+                   QUERY PLAN
+------------------------------------------------
+ Subquery Scan on t1
+   Output: t1.x
+   Filter: (t1.x > 0)
+   ->  Limit
+         Output: "*VALUES*".column1
+         ->  Sort
+               Output: "*VALUES*".column1
+               Sort Key: "*VALUES*".column1
+               ->  Values Scan on "*VALUES*"
+                     Output: "*VALUES*".column1
+(10 rows)
+
+create view tt29v as
+select *
+from (values (4),(2),(3),(1) order by "*VALUES*".column1) t1(a),
+     (values (9),(8),(6),(7) order by "*VALUES*".column1) t2(b);
+select pg_get_viewdef('tt29v', true);
+                pg_get_viewdef
+-----------------------------------------------
+  SELECT t1.a,                                +
+     t2.b                                     +
+    FROM ( VALUES (4), (2), (3), (1)          +
+           ORDER BY "*VALUES*".column1) t1(a),+
+     ( VALUES (9), (8), (6), (7)              +
+           ORDER BY "*VALUES*".column1) t2(b);
+(1 row)
+
+explain (verbose, costs off) select * from tt29v where a > 0;
+                     QUERY PLAN
+----------------------------------------------------
+ Nested Loop
+   Output: "*VALUES*".column1, "*VALUES*_1".column1
+   ->  Sort
+         Output: "*VALUES*".column1
+         Sort Key: "*VALUES*".column1
+         ->  Values Scan on "*VALUES*"
+               Output: "*VALUES*".column1
+               Filter: ("*VALUES*".column1 > 0)
+   ->  Sort
+         Output: "*VALUES*_1".column1
+         Sort Key: "*VALUES*_1".column1
+         ->  Values Scan on "*VALUES*_1"
+               Output: "*VALUES*_1".column1
+(13 rows)
+
 -- clean up all the random objects we made above
 DROP SCHEMA temp_view_test CASCADE;
 NOTICE:  drop cascades to 27 other objects
@@ -2296,7 +2357,7 @@ drop cascades to view aliased_view_2
 drop cascades to view aliased_view_3
 drop cascades to view aliased_view_4
 DROP SCHEMA testviewschm2 CASCADE;
-NOTICE:  drop cascades to 80 other objects
+NOTICE:  drop cascades to 82 other objects
 DETAIL:  drop cascades to table t1
 drop cascades to view temporal1
 drop cascades to view temporal2
@@ -2377,3 +2438,5 @@ drop cascades to view tt25v
 drop cascades to view tt26v
 drop cascades to table tt27v_tbl
 drop cascades to view tt27v
+drop cascades to view tt28v
+drop cascades to view tt29v
diff --git a/src/test/regress/expected/gist.out b/src/test/regress/expected/gist.out
index c75bbb23b6..af85945ea4 100644
--- a/src/test/regress/expected/gist.out
+++ b/src/test/regress/expected/gist.out
@@ -141,11 +141,11 @@ cross join lateral
                              QUERY PLAN
 --------------------------------------------------------------------
  Nested Loop
-   ->  Values Scan on "*VALUES*"
+   ->  Values Scan on v
    ->  Limit
          ->  Index Only Scan using gist_tbl_point_index on gist_tbl
-               Index Cond: (p <@ "*VALUES*".column1)
-               Order By: (p <-> ("*VALUES*".column1)[0])
+               Index Cond: (p <@ v.bb)
+               Order By: (p <-> (v.bb)[0])
 (6 rows)

 select p from
diff --git a/src/test/regress/expected/groupingsets.out b/src/test/regress/expected/groupingsets.out
index d7c9b44605..bed1174c13 100644
--- a/src/test/regress/expected/groupingsets.out
+++ b/src/test/regress/expected/groupingsets.out
@@ -846,17 +846,17 @@ select v.c, (select count(*) from gstest2 group by () having v.c)
 explain (costs off)
   select v.c, (select count(*) from gstest2 group by () having v.c)
     from (values (false),(true)) v(c) order by v.c;
-                        QUERY PLAN
------------------------------------------------------------
+                  QUERY PLAN
+-----------------------------------------------
  Sort
-   Sort Key: "*VALUES*".column1
-   ->  Values Scan on "*VALUES*"
+   Sort Key: v.c
+   ->  Values Scan on v
          SubPlan 1
            ->  Aggregate
                  Group Key: ()
-                 Filter: "*VALUES*".column1
+                 Filter: v.c
                  ->  Result
-                       One-Time Filter: "*VALUES*".column1
+                       One-Time Filter: v.c
                        ->  Seq Scan on gstest2
 (10 rows)

@@ -1349,15 +1349,15 @@ explain (costs off)
     from (values (1),(2)) v(x), gstest_data(v.x)
    group by grouping sets (a,b)
    order by 3, 1, 2;
-                             QUERY PLAN
----------------------------------------------------------------------
+                      QUERY PLAN
+------------------------------------------------------
  Sort
-   Sort Key: (sum("*VALUES*".column1)), gstest_data.a, gstest_data.b
+   Sort Key: (sum(v.x)), gstest_data.a, gstest_data.b
    ->  HashAggregate
          Hash Key: gstest_data.a
          Hash Key: gstest_data.b
          ->  Nested Loop
-               ->  Values Scan on "*VALUES*"
+               ->  Values Scan on v
                ->  Function Scan on gstest_data
 (8 rows)

@@ -1481,7 +1481,7 @@ explain (costs off)
          Hash Key: gstest_data.b
          Group Key: ()
          ->  Nested Loop
-               ->  Values Scan on "*VALUES*"
+               ->  Values Scan on v
                ->  Function Scan on gstest_data
 (10 rows)

@@ -2323,16 +2323,16 @@ select distinct on (a, b) a, b
 from (values (1, 1), (2, 2)) as t (a, b) where a = b
 group by grouping sets((a, b), (a))
 order by a, b;
-                           QUERY PLAN
-----------------------------------------------------------------
+             QUERY PLAN
+-------------------------------------
  Unique
    ->  Sort
-         Sort Key: "*VALUES*".column1, "*VALUES*".column2
+         Sort Key: t.a, t.b
          ->  HashAggregate
-               Hash Key: "*VALUES*".column1, "*VALUES*".column2
-               Hash Key: "*VALUES*".column1
-               ->  Values Scan on "*VALUES*"
-                     Filter: (column1 = column2)
+               Hash Key: t.a, t.b
+               Hash Key: t.a
+               ->  Values Scan on t
+                     Filter: (a = b)
 (8 rows)

 select distinct on (a, b) a, b
@@ -2352,16 +2352,16 @@ select distinct on (a, b+1) a, b+1
 from (values (1, 0), (2, 1)) as t (a, b) where a = b+1
 group by grouping sets((a, b+1), (a))
 order by a, b+1;
-                              QUERY PLAN
-----------------------------------------------------------------------
+                QUERY PLAN
+-------------------------------------------
  Unique
    ->  Sort
-         Sort Key: "*VALUES*".column1, (("*VALUES*".column2 + 1))
+         Sort Key: t.a, ((t.b + 1))
          ->  HashAggregate
-               Hash Key: "*VALUES*".column1, ("*VALUES*".column2 + 1)
-               Hash Key: "*VALUES*".column1
-               ->  Values Scan on "*VALUES*"
-                     Filter: (column1 = (column2 + 1))
+               Hash Key: t.a, (t.b + 1)
+               Hash Key: t.a
+               ->  Values Scan on t
+                     Filter: (a = (b + 1))
 (8 rows)

 select distinct on (a, b+1) a, b+1
@@ -2381,15 +2381,15 @@ select a, b
 from (values (1, 1), (2, 2)) as t (a, b) where a = b
 group by grouping sets((a, b), (a))
 order by a, b nulls first;
-                           QUERY PLAN
-----------------------------------------------------------------
+            QUERY PLAN
+----------------------------------
  Sort
-   Sort Key: "*VALUES*".column1, "*VALUES*".column2 NULLS FIRST
+   Sort Key: t.a, t.b NULLS FIRST
    ->  HashAggregate
-         Hash Key: "*VALUES*".column1, "*VALUES*".column2
-         Hash Key: "*VALUES*".column1
-         ->  Values Scan on "*VALUES*"
-               Filter: (column1 = column2)
+         Hash Key: t.a, t.b
+         Hash Key: t.a
+         ->  Values Scan on t
+               Filter: (a = b)
 (7 rows)

 select a, b
@@ -2427,16 +2427,16 @@ explain (costs off)
 select a, b, row_number() over (order by a, b nulls first)
 from (values (1, 1), (2, 2)) as t (a, b) where a = b
 group by grouping sets((a, b), (a));
-                              QUERY PLAN
-----------------------------------------------------------------------
+               QUERY PLAN
+----------------------------------------
  WindowAgg
    ->  Sort
-         Sort Key: "*VALUES*".column1, "*VALUES*".column2 NULLS FIRST
+         Sort Key: t.a, t.b NULLS FIRST
          ->  HashAggregate
-               Hash Key: "*VALUES*".column1, "*VALUES*".column2
-               Hash Key: "*VALUES*".column1
-               ->  Values Scan on "*VALUES*"
-                     Filter: (column1 = column2)
+               Hash Key: t.a, t.b
+               Hash Key: t.a
+               ->  Values Scan on t
+                     Filter: (a = b)
 (8 rows)

 select a, b, row_number() over (order by a, b nulls first)
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index 9b2973694f..3bd75074ba 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -4508,16 +4508,16 @@ select * from
 (values (1, array[10,20]), (2, array[20,30])) as v1(v1x,v1ys)
 left join (values (1, 10), (2, 20)) as v2(v2x,v2y) on v2x = v1x
 left join unnest(v1ys) as u1(u1y) on u1y = v2y;
-                         QUERY PLAN
--------------------------------------------------------------
+               QUERY PLAN
+----------------------------------------
  Nested Loop Left Join
-   ->  Values Scan on "*VALUES*"
+   ->  Values Scan on v1
    ->  Hash Right Join
-         Hash Cond: (u1.u1y = "*VALUES*_1".column2)
-         Filter: ("*VALUES*_1".column1 = "*VALUES*".column1)
+         Hash Cond: (u1.u1y = v2.v2y)
+         Filter: (v2.v2x = v1.v1x)
          ->  Function Scan on unnest u1
          ->  Hash
-               ->  Values Scan on "*VALUES*_1"
+               ->  Values Scan on v2
 (8 rows)

 select * from
@@ -4654,10 +4654,10 @@ using (join_key);
                                 QUERY PLAN
 --------------------------------------------------------------------------
  Nested Loop Left Join
-   Output: "*VALUES*".column1, i1.f1, (666)
-   Join Filter: ("*VALUES*".column1 = i1.f1)
-   ->  Values Scan on "*VALUES*"
-         Output: "*VALUES*".column1
+   Output: foo1.join_key, i1.f1, (666)
+   Join Filter: (foo1.join_key = i1.f1)
+   ->  Values Scan on foo1
+         Output: foo1.join_key
    ->  Materialize
          Output: i1.f1, (666)
          ->  Nested Loop Left Join
@@ -6541,12 +6541,12 @@ explain (costs off)
    ->  Nested Loop
          ->  Nested Loop
                ->  Index Only Scan using tenk1_unique1 on tenk1 a
-               ->  Values Scan on "*VALUES*"
+               ->  Values Scan on ss
          ->  Memoize
-               Cache Key: "*VALUES*".column1
+               Cache Key: ss.x
                Cache Mode: logical
                ->  Index Only Scan using tenk1_unique2 on tenk1 b
-                     Index Cond: (unique2 = "*VALUES*".column1)
+                     Index Cond: (unique2 = ss.x)
 (10 rows)

 select count(*) from tenk1 a,
@@ -7326,12 +7326,12 @@ select * from
   lateral (select f1 from int4_tbl
            where f1 = any (select unique1 from tenk1
                            where unique2 = v.x offset 0)) ss;
-                              QUERY PLAN
-----------------------------------------------------------------------
+                            QUERY PLAN
+------------------------------------------------------------------
  Nested Loop
-   Output: "*VALUES*".column1, "*VALUES*".column2, int4_tbl.f1
-   ->  Values Scan on "*VALUES*"
-         Output: "*VALUES*".column1, "*VALUES*".column2
+   Output: v.id, v.x, int4_tbl.f1
+   ->  Values Scan on v
+         Output: v.id, v.x
    ->  Nested Loop Semi Join
          Output: int4_tbl.f1
          Join Filter: (int4_tbl.f1 = tenk1.unique1)
@@ -7341,7 +7341,7 @@ select * from
                Output: tenk1.unique1
                ->  Index Scan using tenk1_unique2 on public.tenk1
                      Output: tenk1.unique1
-                     Index Cond: (tenk1.unique2 = "*VALUES*".column2)
+                     Index Cond: (tenk1.unique2 = v.x)
 (14 rows)

 select * from
@@ -7368,13 +7368,13 @@ lateral (select * from int8_tbl t1,
                                                          QUERY PLAN
     

----------------------------------------------------------------------------------------------------------------------------
  Nested Loop
-   Output: "*VALUES*".column1, t1.q1, t1.q2, ss2.q1, ss2.q2
+   Output: v.id, t1.q1, t1.q2, ss2.q1, ss2.q2
    ->  Seq Scan on public.int8_tbl t1
          Output: t1.q1, t1.q2
    ->  Nested Loop
-         Output: "*VALUES*".column1, ss2.q1, ss2.q2
-         ->  Values Scan on "*VALUES*"
-               Output: "*VALUES*".column1
+         Output: v.id, ss2.q1, ss2.q2
+         ->  Values Scan on v
+               Output: v.id
          ->  Subquery Scan on ss2
                Output: ss2.q1, ss2.q2
                Filter: (t1.q1 = ss2.q2)
@@ -7390,7 +7390,7 @@ lateral (select * from int8_tbl t1,
                                      Output: GREATEST(t1.q1, t2.q2)
                              InitPlan 2
                                ->  Result
-                                     Output: ("*VALUES*".column1 = 0)
+                                     Output: (v.id = 0)
                              ->  Seq Scan on public.int8_tbl t3
                                    Output: t3.q1, t3.q2
                                    Filter: (t3.q2 = (InitPlan 1).col1)
diff --git a/src/test/regress/expected/plpgsql.out b/src/test/regress/expected/plpgsql.out
index 0a6945581b..ac0bafe21f 100644
--- a/src/test/regress/expected/plpgsql.out
+++ b/src/test/regress/expected/plpgsql.out
@@ -5180,10 +5180,10 @@ select consumes_rw_array(a), a from returns_rw_array(1) a;
 explain (verbose, costs off)
 select consumes_rw_array(a), a from
   (values (returns_rw_array(1)), (returns_rw_array(2))) v(a);
-                             QUERY PLAN
----------------------------------------------------------------------
- Values Scan on "*VALUES*"
-   Output: consumes_rw_array("*VALUES*".column1), "*VALUES*".column1
+              QUERY PLAN
+---------------------------------------
+ Values Scan on v
+   Output: consumes_rw_array(v.a), v.a
 (2 rows)

 select consumes_rw_array(a), a from
diff --git a/src/test/regress/expected/rowtypes.out b/src/test/regress/expected/rowtypes.out
index 9168979a62..efe8bf1ef6 100644
--- a/src/test/regress/expected/rowtypes.out
+++ b/src/test/regress/expected/rowtypes.out
@@ -1193,10 +1193,10 @@ explain (verbose, costs off)
 select r, r is null as isnull, r is not null as isnotnull
 from (values (1,row(1,2)), (1,row(null,null)), (1,null),
              (null,row(1,2)), (null,row(null,null)), (null,null) ) r(a,b);
-                                                                                                         QUERY PLAN
                                                                                                      

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- Values Scan on "*VALUES*"
-   Output: ROW("*VALUES*".column1, "*VALUES*".column2), (("*VALUES*".column1 IS NULL) AND ("*VALUES*".column2 IS NOT
DISTINCTFROM NULL)), (("*VALUES*".column1 IS NOT NULL) AND ("*VALUES*".column2 IS DISTINCT FROM NULL)) 
+                                                            QUERY PLAN
            

+-----------------------------------------------------------------------------------------------------------------------------------
+ Values Scan on r
+   Output: ROW(r.a, r.b), ((r.a IS NULL) AND (r.b IS NOT DISTINCT FROM NULL)), ((r.a IS NOT NULL) AND (r.b IS DISTINCT
FROMNULL)) 
 (2 rows)

 select r, r is null as isnull, r is not null as isnotnull
diff --git a/src/test/regress/expected/select_parallel.out b/src/test/regress/expected/select_parallel.out
index d17ade278b..84c70efda3 100644
--- a/src/test/regress/expected/select_parallel.out
+++ b/src/test/regress/expected/select_parallel.out
@@ -432,7 +432,7 @@ select * from
                                 QUERY PLAN
 --------------------------------------------------------------------------
  Nested Loop Left Join
-   ->  Values Scan on "*VALUES*"
+   ->  Values Scan on v
    ->  Finalize Aggregate
          ->  Gather
                Workers Planned: 4
@@ -458,7 +458,7 @@ select * from
                                       QUERY PLAN
 --------------------------------------------------------------------------------------
  Nested Loop Left Join
-   ->  Values Scan on "*VALUES*"
+   ->  Values Scan on v
    ->  Finalize Aggregate
          ->  Gather
                Workers Planned: 4
@@ -609,7 +609,7 @@ select * from explain_parallel_sort_stats();
                        explain_parallel_sort_stats
 --------------------------------------------------------------------------
  Nested Loop Left Join (actual rows=30000 loops=1)
-   ->  Values Scan on "*VALUES*" (actual rows=3 loops=1)
+   ->  Values Scan on v (actual rows=3 loops=1)
    ->  Gather Merge (actual rows=10000 loops=3)
          Workers Planned: 4
          Workers Launched: 4
@@ -873,7 +873,7 @@ select * from
                         QUERY PLAN
 ----------------------------------------------------------
  Nested Loop Left Join
-   ->  Values Scan on "*VALUES*"
+   ->  Values Scan on v
    ->  Finalize GroupAggregate
          Group Key: tenk1.string4
          ->  Gather Merge
diff --git a/src/test/regress/expected/sqljson.out b/src/test/regress/expected/sqljson.out
index 435d61dd4a..90959fe79b 100644
--- a/src/test/regress/expected/sqljson.out
+++ b/src/test/regress/expected/sqljson.out
@@ -1070,15 +1070,15 @@ DROP VIEW json_arrayagg_view;
 -- Test JSON_ARRAY(subquery) deparsing
 EXPLAIN (VERBOSE, COSTS OFF)
 SELECT JSON_ARRAY(SELECT i FROM (VALUES (1), (2), (NULL), (4)) foo(i) RETURNING jsonb);
-                             QUERY PLAN
----------------------------------------------------------------------
+                       QUERY PLAN
+--------------------------------------------------------
  Result
    Output: (InitPlan 1).col1
    InitPlan 1
      ->  Aggregate
-           Output: JSON_ARRAYAGG("*VALUES*".column1 RETURNING jsonb)
-           ->  Values Scan on "*VALUES*"
-                 Output: "*VALUES*".column1
+           Output: JSON_ARRAYAGG(foo.i RETURNING jsonb)
+           ->  Values Scan on foo
+                 Output: foo.i
 (7 rows)

 CREATE VIEW json_array_subquery_view AS
diff --git a/src/test/regress/expected/subselect.out b/src/test/regress/expected/subselect.out
index 2d35de3fad..95cc0545ae 100644
--- a/src/test/regress/expected/subselect.out
+++ b/src/test/regress/expected/subselect.out
@@ -1124,7 +1124,7 @@ explain (verbose, costs off)
     (select (select now()) as x from (values(1),(2)) v(y)) ss;
                    QUERY PLAN
 ------------------------------------------------
- Values Scan on "*VALUES*"
+ Values Scan on v
    Output: (InitPlan 1).col1, (InitPlan 2).col1
    InitPlan 1
      ->  Result
@@ -1141,7 +1141,7 @@ explain (verbose, costs off)
 -----------------------------------
  Subquery Scan on ss
    Output: ss.x, ss.x
-   ->  Values Scan on "*VALUES*"
+   ->  Values Scan on v
          Output: (InitPlan 1).col1
          InitPlan 1
            ->  Result
@@ -1151,33 +1151,33 @@ explain (verbose, costs off)
 explain (verbose, costs off)
   select x, x from
     (select (select now() where y=y) as x from (values(1),(2)) v(y)) ss;
-                              QUERY PLAN
-----------------------------------------------------------------------
- Values Scan on "*VALUES*"
+               QUERY PLAN
+----------------------------------------
+ Values Scan on v
    Output: (SubPlan 1), (SubPlan 2)
    SubPlan 1
      ->  Result
            Output: now()
-           One-Time Filter: ("*VALUES*".column1 = "*VALUES*".column1)
+           One-Time Filter: (v.y = v.y)
    SubPlan 2
      ->  Result
            Output: now()
-           One-Time Filter: ("*VALUES*".column1 = "*VALUES*".column1)
+           One-Time Filter: (v.y = v.y)
 (10 rows)

 explain (verbose, costs off)
   select x, x from
     (select (select random() where y=y) as x from (values(1),(2)) v(y)) ss;
-                                 QUERY PLAN
-----------------------------------------------------------------------------
+                  QUERY PLAN
+----------------------------------------------
  Subquery Scan on ss
    Output: ss.x, ss.x
-   ->  Values Scan on "*VALUES*"
+   ->  Values Scan on v
          Output: (SubPlan 1)
          SubPlan 1
            ->  Result
                  Output: random()
-                 One-Time Filter: ("*VALUES*".column1 = "*VALUES*".column1)
+                 One-Time Filter: (v.y = v.y)
 (8 rows)

 --
@@ -1366,13 +1366,13 @@ select * from
   (3 not in (select * from (values (1), (2)) ss1)),
   (false)
 ) ss;
-               QUERY PLAN
-----------------------------------------
- Values Scan on "*VALUES*"
-   Output: "*VALUES*".column1
+          QUERY PLAN
+-------------------------------
+ Values Scan on ss
+   Output: ss.column1
    SubPlan 1
-     ->  Values Scan on "*VALUES*_1"
-           Output: "*VALUES*_1".column1
+     ->  Values Scan on ss1
+           Output: ss1.column1
 (5 rows)

 select * from
diff --git a/src/test/regress/expected/tablesample.out b/src/test/regress/expected/tablesample.out
index 9ff4611640..34b39a7153 100644
--- a/src/test/regress/expected/tablesample.out
+++ b/src/test/regress/expected/tablesample.out
@@ -251,14 +251,14 @@ select pct, count(unique1) from
   (values (0),(100)) v(pct),
   lateral (select * from tenk1 tablesample bernoulli (pct)) ss
   group by pct;
-                       QUERY PLAN
---------------------------------------------------------
+                QUERY PLAN
+-------------------------------------------
  HashAggregate
-   Group Key: "*VALUES*".column1
+   Group Key: v.pct
    ->  Nested Loop
-         ->  Values Scan on "*VALUES*"
+         ->  Values Scan on v
          ->  Sample Scan on tenk1
-               Sampling: bernoulli ("*VALUES*".column1)
+               Sampling: bernoulli (v.pct)
 (6 rows)

 select pct, count(unique1) from
diff --git a/src/test/regress/expected/union.out b/src/test/regress/expected/union.out
index c73631a9a1..19cb0cb7d5 100644
--- a/src/test/regress/expected/union.out
+++ b/src/test/regress/expected/union.out
@@ -481,27 +481,27 @@ reset enable_hashagg;
 set enable_hashagg to on;
 explain (costs off)
 select x from (values ('11'::varbit), ('10'::varbit)) _(x) union select x from (values ('11'::varbit), ('10'::varbit))
_(x);
-                  QUERY PLAN
------------------------------------------------
+              QUERY PLAN
+--------------------------------------
  Unique
    ->  Sort
-         Sort Key: "*VALUES*".column1
+         Sort Key: _.x
          ->  Append
-               ->  Values Scan on "*VALUES*"
-               ->  Values Scan on "*VALUES*_1"
+               ->  Values Scan on _
+               ->  Values Scan on __1
 (6 rows)

 set enable_hashagg to off;
 explain (costs off)
 select x from (values ('11'::varbit), ('10'::varbit)) _(x) union select x from (values ('11'::varbit), ('10'::varbit))
_(x);
-                  QUERY PLAN
------------------------------------------------
+              QUERY PLAN
+--------------------------------------
  Unique
    ->  Sort
-         Sort Key: "*VALUES*".column1
+         Sort Key: _.x
          ->  Append
-               ->  Values Scan on "*VALUES*"
-               ->  Values Scan on "*VALUES*_1"
+               ->  Values Scan on _
+               ->  Values Scan on __1
 (6 rows)

 reset enable_hashagg;
@@ -509,13 +509,13 @@ reset enable_hashagg;
 set enable_hashagg to on;
 explain (costs off)
 select x from (values (array[1, 2]), (array[1, 3])) _(x) union select x from (values (array[1, 2]), (array[1, 4]))
_(x);
-               QUERY PLAN
------------------------------------------
+           QUERY PLAN
+--------------------------------
  HashAggregate
-   Group Key: "*VALUES*".column1
+   Group Key: _.x
    ->  Append
-         ->  Values Scan on "*VALUES*"
-         ->  Values Scan on "*VALUES*_1"
+         ->  Values Scan on _
+         ->  Values Scan on __1
 (5 rows)

 select x from (values (array[1, 2]), (array[1, 3])) _(x) union select x from (values (array[1, 2]), (array[1, 4]))
_(x);
@@ -528,14 +528,14 @@ select x from (values (array[1, 2]), (array[1, 3])) _(x) union select x from (va

 explain (costs off)
 select x from (values (array[1, 2]), (array[1, 3])) _(x) intersect select x from (values (array[1, 2]), (array[1, 4]))
_(x);
-                  QUERY PLAN
------------------------------------------------
+                QUERY PLAN
+-------------------------------------------
  HashSetOp Intersect
    ->  Append
          ->  Subquery Scan on "*SELECT* 1"
-               ->  Values Scan on "*VALUES*"
+               ->  Values Scan on _
          ->  Subquery Scan on "*SELECT* 2"
-               ->  Values Scan on "*VALUES*_1"
+               ->  Values Scan on __1
 (6 rows)

 select x from (values (array[1, 2]), (array[1, 3])) _(x) intersect select x from (values (array[1, 2]), (array[1, 4]))
_(x);
@@ -546,14 +546,14 @@ select x from (values (array[1, 2]), (array[1, 3])) _(x) intersect select x from

 explain (costs off)
 select x from (values (array[1, 2]), (array[1, 3])) _(x) except select x from (values (array[1, 2]), (array[1, 4]))
_(x);
-                  QUERY PLAN
------------------------------------------------
+                QUERY PLAN
+-------------------------------------------
  HashSetOp Except
    ->  Append
          ->  Subquery Scan on "*SELECT* 1"
-               ->  Values Scan on "*VALUES*"
+               ->  Values Scan on _
          ->  Subquery Scan on "*SELECT* 2"
-               ->  Values Scan on "*VALUES*_1"
+               ->  Values Scan on __1
 (6 rows)

 select x from (values (array[1, 2]), (array[1, 3])) _(x) except select x from (values (array[1, 2]), (array[1, 4]))
_(x);
@@ -565,14 +565,14 @@ select x from (values (array[1, 2]), (array[1, 3])) _(x) except select x from (v
 -- non-hashable type
 explain (costs off)
 select x from (values (array['10'::varbit]), (array['11'::varbit])) _(x) union select x from (values
(array['10'::varbit]),(array['01'::varbit])) _(x); 
-                  QUERY PLAN
------------------------------------------------
+              QUERY PLAN
+--------------------------------------
  Unique
    ->  Sort
-         Sort Key: "*VALUES*".column1
+         Sort Key: _.x
          ->  Append
-               ->  Values Scan on "*VALUES*"
-               ->  Values Scan on "*VALUES*_1"
+               ->  Values Scan on _
+               ->  Values Scan on __1
 (6 rows)

 select x from (values (array['10'::varbit]), (array['11'::varbit])) _(x) union select x from (values
(array['10'::varbit]),(array['01'::varbit])) _(x); 
@@ -586,14 +586,14 @@ select x from (values (array['10'::varbit]), (array['11'::varbit])) _(x) union s
 set enable_hashagg to off;
 explain (costs off)
 select x from (values (array[1, 2]), (array[1, 3])) _(x) union select x from (values (array[1, 2]), (array[1, 4]))
_(x);
-                  QUERY PLAN
------------------------------------------------
+              QUERY PLAN
+--------------------------------------
  Unique
    ->  Sort
-         Sort Key: "*VALUES*".column1
+         Sort Key: _.x
          ->  Append
-               ->  Values Scan on "*VALUES*"
-               ->  Values Scan on "*VALUES*_1"
+               ->  Values Scan on _
+               ->  Values Scan on __1
 (6 rows)

 select x from (values (array[1, 2]), (array[1, 3])) _(x) union select x from (values (array[1, 2]), (array[1, 4]))
_(x);
@@ -606,16 +606,16 @@ select x from (values (array[1, 2]), (array[1, 3])) _(x) union select x from (va

 explain (costs off)
 select x from (values (array[1, 2]), (array[1, 3])) _(x) intersect select x from (values (array[1, 2]), (array[1, 4]))
_(x);
-                     QUERY PLAN
------------------------------------------------------
+                   QUERY PLAN
+-------------------------------------------------
  SetOp Intersect
    ->  Sort
          Sort Key: "*SELECT* 1".x
          ->  Append
                ->  Subquery Scan on "*SELECT* 1"
-                     ->  Values Scan on "*VALUES*"
+                     ->  Values Scan on _
                ->  Subquery Scan on "*SELECT* 2"
-                     ->  Values Scan on "*VALUES*_1"
+                     ->  Values Scan on __1
 (8 rows)

 select x from (values (array[1, 2]), (array[1, 3])) _(x) intersect select x from (values (array[1, 2]), (array[1, 4]))
_(x);
@@ -626,16 +626,16 @@ select x from (values (array[1, 2]), (array[1, 3])) _(x) intersect select x from

 explain (costs off)
 select x from (values (array[1, 2]), (array[1, 3])) _(x) except select x from (values (array[1, 2]), (array[1, 4]))
_(x);
-                     QUERY PLAN
------------------------------------------------------
+                   QUERY PLAN
+-------------------------------------------------
  SetOp Except
    ->  Sort
          Sort Key: "*SELECT* 1".x
          ->  Append
                ->  Subquery Scan on "*SELECT* 1"
-                     ->  Values Scan on "*VALUES*"
+                     ->  Values Scan on _
                ->  Subquery Scan on "*SELECT* 2"
-                     ->  Values Scan on "*VALUES*_1"
+                     ->  Values Scan on __1
 (8 rows)

 select x from (values (array[1, 2]), (array[1, 3])) _(x) except select x from (values (array[1, 2]), (array[1, 4]))
_(x);
@@ -649,14 +649,14 @@ reset enable_hashagg;
 set enable_hashagg to on;
 explain (costs off)
 select x from (values (row(1, 2)), (row(1, 3))) _(x) union select x from (values (row(1, 2)), (row(1, 4))) _(x);
-                  QUERY PLAN
------------------------------------------------
+              QUERY PLAN
+--------------------------------------
  Unique
    ->  Sort
-         Sort Key: "*VALUES*".column1
+         Sort Key: _.x
          ->  Append
-               ->  Values Scan on "*VALUES*"
-               ->  Values Scan on "*VALUES*_1"
+               ->  Values Scan on _
+               ->  Values Scan on __1
 (6 rows)

 select x from (values (row(1, 2)), (row(1, 3))) _(x) union select x from (values (row(1, 2)), (row(1, 4))) _(x);
@@ -669,16 +669,16 @@ select x from (values (row(1, 2)), (row(1, 3))) _(x) union select x from (values

 explain (costs off)
 select x from (values (row(1, 2)), (row(1, 3))) _(x) intersect select x from (values (row(1, 2)), (row(1, 4))) _(x);
-                     QUERY PLAN
------------------------------------------------------
+                   QUERY PLAN
+-------------------------------------------------
  SetOp Intersect
    ->  Sort
          Sort Key: "*SELECT* 1".x
          ->  Append
                ->  Subquery Scan on "*SELECT* 1"
-                     ->  Values Scan on "*VALUES*"
+                     ->  Values Scan on _
                ->  Subquery Scan on "*SELECT* 2"
-                     ->  Values Scan on "*VALUES*_1"
+                     ->  Values Scan on __1
 (8 rows)

 select x from (values (row(1, 2)), (row(1, 3))) _(x) intersect select x from (values (row(1, 2)), (row(1, 4))) _(x);
@@ -689,16 +689,16 @@ select x from (values (row(1, 2)), (row(1, 3))) _(x) intersect select x from (va

 explain (costs off)
 select x from (values (row(1, 2)), (row(1, 3))) _(x) except select x from (values (row(1, 2)), (row(1, 4))) _(x);
-                     QUERY PLAN
------------------------------------------------------
+                   QUERY PLAN
+-------------------------------------------------
  SetOp Except
    ->  Sort
          Sort Key: "*SELECT* 1".x
          ->  Append
                ->  Subquery Scan on "*SELECT* 1"
-                     ->  Values Scan on "*VALUES*"
+                     ->  Values Scan on _
                ->  Subquery Scan on "*SELECT* 2"
-                     ->  Values Scan on "*VALUES*_1"
+                     ->  Values Scan on __1
 (8 rows)

 select x from (values (row(1, 2)), (row(1, 3))) _(x) except select x from (values (row(1, 2)), (row(1, 4))) _(x);
@@ -712,14 +712,14 @@ select x from (values (row(1, 2)), (row(1, 3))) _(x) except select x from (value
 -- type is hashable.  (Otherwise, this would fail at execution time.)
 explain (costs off)
 select x from (values (row('10'::varbit)), (row('11'::varbit))) _(x) union select x from (values (row('10'::varbit)),
(row('01'::varbit)))_(x); 
-                  QUERY PLAN
------------------------------------------------
+              QUERY PLAN
+--------------------------------------
  Unique
    ->  Sort
-         Sort Key: "*VALUES*".column1
+         Sort Key: _.x
          ->  Append
-               ->  Values Scan on "*VALUES*"
-               ->  Values Scan on "*VALUES*_1"
+               ->  Values Scan on _
+               ->  Values Scan on __1
 (6 rows)

 select x from (values (row('10'::varbit)), (row('11'::varbit))) _(x) union select x from (values (row('10'::varbit)),
(row('01'::varbit)))_(x); 
@@ -735,14 +735,14 @@ select x from (values (row('10'::varbit)), (row('11'::varbit))) _(x) union selec
 create type ct1 as (f1 varbit);
 explain (costs off)
 select x from (values (row('10'::varbit)::ct1), (row('11'::varbit)::ct1)) _(x) union select x from (values
(row('10'::varbit)::ct1),(row('01'::varbit)::ct1)) _(x); 
-                  QUERY PLAN
------------------------------------------------
+              QUERY PLAN
+--------------------------------------
  Unique
    ->  Sort
-         Sort Key: "*VALUES*".column1
+         Sort Key: _.x
          ->  Append
-               ->  Values Scan on "*VALUES*"
-               ->  Values Scan on "*VALUES*_1"
+               ->  Values Scan on _
+               ->  Values Scan on __1
 (6 rows)

 select x from (values (row('10'::varbit)::ct1), (row('11'::varbit)::ct1)) _(x) union select x from (values
(row('10'::varbit)::ct1),(row('01'::varbit)::ct1)) _(x); 
@@ -757,14 +757,14 @@ drop type ct1;
 set enable_hashagg to off;
 explain (costs off)
 select x from (values (row(1, 2)), (row(1, 3))) _(x) union select x from (values (row(1, 2)), (row(1, 4))) _(x);
-                  QUERY PLAN
------------------------------------------------
+              QUERY PLAN
+--------------------------------------
  Unique
    ->  Sort
-         Sort Key: "*VALUES*".column1
+         Sort Key: _.x
          ->  Append
-               ->  Values Scan on "*VALUES*"
-               ->  Values Scan on "*VALUES*_1"
+               ->  Values Scan on _
+               ->  Values Scan on __1
 (6 rows)

 select x from (values (row(1, 2)), (row(1, 3))) _(x) union select x from (values (row(1, 2)), (row(1, 4))) _(x);
@@ -777,16 +777,16 @@ select x from (values (row(1, 2)), (row(1, 3))) _(x) union select x from (values

 explain (costs off)
 select x from (values (row(1, 2)), (row(1, 3))) _(x) intersect select x from (values (row(1, 2)), (row(1, 4))) _(x);
-                     QUERY PLAN
------------------------------------------------------
+                   QUERY PLAN
+-------------------------------------------------
  SetOp Intersect
    ->  Sort
          Sort Key: "*SELECT* 1".x
          ->  Append
                ->  Subquery Scan on "*SELECT* 1"
-                     ->  Values Scan on "*VALUES*"
+                     ->  Values Scan on _
                ->  Subquery Scan on "*SELECT* 2"
-                     ->  Values Scan on "*VALUES*_1"
+                     ->  Values Scan on __1
 (8 rows)

 select x from (values (row(1, 2)), (row(1, 3))) _(x) intersect select x from (values (row(1, 2)), (row(1, 4))) _(x);
@@ -797,16 +797,16 @@ select x from (values (row(1, 2)), (row(1, 3))) _(x) intersect select x from (va

 explain (costs off)
 select x from (values (row(1, 2)), (row(1, 3))) _(x) except select x from (values (row(1, 2)), (row(1, 4))) _(x);
-                     QUERY PLAN
------------------------------------------------------
+                   QUERY PLAN
+-------------------------------------------------
  SetOp Except
    ->  Sort
          Sort Key: "*SELECT* 1".x
          ->  Append
                ->  Subquery Scan on "*SELECT* 1"
-                     ->  Values Scan on "*VALUES*"
+                     ->  Values Scan on _
                ->  Subquery Scan on "*SELECT* 2"
-                     ->  Values Scan on "*VALUES*_1"
+                     ->  Values Scan on __1
 (8 rows)

 select x from (values (row(1, 2)), (row(1, 3))) _(x) except select x from (values (row(1, 2)), (row(1, 4))) _(x);
diff --git a/src/test/regress/expected/updatable_views.out b/src/test/regress/expected/updatable_views.out
index 8786058ed0..7ce7cddb5b 100644
--- a/src/test/regress/expected/updatable_views.out
+++ b/src/test/regress/expected/updatable_views.out
@@ -2988,15 +2988,15 @@ EXPLAIN (costs off)
 MERGE INTO rw_view1 t
   USING (VALUES ('Tom'), ('Dick'), ('Harry')) AS v(person) ON t.person = v.person
   WHEN MATCHED AND snoop(t.person) THEN UPDATE SET person = v.person;
-                         QUERY PLAN
--------------------------------------------------------------
+                     QUERY PLAN
+-----------------------------------------------------
  Merge on base_tbl
    ->  Nested Loop
-         Join Filter: (base_tbl.person = "*VALUES*".column1)
+         Join Filter: (base_tbl.person = v.person)
          ->  Seq Scan on base_tbl
                Filter: (visibility = 'public'::text)
          ->  Materialize
-               ->  Values Scan on "*VALUES*"
+               ->  Values Scan on v
 (7 rows)

 -- security barrier view on top of security barrier view
@@ -3090,10 +3090,10 @@ MERGE INTO rw_view2 t
 -------------------------------------------------------------------------
  Merge on base_tbl
    ->  Nested Loop
-         Join Filter: (base_tbl.person = "*VALUES*".column1)
+         Join Filter: (base_tbl.person = v.person)
          ->  Seq Scan on base_tbl
                Filter: ((visibility = 'public'::text) AND snoop(person))
-         ->  Values Scan on "*VALUES*"
+         ->  Values Scan on v
 (6 rows)

 DROP TABLE base_tbl CASCADE;
diff --git a/src/test/regress/sql/create_view.sql b/src/test/regress/sql/create_view.sql
index ae6841308b..39983481a0 100644
--- a/src/test/regress/sql/create_view.sql
+++ b/src/test/regress/sql/create_view.sql
@@ -838,6 +838,21 @@ insert into tt27v values (1); -- Error
 select viewname from pg_views where viewname = 'tt27v'; -- Ok to access a system view.
 reset restrict_nonsystem_relation_kind;

+-- test assignment of aliases to VALUES clauses
+
+create view tt28v as
+select *
+from (values (4),(2),(3),(1) order by "*VALUES*".column1 limit 2) as t1(x);
+select pg_get_viewdef('tt28v', true);
+explain (verbose, costs off) select * from tt28v where x > 0;
+
+create view tt29v as
+select *
+from (values (4),(2),(3),(1) order by "*VALUES*".column1) t1(a),
+     (values (9),(8),(6),(7) order by "*VALUES*".column1) t2(b);
+select pg_get_viewdef('tt29v', true);
+explain (verbose, costs off) select * from tt29v where a > 0;
+
 -- clean up all the random objects we made above
 DROP SCHEMA temp_view_test CASCADE;
 DROP SCHEMA testviewschm2 CASCADE;

Re: Alias of VALUES RTE in explain plan

From

Andrei Lepikhov

Date:

05 November 2024, 10:08:51

On 11/3/24 00:09, Tom Lane wrote:
> Ashutosh Bapat <ashutosh.bapat.oss@gmail.com> writes:
>> On Tue, Oct 29, 2024 at 10:49 PM Tom Lane <tgl@sss.pgh.pa.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

Re: Alias of VALUES RTE in explain plan

From

Tom Lane

Date:

08 November 2024, 04:27:30

Just to follow up here --- I put this patch on hold for a few days
because I had to work on release notes.  Now I'm glad I did, because
Robert Haas is pushing a proposal that would change the basis of
discussion:

https://www.postgresql.org/message-id/flat/CA%2BTgmoYSYmDA2GvanzPMci084n%2BmVucv0bJ0HPbs6uhmMN6HMg%40mail.gmail.com

If we do what he suggests there, then referencing "*VALUES*" in a
subquery with VALUES and ORDER BY would be broken anyway.  In that
case I'd favor committing this patch as-is, to provide users with
a well-defined new behavior.  So I think this should stay on hold
a bit longer to see what the outcome of that discussion is.

            regards, tom lane

Re: Alias of VALUES RTE in explain plan

From

Robert Haas

Date:

03 January, 01:51:46

On Sat, Nov 2, 2024 at 1:09 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> regression=# create view vv as SELECT * FROM (VALUES (4),(2),(3),(1) ORDER BY t1.x LIMIT 2) AS t1(x);
> CREATE VIEW
> regression=# \d+ vv
>                              View "public.vv"
>  Column |  Type   | Collation | Nullable | Default | Storage | Description
> --------+---------+-----------+----------+---------+---------+-------------
>  x      | integer |           |          |         | plain   |
> View definition:
>  SELECT x
>    FROM ( VALUES (4), (2), (3), (1)
>           ORDER BY t1_1.x
>          LIMIT 2) t1(x);
>
> ruleutils has decided that it needs to make the two "t1" table
> aliases distinct.  But of course that will fail on reload:
>
> 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
>                            ^
> 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'm concerned about taking things in this direction. There's two scans
here, really: a Values Scan for the VALUES construct, and then a
Subquery Scan sitting on top of it that will normally be optimized
away. It seems to me that you're basically guessing whether the
subquery scan will be optimized away to a sufficient degree that its
alias will not leak out anywhere. But that seems a bit fragile and
error-prone. Whether to elide the subquery scan is a planner decision;
what aliases to assign to the planner output is a ruleutils.c
decision; but here you're talking about rejiggering things at parse
time. The correctness of that rejiggering depends crucially on what
will happen at plan time and then at EXPLAIN/ruleutils time, but the
rules for what will happen at those later times are pretty darn
complicated, so I feel like this is creating an awful lot of action at
a distance.

If were able (and I suspect we're not, but hypothetically) to in
effect pull up the subquery at parse time, so that to the planner and
executor it doesn't even exist, then I think that would be perfectly
fine, because then we would have strong reasons for believing that no
later decision can turn our parse-time decision into a problem. But to
leave that subquery there and guess that it's going to disappear
before we get to EXPLAIN doesn't seem nearly as safe. It seems pretty
easy to either miss things (like the ORDER BY case) or even to have
future planner changes break stuff.

--
Robert Haas
EDB: http://www.enterprisedb.com

Re: Alias of VALUES RTE in explain plan

From

Tom Lane

Date:

03 January, 03:41:48

Robert Haas <robertmhaas@gmail.com> writes:
> On Sat, Nov 2, 2024 at 1:09 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> 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.

> ... The correctness of that rejiggering depends crucially on what
> will happen at plan time and then at EXPLAIN/ruleutils time, but the
> rules for what will happen at those later times are pretty darn
> complicated, so I feel like this is creating an awful lot of action at
> a distance.

I'm not seeing where there's a correctness issue here?  The parser
is charged with assigning aliases to RTEs that lack one, and with
this patch that's still true.  It's just assigning a different alias
than it did before.  There is no question of whether the alias can
"leak" out of the implicit sub-select; it cannot, by SQL's scoping
rules.  We have to avoid changing anything if there are clauses
inside the implicit sub-select that could reference the old choice
of alias, but the patch does that.  (Or we could decide to simplify
things at the cost of breaking such SQL code, since there probably
is none in the field.  It's still not clear to me which choice is
better.)

Yes, changing the parser to get an effect in ruleutils.c is indeed
action-at-a-distance-y, but the same can be said of an awful lot
of the behavior in this area.  If we were to do this differently,
we'd be breaking existing conventions like "the parser is what
initially assigns aliases", and we'd be much more likely to create
new bugs that way (cf. my criticisms upthread of the v1 patch).
So I'm not really seeing another way.

We could just reject this patch series on the grounds of "it's
not a bug and we're not going to change the behavior".  But I do
think the proposed new output looks nicer.

> If were able (and I suspect we're not, but hypothetically) to in
> effect pull up the subquery at parse time, so that to the planner and
> executor it doesn't even exist, then I think that would be perfectly
> fine, because then we would have strong reasons for believing that no
> later decision can turn our parse-time decision into a problem. But to
> leave that subquery there and guess that it's going to disappear
> before we get to EXPLAIN doesn't seem nearly as safe. It seems pretty
> easy to either miss things (like the ORDER BY case) or even to have
> future planner changes break stuff.

I completely fail to understand what you think might break.  The
planner is not especially interested in aliases, and ruleutils already
has sufficient defenses against cases like duplicate aliases --- it
must, because such cases can arise anyway.

            regards, tom lane

Re: Alias of VALUES RTE in explain plan

From

Robert Haas

Date:

06 January, 23:07:38

On Thu, Jan 2, 2025 at 4:41 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> I'm not seeing where there's a correctness issue here?  The parser
> is charged with assigning aliases to RTEs that lack one, and with
> this patch that's still true.  It's just assigning a different alias
> than it did before.  There is no question of whether the alias can
> "leak" out of the implicit sub-select; it cannot, by SQL's scoping
> rules.  We have to avoid changing anything if there are clauses
> inside the implicit sub-select that could reference the old choice
> of alias, but the patch does that.

Hmm, maybe you're right. I think when I looked at this initially, I
thought the problem had to do with conflating the plan choice, but
looking at it again, it looks like more of a
using-the-alias-before-it's-defined problem, which I guess is easy
enough to fix without much action at a distance.

> (Or we could decide to simplify
> things at the cost of breaking such SQL code, since there probably
> is none in the field.  It's still not clear to me which choice is
> better.)

This part I don't understand.

--
Robert Haas
EDB: http://www.enterprisedb.com

Re: Alias of VALUES RTE in explain plan

From

Tom Lane

Date:

07 January, 02:45:37

Robert Haas <robertmhaas@gmail.com> writes:
> On Thu, Jan 2, 2025 at 4:41 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> (Or we could decide to simplify
>> things at the cost of breaking such SQL code, since there probably
>> is none in the field.  It's still not clear to me which choice is
>> better.)

> This part I don't understand.

Sorry, not enough context there.  The two alternatives that I think
are on the table now are the v2 patch [1] and the v3 patch [2].
(v2 lacks some test cases that are in v3, but here I'm just talking
about the code differences.)  The difference between them is that
given

    SELECT ... FROM (VALUES (...)) v(a,b,c)

v2 always pushes the outer query's alias "v(a,b,c)" down to the
implicit subquery's VALUES RTE.  But v3 does so only if the
implicit subquery contains no ORDER BY, that is a behavioral
difference appears for

    SELECT ... FROM (VALUES (...) ORDER BY column1) v(a,b,c)

With v3, if you write the latter then EXPLAIN will still talk about
"*VALUES*" and denote its columns as column1 etc, which is
inconsistent with what happens without an ORDER BY.  If we use v2 then
queries like this example will start to fail because they will use
the wrong names for the VALUES columns.  Now, you could adapt such a
query easily enough:

    SELECT ... FROM (VALUES (...) ORDER BY a) v(a,b,c)

If it'd been like that all along, nobody would blink at it I think,
even though you could argue that it's action-at-a-distance to let
an outer alias affect what happens inside the implicit subquery.

I'm fairly sure that the SQL spec says that such unspecified column
aliases are implementation-defined or maybe even
implementation-dependent, so that there's no issue of standards
compliance here: we can do what we please as far as the spec is
concerned.

So the question is: are there enough people using this sort of
query to justify our worrying about preserving compatibility
for it?  It's a mighty weird construct, but AFAICS it's legal
per spec, even though the spec doesn't tell you how you can
name the VALUES' columns in that ORDER BY.

            regards, tom lane

[1] https://www.postgresql.org/message-id/3002521.1729877700%40sss.pgh.pa.us
[2] https://www.postgresql.org/message-id/2192705.1730567369%40sss.pgh.pa.us

Re: Alias of VALUES RTE in explain plan

From

Robert Haas

Date:

07 January, 03:15:21

On Mon, Jan 6, 2025 at 3:45 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>         SELECT ... FROM (VALUES (...) ORDER BY a) v(a,b,c)
>
> If it'd been like that all along, nobody would blink at it I think,
> even though you could argue that it's action-at-a-distance to let
> an outer alias affect what happens inside the implicit subquery.

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. It doesn't really
seem evil in a vacuum, but it seems non-orthogonal with what we do
everywhere else, because aliases in SQL generally flow outward, not
inward.

--
Robert Haas
EDB: http://www.enterprisedb.com

Re: Alias of VALUES RTE in explain plan

From

Tom Lane

Date:

07 January, 04:21:55

Robert Haas <robertmhaas@gmail.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

Re: Alias of VALUES RTE in explain plan

From

Tom Lane

Date:

07 January, 04:46:19

BTW, it suddenly strikes me that if anyone actually is using this
syntax in the field, they're most likely doing it like this:

regression=# create view v as
regression-# select * from (values (1),(2),(3) order by 1) v(x);
CREATE VIEW

which nicely sidesteps the question of what column aliases apply.
However, ruleutils.c unhelpfully regurgitates that as

regression=# \d+ v
                              View "public.v"
 Column |  Type   | Collation | Nullable | Default | Storage | Description 
--------+---------+-----------+----------+---------+---------+-------------
 x      | integer |           |          |         | plain   | 
View definition:
 SELECT x
   FROM ( VALUES (1), (2), (3)
          ORDER BY "*VALUES*".column1) v(x);

Maybe we could teach it that in this one case, using column numbers
is a better idea.  It's not SQL-spec anymore (since SQL99 anyway).
But the SQL spec has noplace to hide here, because they don't say
what column names you could use in this misbegotten but
spec-sanctioned syntax.

            regards, tom lane