Skip to content

Commit b919a97

Browse files
tglsfdctglsfdc
committed
Fix "failed to find plan for subquery/CTE" errors in EXPLAIN.
To deparse a reference to a field of a RECORD-type output of a subquery, EXPLAIN normally digs down into the subquery's plan to try to discover exactly which anonymous RECORD type is meant. However, this can fail if the subquery has been optimized out of the plan altogether on the grounds that no rows could pass the WHERE quals, which has been possible at least since 3fc6e2d. There isn't anything remaining in the plan tree that would help us, so fall back to printing the field name as "fN" for the N'th column of the record. (This will actually be the right thing some of the time, since it matches the column names we assign to RowExprs.) In passing, fix a comment typo in create_projection_plan, which I noticed while experimenting with an alternative fix for this. Per bug #18576 from Vasya B. Back-patch to all supported branches. Richard Guo and Tom Lane Discussion: https://postgr.es/m/18576-9feac34e132fea9e@postgresql.org
1 parent 7da1bdc commit b919a97

File tree

4 files changed

+113
-14
lines changed

4 files changed

+113
-14
lines changed

‎src/backend/optimizer/plan/createplan.c

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -2022,7 +2022,7 @@ create_projection_plan(PlannerInfo *root, ProjectionPath *best_path, int flags)
20222022
* Convert our subpath to a Plan and determine whether we need a Result
20232023
* node.
20242024
*
2025-
* In most cases where we don't need to project, creation_projection_path
2025+
* In most cases where we don't need to project, create_projection_path
20262026
* will have set dummypp, but not always. First, some createplan.c
20272027
* routines change the tlists of their nodes. (An example is that
20282028
* create_merge_append_plan might add resjunk sort columns to a

‎src/backend/utils/adt/ruleutils.c

Lines changed: 37 additions & 13 deletions
Original file line numberDiff line numberDiff line change
@@ -7895,17 +7895,31 @@ get_name_for_var_field(Var *var, int fieldno,
78957895
/*
78967896
* We're deparsing a Plan tree so we don't have complete
78977897
* RTE entries (in particular, rte->subquery is NULL). But
7898-
* the only place we'd see a Var directly referencing a
7899-
* SUBQUERY RTE is in a SubqueryScan plan node, and we can
7900-
* look into the child plan's tlist instead.
7898+
* the only place we'd normally see a Var directly
7899+
* referencing a SUBQUERY RTE is in a SubqueryScan plan
7900+
* node, and we can look into the child plan's tlist
7901+
* instead. An exception occurs if the subquery was
7902+
* proven empty and optimized away: then we'd find such a
7903+
* Var in a childless Result node, and there's nothing in
7904+
* the plan tree that would let us figure out what it had
7905+
* originally referenced. In that case, fall back on
7906+
* printing "fN", analogously to the default column names
7907+
* for RowExprs.
79017908
*/
79027909
TargetEntry *tle;
79037910
deparse_namespace save_dpns;
79047911
const char *result;
79057912

79067913
if (!dpns->inner_plan)
7907-
elog(ERROR, "failed to find plan for subquery %s",
7908-
rte->eref->aliasname);
7914+
{
7915+
char *dummy_name = palloc(32);
7916+
7917+
Assert(IsA(dpns->plan, Result));
7918+
snprintf(dummy_name, 32, "f%d", fieldno);
7919+
return dummy_name;
7920+
}
7921+
Assert(IsA(dpns->plan, SubqueryScan));
7922+
79097923
tle = get_tle_by_resno(dpns->inner_tlist, attnum);
79107924
if (!tle)
79117925
elog(ERROR, "bogus varattno for subquery var: %d",
@@ -8014,20 +8028,30 @@ get_name_for_var_field(Var *var, int fieldno,
80148028
{
80158029
/*
80168030
* We're deparsing a Plan tree so we don't have a CTE
8017-
* list. But the only places we'd see a Var directly
8018-
* referencing a CTE RTE are in CteScan or WorkTableScan
8019-
* plan nodes. For those cases, set_deparse_plan arranged
8020-
* for dpns->inner_plan to be the plan node that emits the
8021-
* CTE or RecursiveUnion result, and we can look at its
8022-
* tlist instead.
8031+
* list. But the only places we'd normally see a Var
8032+
* directly referencing a CTE RTE are in CteScan or
8033+
* WorkTableScan plan nodes. For those cases,
8034+
* set_deparse_plan arranged for dpns->inner_plan to be
8035+
* the plan node that emits the CTE or RecursiveUnion
8036+
* result, and we can look at its tlist instead. As
8037+
* above, this can fail if the CTE has been proven empty,
8038+
* in which case fall back to "fN".
80238039
*/
80248040
TargetEntry *tle;
80258041
deparse_namespace save_dpns;
80268042
const char *result;
80278043

80288044
if (!dpns->inner_plan)
8029-
elog(ERROR, "failed to find plan for CTE %s",
8030-
rte->eref->aliasname);
8045+
{
8046+
char *dummy_name = palloc(32);
8047+
8048+
Assert(IsA(dpns->plan, Result));
8049+
snprintf(dummy_name, 32, "f%d", fieldno);
8050+
return dummy_name;
8051+
}
8052+
Assert(IsA(dpns->plan, CteScan) ||
8053+
IsA(dpns->plan, WorkTableScan));
8054+
80318055
tle = get_tle_by_resno(dpns->inner_tlist, attnum);
80328056
if (!tle)
80338057
elog(ERROR, "bogus varattno for subquery var: %d",

‎src/test/regress/expected/rowtypes.out

Lines changed: 54 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1300,6 +1300,60 @@ select pg_get_viewdef('composite_v', true);
13001300
(1 row)
13011301

13021302
drop view composite_v;
1303+
--
1304+
-- Check cases where the composite comes from a proven-dummy rel (bug #18576)
1305+
--
1306+
explain (verbose, costs off)
1307+
select (ss.a).x, (ss.a).n from
1308+
(select information_schema._pg_expandarray(array[1,2]) AS a) ss;
1309+
QUERY PLAN
1310+
------------------------------------------------------------------------
1311+
Subquery Scan on ss
1312+
Output: (ss.a).x, (ss.a).n
1313+
-> ProjectSet
1314+
Output: information_schema._pg_expandarray('{1,2}'::integer[])
1315+
-> Result
1316+
(5 rows)
1317+
1318+
explain (verbose, costs off)
1319+
select (ss.a).x, (ss.a).n from
1320+
(select information_schema._pg_expandarray(array[1,2]) AS a) ss
1321+
where false;
1322+
QUERY PLAN
1323+
--------------------------
1324+
Result
1325+
Output: (a).f1, (a).f2
1326+
One-Time Filter: false
1327+
(3 rows)
1328+
1329+
explain (verbose, costs off)
1330+
with cte(c) as materialized (select row(1, 2)),
1331+
cte2(c) as (select * from cte)
1332+
select (c).f1 from cte2 as t;
1333+
QUERY PLAN
1334+
-----------------------------------
1335+
CTE Scan on cte
1336+
Output: (cte.c).f1
1337+
CTE cte
1338+
-> Result
1339+
Output: '(1,2)'::record
1340+
(5 rows)
1341+
1342+
explain (verbose, costs off)
1343+
with cte(c) as materialized (select row(1, 2)),
1344+
cte2(c) as (select * from cte)
1345+
select (c).f1 from cte2 as t
1346+
where false;
1347+
QUERY PLAN
1348+
-----------------------------------
1349+
Result
1350+
Output: (cte.c).f1
1351+
One-Time Filter: false
1352+
CTE cte
1353+
-> Result
1354+
Output: '(1,2)'::record
1355+
(6 rows)
1356+
13031357
--
13041358
-- Tests for component access / FieldSelect
13051359
--

‎src/test/regress/sql/rowtypes.sql

Lines changed: 21 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -520,6 +520,27 @@ where (select * from (select c as c1) s
520520
select pg_get_viewdef('composite_v', true);
521521
drop view composite_v;
522522

523+
--
524+
-- Check cases where the composite comes from a proven-dummy rel (bug #18576)
525+
--
526+
explain (verbose, costs off)
527+
select (ss.a).x, (ss.a).n from
528+
(select information_schema._pg_expandarray(array[1,2]) AS a) ss;
529+
explain (verbose, costs off)
530+
select (ss.a).x, (ss.a).n from
531+
(select information_schema._pg_expandarray(array[1,2]) AS a) ss
532+
where false;
533+
534+
explain (verbose, costs off)
535+
with cte(c) as materialized (select row(1, 2)),
536+
cte2(c) as (select * from cte)
537+
select (c).f1 from cte2 as t;
538+
explain (verbose, costs off)
539+
with cte(c) as materialized (select row(1, 2)),
540+
cte2(c) as (select * from cte)
541+
select (c).f1 from cte2 as t
542+
where false;
543+
523544
--
524545
-- Tests for component access / FieldSelect
525546
--

0 commit comments

Comments
 (0)