diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out new file mode 100644 index c355e8f..f0ff936 --- a/contrib/postgres_fdw/expected/postgres_fdw.out +++ b/contrib/postgres_fdw/expected/postgres_fdw.out @@ -3021,7 +3021,7 @@ select exists(select 1 from pg_enum), su QUERY PLAN -------------------------------------------------- Foreign Scan - Output: $0, (sum(ft1.c1)) + Output: (InitPlan 1).$0, (sum(ft1.c1)) Relations: Aggregate on (public.ft1) Remote SQL: SELECT sum("C 1") FROM "S 1"."T 1" InitPlan 1 (returns $0) @@ -3039,7 +3039,7 @@ select exists(select 1 from pg_enum), su QUERY PLAN --------------------------------------------------- GroupAggregate - Output: $0, sum(ft1.c1) + Output: (InitPlan 1).$0, sum(ft1.c1) InitPlan 1 (returns $0) -> Seq Scan on pg_catalog.pg_enum -> Foreign Scan on public.ft1 @@ -3264,14 +3264,14 @@ select sum(c1) filter (where (c1 / c1) * 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).$0))) -> Foreign Scan on public.ft1 Output: ft1.c2 Remote SQL: SELECT c2 FROM "S 1"."T 1" - SubPlan 1 + SubPlan 1 (returns $0) -> Foreign Scan on public.ft1 ft1_1 Output: ft1_1.c2 Remote SQL: SELECT c2 FROM "S 1"."T 1" WHERE ((c2 < 5)) @@ -11895,12 +11895,12 @@ CREATE FOREIGN TABLE foreign_tbl2 () INH 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) - SubPlan 1 + Filter: (ANY ((base_tbl.a = (SubPlan 1).$1) AND ((random() > '0'::double precision) = (SubPlan 1).$2))) + SubPlan 1 (returns $1,$2) -> Result Output: base_tbl.a, (random() > '0'::double precision) -> Append diff --git a/src/backend/optimizer/plan/subselect.c b/src/backend/optimizer/plan/subselect.c new file mode 100644 index 47e1472..d5919a9 --- a/src/backend/optimizer/plan/subselect.c +++ b/src/backend/optimizer/plan/subselect.c @@ -326,6 +326,7 @@ build_subplan(PlannerInfo *root, Plan *p Node *result; SubPlan *splan; bool isInitPlan; + StringInfoData splanname; ListCell *lc; /* @@ -560,22 +561,31 @@ build_subplan(PlannerInfo *root, Plan *p 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); + initStringInfo(&splanname); + appendStringInfo(&splanname, "%s %d", + isInitPlan ? "InitPlan" : "SubPlan", + splan->plan_id); if (splan->setParam) { - char *ptr = splan->plan_name + strlen(splan->plan_name); - - ptr += sprintf(ptr, " (returns "); + appendStringInfoString(&splanname, " (returns "); foreach(lc, splan->setParam) { - ptr += sprintf(ptr, "$%d%s", - lfirst_int(lc), - lnext(splan->setParam, lc) ? "," : ")"); + appendStringInfo(&splanname, "$%d%s", + lfirst_int(lc), + lnext(splan->setParam, lc) ? "," : ")"); } } + else if (splan->paramIds) + { + appendStringInfoString(&splanname, " (returns "); + foreach(lc, splan->paramIds) + { + appendStringInfo(&splanname, "$%d%s", + lfirst_int(lc), + lnext(splan->paramIds, lc) ? "," : ")"); + } + } + splan->plan_name = splanname.data; /* Lastly, fill in the cost estimates for use later */ cost_subplan(root, splan, plan); diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c new file mode 100644 index 2a1ee69..44de557 --- 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,80 @@ get_parameter(Param *param, deparse_cont return; } + /* Is it a subplan output? */ + if (param->paramkind == PARAM_EXEC) + { + SubPlan *subplan = NULL; + + 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; + break; + } + } + } + else if (dpns->in_subplan->paramIds) + { + foreach_int(paramid, dpns->in_subplan->paramIds) + { + if (paramid == param->paramid) + { + subplan = dpns->in_subplan; + 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; + break; + } + } + } + else if (initplan->paramIds) + { + foreach_int(paramid, initplan->paramIds) + { + if (paramid == param->paramid) + { + subplan = initplan; + break; + } + } + } + } + } + + if (subplan) + { + appendStringInfo(context->buf, "(%s%s %d).$%d", + subplan->useHashTable ? "hashed " : "", + subplan == dpns->in_subplan ? "SubPlan" : "InitPlan", + subplan->plan_id, param->paramid); + return; + } + } + /* * If it's an external parameter, see if the outermost namespace provides * function argument names. @@ -8863,17 +8938,68 @@ get_rule_expr(Node *node, deparse_contex 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 new file mode 100644 index f86cf8d..94afdf5 --- 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 diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out new file mode 100644 index 130a924..85a3c14 --- a/src/test/regress/expected/inherit.out +++ b/src/test/regress/expected/inherit.out @@ -1643,7 +1643,7 @@ explain (verbose, costs off) select min( QUERY PLAN --------------------------------------------------------------------------------- Result - Output: $0 + Output: (InitPlan 1).$0 InitPlan 1 (returns $0) -> Limit Output: ((1 - matest0.id)) diff --git a/src/test/regress/expected/insert_conflict.out b/src/test/regress/expected/insert_conflict.out new file mode 100644 index 563c5eb..701217d --- 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 insertco 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 new file mode 100644 index 9605400..06b6b8c --- a/src/test/regress/expected/join.out +++ b/src/test/regress/expected/join.out @@ -3028,11 +3028,11 @@ where unique1 in (select unique2 from te 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)) - SubPlan 1 + Filter: (NOT (ANY (unique1 = (hashed SubPlan 1).$0))) + SubPlan 1 (returns $0) -> Index Only Scan using tenk1_unique2 on tenk1 b (4 rows) @@ -5278,13 +5278,13 @@ 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) - SubPlan 1 + Filter: (ANY ((unique2 = (SubPlan 1).$1) AND ((random() > '0'::double precision) = (SubPlan 1).$2))) + SubPlan 1 (returns $1,$2) -> Seq Scan on int8_tbl c Filter: (q1 < b.unique1) -> Hash @@ -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,11 +8277,11 @@ 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) - SubPlan 3 + Filter: (ANY ((t2.q1 = (SubPlan 3).$5) AND ((random() > '0'::double precision) = (SubPlan 3).$6))) + SubPlan 3 (returns $5,$6) -> Result Output: t3.q2, (random() > '0'::double precision) - One-Time Filter: $4 + One-Time Filter: (InitPlan 2).$4 InitPlan 1 (returns $2) -> Result Output: GREATEST(t1.q1, t2.q2) diff --git a/src/test/regress/expected/memoize.out b/src/test/regress/expected/memoize.out new file mode 100644 index cf6886a..8fb2406 --- a/src/test/regress/expected/memoize.out +++ b/src/test/regress/expected/memoize.out @@ -313,7 +313,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 new file mode 100644 index bf0657b..89312a5 --- a/src/test/regress/expected/partition_prune.out +++ b/src/test/regress/expected/partition_prune.out @@ -3976,7 +3976,7 @@ select * from listp where a = (select 2) QUERY PLAN -------------------------------------------------- Seq Scan on listp1 listp (actual rows=0 loops=1) - Filter: ((b <> 10) AND (a = $0)) + Filter: ((b <> 10) AND (a = (InitPlan 1).$0)) InitPlan 1 (returns $0) -> Result (never executed) (4 rows) diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.out new file mode 100644 index 6988128..55db827 --- a/src/test/regress/expected/rowsecurity.out +++ b/src/test/regress/expected/rowsecurity.out @@ -265,10 +265,10 @@ NOTICE: f_leak => awesome science ficti (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)) + Filter: ((dlevel <= (InitPlan 1).$0) AND f_leak(dtitle)) InitPlan 1 (returns $0) -> Index Scan using uaccount_pkey on uaccount Index Cond: (pguser = CURRENT_USER) @@ -329,10 +329,10 @@ NOTICE: f_leak => awesome technology bo (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)) + Filter: ((cid <> 44) AND (cid <> 44) AND (cid < 50) AND (dlevel <= (InitPlan 1).$0) AND f_leak(dtitle)) InitPlan 1 (returns $0) -> Index Scan using uaccount_pkey on uaccount Index Cond: (pguser = CURRENT_USER) @@ -1059,10 +1059,10 @@ NOTICE: f_leak => awesome science ficti (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)) + Filter: ((cid < 55) AND (dlevel <= (InitPlan 1).$0) AND f_leak(dtitle)) InitPlan 1 (returns $0) -> Index Scan using uaccount_pkey on uaccount Index Cond: (pguser = CURRENT_USER) @@ -1137,10 +1137,10 @@ NOTICE: f_leak => awesome science ficti (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)) + Filter: ((cid < 55) AND (dlevel <= (InitPlan 1).$0) AND f_leak(dtitle)) InitPlan 1 (returns $0) -> Index Scan using uaccount_pkey on uaccount Index Cond: (pguser = CURRENT_USER) @@ -1437,11 +1437,11 @@ NOTICE: f_leak => 03b26944890929ff75165 (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)) - SubPlan 1 + Filter: ((ANY (a = (hashed SubPlan 1).$0)) AND f_leak(b)) + SubPlan 1 (returns $0) -> Seq Scan on s2 Filter: (((x % 2) = 0) AND (y ~~ '%2f%'::text)) (5 rows) @@ -1457,11 +1457,11 @@ NOTICE: f_leak => 03b26944890929ff75165 (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)) - SubPlan 1 + Filter: ((ANY (a = (hashed SubPlan 1).$0)) AND f_leak(b)) + SubPlan 1 (returns $0) -> Seq Scan on s2 Filter: (((x % 2) = 0) AND (y ~~ '%af%'::text)) (5 rows) @@ -1480,8 +1480,8 @@ EXPLAIN (COSTS OFF) SELECT (SELECT x FRO SubPlan 2 -> Limit -> Seq Scan on s1 - Filter: (hashed SubPlan 1) - SubPlan 1 + Filter: (ANY (a = (hashed SubPlan 1).$1)) + SubPlan 1 (returns $1) -> Seq Scan on s2 s2_1 Filter: (((x % 2) = 0) AND (y ~~ '%af%'::text)) (9 rows) @@ -2687,11 +2687,11 @@ 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)) - SubPlan 1 + Filter: ((NOT (ANY (a = (hashed SubPlan 1).$0))) AND ((a % 2) = 0) AND f_leak(b)) + SubPlan 1 (returns $0) -> Seq Scan on z1_blacklist (4 rows) @@ -2705,11 +2705,11 @@ 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)) - SubPlan 1 + Filter: ((NOT (ANY (a = (hashed SubPlan 1).$0))) AND ((a % 2) = 0) AND f_leak(b)) + SubPlan 1 (returns $0) -> Seq Scan on z1_blacklist (4 rows) @@ -2877,11 +2877,11 @@ 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)) - SubPlan 1 + Filter: ((NOT (ANY (a = (hashed SubPlan 1).$0))) AND ((a % 2) = 0) AND f_leak(b)) + SubPlan 1 (returns $0) -> Seq Scan on z1_blacklist (4 rows) @@ -2903,11 +2903,11 @@ 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)) - SubPlan 1 + Filter: ((NOT (ANY (a = (hashed SubPlan 1).$0))) AND ((a % 2) = 1) AND f_leak(b)) + SubPlan 1 (returns $0) -> Seq Scan on z1_blacklist (4 rows) diff --git a/src/test/regress/expected/rowtypes.out b/src/test/regress/expected/rowtypes.out new file mode 100644 index 8f3c153..30cb35e --- a/src/test/regress/expected/rowtypes.out +++ b/src/test/regress/expected/rowtypes.out @@ -1260,7 +1260,7 @@ where (select * from (select c as c1) s SubPlan 3 -> Result Output: cte.c - One-Time Filter: $2 + One-Time Filter: (InitPlan 2).$2 InitPlan 2 (returns $2) -> Result Output: ((cte.c).f1 > 0) diff --git a/src/test/regress/expected/select_parallel.out b/src/test/regress/expected/select_parallel.out new file mode 100644 index 7a0d78d..a4aecb2 --- a/src/test/regress/expected/select_parallel.out +++ b/src/test/regress/expected/select_parallel.out @@ -291,15 +291,15 @@ alter table tenk2 set (parallel_workers 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)) - SubPlan 1 + Filter: (NOT (ANY ((two = (hashed SubPlan 1).$0) AND (four = (hashed SubPlan 1).$1)))) + SubPlan 1 (returns $0,$1) -> Seq Scan on tenk2 Filter: (thousand > 100) (9 rows) @@ -315,11 +315,11 @@ select count(*) from tenk1 where (two, f 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)) - SubPlan 1 + Filter: (NOT (ANY ((((unique1)::double precision + random()))::integer = (hashed SubPlan 1).$0))) + SubPlan 1 (returns $0) -> Seq Scan on tenk2 (4 rows) @@ -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 new file mode 100644 index 5e7da96..14015bb --- a/src/test/regress/expected/sqljson.out +++ b/src/test/regress/expected/sqljson.out @@ -1064,7 +1064,7 @@ SELECT JSON_ARRAY(SELECT i FROM (VALUES QUERY PLAN --------------------------------------------------------------------- Result - Output: $0 + Output: (InitPlan 1).$0 InitPlan 1 (returns $0) -> Aggregate Output: JSON_ARRAYAGG("*VALUES*".column1 RETURNING jsonb) diff --git a/src/test/regress/expected/subselect.out b/src/test/regress/expected/subselect.out new file mode 100644 index e41b728..8be66a1 --- 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).$2) AND (2 = (SubPlan 1).$3))) + SubPlan 1 (returns $2,$3) + -> 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).$0) AND (2 = (InitPlan 1).$1)) + InitPlan 1 (returns $0,$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,11 +375,11 @@ explain (verbose, costs off) select '42' -- 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) - SubPlan 2 + Output: (ALL (1 = (SubPlan 2).$1)) + SubPlan 2 (returns $1) -> Materialize Output: ($0) InitPlan 1 (returns $0) @@ -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,11 +891,11 @@ select * from outer_text where (f1, f2) -- 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) - SubPlan 1 + Output: (ANY ('foo'::text = (hashed SubPlan 1).$0)) + SubPlan 1 (returns $0) -> Append -> Result Output: 'bar'::name @@ -864,11 +915,11 @@ select 'foo'::text in (select 'bar'::nam -- explain (verbose, costs off) select row(row(row(1))) = any (select row(row(1))); - QUERY PLAN -------------------------------------------- + QUERY PLAN +------------------------------------------------------ Result - Output: (SubPlan 1) - SubPlan 1 + Output: (ANY ('("(1)")'::record = (SubPlan 1).$0)) + SubPlan 1 (returns $0) -> Materialize Output: '("(1)")'::record -> Result @@ -907,11 +958,11 @@ 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) - SubPlan 1 + Filter: (ANY ((q1)::text = (hashed SubPlan 1).$0)) + SubPlan 1 (returns $0) -> Seq Scan on inner_text (4 rows) @@ -928,11 +979,11 @@ create or replace function bogus_int8_te 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) - SubPlan 1 + Filter: (ANY (((q1)::text = (hashed SubPlan 1).$0) AND ((q1)::text = (hashed SubPlan 1).$0))) + SubPlan 1 (returns $0) -> Seq Scan on inner_text (4 rows) @@ -949,11 +1000,11 @@ create or replace function bogus_int8_te 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) - SubPlan 1 + Filter: (ANY ((SubPlan 1).$0 = (q1)::text)) + SubPlan 1 (returns $0) -> Materialize -> Seq Scan on inner_text (5 rows) @@ -972,12 +1023,12 @@ rollback; -- to get rid of the bogus op 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)) - SubPlan 2 + Filter: ((ANY (unique2 = (hashed SubPlan 2).$1)) OR (ten < 0)) + SubPlan 2 (returns $1) -> Index Only Scan using tenk1_unique1 on tenk1 k (5 rows) @@ -997,7 +1048,7 @@ where (exists(select 1 from tenk1 k wher 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,8 +1085,8 @@ 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)) - SubPlan 2 + Filter: ((ANY (c1 = (hashed SubPlan 2).$1)) OR (c3 < 0)) + SubPlan 2 (returns $1) -> Append -> Seq Scan on exists_tbl_null t2_4 -> Seq Scan on exists_tbl_def t2_5 @@ -1071,10 +1122,10 @@ 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 + Output: (InitPlan 1).$0, (InitPlan 2).$1 InitPlan 1 (returns $0) -> Result Output: now() @@ -1091,7 +1142,7 @@ explain (verbose, costs off) Subquery Scan on ss Output: ss.x, ss.x -> Values Scan on "*VALUES*" - Output: $0 + Output: (InitPlan 1).$0 InitPlan 1 (returns $0) -> Result Output: random() @@ -1143,16 +1194,16 @@ where o.ten = 0; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Aggregate - Output: sum((((hashed SubPlan 1)))::integer) + Output: sum((((ANY (i.ten = (hashed SubPlan 1).$1))))::integer) -> Nested Loop - Output: ((hashed SubPlan 1)) + Output: ((ANY (i.ten = (hashed SubPlan 1).$1))) -> 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).$1)), random() Index Cond: (i.unique1 = o.unique1) - SubPlan 1 + SubPlan 1 (returns $1) -> Seq Scan on public.int4_tbl Output: int4_tbl.f1 Filter: (int4_tbl.f1 <= o.hundred) @@ -1319,7 +1370,7 @@ select * from ---------------------------------------- Values Scan on "*VALUES*" Output: "*VALUES*".column1 - SubPlan 1 + SubPlan 1 (returns $0) -> Values Scan on "*VALUES*_1" Output: "*VALUES*_1".column1 (5 rows) @@ -1346,12 +1397,12 @@ 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).$0)) 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 Output: b.unique1, b.unique2, b.two, b.four, b.ten, b.twenty, b.hundred, b.thousand, b.twothousand, b.fivethous, b.tenthous, b.odd, b.even, b.stringu1, b.stringu2, b.string4 - SubPlan 1 + SubPlan 1 (returns $0) -> Index Only Scan using tenk1_unique1 on public.tenk1 a Output: a.unique1 (10 rows) @@ -1945,14 +1996,14 @@ 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).$1)) -> Seq Scan on tenk1 a -> Materialize -> Seq Scan on tenk2 b - SubPlan 1 + SubPlan 1 (returns $1) -> Seq Scan on tenk2 c Filter: (odd = b.odd) (8 rows) @@ -1962,14 +2013,14 @@ 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).$1)) -> Seq Scan on tenk1 a -> Materialize -> Seq Scan on tenk2 b - SubPlan 1 + SubPlan 1 (returns $1) -> Seq Scan on tenk2 c Filter: (odd = b.odd) (8 rows) @@ -1979,14 +2030,14 @@ ON A.hundred in (SELECT c.hundred FROM t 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).$1)) -> Seq Scan on tenk1 a -> Materialize -> Seq Scan on tenk2 b - SubPlan 1 + SubPlan 1 (returns $1) -> Seq Scan on tenk2 c Filter: (odd = a.odd) (8 rows) diff --git a/src/test/regress/expected/updatable_views.out b/src/test/regress/expected/updatable_views.out new file mode 100644 index 794cf9c..f25c042 --- a/src/test/regress/expected/updatable_views.out +++ b/src/test/regress/expected/updatable_views.out @@ -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 l -> 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 l -> 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 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 l -> 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 l -> 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; diff --git a/src/test/regress/sql/subselect.sql b/src/test/regress/sql/subselect.sql new file mode 100644 index 2f3601a..7c42ebc --- 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);