Re: Alias of VALUES RTE in explain plan - Mailing list pgsql-hackers
| From | Tom Lane |
|---|---|
| Subject | Re: Alias of VALUES RTE in explain plan |
| Date | |
| Msg-id | 2192705.1730567369@sss.pgh.pa.us Whole thread Raw |
| In response to | Re: Alias of VALUES RTE in explain plan (Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>) |
| Responses |
Re: Alias of VALUES RTE in explain plan
|
| List | pgsql-hackers |
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;
pgsql-hackers by date: