Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit8122160

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

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
returnTYPEFUNC_COMPOSITE;
272272
}
273+
elseif (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+
HeapTupleHeaderrec;
283+
OidtupType;
284+
int32tupTypmod;
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+
returnTYPEFUNC_COMPOSITE;
298+
}
299+
else
300+
{
301+
/* This shouldn't really happen ... */
302+
if (resultTupleDesc)
303+
*resultTupleDesc=NULL;
304+
returnTYPEFUNC_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, tset seq
415415
select*from search_graphorder by seq;
416416

417+
-- a constant initial value causes issues for EXPLAIN
418+
explain (verbose, costs off)
419+
with recursive testas (
420+
select1as x
421+
union all
422+
select x+1
423+
from test
424+
) search depth first by xset y
425+
select*from testlimit5;
426+
427+
with recursive testas (
428+
select1as x
429+
union all
430+
select x+1
431+
from test
432+
) search depth first by xset y
433+
select*from testlimit5;
434+
435+
explain (verbose, costs off)
436+
with recursive testas (
437+
select1as x
438+
union all
439+
select x+1
440+
from test
441+
) search breadth first by xset y
442+
select*from testlimit5;
443+
444+
with recursive testas (
445+
select1as x
446+
union all
447+
select x+1
448+
from test
449+
) search breadth first by xset y
450+
select*from testlimit5;
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, tset is_cycle to'Y' default'N' usingpath
554589
select*from search_graph;
555590

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

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp