Skip to content

Commit 12010f4

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 8c0944a commit 12010f4

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
@@ -2015,7 +2015,7 @@ create_projection_plan(PlannerInfo *root, ProjectionPath *best_path, int flags)
20152015
* Convert our subpath to a Plan and determine whether we need a Result
20162016
* node.
20172017
*
2018-
* In most cases where we don't need to project, creation_projection_path
2018+
* In most cases where we don't need to project, create_projection_path
20192019
* will have set dummypp, but not always. First, some createplan.c
20202020
* routines change the tlists of their nodes. (An example is that
20212021
* 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
@@ -7878,17 +7878,31 @@ get_name_for_var_field(Var *var, int fieldno,
78787878
/*
78797879
* We're deparsing a Plan tree so we don't have complete
78807880
* RTE entries (in particular, rte->subquery is NULL). But
7881-
* the only place we'd see a Var directly referencing a
7882-
* SUBQUERY RTE is in a SubqueryScan plan node, and we can
7883-
* look into the child plan's tlist instead.
7881+
* the only place we'd normally see a Var directly
7882+
* referencing a SUBQUERY RTE is in a SubqueryScan plan
7883+
* node, and we can look into the child plan's tlist
7884+
* instead. An exception occurs if the subquery was
7885+
* proven empty and optimized away: then we'd find such a
7886+
* Var in a childless Result node, and there's nothing in
7887+
* the plan tree that would let us figure out what it had
7888+
* originally referenced. In that case, fall back on
7889+
* printing "fN", analogously to the default column names
7890+
* for RowExprs.
78847891
*/
78857892
TargetEntry *tle;
78867893
deparse_namespace save_dpns;
78877894
const char *result;
78887895

78897896
if (!dpns->inner_plan)
7890-
elog(ERROR, "failed to find plan for subquery %s",
7891-
rte->eref->aliasname);
7897+
{
7898+
char *dummy_name = palloc(32);
7899+
7900+
Assert(IsA(dpns->plan, Result));
7901+
snprintf(dummy_name, 32, "f%d", fieldno);
7902+
return dummy_name;
7903+
}
7904+
Assert(IsA(dpns->plan, SubqueryScan));
7905+
78927906
tle = get_tle_by_resno(dpns->inner_tlist, attnum);
78937907
if (!tle)
78947908
elog(ERROR, "bogus varattno for subquery var: %d",
@@ -7997,20 +8011,30 @@ get_name_for_var_field(Var *var, int fieldno,
79978011
{
79988012
/*
79998013
* We're deparsing a Plan tree so we don't have a CTE
8000-
* list. But the only places we'd see a Var directly
8001-
* referencing a CTE RTE are in CteScan or WorkTableScan
8002-
* plan nodes. For those cases, set_deparse_plan arranged
8003-
* for dpns->inner_plan to be the plan node that emits the
8004-
* CTE or RecursiveUnion result, and we can look at its
8005-
* tlist instead.
8014+
* list. But the only places we'd normally see a Var
8015+
* directly referencing a CTE RTE are in CteScan or
8016+
* WorkTableScan plan nodes. For those cases,
8017+
* set_deparse_plan arranged for dpns->inner_plan to be
8018+
* the plan node that emits the CTE or RecursiveUnion
8019+
* result, and we can look at its tlist instead. As
8020+
* above, this can fail if the CTE has been proven empty,
8021+
* in which case fall back to "fN".
80068022
*/
80078023
TargetEntry *tle;
80088024
deparse_namespace save_dpns;
80098025
const char *result;
80108026

80118027
if (!dpns->inner_plan)
8012-
elog(ERROR, "failed to find plan for CTE %s",
8013-
rte->eref->aliasname);
8028+
{
8029+
char *dummy_name = palloc(32);
8030+
8031+
Assert(IsA(dpns->plan, Result));
8032+
snprintf(dummy_name, 32, "f%d", fieldno);
8033+
return dummy_name;
8034+
}
8035+
Assert(IsA(dpns->plan, CteScan) ||
8036+
IsA(dpns->plan, WorkTableScan));
8037+
80148038
tle = get_tle_by_resno(dpns->inner_tlist, attnum);
80158039
if (!tle)
80168040
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
@@ -1269,6 +1269,60 @@ select pg_get_viewdef('composite_v', true);
12691269
(1 row)
12701270

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

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

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

516+
--
517+
-- Check cases where the composite comes from a proven-dummy rel (bug #18576)
518+
--
519+
explain (verbose, costs off)
520+
select (ss.a).x, (ss.a).n from
521+
(select information_schema._pg_expandarray(array[1,2]) AS a) ss;
522+
explain (verbose, costs off)
523+
select (ss.a).x, (ss.a).n from
524+
(select information_schema._pg_expandarray(array[1,2]) AS a) ss
525+
where false;
526+
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+
explain (verbose, costs off)
532+
with cte(c) as materialized (select row(1, 2)),
533+
cte2(c) as (select * from cte)
534+
select (c).f1 from cte2 as t
535+
where false;
536+
516537
--
517538
-- Tests for component access / FieldSelect
518539
--

0 commit comments

Comments
 (0)