Skip to content

Commit 3ad35d5

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 2ee02c9 commit 3ad35d5

File tree

4 files changed

+109
-11
lines changed

4 files changed

+109
-11
lines changed

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

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

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

Lines changed: 33 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -7279,17 +7279,31 @@ get_name_for_var_field(Var *var, int fieldno,
72797279
/*
72807280
* We're deparsing a Plan tree so we don't have complete
72817281
* RTE entries (in particular, rte->subquery is NULL). But
7282-
* the only place we'd see a Var directly referencing a
7283-
* SUBQUERY RTE is in a SubqueryScan plan node, and we can
7284-
* look into the child plan's tlist instead.
7282+
* the only place we'd normally see a Var directly
7283+
* referencing a SUBQUERY RTE is in a SubqueryScan plan
7284+
* node, and we can look into the child plan's tlist
7285+
* instead. An exception occurs if the subquery was
7286+
* proven empty and optimized away: then we'd find such a
7287+
* Var in a childless Result node, and there's nothing in
7288+
* the plan tree that would let us figure out what it had
7289+
* originally referenced. In that case, fall back on
7290+
* printing "fN", analogously to the default column names
7291+
* for RowExprs.
72857292
*/
72867293
TargetEntry *tle;
72877294
deparse_namespace save_dpns;
72887295
const char *result;
72897296

72907297
if (!dpns->inner_plan)
7291-
elog(ERROR, "failed to find plan for subquery %s",
7292-
rte->eref->aliasname);
7298+
{
7299+
char *dummy_name = palloc(32);
7300+
7301+
Assert(IsA(dpns->plan, Result));
7302+
snprintf(dummy_name, 32, "f%d", fieldno);
7303+
return dummy_name;
7304+
}
7305+
Assert(IsA(dpns->plan, SubqueryScan));
7306+
72937307
tle = get_tle_by_resno(dpns->inner_tlist, attnum);
72947308
if (!tle)
72957309
elog(ERROR, "bogus varattno for subquery var: %d",
@@ -7398,17 +7412,26 @@ get_name_for_var_field(Var *var, int fieldno,
73987412
{
73997413
/*
74007414
* We're deparsing a Plan tree so we don't have a CTE
7401-
* list. But the only place we'd see a Var directly
7402-
* referencing a CTE RTE is in a CteScan plan node, and we
7403-
* can look into the subplan's tlist instead.
7415+
* list. But the only place we'd normally see a Var
7416+
* directly referencing a CTE RTE is in a CteScan plan
7417+
* node, and we can look into the subplan's tlist instead.
7418+
* As above, this can fail if the CTE has been proven
7419+
* empty, in which case fall back to "fN".
74047420
*/
74057421
TargetEntry *tle;
74067422
deparse_namespace save_dpns;
74077423
const char *result;
74087424

74097425
if (!dpns->inner_plan)
7410-
elog(ERROR, "failed to find plan for CTE %s",
7411-
rte->eref->aliasname);
7426+
{
7427+
char *dummy_name = palloc(32);
7428+
7429+
Assert(IsA(dpns->plan, Result));
7430+
snprintf(dummy_name, 32, "f%d", fieldno);
7431+
return dummy_name;
7432+
}
7433+
Assert(IsA(dpns->plan, CteScan));
7434+
74127435
tle = get_tle_by_resno(dpns->inner_tlist, attnum);
74137436
if (!tle)
74147437
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
@@ -1263,6 +1263,60 @@ select pg_get_viewdef('composite_v', true);
12631263
(1 row)
12641264

12651265
drop view composite_v;
1266+
--
1267+
-- Check cases where the composite comes from a proven-dummy rel (bug #18576)
1268+
--
1269+
explain (verbose, costs off)
1270+
select (ss.a).x, (ss.a).n from
1271+
(select information_schema._pg_expandarray(array[1,2]) AS a) ss;
1272+
QUERY PLAN
1273+
------------------------------------------------------------------------
1274+
Subquery Scan on ss
1275+
Output: (ss.a).x, (ss.a).n
1276+
-> ProjectSet
1277+
Output: information_schema._pg_expandarray('{1,2}'::integer[])
1278+
-> Result
1279+
(5 rows)
1280+
1281+
explain (verbose, costs off)
1282+
select (ss.a).x, (ss.a).n from
1283+
(select information_schema._pg_expandarray(array[1,2]) AS a) ss
1284+
where false;
1285+
QUERY PLAN
1286+
--------------------------
1287+
Result
1288+
Output: (a).f1, (a).f2
1289+
One-Time Filter: false
1290+
(3 rows)
1291+
1292+
explain (verbose, costs off)
1293+
with cte(c) as materialized (select row(1, 2)),
1294+
cte2(c) as (select * from cte)
1295+
select (c).f1 from cte2 as t;
1296+
QUERY PLAN
1297+
-----------------------------------
1298+
CTE Scan on cte
1299+
Output: (cte.c).f1
1300+
CTE cte
1301+
-> Result
1302+
Output: '(1,2)'::record
1303+
(5 rows)
1304+
1305+
explain (verbose, costs off)
1306+
with cte(c) as materialized (select row(1, 2)),
1307+
cte2(c) as (select * from cte)
1308+
select (c).f1 from cte2 as t
1309+
where false;
1310+
QUERY PLAN
1311+
-----------------------------------
1312+
Result
1313+
Output: (cte.c).f1
1314+
One-Time Filter: false
1315+
CTE cte
1316+
-> Result
1317+
Output: '(1,2)'::record
1318+
(6 rows)
1319+
12661320
--
12671321
-- Tests for component access / FieldSelect
12681322
--

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

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

512+
--
513+
-- Check cases where the composite comes from a proven-dummy rel (bug #18576)
514+
--
515+
explain (verbose, costs off)
516+
select (ss.a).x, (ss.a).n from
517+
(select information_schema._pg_expandarray(array[1,2]) AS a) ss;
518+
explain (verbose, costs off)
519+
select (ss.a).x, (ss.a).n from
520+
(select information_schema._pg_expandarray(array[1,2]) AS a) ss
521+
where false;
522+
523+
explain (verbose, costs off)
524+
with cte(c) as materialized (select row(1, 2)),
525+
cte2(c) as (select * from cte)
526+
select (c).f1 from cte2 as t;
527+
explain (verbose, costs off)
528+
with cte(c) as materialized (select row(1, 2)),
529+
cte2(c) as (select * from cte)
530+
select (c).f1 from cte2 as t
531+
where false;
532+
512533
--
513534
-- Tests for component access / FieldSelect
514535
--

0 commit comments

Comments
 (0)