Re: Improving EXPLAIN's display of SubPlan nodes
| От | Tom Lane |
|---|---|
| Тема | Re: Improving EXPLAIN's display of SubPlan nodes |
| Дата | |
| Msg-id | 2982030.1710609907@sss.pgh.pa.us обсуждение исходный текст |
| Ответ на | Re: Improving EXPLAIN's display of SubPlan nodes (Tom Lane <tgl@sss.pgh.pa.us>) |
| Ответы |
Re: Improving EXPLAIN's display of SubPlan nodes
(Dean Rasheed <dean.a.rasheed@gmail.com>)
|
| Список | pgsql-hackers |
I wrote:
> Dean Rasheed <dean.a.rasheed@gmail.com> writes:
>> One thing that concerns me about making even greater use of "$n" is
>> the potential for confusion with generic plan parameters.
> True.
After looking at your draft some more, it occurred to me that we're
not that far from getting rid of showing "$n" entirely in this
context. Instead of (subplan_name).$n, we could write something like
(subplan_name).colN or (subplan_name).columnN or (subplan_name).fN,
depending on your taste for verboseness. "fN" would correspond to the
names we assign to columns of anonymous record types, but it hasn't
got much else to recommend it. In the attached I used "colN";
"columnN" would be my next choice.
You could also imagine trying to use the sub-SELECT's actual output
column names, but I fear that would be ambiguous --- too often it'd
be "?column?" or some other non-unique name.
> Hmm. I guess what bothers me about that is that it could be read to
> suggest that the initplan or subplan is evaluated again for each
> output parameter.
This objection seems like it could be solved through documentation,
so I wrote some.
The attached proof-of-concept is incomplete: it fails to replace some
$n occurrences with subplan references, as is visible in some of the
test cases. I believe your quick hack in get_parameter() is not
correct in detail, but for the moment I didn't bother to debug it.
I'm just presenting this as a POC to see if this is the direction
people would like to go in. If there's not objections, I'll try to
make a bulletproof implementation.
regards, tom lane
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index 58a603ac56..585ba60ae3 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -3062,10 +3062,10 @@ select exists(select 1 from pg_enum), sum(c1) from ft1;
QUERY PLAN
--------------------------------------------------
Foreign Scan
- Output: $0, (sum(ft1.c1))
+ Output: (InitPlan 1).col1, (sum(ft1.c1))
Relations: Aggregate on (public.ft1)
Remote SQL: SELECT sum("C 1") FROM "S 1"."T 1"
- InitPlan 1 (returns $0)
+ InitPlan 1
-> Seq Scan on pg_catalog.pg_enum
(6 rows)
@@ -3080,8 +3080,8 @@ select exists(select 1 from pg_enum), sum(c1) from ft1 group by 1;
QUERY PLAN
---------------------------------------------------
GroupAggregate
- Output: $0, sum(ft1.c1)
- InitPlan 1 (returns $0)
+ Output: (InitPlan 1).col1, sum(ft1.c1)
+ InitPlan 1
-> Seq Scan on pg_catalog.pg_enum
-> Foreign Scan on public.ft1
Output: ft1.c1
@@ -3305,10 +3305,10 @@ select sum(c1) filter (where (c1 / c1) * random() <= 1) from ft1 group by c2 ord
explain (verbose, costs off)
select sum(c2) filter (where c2 in (select c2 from ft1 where c2 < 5)) from ft1;
- QUERY PLAN
--------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------------
Aggregate
- Output: sum(ft1.c2) FILTER (WHERE (hashed SubPlan 1))
+ Output: sum(ft1.c2) FILTER (WHERE (ANY (ft1.c2 = (hashed SubPlan 1).col1)))
-> Foreign Scan on public.ft1
Output: ft1.c2
Remote SQL: SELECT c2 FROM "S 1"."T 1"
@@ -6171,9 +6171,9 @@ UPDATE ft2 AS target SET (c2, c7) = (
Update on public.ft2 target
Remote SQL: UPDATE "S 1"."T 1" SET c2 = $2, c7 = $3 WHERE ctid = $1
-> Foreign Scan on public.ft2 target
- Output: $1, $2, (SubPlan 1 (returns $1,$2)), target.ctid, target.*
+ Output: $1, $2, (SubPlan 1), target.ctid, target.*
Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8, ctid FROM "S 1"."T 1" WHERE (("C 1" > 1100)) FOR UPDATE
- SubPlan 1 (returns $1,$2)
+ SubPlan 1
-> Foreign Scan on public.ft2 src
Output: (src.c2 * 10), src.c7
Remote SQL: SELECT c2, c7 FROM "S 1"."T 1" WHERE (($1::integer = "C 1"))
@@ -11687,7 +11687,7 @@ SELECT * FROM local_tbl t1 LEFT JOIN (SELECT *, (SELECT count(*) FROM async_pt W
Nested Loop Left Join
Output: t1.a, t1.b, t1.c, async_pt.a, async_pt.b, async_pt.c, ($0)
Join Filter: (t1.a = async_pt.a)
- InitPlan 1 (returns $0)
+ InitPlan 1
-> Aggregate
Output: count(*)
-> Append
@@ -11713,7 +11713,7 @@ SELECT * FROM local_tbl t1 LEFT JOIN (SELECT *, (SELECT count(*) FROM async_pt W
Nested Loop Left Join (actual rows=1 loops=1)
Join Filter: (t1.a = async_pt.a)
Rows Removed by Join Filter: 399
- InitPlan 1 (returns $0)
+ InitPlan 1
-> Aggregate (actual rows=1 loops=1)
-> Append (actual rows=400 loops=1)
-> Async Foreign Scan on async_p1 async_pt_4 (actual rows=200 loops=1)
@@ -11936,11 +11936,11 @@ CREATE FOREIGN TABLE foreign_tbl2 () INHERITS (foreign_tbl)
SERVER loopback OPTIONS (table_name 'base_tbl');
EXPLAIN (VERBOSE, COSTS OFF)
SELECT a FROM base_tbl WHERE (a, random() > 0) IN (SELECT a, random() > 0 FROM foreign_tbl);
- QUERY PLAN
------------------------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------------
Seq Scan on public.base_tbl
Output: base_tbl.a
- Filter: (SubPlan 1)
+ Filter: (ANY ((base_tbl.a = (SubPlan 1).col1) AND ((random() > '0'::double precision) = (SubPlan 1).col2)))
SubPlan 1
-> Result
Output: base_tbl.a, (random() > '0'::double precision)
diff --git a/doc/src/sgml/perform.sgml b/doc/src/sgml/perform.sgml
index 90822b3f4c..a3ed0aff1f 100644
--- a/doc/src/sgml/perform.sgml
+++ b/doc/src/sgml/perform.sgml
@@ -573,8 +573,70 @@ WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;
which shows that the planner thinks that sorting <literal>onek</literal> by
index-scanning is about 12% more expensive than sequential-scan-and-sort.
Of course, the next question is whether it's right about that.
- We can investigate that using <command>EXPLAIN ANALYZE</command>, as discussed
- below.
+ We can investigate that using <command>EXPLAIN ANALYZE</command>, as
+ discussed <link linkend="using-explain-analyze">below</link>.
+ </para>
+
+ <para>
+ Some query plans involve <firstterm>subplans</firstterm>, which arise
+ from sub-<literal>SELECT</literal>s in the original query. Such
+ queries can sometimes be transformed into ordinary join plans, but
+ when they cannot be, we get plans like:
+
+<screen>
+EXPLAIN VERBOSE SELECT unique1
+FROM tenk1 t1 WHERE ROW(1, 2) = (SELECT t1.ten, t1.four);
+
+ QUERY PLAN
+------------------------------------------------------------&zwsp;--------
+ Seq Scan on public.tenk1 t1 (cost=0.00..595.00 rows=5000 width=4)
+ Output: t1.unique1
+ Filter: (((1 = (SubPlan 1).col1) AND (2 = (SubPlan 1).col2)))
+ SubPlan 1
+ -> Result (cost=0.00..0.01 rows=1 width=8)
+ Output: t1.ten, t1.four
+</screen>
+
+ This simple example is artificial, of course; it could easily be
+ rewritten to not use a sub-<literal>SELECT</literal>. But it serves
+ to illustrate a couple of points: values from the outer plan level can
+ be passed down into a subplan (here, <literal>t1.ten</literal>
+ and <literal>t1.four</literal> are passed down) and the results of the
+ sub-select are available to the outer plan. Those result values are
+ shown by <command>EXPLAIN</command> with notations like
+ <literal>(<replaceable>subplan_name</replaceable>).col<replaceable>N</replaceable></literal>,
+ which refers to the <replaceable>N</replaceable>'th output column of
+ the sub-<literal>SELECT</literal>.
+ </para>
+
+ <para>
+ <indexterm>
+ <primary>initplan</primary>
+ </indexterm>
+ A subplan like the above is run once for each row processed by the
+ outer plan. However, if the sub-<literal>SELECT</literal> does not
+ reference any variables of the outer query, it may instead be
+ implemented as an <firstterm>initplan</firstterm>:
+
+<screen>
+EXPLAIN VERBOSE SELECT unique1
+FROM tenk1 t1 WHERE t1.ten = (SELECT (random() * 10)::integer);
+
+ QUERY PLAN
+------------------------------------------------------------&zwsp;--------
+ Seq Scan on public.tenk1 t1 (cost=0.02..470.02 rows=1000 width=4)
+ Output: t1.unique1
+ Filter: (t1.ten = (InitPlan 1).col1)
+ InitPlan 1
+ -> Result (cost=0.00..0.02 rows=1 width=4)
+ Output: ((random() * '10'::double precision))::integer
+</screen>
+
+ An initplan is run only once per execution of the outer plan, so in
+ this example <literal>random()</literal> is evaluated only once and
+ all the values of <literal>t1.ten</literal> are compared to the same
+ randomly-chosen integer. That's quite different from what would
+ happen without the sub-<literal>SELECT</literal> construct.
</para>
</sect2>
diff --git a/src/backend/optimizer/plan/subselect.c b/src/backend/optimizer/plan/subselect.c
index 47e14723d2..d4360c5659 100644
--- a/src/backend/optimizer/plan/subselect.c
+++ b/src/backend/optimizer/plan/subselect.c
@@ -560,22 +560,9 @@ build_subplan(PlannerInfo *root, Plan *plan, PlannerInfo *subroot,
splan->plan_id);
/* Label the subplan for EXPLAIN purposes */
- splan->plan_name = palloc(32 + 12 * list_length(splan->setParam));
- sprintf(splan->plan_name, "%s %d",
- isInitPlan ? "InitPlan" : "SubPlan",
- splan->plan_id);
- if (splan->setParam)
- {
- char *ptr = splan->plan_name + strlen(splan->plan_name);
-
- ptr += sprintf(ptr, " (returns ");
- foreach(lc, splan->setParam)
- {
- ptr += sprintf(ptr, "$%d%s",
- lfirst_int(lc),
- lnext(splan->setParam, lc) ? "," : ")");
- }
- }
+ splan->plan_name = psprintf("%s %d",
+ isInitPlan ? "InitPlan" : "SubPlan",
+ splan->plan_id);
/* Lastly, fill in the cost estimates for use later */
cost_subplan(root, splan, plan);
@@ -3040,8 +3027,7 @@ SS_make_initplan_from_plan(PlannerInfo *root,
node = makeNode(SubPlan);
node->subLinkType = EXPR_SUBLINK;
node->plan_id = list_length(root->glob->subplans);
- node->plan_name = psprintf("InitPlan %d (returns $%d)",
- node->plan_id, prm->paramid);
+ node->plan_name = psprintf("InitPlan %d", node->plan_id);
get_first_col_type(plan, &node->firstColType, &node->firstColTypmod,
&node->firstColCollation);
node->parallel_safe = plan->parallel_safe;
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 2a1ee69970..3fc9d2ca0d 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -171,6 +171,7 @@ typedef struct
List *using_names; /* List of assigned names for USING columns */
/* Remaining fields are used only when deparsing a Plan tree: */
Plan *plan; /* immediate parent of current expression */
+ SubPlan *in_subplan; /* subplan containing current expression */
List *ancestors; /* ancestors of plan */
Plan *outer_plan; /* outer subnode, or NULL if none */
Plan *inner_plan; /* inner subnode, or NULL if none */
@@ -8184,6 +8185,84 @@ get_parameter(Param *param, deparse_context *context)
return;
}
+ /* Is it a subplan output? */
+ if (param->paramkind == PARAM_EXEC)
+ {
+ SubPlan *subplan = NULL;
+ int colno = 0;
+
+ dpns = (deparse_namespace *) linitial(context->namespaces);
+
+ /* subplan containing this expression? */
+ if (dpns->in_subplan)
+ {
+ if (dpns->in_subplan->setParam)
+ {
+ foreach_int(paramid, dpns->in_subplan->setParam)
+ {
+ if (paramid == param->paramid)
+ {
+ subplan = dpns->in_subplan;
+ colno = foreach_current_index(paramid);
+ break;
+ }
+ }
+ }
+ else if (dpns->in_subplan->paramIds)
+ {
+ foreach_int(paramid, dpns->in_subplan->paramIds)
+ {
+ if (paramid == param->paramid)
+ {
+ subplan = dpns->in_subplan;
+ colno = foreach_current_index(paramid);
+ break;
+ }
+ }
+ }
+ }
+
+ /* else initplan output? */
+ if (subplan == NULL)
+ {
+ foreach_node(SubPlan, initplan, dpns->plan->initPlan)
+ {
+ if (initplan->setParam)
+ {
+ foreach_int(paramid, initplan->setParam)
+ {
+ if (paramid == param->paramid)
+ {
+ subplan = initplan;
+ colno = foreach_current_index(paramid);
+ break;
+ }
+ }
+ }
+ else if (initplan->paramIds)
+ {
+ foreach_int(paramid, initplan->paramIds)
+ {
+ if (paramid == param->paramid)
+ {
+ subplan = initplan;
+ colno = foreach_current_index(paramid);
+ break;
+ }
+ }
+ }
+ }
+ }
+
+ if (subplan)
+ {
+ appendStringInfo(context->buf, "(%s%s).col%d",
+ subplan->useHashTable ? "hashed " : "",
+ subplan->plan_name, colno + 1);
+ return;
+ }
+ }
+
/*
* If it's an external parameter, see if the outermost namespace provides
* function argument names.
@@ -8863,17 +8942,68 @@ get_rule_expr(Node *node, deparse_context *context,
case T_SubPlan:
{
SubPlan *subplan = (SubPlan *) node;
+ bool show_subplan_name = true;
+ deparse_namespace *dpns;
/*
* We cannot see an already-planned subplan in rule deparsing,
* only while EXPLAINing a query plan. We don't try to
* reconstruct the original SQL, just reference the subplan
- * that appears elsewhere in EXPLAIN's result.
+ * that appears elsewhere in EXPLAIN's result. It does seem
+ * useful to show the subLinkType and testexpr, however, and
+ * we also note whether the subplan will be hashed.
*/
- if (subplan->useHashTable)
- appendStringInfo(buf, "(hashed %s)", subplan->plan_name);
- else
- appendStringInfo(buf, "(%s)", subplan->plan_name);
+ dpns = linitial(context->namespaces);
+ dpns->in_subplan = subplan;
+
+ switch (subplan->subLinkType)
+ {
+ case EXISTS_SUBLINK:
+ appendStringInfoString(buf, "EXISTS(");
+ Assert(subplan->testexpr == NULL);
+ break;
+ case ALL_SUBLINK:
+ appendStringInfoString(buf, "(ALL ");
+ get_rule_expr(subplan->testexpr, context, showimplicit);
+ appendStringInfoString(buf, ")");
+ show_subplan_name = false;
+ break;
+ case ANY_SUBLINK:
+ appendStringInfoString(buf, "(ANY ");
+ get_rule_expr(subplan->testexpr, context, showimplicit);
+ appendStringInfoString(buf, ")");
+ show_subplan_name = false;
+ break;
+ case ROWCOMPARE_SUBLINK:
+ appendStringInfoString(buf, "(");
+ get_rule_expr(subplan->testexpr, context, showimplicit);
+ appendStringInfoString(buf, ")");
+ show_subplan_name = false;
+ break;
+ case EXPR_SUBLINK:
+ case MULTIEXPR_SUBLINK:
+ /* No need to decorate these subplan references */
+ appendStringInfoString(buf, "(");
+ Assert(subplan->testexpr == NULL);
+ break;
+ case ARRAY_SUBLINK:
+ appendStringInfoString(buf, "ARRAY(");
+ Assert(subplan->testexpr == NULL);
+ break;
+ case CTE_SUBLINK:
+ /* This case is unreachable within expressions */
+ appendStringInfoString(buf, "CTE(");
+ Assert(subplan->testexpr == NULL);
+ break;
+ }
+ dpns->in_subplan = NULL;
+ if (show_subplan_name)
+ {
+ if (subplan->useHashTable)
+ appendStringInfo(buf, "hashed %s)", subplan->plan_name);
+ else
+ appendStringInfo(buf, "%s)", subplan->plan_name);
+ }
}
break;
diff --git a/src/test/regress/expected/aggregates.out b/src/test/regress/expected/aggregates.out
index f86cf8d258..c838e006f6 100644
--- a/src/test/regress/expected/aggregates.out
+++ b/src/test/regress/expected/aggregates.out
@@ -738,7 +738,7 @@ select array(select sum(x+y) s
QUERY PLAN
-------------------------------------------------------------------
Function Scan on pg_catalog.generate_series x
- Output: (SubPlan 1)
+ Output: ARRAY(SubPlan 1)
Function Call: generate_series(1, 3)
SubPlan 1
-> Sort
@@ -915,7 +915,7 @@ explain (costs off)
QUERY PLAN
------------------------------------------------------------
Result
- InitPlan 1 (returns $0)
+ InitPlan 1
-> Limit
-> Index Only Scan using tenk1_unique1 on tenk1
Index Cond: (unique1 IS NOT NULL)
@@ -932,7 +932,7 @@ explain (costs off)
QUERY PLAN
---------------------------------------------------------------------
Result
- InitPlan 1 (returns $0)
+ InitPlan 1
-> Limit
-> Index Only Scan Backward using tenk1_unique1 on tenk1
Index Cond: (unique1 IS NOT NULL)
@@ -949,7 +949,7 @@ explain (costs off)
QUERY PLAN
------------------------------------------------------------------------
Result
- InitPlan 1 (returns $0)
+ InitPlan 1
-> Limit
-> Index Only Scan Backward using tenk1_unique1 on tenk1
Index Cond: ((unique1 IS NOT NULL) AND (unique1 < 42))
@@ -966,7 +966,7 @@ explain (costs off)
QUERY PLAN
------------------------------------------------------------------------
Result
- InitPlan 1 (returns $0)
+ InitPlan 1
-> Limit
-> Index Only Scan Backward using tenk1_unique1 on tenk1
Index Cond: ((unique1 IS NOT NULL) AND (unique1 > 42))
@@ -989,7 +989,7 @@ explain (costs off)
QUERY PLAN
---------------------------------------------------------------------------
Result
- InitPlan 1 (returns $0)
+ InitPlan 1
-> Limit
-> Index Only Scan Backward using tenk1_unique1 on tenk1
Index Cond: ((unique1 IS NOT NULL) AND (unique1 > 42000))
@@ -1008,7 +1008,7 @@ explain (costs off)
QUERY PLAN
----------------------------------------------------------------------------
Result
- InitPlan 1 (returns $0)
+ InitPlan 1
-> Limit
-> Index Only Scan Backward using tenk1_thous_tenthous on tenk1
Index Cond: ((thousand = 33) AND (tenthous IS NOT NULL))
@@ -1025,7 +1025,7 @@ explain (costs off)
QUERY PLAN
--------------------------------------------------------------------------
Result
- InitPlan 1 (returns $0)
+ InitPlan 1
-> Limit
-> Index Only Scan using tenk1_thous_tenthous on tenk1
Index Cond: ((thousand = 33) AND (tenthous IS NOT NULL))
@@ -1046,7 +1046,7 @@ explain (costs off)
Seq Scan on int4_tbl
SubPlan 2
-> Result
- InitPlan 1 (returns $1)
+ InitPlan 1
-> Limit
-> Index Only Scan using tenk1_unique1 on tenk1
Index Cond: ((unique1 IS NOT NULL) AND (unique1 > int4_tbl.f1))
@@ -1070,7 +1070,7 @@ explain (costs off)
---------------------------------------------------------------------
HashAggregate
Group Key: $0
- InitPlan 1 (returns $0)
+ InitPlan 1
-> Limit
-> Index Only Scan Backward using tenk1_unique2 on tenk1
Index Cond: (unique2 IS NOT NULL)
@@ -1089,7 +1089,7 @@ explain (costs off)
---------------------------------------------------------------------
Sort
Sort Key: ($0)
- InitPlan 1 (returns $0)
+ InitPlan 1
-> Limit
-> Index Only Scan Backward using tenk1_unique2 on tenk1
Index Cond: (unique2 IS NOT NULL)
@@ -1108,7 +1108,7 @@ explain (costs off)
---------------------------------------------------------------------
Sort
Sort Key: ($0)
- InitPlan 1 (returns $0)
+ InitPlan 1
-> Limit
-> Index Only Scan Backward using tenk1_unique2 on tenk1
Index Cond: (unique2 IS NOT NULL)
@@ -1127,7 +1127,7 @@ explain (costs off)
---------------------------------------------------------------------
Sort
Sort Key: (($0 + 1))
- InitPlan 1 (returns $0)
+ InitPlan 1
-> Limit
-> Index Only Scan Backward using tenk1_unique2 on tenk1
Index Cond: (unique2 IS NOT NULL)
@@ -1146,7 +1146,7 @@ explain (costs off)
---------------------------------------------------------------------
Sort
Sort Key: (generate_series(1, 3)) DESC
- InitPlan 1 (returns $0)
+ InitPlan 1
-> Limit
-> Index Only Scan Backward using tenk1_unique2 on tenk1
Index Cond: (unique2 IS NOT NULL)
@@ -1168,7 +1168,7 @@ explain (costs off)
QUERY PLAN
----------------------------------------------------
Result
- InitPlan 1 (returns $0)
+ InitPlan 1
-> Limit
-> Result
One-Time Filter: (100 IS NOT NULL)
@@ -1199,7 +1199,7 @@ explain (costs off)
QUERY PLAN
---------------------------------------------------------------------------------------------
Result
- InitPlan 1 (returns $0)
+ InitPlan 1
-> Limit
-> Merge Append
Sort Key: minmaxtest.f1
@@ -1210,7 +1210,7 @@ explain (costs off)
-> Index Only Scan Backward using minmaxtest2i on minmaxtest2 minmaxtest_3
Index Cond: (f1 IS NOT NULL)
-> Index Only Scan using minmaxtest3i on minmaxtest3 minmaxtest_4
- InitPlan 2 (returns $1)
+ InitPlan 2
-> Limit
-> Merge Append
Sort Key: minmaxtest_5.f1 DESC
@@ -1235,7 +1235,7 @@ explain (costs off)
QUERY PLAN
---------------------------------------------------------------------------------------------
Unique
- InitPlan 1 (returns $0)
+ InitPlan 1
-> Limit
-> Merge Append
Sort Key: minmaxtest.f1
@@ -1246,7 +1246,7 @@ explain (costs off)
-> Index Only Scan Backward using minmaxtest2i on minmaxtest2 minmaxtest_3
Index Cond: (f1 IS NOT NULL)
-> Index Only Scan using minmaxtest3i on minmaxtest3 minmaxtest_4
- InitPlan 2 (returns $1)
+ InitPlan 2
-> Limit
-> Merge Append
Sort Key: minmaxtest_5.f1 DESC
diff --git a/src/test/regress/expected/groupingsets.out b/src/test/regress/expected/groupingsets.out
index a3b9aaca84..7c8094f7c7 100644
--- a/src/test/regress/expected/groupingsets.out
+++ b/src/test/regress/expected/groupingsets.out
@@ -559,7 +559,7 @@ explain (costs off)
QUERY PLAN
------------------------------------------------------------
Result
- InitPlan 1 (returns $0)
+ InitPlan 1
-> Limit
-> Index Only Scan using tenk1_unique1 on tenk1
Index Cond: (unique1 IS NOT NULL)
@@ -2109,14 +2109,14 @@ order by a, b, c;
-- test handling of outer GroupingFunc within subqueries
explain (costs off)
select (select grouping(v1)) from (values ((select 1))) v(v1) group by cube(v1);
- QUERY PLAN
----------------------------
+ QUERY PLAN
+-----------------
MixedAggregate
Hash Key: $2
Group Key: ()
- InitPlan 1 (returns $1)
+ InitPlan 1
-> Result
- InitPlan 3 (returns $2)
+ InitPlan 3
-> Result
-> Result
SubPlan 2
@@ -2132,12 +2132,12 @@ select (select grouping(v1)) from (values ((select 1))) v(v1) group by cube(v1);
explain (costs off)
select (select grouping(v1)) from (values ((select 1))) v(v1) group by v1;
- QUERY PLAN
----------------------------
+ QUERY PLAN
+----------------
GroupAggregate
- InitPlan 1 (returns $1)
+ InitPlan 1
-> Result
- InitPlan 3 (returns $2)
+ InitPlan 3
-> Result
-> Result
SubPlan 2
diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out
index 130a924228..e0dbfb83a9 100644
--- a/src/test/regress/expected/inherit.out
+++ b/src/test/regress/expected/inherit.out
@@ -1643,8 +1643,8 @@ explain (verbose, costs off) select min(1-id) from matest0;
QUERY PLAN
---------------------------------------------------------------------------------
Result
- Output: $0
- InitPlan 1 (returns $0)
+ Output: (InitPlan 1).col1
+ InitPlan 1
-> Limit
Output: ((1 - matest0.id))
-> Result
@@ -1800,7 +1800,7 @@ SELECT min(x) FROM
QUERY PLAN
--------------------------------------------------------------------
Result
- InitPlan 1 (returns $0)
+ InitPlan 1
-> Limit
-> Merge Append
Sort Key: a.unique1
@@ -1818,7 +1818,7 @@ SELECT min(y) FROM
QUERY PLAN
--------------------------------------------------------------------
Result
- InitPlan 1 (returns $0)
+ InitPlan 1
-> Limit
-> Merge Append
Sort Key: a.unique1
@@ -1898,19 +1898,19 @@ insert into inhpar select x, x::text from generate_series(1,5) x;
insert into inhcld select x::text, x from generate_series(6,10) x;
explain (verbose, costs off)
update inhpar i set (f1, f2) = (select i.f1, i.f2 || '-' from int4_tbl limit 1);
- QUERY PLAN
--------------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------
Update on public.inhpar i
Update on public.inhpar i_1
Update on public.inhcld i_2
-> Result
- Output: $2, $3, (SubPlan 1 (returns $2,$3)), i.tableoid, i.ctid
+ Output: $2, $3, (SubPlan 1), i.tableoid, i.ctid
-> Append
-> Seq Scan on public.inhpar i_1
Output: i_1.f1, i_1.f2, i_1.tableoid, i_1.ctid
-> Seq Scan on public.inhcld i_2
Output: i_2.f1, i_2.f2, i_2.tableoid, i_2.ctid
- SubPlan 1 (returns $2,$3)
+ SubPlan 1
-> Limit
Output: (i.f1), (((i.f2)::text || '-'::text))
-> Seq Scan on public.int4_tbl
@@ -1946,21 +1946,21 @@ alter table inhpar attach partition inhcld2 for values from (5) to (100);
insert into inhpar select x, x::text from generate_series(1,10) x;
explain (verbose, costs off)
update inhpar i set (f1, f2) = (select i.f1, i.f2 || '-' from int4_tbl limit 1);
- QUERY PLAN
------------------------------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------------
Update on public.inhpar i
Update on public.inhcld1 i_1
Update on public.inhcld2 i_2
-> Append
-> Seq Scan on public.inhcld1 i_1
- Output: $2, $3, (SubPlan 1 (returns $2,$3)), i_1.tableoid, i_1.ctid
- SubPlan 1 (returns $2,$3)
+ Output: $2, $3, (SubPlan 1), i_1.tableoid, i_1.ctid
+ SubPlan 1
-> Limit
Output: (i_1.f1), (((i_1.f2)::text || '-'::text))
-> Seq Scan on public.int4_tbl
Output: i_1.f1, ((i_1.f2)::text || '-'::text)
-> Seq Scan on public.inhcld2 i_2
- Output: $2, $3, (SubPlan 1 (returns $2,$3)), i_2.tableoid, i_2.ctid
+ Output: $2, $3, (SubPlan 1), i_2.tableoid, i_2.ctid
(13 rows)
update inhpar i set (f1, f2) = (select i.f1, i.f2 || '-' from int4_tbl limit 1);
@@ -2845,11 +2845,11 @@ explain (costs off) select min(a), max(a) from parted_minmax where b = '12345';
QUERY PLAN
------------------------------------------------------------------------------------------------
Result
- InitPlan 1 (returns $0)
+ InitPlan 1
-> Limit
-> Index Only Scan using parted_minmax1i on parted_minmax1 parted_minmax
Index Cond: ((a IS NOT NULL) AND (b = '12345'::text))
- InitPlan 2 (returns $1)
+ InitPlan 2
-> Limit
-> Index Only Scan Backward using parted_minmax1i on parted_minmax1 parted_minmax_1
Index Cond: ((a IS NOT NULL) AND (b = '12345'::text))
diff --git a/src/test/regress/expected/insert_conflict.out b/src/test/regress/expected/insert_conflict.out
index 563c5eb52a..701217ddbc 100644
--- a/src/test/regress/expected/insert_conflict.out
+++ b/src/test/regress/expected/insert_conflict.out
@@ -50,7 +50,7 @@ explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on con
Insert on insertconflicttest
Conflict Resolution: UPDATE
Conflict Arbiter Indexes: op_index_key, collation_index_key, both_index_key
- Conflict Filter: (SubPlan 1)
+ Conflict Filter: EXISTS(SubPlan 1)
-> Result
SubPlan 1
-> Index Only Scan using both_index_expr_key on insertconflicttest ii
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index 9605400021..4cb22f783b 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -3028,10 +3028,10 @@ where unique1 in (select unique2 from tenk1 b);
explain (costs off)
select a.* from tenk1 a
where unique1 not in (select unique2 from tenk1 b);
- QUERY PLAN
---------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------
Seq Scan on tenk1 a
- Filter: (NOT (hashed SubPlan 1))
+ Filter: (NOT (ANY (unique1 = (hashed SubPlan 1).col1)))
SubPlan 1
-> Index Only Scan using tenk1_unique2 on tenk1 b
(4 rows)
@@ -5278,12 +5278,12 @@ explain (costs off)
select a.unique1, b.unique2
from onek a left join onek b on a.unique1 = b.unique2
where (b.unique2, random() > 0) = any (select q1, random() > 0 from int8_tbl c where c.q1 < b.unique1);
- QUERY PLAN
-----------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------------
Hash Join
Hash Cond: (b.unique2 = a.unique1)
-> Seq Scan on onek b
- Filter: (SubPlan 1)
+ Filter: (ANY ((unique2 = (SubPlan 1).col1) AND ((random() > '0'::double precision) = (SubPlan 1).col2)))
SubPlan 1
-> Seq Scan on int8_tbl c
Filter: (q1 < b.unique1)
@@ -8262,8 +8262,8 @@ lateral (select * from int8_tbl t1,
where q2 = (select greatest(t1.q1,t2.q2))
and (select v.id=0)) offset 0) ss2) ss
where t1.q1 = ss.q2) ss0;
- QUERY PLAN
--------------------------------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------------------
Nested Loop
Output: "*VALUES*".column1, t1.q1, t1.q2, ss2.q1, ss2.q2
-> Seq Scan on public.int8_tbl t1
@@ -8277,15 +8277,15 @@ lateral (select * from int8_tbl t1,
Filter: (t1.q1 = ss2.q2)
-> Seq Scan on public.int8_tbl t2
Output: t2.q1, t2.q2
- Filter: (SubPlan 3)
+ Filter: (ANY ((t2.q1 = (SubPlan 3).col1) AND ((random() > '0'::double precision) = (SubPlan
3).col2)))
SubPlan 3
-> Result
Output: t3.q2, (random() > '0'::double precision)
- One-Time Filter: $4
- InitPlan 1 (returns $2)
+ One-Time Filter: (InitPlan 2).col1
+ InitPlan 1
-> Result
Output: GREATEST(t1.q1, t2.q2)
- InitPlan 2 (returns $4)
+ InitPlan 2
-> Result
Output: ("*VALUES*".column1 = 0)
-> Seq Scan on public.int8_tbl t3
diff --git a/src/test/regress/expected/memoize.out b/src/test/regress/expected/memoize.out
index 1c8d996740..0fd103c06b 100644
--- a/src/test/regress/expected/memoize.out
+++ b/src/test/regress/expected/memoize.out
@@ -342,7 +342,7 @@ WHERE unique1 < 3
----------------------------------------------------------------
Index Scan using tenk1_unique1 on tenk1 t0
Index Cond: (unique1 < 3)
- Filter: (SubPlan 1)
+ Filter: EXISTS(SubPlan 1)
SubPlan 1
-> Nested Loop
-> Index Scan using tenk1_hundred on tenk1 t2
diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.out
index bf0657b9f2..e6f732f5e9 100644
--- a/src/test/regress/expected/partition_prune.out
+++ b/src/test/regress/expected/partition_prune.out
@@ -2180,7 +2180,7 @@ explain (analyze, costs off, summary off, timing off) execute ab_q2 (2, 2);
---------------------------------------------------------
Append (actual rows=0 loops=1)
Subplans Removed: 6
- InitPlan 1 (returns $0)
+ InitPlan 1
-> Result (actual rows=1 loops=1)
-> Seq Scan on ab_a2_b1 ab_1 (actual rows=0 loops=1)
Filter: ((a >= $1) AND (a <= $2) AND (b < $0))
@@ -2198,7 +2198,7 @@ explain (analyze, costs off, summary off, timing off) execute ab_q3 (2, 2);
---------------------------------------------------------
Append (actual rows=0 loops=1)
Subplans Removed: 6
- InitPlan 1 (returns $0)
+ InitPlan 1
-> Result (actual rows=1 loops=1)
-> Seq Scan on ab_a1_b2 ab_1 (actual rows=0 loops=1)
Filter: ((b >= $1) AND (b <= $2) AND (a < $0))
@@ -2414,9 +2414,9 @@ select explain_parallel_append('select count(*) from ab where (a = (select 1) or
explain_parallel_append
------------------------------------------------------------------------------
Aggregate (actual rows=N loops=N)
- InitPlan 1 (returns $0)
+ InitPlan 1
-> Result (actual rows=N loops=N)
- InitPlan 2 (returns $1)
+ InitPlan 2
-> Result (actual rows=N loops=N)
-> Gather (actual rows=N loops=N)
Workers Planned: 2
@@ -2629,10 +2629,10 @@ select * from ab where a = (select max(a) from lprt_a) and b = (select max(a)-1
QUERY PLAN
-------------------------------------------------------------------------
Append (actual rows=0 loops=1)
- InitPlan 1 (returns $0)
+ InitPlan 1
-> Aggregate (actual rows=1 loops=1)
-> Seq Scan on lprt_a (actual rows=102 loops=1)
- InitPlan 2 (returns $1)
+ InitPlan 2
-> Aggregate (actual rows=1 loops=1)
-> Seq Scan on lprt_a lprt_a_1 (actual rows=102 loops=1)
-> Bitmap Heap Scan on ab_a1_b1 ab_1 (never executed)
@@ -2688,7 +2688,7 @@ select * from (select * from ab where a = 1 union all select * from ab) ab where
QUERY PLAN
-------------------------------------------------------------------------------
Append (actual rows=0 loops=1)
- InitPlan 1 (returns $0)
+ InitPlan 1
-> Result (actual rows=1 loops=1)
-> Append (actual rows=0 loops=1)
-> Bitmap Heap Scan on ab_a1_b1 ab_11 (actual rows=0 loops=1)
@@ -2732,7 +2732,7 @@ select * from (select * from ab where a = 1 union all (values(10,5)) union all s
QUERY PLAN
-------------------------------------------------------------------------------
Append (actual rows=0 loops=1)
- InitPlan 1 (returns $0)
+ InitPlan 1
-> Result (actual rows=1 loops=1)
-> Append (actual rows=0 loops=1)
-> Bitmap Heap Scan on ab_a1_b1 ab_11 (actual rows=0 loops=1)
@@ -2791,7 +2791,7 @@ explain (analyze, costs off, summary off, timing off) execute ab_q6(1);
--------------------------------------------------
Append (actual rows=0 loops=1)
Subplans Removed: 12
- InitPlan 1 (returns $0)
+ InitPlan 1
-> Result (actual rows=1 loops=1)
-> Seq Scan on ab_a1_b1 ab_1 (never executed)
Filter: ((a = $1) AND (b = $0))
@@ -2884,7 +2884,7 @@ update ab_a1 set b = 3 from ab_a2 where ab_a2.b = (select 1);
Update on ab_a1_b1 ab_a1_1
Update on ab_a1_b2 ab_a1_2
Update on ab_a1_b3 ab_a1_3
- InitPlan 1 (returns $0)
+ InitPlan 1
-> Result (actual rows=1 loops=1)
-> Nested Loop (actual rows=3 loops=1)
-> Append (actual rows=3 loops=1)
@@ -3232,7 +3232,7 @@ select * from listp where a = (select null::int);
QUERY PLAN
------------------------------------------------------
Append (actual rows=0 loops=1)
- InitPlan 1 (returns $0)
+ InitPlan 1
-> Result (actual rows=1 loops=1)
-> Seq Scan on listp_1_1 listp_1 (never executed)
Filter: (a = $0)
@@ -3379,7 +3379,7 @@ execute ps1(1);
--------------------------------------------------------
Append (actual rows=1 loops=1)
Subplans Removed: 2
- InitPlan 1 (returns $0)
+ InitPlan 1
-> Result (actual rows=1 loops=1)
-> Seq Scan on mc3p1 mc3p_1 (actual rows=1 loops=1)
Filter: ((a = $1) AND (abs(b) < $0))
@@ -3394,7 +3394,7 @@ execute ps2(1);
--------------------------------------------------------
Append (actual rows=2 loops=1)
Subplans Removed: 1
- InitPlan 1 (returns $0)
+ InitPlan 1
-> Result (actual rows=1 loops=1)
-> Seq Scan on mc3p0 mc3p_1 (actual rows=1 loops=1)
Filter: ((a <= $1) AND (abs(b) < $0))
@@ -3415,7 +3415,7 @@ select * from boolp where a = (select value from boolvalues where value);
QUERY PLAN
-----------------------------------------------------------
Append (actual rows=0 loops=1)
- InitPlan 1 (returns $0)
+ InitPlan 1
-> Seq Scan on boolvalues (actual rows=1 loops=1)
Filter: value
Rows Removed by Filter: 1
@@ -3430,7 +3430,7 @@ select * from boolp where a = (select value from boolvalues where not value);
QUERY PLAN
-----------------------------------------------------------
Append (actual rows=0 loops=1)
- InitPlan 1 (returns $0)
+ InitPlan 1
-> Seq Scan on boolvalues (actual rows=1 loops=1)
Filter: (NOT value)
Rows Removed by Filter: 1
@@ -3526,9 +3526,9 @@ explain (analyze, costs off, summary off, timing off) select * from ma_test wher
-----------------------------------------------------------------------------------------------
Merge Append (actual rows=20 loops=1)
Sort Key: ma_test.b
- InitPlan 2 (returns $1)
+ InitPlan 2
-> Result (actual rows=1 loops=1)
- InitPlan 1 (returns $0)
+ InitPlan 1
-> Limit (actual rows=1 loops=1)
-> Index Scan using ma_test_p2_b_idx on ma_test_p2 (actual rows=1 loops=1)
Index Cond: (b IS NOT NULL)
@@ -3909,7 +3909,7 @@ where s.a = 1 and s.b = 1 and s.c = (select 1);
QUERY PLAN
----------------------------------------------------
Append
- InitPlan 1 (returns $0)
+ InitPlan 1
-> Result
-> Seq Scan on p1 p
Filter: ((a = 1) AND (b = 1) AND (c = $0))
@@ -3948,7 +3948,7 @@ explain (costs off) execute q (1, 1);
---------------------------------------------------------------
Append
Subplans Removed: 1
- InitPlan 1 (returns $0)
+ InitPlan 1
-> Result
-> Seq Scan on p1 p
Filter: ((a = $1) AND (b = $2) AND (c = $0))
@@ -3973,11 +3973,11 @@ create table listp2 partition of listp for values in(2) partition by list(b);
create table listp2_10 partition of listp2 for values in (10);
explain (analyze, costs off, summary off, timing off)
select * from listp where a = (select 2) and b <> 10;
- QUERY PLAN
---------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------
Seq Scan on listp1 listp (actual rows=0 loops=1)
- Filter: ((b <> 10) AND (a = $0))
- InitPlan 1 (returns $0)
+ Filter: ((b <> 10) AND (a = (InitPlan 1).col1))
+ InitPlan 1
-> Result (never executed)
(4 rows)
@@ -4044,7 +4044,7 @@ select explain_parallel_append('select * from listp where a = (select 1);');
Workers Planned: 2
Params Evaluated: $0
Workers Launched: N
- InitPlan 1 (returns $0)
+ InitPlan 1
-> Result (actual rows=N loops=N)
-> Parallel Append (actual rows=N loops=N)
-> Seq Scan on listp_12_1 listp_1 (actual rows=N loops=N)
@@ -4067,14 +4067,14 @@ select * from listp where a = (select 2);');
Workers Launched: N
-> Parallel Append (actual rows=N loops=N)
-> Parallel Append (actual rows=N loops=N)
- InitPlan 2 (returns $1)
+ InitPlan 2
-> Result (actual rows=N loops=N)
-> Seq Scan on listp_12_1 listp_1 (never executed)
Filter: (a = $1)
-> Parallel Seq Scan on listp_12_2 listp_2 (actual rows=N loops=N)
Filter: (a = $1)
-> Parallel Append (actual rows=N loops=N)
- InitPlan 1 (returns $0)
+ InitPlan 1
-> Result (actual rows=N loops=N)
-> Seq Scan on listp_12_1 listp_4 (actual rows=N loops=N)
Filter: (a = $0)
@@ -4102,9 +4102,9 @@ select * from rangep where b IN((select 1),(select 2)) order by a;
QUERY PLAN
------------------------------------------------------------------------------------------------------------
Append (actual rows=0 loops=1)
- InitPlan 1 (returns $0)
+ InitPlan 1
-> Result (actual rows=1 loops=1)
- InitPlan 2 (returns $1)
+ InitPlan 2
-> Result (actual rows=1 loops=1)
-> Merge Append (actual rows=0 loops=1)
Sort Key: rangep_2.a
diff --git a/src/test/regress/expected/portals.out b/src/test/regress/expected/portals.out
index f71e0b3d41..06726ed4ab 100644
--- a/src/test/regress/expected/portals.out
+++ b/src/test/regress/expected/portals.out
@@ -1472,18 +1472,18 @@ rollback;
-- Check handling of non-backwards-scan-capable plans with scroll cursors
begin;
explain (costs off) declare c1 cursor for select (select 42) as x;
- QUERY PLAN
----------------------------
+ QUERY PLAN
+----------------
Result
- InitPlan 1 (returns $0)
+ InitPlan 1
-> Result
(3 rows)
explain (costs off) declare c1 scroll cursor for select (select 42) as x;
- QUERY PLAN
----------------------------
+ QUERY PLAN
+----------------
Materialize
- InitPlan 1 (returns $0)
+ InitPlan 1
-> Result
-> Result
(4 rows)
diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.out
index 4538f0c37d..d5bdc87dde 100644
--- a/src/test/regress/expected/rowsecurity.out
+++ b/src/test/regress/expected/rowsecurity.out
@@ -265,11 +265,11 @@ NOTICE: f_leak => awesome science fiction
(5 rows)
EXPLAIN (COSTS OFF) SELECT * FROM document WHERE f_leak(dtitle);
- QUERY PLAN
-----------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------
Seq Scan on document
- Filter: ((dlevel <= $0) AND f_leak(dtitle))
- InitPlan 1 (returns $0)
+ Filter: ((dlevel <= (InitPlan 1).col1) AND f_leak(dtitle))
+ InitPlan 1
-> Index Scan using uaccount_pkey on uaccount
Index Cond: (pguser = CURRENT_USER)
(5 rows)
@@ -279,7 +279,7 @@ EXPLAIN (COSTS OFF) SELECT * FROM document NATURAL JOIN category WHERE f_leak(dt
-----------------------------------------------------------
Hash Join
Hash Cond: (category.cid = document.cid)
- InitPlan 1 (returns $0)
+ InitPlan 1
-> Index Scan using uaccount_pkey on uaccount
Index Cond: (pguser = CURRENT_USER)
-> Seq Scan on category
@@ -329,11 +329,11 @@ NOTICE: f_leak => awesome technology book
(7 rows)
EXPLAIN (COSTS OFF) SELECT * FROM document WHERE f_leak(dtitle);
- QUERY PLAN
-----------------------------------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------
Seq Scan on document
- Filter: ((cid <> 44) AND (cid <> 44) AND (cid < 50) AND (dlevel <= $0) AND f_leak(dtitle))
- InitPlan 1 (returns $0)
+ Filter: ((cid <> 44) AND (cid <> 44) AND (cid < 50) AND (dlevel <= (InitPlan 1).col1) AND f_leak(dtitle))
+ InitPlan 1
-> Index Scan using uaccount_pkey on uaccount
Index Cond: (pguser = CURRENT_USER)
(5 rows)
@@ -343,7 +343,7 @@ EXPLAIN (COSTS OFF) SELECT * FROM document NATURAL JOIN category WHERE f_leak(dt
----------------------------------------------------------------------------------------------------------
Hash Join
Hash Cond: (category.cid = document.cid)
- InitPlan 1 (returns $0)
+ InitPlan 1
-> Index Scan using uaccount_pkey on uaccount
Index Cond: (pguser = CURRENT_USER)
-> Seq Scan on category
@@ -990,7 +990,7 @@ EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle);
QUERY PLAN
------------------------------------------------------------
Append
- InitPlan 1 (returns $0)
+ InitPlan 1
-> Index Scan using uaccount_pkey on uaccount
Index Cond: (pguser = CURRENT_USER)
-> Seq Scan on part_document_fiction part_document_1
@@ -1032,7 +1032,7 @@ EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle);
QUERY PLAN
------------------------------------------------------------
Append
- InitPlan 1 (returns $0)
+ InitPlan 1
-> Index Scan using uaccount_pkey on uaccount
Index Cond: (pguser = CURRENT_USER)
-> Seq Scan on part_document_fiction part_document_1
@@ -1059,11 +1059,11 @@ NOTICE: f_leak => awesome science fiction
(4 rows)
EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle);
- QUERY PLAN
---------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------
Seq Scan on part_document_fiction part_document
- Filter: ((cid < 55) AND (dlevel <= $0) AND f_leak(dtitle))
- InitPlan 1 (returns $0)
+ Filter: ((cid < 55) AND (dlevel <= (InitPlan 1).col1) AND f_leak(dtitle))
+ InitPlan 1
-> Index Scan using uaccount_pkey on uaccount
Index Cond: (pguser = CURRENT_USER)
(5 rows)
@@ -1137,11 +1137,11 @@ NOTICE: f_leak => awesome science fiction
(4 rows)
EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle);
- QUERY PLAN
---------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------
Seq Scan on part_document_fiction part_document
- Filter: ((cid < 55) AND (dlevel <= $0) AND f_leak(dtitle))
- InitPlan 1 (returns $0)
+ Filter: ((cid < 55) AND (dlevel <= (InitPlan 1).col1) AND f_leak(dtitle))
+ InitPlan 1
-> Index Scan using uaccount_pkey on uaccount
Index Cond: (pguser = CURRENT_USER)
(5 rows)
@@ -1179,7 +1179,7 @@ EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle);
QUERY PLAN
------------------------------------------------------------
Append
- InitPlan 1 (returns $0)
+ InitPlan 1
-> Index Scan using uaccount_pkey on uaccount
Index Cond: (pguser = CURRENT_USER)
-> Seq Scan on part_document_fiction part_document_1
@@ -1437,10 +1437,10 @@ NOTICE: f_leak => 03b26944890929ff751653acb2f2af79
(1 row)
EXPLAIN (COSTS OFF) SELECT * FROM only s1 WHERE f_leak(b);
- QUERY PLAN
------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------
Seq Scan on s1
- Filter: ((hashed SubPlan 1) AND f_leak(b))
+ Filter: ((ANY (a = (hashed SubPlan 1).col1)) AND f_leak(b))
SubPlan 1
-> Seq Scan on s2
Filter: (((x % 2) = 0) AND (y ~~ '%2f%'::text))
@@ -1457,10 +1457,10 @@ NOTICE: f_leak => 03b26944890929ff751653acb2f2af79
(1 row)
EXPLAIN (COSTS OFF) SELECT * FROM s1 WHERE f_leak(b);
- QUERY PLAN
------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------
Seq Scan on s1
- Filter: ((hashed SubPlan 1) AND f_leak(b))
+ Filter: ((ANY (a = (hashed SubPlan 1).col1)) AND f_leak(b))
SubPlan 1
-> Seq Scan on s2
Filter: (((x % 2) = 0) AND (y ~~ '%af%'::text))
@@ -1480,7 +1480,7 @@ EXPLAIN (COSTS OFF) SELECT (SELECT x FROM s1 LIMIT 1) xx, * FROM s2 WHERE y like
SubPlan 2
-> Limit
-> Seq Scan on s1
- Filter: (hashed SubPlan 1)
+ Filter: (ANY (a = (hashed SubPlan 1).col1))
SubPlan 1
-> Seq Scan on s2 s2_1
Filter: (((x % 2) = 0) AND (y ~~ '%af%'::text))
@@ -2687,10 +2687,10 @@ NOTICE: f_leak => bbb
(1 row)
EXPLAIN (COSTS OFF) SELECT * FROM rls_view;
- QUERY PLAN
-----------------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------------------------
Seq Scan on z1
- Filter: ((NOT (hashed SubPlan 1)) AND ((a % 2) = 0) AND f_leak(b))
+ Filter: ((NOT (ANY (a = (hashed SubPlan 1).col1))) AND ((a % 2) = 0) AND f_leak(b))
SubPlan 1
-> Seq Scan on z1_blacklist
(4 rows)
@@ -2705,10 +2705,10 @@ NOTICE: f_leak => bbb
(1 row)
EXPLAIN (COSTS OFF) SELECT * FROM rls_view;
- QUERY PLAN
-----------------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------------------------
Seq Scan on z1
- Filter: ((NOT (hashed SubPlan 1)) AND ((a % 2) = 0) AND f_leak(b))
+ Filter: ((NOT (ANY (a = (hashed SubPlan 1).col1))) AND ((a % 2) = 0) AND f_leak(b))
SubPlan 1
-> Seq Scan on z1_blacklist
(4 rows)
@@ -2877,10 +2877,10 @@ NOTICE: f_leak => bbb
(1 row)
EXPLAIN (COSTS OFF) SELECT * FROM rls_view;
- QUERY PLAN
-----------------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------------------------
Seq Scan on z1
- Filter: ((NOT (hashed SubPlan 1)) AND ((a % 2) = 0) AND f_leak(b))
+ Filter: ((NOT (ANY (a = (hashed SubPlan 1).col1))) AND ((a % 2) = 0) AND f_leak(b))
SubPlan 1
-> Seq Scan on z1_blacklist
(4 rows)
@@ -2903,10 +2903,10 @@ NOTICE: f_leak => aba
(1 row)
EXPLAIN (COSTS OFF) SELECT * FROM rls_view;
- QUERY PLAN
-----------------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------------------------
Seq Scan on z1
- Filter: ((NOT (hashed SubPlan 1)) AND ((a % 2) = 1) AND f_leak(b))
+ Filter: ((NOT (ANY (a = (hashed SubPlan 1).col1))) AND ((a % 2) = 1) AND f_leak(b))
SubPlan 1
-> Seq Scan on z1_blacklist
(4 rows)
diff --git a/src/test/regress/expected/rowtypes.out b/src/test/regress/expected/rowtypes.out
index 8f3c153bac..b400b58f76 100644
--- a/src/test/regress/expected/rowtypes.out
+++ b/src/test/regress/expected/rowtypes.out
@@ -1249,8 +1249,8 @@ with cte(c) as materialized (select row(1, 2)),
select * from cte2 as t
where (select * from (select c as c1) s
where (select (c1).f1 > 0)) is not null;
- QUERY PLAN
---------------------------------------------
+ QUERY PLAN
+----------------------------------------------
CTE Scan on cte
Output: cte.c
Filter: ((SubPlan 3) IS NOT NULL)
@@ -1260,8 +1260,8 @@ where (select * from (select c as c1) s
SubPlan 3
-> Result
Output: cte.c
- One-Time Filter: $2
- InitPlan 2 (returns $2)
+ One-Time Filter: (InitPlan 2).col1
+ InitPlan 2
-> Result
Output: ((cte.c).f1 > 0)
(13 rows)
diff --git a/src/test/regress/expected/select_parallel.out b/src/test/regress/expected/select_parallel.out
index 7a0d78dfe3..eea019737e 100644
--- a/src/test/regress/expected/select_parallel.out
+++ b/src/test/regress/expected/select_parallel.out
@@ -291,14 +291,14 @@ alter table tenk2 set (parallel_workers = 0);
explain (costs off)
select count(*) from tenk1 where (two, four) not in
(select hundred, thousand from tenk2 where thousand > 100);
- QUERY PLAN
-------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------
Finalize Aggregate
-> Gather
Workers Planned: 4
-> Partial Aggregate
-> Parallel Seq Scan on tenk1
- Filter: (NOT (hashed SubPlan 1))
+ Filter: (NOT (ANY ((two = (hashed SubPlan 1).col1) AND (four = (hashed SubPlan 1).col2))))
SubPlan 1
-> Seq Scan on tenk2
Filter: (thousand > 100)
@@ -315,10 +315,10 @@ select count(*) from tenk1 where (two, four) not in
explain (costs off)
select * from tenk1 where (unique1 + random())::integer not in
(select ten from tenk2);
- QUERY PLAN
-------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------
Seq Scan on tenk1
- Filter: (NOT (hashed SubPlan 1))
+ Filter: (NOT (ANY ((((unique1)::double precision + random()))::integer = (hashed SubPlan 1).col1)))
SubPlan 1
-> Seq Scan on tenk2
(4 rows)
@@ -335,7 +335,7 @@ explain (costs off)
QUERY PLAN
------------------------------------------------------
Aggregate
- InitPlan 1 (returns $2)
+ InitPlan 1
-> Finalize Aggregate
-> Gather
Workers Planned: 2
@@ -1151,7 +1151,7 @@ ORDER BY 1;
-> Gather
Workers Planned: 4
Params Evaluated: $1
- InitPlan 1 (returns $1)
+ InitPlan 1
-> Limit
-> Gather
Workers Planned: 4
@@ -1162,7 +1162,7 @@ ORDER BY 1;
-> Gather
Workers Planned: 4
Params Evaluated: $3
- InitPlan 2 (returns $3)
+ InitPlan 2
-> Limit
-> Gather
Workers Planned: 4
@@ -1182,10 +1182,10 @@ ORDER BY 1, 2, 3;
EXPLAIN (VERBOSE, COSTS OFF)
SELECT generate_series(1, two), array(select generate_series(1, two))
FROM tenk1 ORDER BY tenthous;
- QUERY PLAN
-----------------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------------
ProjectSet
- Output: generate_series(1, tenk1.two), (SubPlan 1), tenk1.tenthous
+ Output: generate_series(1, tenk1.two), ARRAY(SubPlan 1), tenk1.tenthous
-> Gather Merge
Output: tenk1.two, tenk1.tenthous
Workers Planned: 4
diff --git a/src/test/regress/expected/sqljson.out b/src/test/regress/expected/sqljson.out
index 5e7da96be5..cbf8542d8d 100644
--- a/src/test/regress/expected/sqljson.out
+++ b/src/test/regress/expected/sqljson.out
@@ -1064,8 +1064,8 @@ SELECT JSON_ARRAY(SELECT i FROM (VALUES (1), (2), (NULL), (4)) foo(i) RETURNING
QUERY PLAN
---------------------------------------------------------------------
Result
- Output: $0
- InitPlan 1 (returns $0)
+ Output: (InitPlan 1).col1
+ InitPlan 1
-> Aggregate
Output: JSON_ARRAYAGG("*VALUES*".column1 RETURNING jsonb)
-> Values Scan on "*VALUES*"
diff --git a/src/test/regress/expected/subselect.out b/src/test/regress/expected/subselect.out
index e41b728df8..eb0efa60d9 100644
--- a/src/test/regress/expected/subselect.out
+++ b/src/test/regress/expected/subselect.out
@@ -202,6 +202,57 @@ SELECT f1 AS "Correlated Field"
3
(5 rows)
+-- Check ROWCOMPARE cases, both correlated and not
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT ROW(1, 2) = (SELECT f1, f2) AS eq FROM SUBSELECT_TBL;
+ QUERY PLAN
+-----------------------------------------------------------------
+ Seq Scan on public.subselect_tbl
+ Output: (((1 = (SubPlan 1).col1) AND (2 = (SubPlan 1).col2)))
+ SubPlan 1
+ -> Result
+ Output: subselect_tbl.f1, subselect_tbl.f2
+(5 rows)
+
+SELECT ROW(1, 2) = (SELECT f1, f2) AS eq FROM SUBSELECT_TBL;
+ eq
+----
+ t
+ f
+ f
+ f
+ f
+ f
+ f
+ f
+(8 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT ROW(1, 2) = (SELECT 3, 4) AS eq FROM SUBSELECT_TBL;
+ QUERY PLAN
+-----------------------------------------------------------------
+ Seq Scan on public.subselect_tbl
+ Output: ((1 = (InitPlan 1).col1) AND (2 = (InitPlan 1).col2))
+ InitPlan 1
+ -> Result
+ Output: 3, 4
+(5 rows)
+
+SELECT ROW(1, 2) = (SELECT 3, 4) AS eq FROM SUBSELECT_TBL;
+ eq
+----
+ f
+ f
+ f
+ f
+ f
+ f
+ f
+ f
+(8 rows)
+
+SELECT ROW(1, 2) = (SELECT f1, f2 FROM SUBSELECT_TBL); -- error
+ERROR: more than one row returned by a subquery used as an expression
-- Subselects without aliases
SELECT count FROM (SELECT COUNT(DISTINCT name) FROM road);
count
@@ -324,14 +375,14 @@ explain (verbose, costs off) select '42' union all select 43;
-- check materialization of an initplan reference (bug #14524)
explain (verbose, costs off)
select 1 = all (select (select 1));
- QUERY PLAN
------------------------------------
+ QUERY PLAN
+----------------------------------------
Result
- Output: (SubPlan 2)
+ Output: (ALL (1 = (SubPlan 2).col1))
SubPlan 2
-> Materialize
Output: ($0)
- InitPlan 1 (returns $0)
+ InitPlan 1
-> Result
Output: 1
-> Result
@@ -377,7 +428,7 @@ select * from int4_tbl o where exists
QUERY PLAN
--------------------------------------
Seq Scan on int4_tbl o
- Filter: (SubPlan 1)
+ Filter: EXISTS(SubPlan 1)
SubPlan 1
-> Limit
-> Seq Scan on int4_tbl i
@@ -840,10 +891,10 @@ select * from outer_text where (f1, f2) not in (select * from inner_text);
--
explain (verbose, costs off)
select 'foo'::text in (select 'bar'::name union all select 'bar'::name);
- QUERY PLAN
--------------------------------------
+ QUERY PLAN
+---------------------------------------------------------
Result
- Output: (hashed SubPlan 1)
+ Output: (ANY ('foo'::text = (hashed SubPlan 1).col1))
SubPlan 1
-> Append
-> Result
@@ -864,10 +915,10 @@ select 'foo'::text in (select 'bar'::name union all select 'bar'::name);
--
explain (verbose, costs off)
select row(row(row(1))) = any (select row(row(1)));
- QUERY PLAN
--------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------
Result
- Output: (SubPlan 1)
+ Output: (ANY ('("(1)")'::record = (SubPlan 1).col1))
SubPlan 1
-> Materialize
Output: '("(1)")'::record
@@ -907,10 +958,10 @@ language sql as 'select $1::text = $2';
create operator = (procedure=bogus_int8_text_eq, leftarg=int8, rightarg=text);
explain (costs off)
select * from int8_tbl where q1 in (select c1 from inner_text);
- QUERY PLAN
---------------------------------
+ QUERY PLAN
+--------------------------------------------------------
Seq Scan on int8_tbl
- Filter: (hashed SubPlan 1)
+ Filter: (ANY ((q1)::text = (hashed SubPlan 1).col1))
SubPlan 1
-> Seq Scan on inner_text
(4 rows)
@@ -928,10 +979,10 @@ create or replace function bogus_int8_text_eq(int8, text) returns boolean
language sql as 'select $1::text = $2 and $1::text = $2';
explain (costs off)
select * from int8_tbl where q1 in (select c1 from inner_text);
- QUERY PLAN
---------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------
Seq Scan on int8_tbl
- Filter: (hashed SubPlan 1)
+ Filter: (ANY (((q1)::text = (hashed SubPlan 1).col1) AND ((q1)::text = (hashed SubPlan 1).col1)))
SubPlan 1
-> Seq Scan on inner_text
(4 rows)
@@ -949,10 +1000,10 @@ create or replace function bogus_int8_text_eq(int8, text) returns boolean
language sql as 'select $2 = $1::text';
explain (costs off)
select * from int8_tbl where q1 in (select c1 from inner_text);
- QUERY PLAN
---------------------------------------
+ QUERY PLAN
+-------------------------------------------------
Seq Scan on int8_tbl
- Filter: (SubPlan 1)
+ Filter: (ANY ((SubPlan 1).col1 = (q1)::text))
SubPlan 1
-> Materialize
-> Seq Scan on inner_text
@@ -972,11 +1023,11 @@ rollback; -- to get rid of the bogus operator
explain (costs off)
select count(*) from tenk1 t
where (exists(select 1 from tenk1 k where k.unique1 = t.unique2) or ten < 0);
- QUERY PLAN
---------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------
Aggregate
-> Seq Scan on tenk1 t
- Filter: ((hashed SubPlan 2) OR (ten < 0))
+ Filter: ((ANY (unique2 = (hashed SubPlan 2).col1)) OR (ten < 0))
SubPlan 2
-> Index Only Scan using tenk1_unique1 on tenk1 k
(5 rows)
@@ -997,7 +1048,7 @@ where (exists(select 1 from tenk1 k where k.unique1 = t.unique2) or ten < 0)
Aggregate
-> Bitmap Heap Scan on tenk1 t
Recheck Cond: (thousand = 1)
- Filter: ((SubPlan 1) OR (ten < 0))
+ Filter: (EXISTS(SubPlan 1) OR (ten < 0))
-> Bitmap Index Scan on tenk1_thous_tenthous
Index Cond: (thousand = 1)
SubPlan 1
@@ -1022,11 +1073,11 @@ analyze exists_tbl;
explain (costs off)
select * from exists_tbl t1
where (exists(select 1 from exists_tbl t2 where t1.c1 = t2.c2) or c3 < 0);
- QUERY PLAN
-------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------
Append
-> Seq Scan on exists_tbl_null t1_1
- Filter: ((SubPlan 1) OR (c3 < 0))
+ Filter: (EXISTS(SubPlan 1) OR (c3 < 0))
SubPlan 1
-> Append
-> Seq Scan on exists_tbl_null t2_1
@@ -1034,7 +1085,7 @@ select * from exists_tbl t1
-> Seq Scan on exists_tbl_def t2_2
Filter: (t1_1.c1 = c2)
-> Seq Scan on exists_tbl_def t1_2
- Filter: ((hashed SubPlan 2) OR (c3 < 0))
+ Filter: ((ANY (c1 = (hashed SubPlan 2).col1)) OR (c3 < 0))
SubPlan 2
-> Append
-> Seq Scan on exists_tbl_null t2_4
@@ -1071,14 +1122,14 @@ where a.thousand = b.thousand
explain (verbose, costs off)
select x, x from
(select (select now()) as x from (values(1),(2)) v(y)) ss;
- QUERY PLAN
----------------------------
+ QUERY PLAN
+------------------------------------------------
Values Scan on "*VALUES*"
- Output: $0, $1
- InitPlan 1 (returns $0)
+ Output: (InitPlan 1).col1, (InitPlan 2).col1
+ InitPlan 1
-> Result
Output: now()
- InitPlan 2 (returns $1)
+ InitPlan 2
-> Result
Output: now()
(8 rows)
@@ -1086,13 +1137,13 @@ explain (verbose, costs off)
explain (verbose, costs off)
select x, x from
(select (select random()) 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*"
- Output: $0
- InitPlan 1 (returns $0)
+ Output: (InitPlan 1).col1
+ InitPlan 1
-> Result
Output: random()
(7 rows)
@@ -1143,14 +1194,14 @@ where o.ten = 0;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate
- Output: sum((((hashed SubPlan 1)))::integer)
+ Output: sum((((ANY (i.ten = (hashed SubPlan 1).col1))))::integer)
-> Nested Loop
- Output: ((hashed SubPlan 1))
+ Output: ((ANY (i.ten = (hashed SubPlan 1).col1)))
-> Seq Scan on public.onek o
Output: o.unique1, o.unique2, o.two, o.four, o.ten, o.twenty, o.hundred, o.thousand, o.twothousand,
o.fivethous,o.tenthous, o.odd, o.even, o.stringu1, o.stringu2, o.string4
Filter: (o.ten = 0)
-> Index Scan using onek_unique1 on public.onek i
- Output: (hashed SubPlan 1), random()
+ Output: (ANY (i.ten = (hashed SubPlan 1).col1)), random()
Index Cond: (i.unique1 = o.unique1)
SubPlan 1
-> Seq Scan on public.int4_tbl
@@ -1346,7 +1397,7 @@ select * from int4_tbl where
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop Semi Join
Output: int4_tbl.f1
- Join Filter: (CASE WHEN (hashed SubPlan 1) THEN int4_tbl.f1 ELSE NULL::integer END = b.ten)
+ Join Filter: (CASE WHEN (ANY (int4_tbl.f1 = (hashed SubPlan 1).col1)) THEN int4_tbl.f1 ELSE NULL::integer END =
b.ten)
-> Seq Scan on public.int4_tbl
Output: int4_tbl.f1
-> Seq Scan on public.tenk1 b
@@ -1945,10 +1996,10 @@ select * from tenk1 A where exists
(select 1 from tenk2 B
where A.hundred in (select C.hundred FROM tenk2 C
WHERE c.odd = b.odd));
- QUERY PLAN
----------------------------------
+ QUERY PLAN
+-----------------------------------------------------
Nested Loop Semi Join
- Join Filter: (SubPlan 1)
+ Join Filter: (ANY (a.hundred = (SubPlan 1).col1))
-> Seq Scan on tenk1 a
-> Materialize
-> Seq Scan on tenk2 b
@@ -1962,10 +2013,10 @@ WHERE c.odd = b.odd));
explain (costs off)
SELECT * FROM tenk1 A LEFT JOIN tenk2 B
ON A.hundred in (SELECT c.hundred FROM tenk2 C WHERE c.odd = b.odd);
- QUERY PLAN
----------------------------------
+ QUERY PLAN
+-----------------------------------------------------
Nested Loop Left Join
- Join Filter: (SubPlan 1)
+ Join Filter: (ANY (a.hundred = (SubPlan 1).col1))
-> Seq Scan on tenk1 a
-> Materialize
-> Seq Scan on tenk2 b
@@ -1979,10 +2030,10 @@ ON A.hundred in (SELECT c.hundred FROM tenk2 C WHERE c.odd = b.odd);
explain (costs off)
SELECT * FROM tenk1 A LEFT JOIN tenk2 B
ON B.hundred in (SELECT c.hundred FROM tenk2 C WHERE c.odd = a.odd);
- QUERY PLAN
----------------------------------
+ QUERY PLAN
+-----------------------------------------------------
Nested Loop Left Join
- Join Filter: (SubPlan 1)
+ Join Filter: (ANY (b.hundred = (SubPlan 1).col1))
-> Seq Scan on tenk1 a
-> Materialize
-> Seq Scan on tenk2 b
@@ -2045,7 +2096,7 @@ ON B.hundred in (SELECT min(c.hundred) FROM tenk2 C WHERE c.odd = b.odd);
-> Subquery Scan on "ANY_subquery"
Filter: (b.hundred = "ANY_subquery".min)
-> Result
- InitPlan 1 (returns $1)
+ InitPlan 1
-> Limit
-> Index Scan using tenk2_hundred on tenk2 c
Index Cond: (hundred IS NOT NULL)
diff --git a/src/test/regress/expected/updatable_views.out b/src/test/regress/expected/updatable_views.out
index 794cf9cf93..1666a57775 100644
--- a/src/test/regress/expected/updatable_views.out
+++ b/src/test/regress/expected/updatable_views.out
@@ -3014,14 +3014,14 @@ EXPLAIN (costs off) INSERT INTO rw_view1 VALUES (2, 'New row 2');
QUERY PLAN
-----------------------------------------------------------
Insert on base_tbl
- InitPlan 1 (returns $0)
+ InitPlan 1
-> Index Only Scan using base_tbl_pkey on base_tbl t
Index Cond: (id = 2)
-> Result
One-Time Filter: ($0 IS NOT TRUE)
Update on base_tbl
- InitPlan 1 (returns $0)
+ InitPlan 1
-> Index Only Scan using base_tbl_pkey on base_tbl t
Index Cond: (id = 2)
-> Result
@@ -3084,8 +3084,8 @@ SELECT * FROM v1 WHERE a=8;
EXPLAIN (VERBOSE, COSTS OFF)
UPDATE v1 SET a=100 WHERE snoop(a) AND leakproof(a) AND a < 7 AND a != 6;
- QUERY PLAN
------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------
Update on public.t1
Update on public.t1 t1_1
Update on public.t11 t1_2
@@ -3097,7 +3097,7 @@ UPDATE v1 SET a=100 WHERE snoop(a) AND leakproof(a) AND a < 7 AND a != 6;
-> Index Scan using t1_a_idx on public.t1 t1_1
Output: t1_1.tableoid, t1_1.ctid
Index Cond: ((t1_1.a > 5) AND (t1_1.a < 7))
- Filter: ((t1_1.a <> 6) AND (SubPlan 1) AND snoop(t1_1.a) AND leakproof(t1_1.a))
+ Filter: ((t1_1.a <> 6) AND EXISTS(SubPlan 1) AND snoop(t1_1.a) AND leakproof(t1_1.a))
SubPlan 1
-> Append
-> Seq Scan on public.t12 t12_1
@@ -3107,15 +3107,15 @@ UPDATE v1 SET a=100 WHERE snoop(a) AND leakproof(a) AND a < 7 AND a != 6;
-> Index Scan using t11_a_idx on public.t11 t1_2
Output: t1_2.tableoid, t1_2.ctid
Index Cond: ((t1_2.a > 5) AND (t1_2.a < 7))
- Filter: ((t1_2.a <> 6) AND (SubPlan 1) AND snoop(t1_2.a) AND leakproof(t1_2.a))
+ Filter: ((t1_2.a <> 6) AND EXISTS(SubPlan 1) AND snoop(t1_2.a) AND leakproof(t1_2.a))
-> Index Scan using t12_a_idx on public.t12 t1_3
Output: t1_3.tableoid, t1_3.ctid
Index Cond: ((t1_3.a > 5) AND (t1_3.a < 7))
- Filter: ((t1_3.a <> 6) AND (SubPlan 1) AND snoop(t1_3.a) AND leakproof(t1_3.a))
+ Filter: ((t1_3.a <> 6) AND EXISTS(SubPlan 1) AND snoop(t1_3.a) AND leakproof(t1_3.a))
-> Index Scan using t111_a_idx on public.t111 t1_4
Output: t1_4.tableoid, t1_4.ctid
Index Cond: ((t1_4.a > 5) AND (t1_4.a < 7))
- Filter: ((t1_4.a <> 6) AND (SubPlan 1) AND snoop(t1_4.a) AND leakproof(t1_4.a))
+ Filter: ((t1_4.a <> 6) AND EXISTS(SubPlan 1) AND snoop(t1_4.a) AND leakproof(t1_4.a))
(30 rows)
UPDATE v1 SET a=100 WHERE snoop(a) AND leakproof(a) AND a < 7 AND a != 6;
@@ -3131,8 +3131,8 @@ SELECT * FROM t1 WHERE a=100; -- Nothing should have been changed to 100
EXPLAIN (VERBOSE, COSTS OFF)
UPDATE v1 SET a=a+1 WHERE snoop(a) AND leakproof(a) AND a = 8;
- QUERY PLAN
------------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------------
Update on public.t1
Update on public.t1 t1_1
Update on public.t11 t1_2
@@ -3144,7 +3144,7 @@ UPDATE v1 SET a=a+1 WHERE snoop(a) AND leakproof(a) AND a = 8;
-> Index Scan using t1_a_idx on public.t1 t1_1
Output: t1_1.a, t1_1.tableoid, t1_1.ctid
Index Cond: ((t1_1.a > 5) AND (t1_1.a = 8))
- Filter: ((SubPlan 1) AND snoop(t1_1.a) AND leakproof(t1_1.a))
+ Filter: (EXISTS(SubPlan 1) AND snoop(t1_1.a) AND leakproof(t1_1.a))
SubPlan 1
-> Append
-> Seq Scan on public.t12 t12_1
@@ -3154,15 +3154,15 @@ UPDATE v1 SET a=a+1 WHERE snoop(a) AND leakproof(a) AND a = 8;
-> Index Scan using t11_a_idx on public.t11 t1_2
Output: t1_2.a, t1_2.tableoid, t1_2.ctid
Index Cond: ((t1_2.a > 5) AND (t1_2.a = 8))
- Filter: ((SubPlan 1) AND snoop(t1_2.a) AND leakproof(t1_2.a))
+ Filter: (EXISTS(SubPlan 1) AND snoop(t1_2.a) AND leakproof(t1_2.a))
-> Index Scan using t12_a_idx on public.t12 t1_3
Output: t1_3.a, t1_3.tableoid, t1_3.ctid
Index Cond: ((t1_3.a > 5) AND (t1_3.a = 8))
- Filter: ((SubPlan 1) AND snoop(t1_3.a) AND leakproof(t1_3.a))
+ Filter: (EXISTS(SubPlan 1) AND snoop(t1_3.a) AND leakproof(t1_3.a))
-> Index Scan using t111_a_idx on public.t111 t1_4
Output: t1_4.a, t1_4.tableoid, t1_4.ctid
Index Cond: ((t1_4.a > 5) AND (t1_4.a = 8))
- Filter: ((SubPlan 1) AND snoop(t1_4.a) AND leakproof(t1_4.a))
+ Filter: (EXISTS(SubPlan 1) AND snoop(t1_4.a) AND leakproof(t1_4.a))
(30 rows)
UPDATE v1 SET a=a+1 WHERE snoop(a) AND leakproof(a) AND a = 8;
@@ -3349,7 +3349,7 @@ EXPLAIN (COSTS OFF) UPDATE v2 SET a = 1;
QUERY PLAN
---------------------------------------------------
Update on t1
- InitPlan 1 (returns $0)
+ InitPlan 1
-> Result
-> Merge Join
Merge Cond: (t1.a = v1.a)
diff --git a/src/test/regress/expected/update.out b/src/test/regress/expected/update.out
index cb0b033da6..153934bf90 100644
--- a/src/test/regress/expected/update.out
+++ b/src/test/regress/expected/update.out
@@ -178,15 +178,15 @@ EXPLAIN (VERBOSE, COSTS OFF)
UPDATE update_test t
SET (a, b) = (SELECT b, a FROM update_test s WHERE s.a = t.a)
WHERE CURRENT_USER = SESSION_USER;
- QUERY PLAN
--------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------
Update on public.update_test t
-> Result
- Output: $1, $2, (SubPlan 1 (returns $1,$2)), t.ctid
+ Output: $1, $2, (SubPlan 1), t.ctid
One-Time Filter: (CURRENT_USER = SESSION_USER)
-> Seq Scan on public.update_test t
Output: t.a, t.ctid
- SubPlan 1 (returns $1,$2)
+ SubPlan 1
-> Seq Scan on public.update_test s
Output: s.b, s.a
Filter: (s.a = t.a)
diff --git a/src/test/regress/expected/window.out b/src/test/regress/expected/window.out
index 60de2cbf96..e46710cf31 100644
--- a/src/test/regress/expected/window.out
+++ b/src/test/regress/expected/window.out
@@ -4279,7 +4279,7 @@ WHERE c = 1;
Subquery Scan on emp
Filter: (emp.c = 1)
-> WindowAgg
- InitPlan 1 (returns $0)
+ InitPlan 1
-> Result
-> Sort
Sort Key: empsalary.empno DESC
diff --git a/src/test/regress/expected/with.out b/src/test/regress/expected/with.out
index 7d796ea69c..1e3512c21d 100644
--- a/src/test/regress/expected/with.out
+++ b/src/test/regress/expected/with.out
@@ -3100,7 +3100,7 @@ WHEN NOT MATCHED THEN INSERT VALUES(o.k, o.v);
CTE cte_init
-> Result
Output: 1, 'cte_init val'::text
- InitPlan 2 (returns $1)
+ InitPlan 2
-> Limit
Output: ((cte_init.b || ' merge update'::text))
-> CTE Scan on cte_init
@@ -3143,11 +3143,11 @@ WHEN NOT MATCHED THEN INSERT VALUES(o.a, o.b || (SELECT merge_source_cte.*::text
CTE merge_source_cte
-> Result
Output: 15, 'merge_source_cte val'::text
- InitPlan 2 (returns $1)
+ InitPlan 2
-> CTE Scan on merge_source_cte merge_source_cte_1
Output: ((merge_source_cte_1.b || (merge_source_cte_1.*)::text) || ' merge update'::text)
Filter: (merge_source_cte_1.a = 15)
- InitPlan 3 (returns $2)
+ InitPlan 3
-> CTE Scan on merge_source_cte merge_source_cte_2
Output: ((merge_source_cte_2.*)::text || ' merge insert'::text)
-> Hash Right Join
diff --git a/src/test/regress/sql/subselect.sql b/src/test/regress/sql/subselect.sql
index 2f3601a058..7c42ebc36f 100644
--- a/src/test/regress/sql/subselect.sql
+++ b/src/test/regress/sql/subselect.sql
@@ -82,6 +82,20 @@ SELECT f1 AS "Correlated Field"
WHERE (f1, f2) IN (SELECT f2, CAST(f3 AS int4) FROM SUBSELECT_TBL
WHERE f3 IS NOT NULL);
+-- Check ROWCOMPARE cases, both correlated and not
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT ROW(1, 2) = (SELECT f1, f2) AS eq FROM SUBSELECT_TBL;
+
+SELECT ROW(1, 2) = (SELECT f1, f2) AS eq FROM SUBSELECT_TBL;
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT ROW(1, 2) = (SELECT 3, 4) AS eq FROM SUBSELECT_TBL;
+
+SELECT ROW(1, 2) = (SELECT 3, 4) AS eq FROM SUBSELECT_TBL;
+
+SELECT ROW(1, 2) = (SELECT f1, f2 FROM SUBSELECT_TBL); -- error
+
-- Subselects without aliases
SELECT count FROM (SELECT COUNT(DISTINCT name) FROM road);
В списке pgsql-hackers по дате отправления:
Следующее
От: Ayush VatsaДата:
Сообщение: Re: Proposal to include --exclude-extension Flag in pg_dump