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

Commitd575347

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 parent717ec1a commitd575347

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
@@ -7500,7 +7500,8 @@ get_name_for_var_field(Var *var, int fieldno,
75007500

75017501
/*
75027502
* If it's a RowExpr that was expanded from a whole-row Var, use the
7503-
* column names attached to it.
7503+
* column names attached to it. (We could let get_expr_result_tupdesc()
7504+
* handle this, but it's much cheaper to just pull out the name we need.)
75047505
*/
75057506
if (IsA(var,RowExpr))
75067507
{

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

Lines changed: 34 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -339,6 +339,40 @@ get_expr_result_type(Node *expr,
339339
*resultTupleDesc=BlessTupleDesc(tupdesc);
340340
returnTYPEFUNC_COMPOSITE;
341341
}
342+
elseif (expr&&IsA(expr,Const)&&
343+
((Const*)expr)->consttype==RECORDOID&&
344+
!((Const*)expr)->constisnull)
345+
{
346+
/*
347+
* When EXPLAIN'ing some queries with SEARCH/CYCLE clauses, we may
348+
* need to resolve field names of a RECORD-type Const. The datum
349+
* should contain a typmod that will tell us that.
350+
*/
351+
HeapTupleHeaderrec;
352+
OidtupType;
353+
int32tupTypmod;
354+
355+
rec=DatumGetHeapTupleHeader(((Const*)expr)->constvalue);
356+
tupType=HeapTupleHeaderGetTypeId(rec);
357+
tupTypmod=HeapTupleHeaderGetTypMod(rec);
358+
if (resultTypeId)
359+
*resultTypeId=tupType;
360+
if (tupType!=RECORDOID||tupTypmod >=0)
361+
{
362+
/* Should be able to look it up */
363+
if (resultTupleDesc)
364+
*resultTupleDesc=lookup_rowtype_tupdesc_copy(tupType,
365+
tupTypmod);
366+
returnTYPEFUNC_COMPOSITE;
367+
}
368+
else
369+
{
370+
/* This shouldn't really happen ... */
371+
if (resultTupleDesc)
372+
*resultTupleDesc=NULL;
373+
returnTYPEFUNC_RECORD;
374+
}
375+
}
342376
else
343377
{
344378
/* 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
@@ -1132,6 +1209,49 @@ select * from search_graph;
11321209
2 | 3 | arc 2 -> 3 | N | {"(1,4)","(4,5)","(5,1)","(1,2)","(2,3)"}
11331210
(25 rows)
11341211

1212+
explain (verbose, costs off)
1213+
with recursive test as (
1214+
select 0 as x
1215+
union all
1216+
select (x + 1) % 10
1217+
from test
1218+
) cycle x set is_cycle using path
1219+
select * from test;
1220+
QUERY PLAN
1221+
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1222+
CTE Scan on test
1223+
Output: test.x, test.is_cycle, test.path
1224+
CTE test
1225+
-> Recursive Union
1226+
-> Result
1227+
Output: 0, false, '{(0)}'::record[]
1228+
-> WorkTable Scan on test test_1
1229+
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))])
1230+
Filter: (NOT test_1.is_cycle)
1231+
(9 rows)
1232+
1233+
with recursive test as (
1234+
select 0 as x
1235+
union all
1236+
select (x + 1) % 10
1237+
from test
1238+
) cycle x set is_cycle using path
1239+
select * from test;
1240+
x | is_cycle | path
1241+
---+----------+-----------------------------------------------
1242+
0 | f | {(0)}
1243+
1 | f | {(0),(1)}
1244+
2 | f | {(0),(1),(2)}
1245+
3 | f | {(0),(1),(2),(3)}
1246+
4 | f | {(0),(1),(2),(3),(4)}
1247+
5 | f | {(0),(1),(2),(3),(4),(5)}
1248+
6 | f | {(0),(1),(2),(3),(4),(5),(6)}
1249+
7 | f | {(0),(1),(2),(3),(4),(5),(6),(7)}
1250+
8 | f | {(0),(1),(2),(3),(4),(5),(6),(7),(8)}
1251+
9 | f | {(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)}
1252+
0 | t | {(0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(0)}
1253+
(11 rows)
1254+
11351255
-- multiple CTEs
11361256
with recursive
11371257
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
@@ -561,6 +596,23 @@ with recursive search_graph(f, t, label) as (
561596
) cycle f, tset is_cycle to'Y' default'N' usingpath
562597
select*from search_graph;
563598

599+
explain (verbose, costs off)
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+
608+
with recursive testas (
609+
select0as x
610+
union all
611+
select (x+1) %10
612+
from test
613+
) cycle xset is_cycle usingpath
614+
select*from test;
615+
564616
-- multiple CTEs
565617
with recursive
566618
graph(f, t, label)as (

0 commit comments

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