Skip to content

Commit 8122160

Browse files
tglsfdctglsfdc
committed
Fix EXPLAIN of SEARCH BREADTH FIRST with a constant initial value.
If the non-recursive term of a SEARCH BREADTH FIRST recursive query has only constants in its target list, the planner will fold the starting RowExpr added by rewrite into a simple Const of type RECORD. The executor doesn't have any problem with that --- but EXPLAIN VERBOSE will encounter the Const as the ultimate source of truth about what the field names of the SET column are, and it didn't know what to do with that. Fortunately, we can pull the identifying typmod out of the Const, in much the same way that record_out would. For reasons that remain a bit obscure to me, this only fails with SEARCH BREADTH FIRST, not SEARCH DEPTH FIRST or CYCLE. But I added regression test cases for both of those options too, just to make sure we don't break it in future. Per bug #17644 from Matthijs van der Vleuten. Back-patch to v14 where these constructs were added. Discussion: https://postgr.es/m/17644-3bd1f3036d6d7a16@postgresql.org
1 parent 18e6071 commit 8122160

File tree

4 files changed

+208
-1
lines changed

4 files changed

+208
-1
lines changed

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

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7404,7 +7404,8 @@ get_name_for_var_field(Var *var, int fieldno,
74047404

74057405
/*
74067406
* If it's a RowExpr that was expanded from a whole-row Var, use the
7407-
* column names attached to it.
7407+
* column names attached to it. (We could let get_expr_result_tupdesc()
7408+
* handle this, but it's much cheaper to just pull out the name we need.)
74087409
*/
74097410
if (IsA(var, RowExpr))
74107411
{

‎src/backend/utils/fmgr/funcapi.c

Lines changed: 34 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -270,6 +270,40 @@ get_expr_result_type(Node *expr,
270270
*resultTupleDesc = BlessTupleDesc(tupdesc);
271271
return TYPEFUNC_COMPOSITE;
272272
}
273+
else if (expr && IsA(expr, Const) &&
274+
((Const *) expr)->consttype == RECORDOID &&
275+
!((Const *) expr)->constisnull)
276+
{
277+
/*
278+
* When EXPLAIN'ing some queries with SEARCH/CYCLE clauses, we may
279+
* need to resolve field names of a RECORD-type Const. The datum
280+
* should contain a typmod that will tell us that.
281+
*/
282+
HeapTupleHeader rec;
283+
Oid tupType;
284+
int32 tupTypmod;
285+
286+
rec = DatumGetHeapTupleHeader(((Const *) expr)->constvalue);
287+
tupType = HeapTupleHeaderGetTypeId(rec);
288+
tupTypmod = HeapTupleHeaderGetTypMod(rec);
289+
if (resultTypeId)
290+
*resultTypeId = tupType;
291+
if (tupType != RECORDOID || tupTypmod >= 0)
292+
{
293+
/* Should be able to look it up */
294+
if (resultTupleDesc)
295+
*resultTupleDesc = lookup_rowtype_tupdesc_copy(tupType,
296+
tupTypmod);
297+
return TYPEFUNC_COMPOSITE;
298+
}
299+
else
300+
{
301+
/* This shouldn't really happen ... */
302+
if (resultTupleDesc)
303+
*resultTupleDesc = NULL;
304+
return TYPEFUNC_RECORD;
305+
}
306+
}
273307
else
274308
{
275309
/* handle as a generic expression; no chance to resolve RECORD */

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

Lines changed: 120 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -790,6 +790,83 @@ select * from search_graph order by seq;
790790
4 | 5 | arc 4 -> 5 | (1,4,5)
791791
(7 rows)
792792

793+
-- a constant initial value causes issues for EXPLAIN
794+
explain (verbose, costs off)
795+
with recursive test as (
796+
select 1 as x
797+
union all
798+
select x + 1
799+
from test
800+
) search depth first by x set y
801+
select * from test limit 5;
802+
QUERY PLAN
803+
-----------------------------------------------------------------------------------------
804+
Limit
805+
Output: test.x, test.y
806+
CTE test
807+
-> Recursive Union
808+
-> Result
809+
Output: 1, '{(1)}'::record[]
810+
-> WorkTable Scan on test test_1
811+
Output: (test_1.x + 1), array_cat(test_1.y, ARRAY[ROW((test_1.x + 1))])
812+
-> CTE Scan on test
813+
Output: test.x, test.y
814+
(10 rows)
815+
816+
with recursive test as (
817+
select 1 as x
818+
union all
819+
select x + 1
820+
from test
821+
) search depth first by x set y
822+
select * from test limit 5;
823+
x | y
824+
---+-----------------------
825+
1 | {(1)}
826+
2 | {(1),(2)}
827+
3 | {(1),(2),(3)}
828+
4 | {(1),(2),(3),(4)}
829+
5 | {(1),(2),(3),(4),(5)}
830+
(5 rows)
831+
832+
explain (verbose, costs off)
833+
with recursive test as (
834+
select 1 as x
835+
union all
836+
select x + 1
837+
from test
838+
) search breadth first by x set y
839+
select * from test limit 5;
840+
QUERY PLAN
841+
--------------------------------------------------------------------------------------------
842+
Limit
843+
Output: test.x, test.y
844+
CTE test
845+
-> Recursive Union
846+
-> Result
847+
Output: 1, '(0,1)'::record
848+
-> WorkTable Scan on test test_1
849+
Output: (test_1.x + 1), ROW(int8inc((test_1.y)."*DEPTH*"), (test_1.x + 1))
850+
-> CTE Scan on test
851+
Output: test.x, test.y
852+
(10 rows)
853+
854+
with recursive test as (
855+
select 1 as x
856+
union all
857+
select x + 1
858+
from test
859+
) search breadth first by x set y
860+
select * from test limit 5;
861+
x | y
862+
---+-------
863+
1 | (0,1)
864+
2 | (1,2)
865+
3 | (2,3)
866+
4 | (3,4)
867+
5 | (4,5)
868+
(5 rows)
869+
793870
-- various syntax errors
794871
with recursive search_graph(f, t, label) as (
795872
select * from graph0 g
@@ -1101,6 +1178,49 @@ select * from search_graph;
11011178
2 | 3 | arc 2 -> 3 | N | {"(1,4)","(4,5)","(5,1)","(1,2)","(2,3)"}
11021179
(25 rows)
11031180

1181+
explain (verbose, costs off)
1182+
with recursive test as (
1183+
select 0 as x
1184+
union all
1185+
select (x + 1) % 10
1186+
from test
1187+
) cycle x set is_cycle using path
1188+
select * from test;
1189+
QUERY PLAN
1190+
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1191+
CTE Scan on test
1192+
Output: test.x, test.is_cycle, test.path
1193+
CTE test
1194+
-> Recursive Union
1195+
-> Result
1196+
Output: 0, false, '{(0)}'::record[]
1197+
-> WorkTable Scan on test test_1
1198+
Output: ((test_1.x + 1) % 10), CASE WHEN (ROW(((test_1.x + 1) % 10)) = ANY (test_1.path)) THEN true ELSE false END, array_cat(test_1.path, ARRAY[ROW(((test_1.x + 1) % 10))])
1199+
Filter: (NOT test_1.is_cycle)
1200+
(9 rows)
1201+
1202+
with recursive test as (
1203+
select 0 as x
1204+
union all
1205+
select (x + 1) % 10
1206+
from test
1207+
) cycle x set is_cycle using path
1208+
select * from test;
1209+
x | is_cycle | path
1210+
---+----------+-----------------------------------------------
1211+
0 | f | {(0)}
1212+
1 | f | {(0),(1)}
1213+
2 | f | {(0),(1),(2)}
1214+
3 | f | {(0),(1),(2),(3)}
1215+
4 | f | {(0),(1),(2),(3),(4)}
1216+
5 | f | {(0),(1),(2),(3),(4),(5)}
1217+
6 | f | {(0),(1),(2),(3),(4),(5),(6)}
1218+
7 | f | {(0),(1),(2),(3),(4),(5),(6),(7)}
1219+
8 | f | {(0),(1),(2),(3),(4),(5),(6),(7),(8)}
1220+
9 | f | {(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)}
1221+
0 | t | {(0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(0)}
1222+
(11 rows)
1223+
11041224
-- multiple CTEs
11051225
with recursive
11061226
graph(f, t, label) as (

‎src/test/regress/sql/with.sql

Lines changed: 52 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -414,6 +414,41 @@ with recursive search_graph(f, t, label) as (
414414
) search breadth first by f, t set seq
415415
select * from search_graph order by seq;
416416

417+
-- a constant initial value causes issues for EXPLAIN
418+
explain (verbose, costs off)
419+
with recursive test as (
420+
select 1 as x
421+
union all
422+
select x + 1
423+
from test
424+
) search depth first by x set y
425+
select * from test limit 5;
426+
427+
with recursive test as (
428+
select 1 as x
429+
union all
430+
select x + 1
431+
from test
432+
) search depth first by x set y
433+
select * from test limit 5;
434+
435+
explain (verbose, costs off)
436+
with recursive test as (
437+
select 1 as x
438+
union all
439+
select x + 1
440+
from test
441+
) search breadth first by x set y
442+
select * from test limit 5;
443+
444+
with recursive test as (
445+
select 1 as x
446+
union all
447+
select x + 1
448+
from test
449+
) search breadth first by x set y
450+
select * from test limit 5;
451+
417452
-- various syntax errors
418453
with recursive search_graph(f, t, label) as (
419454
select * from graph0 g
@@ -553,6 +588,23 @@ with recursive search_graph(f, t, label) as (
553588
) cycle f, t set is_cycle to 'Y' default 'N' using path
554589
select * from search_graph;
555590

591+
explain (verbose, costs off)
592+
with recursive test as (
593+
select 0 as x
594+
union all
595+
select (x + 1) % 10
596+
from test
597+
) cycle x set is_cycle using path
598+
select * from test;
599+
600+
with recursive test as (
601+
select 0 as x
602+
union all
603+
select (x + 1) % 10
604+
from test
605+
) cycle x set is_cycle using path
606+
select * from test;
607+
556608
-- multiple CTEs
557609
with recursive
558610
graph(f, t, label) as (

0 commit comments

Comments
 (0)