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

Commit9476131

Browse files
committed
Prevent inlining of multiply-referenced CTEs with outer recursive refs.
This has to be prevented because inlining would result in multipleself-references, which we don't support (and in fact that's disallowedby the SQL spec, see statements about linearly vs. nonlinearlyrecursive queries). Bug fix for commit608b167.Per report from Yaroslav Schekin (via Andrew Gierth)Discussion:https://postgr.es/m/87wolmg60q.fsf@news-spur.riddles.org.uk
1 parent4dba0f6 commit9476131

File tree

3 files changed

+201
-0
lines changed

3 files changed

+201
-0
lines changed

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

Lines changed: 66 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -85,6 +85,8 @@ static bool testexpr_is_hashable(Node *testexpr);
8585
staticboolhash_ok_operator(OpExpr*expr);
8686
staticboolcontain_dml(Node*node);
8787
staticboolcontain_dml_walker(Node*node,void*context);
88+
staticboolcontain_outer_selfref(Node*node);
89+
staticboolcontain_outer_selfref_walker(Node*node,Index*depth);
8890
staticvoidinline_cte(PlannerInfo*root,CommonTableExpr*cte);
8991
staticboolinline_cte_walker(Node*node,inline_cte_walker_context*context);
9092
staticboolsimplify_EXISTS_query(PlannerInfo*root,Query*query);
@@ -867,6 +869,10 @@ SS_process_ctes(PlannerInfo *root)
867869
* SELECT, or containing volatile functions. Inlining might change
868870
* the side-effects, which would be bad.
869871
*
872+
* 4. The CTE is multiply-referenced and contains a self-reference to
873+
* a recursive CTE outside itself. Inlining would result in multiple
874+
* recursive self-references, which we don't support.
875+
*
870876
* Otherwise, we have an option whether to inline or not. That should
871877
* always be a win if there's just a single reference, but if the CTE
872878
* is multiply-referenced then it's unclear: inlining adds duplicate
@@ -876,13 +882,18 @@ SS_process_ctes(PlannerInfo *root)
876882
* the user express a preference. Our default behavior is to inline
877883
* only singly-referenced CTEs, but a CTE marked CTEMaterializeNever
878884
* will be inlined even if multiply referenced.
885+
*
886+
* Note: we check for volatile functions last, because that's more
887+
* expensive than the other tests needed.
879888
*/
880889
if ((cte->ctematerialized==CTEMaterializeNever||
881890
(cte->ctematerialized==CTEMaterializeDefault&&
882891
cte->cterefcount==1))&&
883892
!cte->cterecursive&&
884893
cmdType==CMD_SELECT&&
885894
!contain_dml(cte->ctequery)&&
895+
(cte->cterefcount <=1||
896+
!contain_outer_selfref(cte->ctequery))&&
886897
!contain_volatile_functions(cte->ctequery))
887898
{
888899
inline_cte(root,cte);
@@ -1016,6 +1027,61 @@ contain_dml_walker(Node *node, void *context)
10161027
returnexpression_tree_walker(node,contain_dml_walker,context);
10171028
}
10181029

1030+
/*
1031+
* contain_outer_selfref: is there an external recursive self-reference?
1032+
*/
1033+
staticbool
1034+
contain_outer_selfref(Node*node)
1035+
{
1036+
Indexdepth=0;
1037+
1038+
/*
1039+
* We should be starting with a Query, so that depth will be 1 while
1040+
* examining its immediate contents.
1041+
*/
1042+
Assert(IsA(node,Query));
1043+
1044+
returncontain_outer_selfref_walker(node,&depth);
1045+
}
1046+
1047+
staticbool
1048+
contain_outer_selfref_walker(Node*node,Index*depth)
1049+
{
1050+
if (node==NULL)
1051+
return false;
1052+
if (IsA(node,RangeTblEntry))
1053+
{
1054+
RangeTblEntry*rte= (RangeTblEntry*)node;
1055+
1056+
/*
1057+
* Check for a self-reference to a CTE that's above the Query that our
1058+
* search started at.
1059+
*/
1060+
if (rte->rtekind==RTE_CTE&&
1061+
rte->self_reference&&
1062+
rte->ctelevelsup >=*depth)
1063+
return true;
1064+
return false;/* allow range_table_walker to continue */
1065+
}
1066+
if (IsA(node,Query))
1067+
{
1068+
/* Recurse into subquery, tracking nesting depth properly */
1069+
Query*query= (Query*)node;
1070+
boolresult;
1071+
1072+
(*depth)++;
1073+
1074+
result=query_tree_walker(query,contain_outer_selfref_walker,
1075+
(void*)depth,QTW_EXAMINE_RTES_BEFORE);
1076+
1077+
(*depth)--;
1078+
1079+
returnresult;
1080+
}
1081+
returnexpression_tree_walker(node,contain_outer_selfref_walker,
1082+
(void*)depth);
1083+
}
1084+
10191085
/*
10201086
* inline_cte: convert RTE_CTE references to given CTE into RTE_SUBQUERYs
10211087
*/

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

Lines changed: 100 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1299,6 +1299,106 @@ select * from x, x x2 where x.n = x2.n;
12991299
Output: subselect_tbl_1.f1
13001300
(11 rows)
13011301

1302+
-- Multiply-referenced CTEs can't be inlined if they contain outer self-refs
1303+
explain (verbose, costs off)
1304+
with recursive x(a) as
1305+
((values ('a'), ('b'))
1306+
union all
1307+
(with z as not materialized (select * from x)
1308+
select z.a || z1.a as a from z cross join z as z1
1309+
where length(z.a || z1.a) < 5))
1310+
select * from x;
1311+
QUERY PLAN
1312+
----------------------------------------------------------
1313+
CTE Scan on x
1314+
Output: x.a
1315+
CTE x
1316+
-> Recursive Union
1317+
-> Values Scan on "*VALUES*"
1318+
Output: "*VALUES*".column1
1319+
-> Nested Loop
1320+
Output: (z.a || z1.a)
1321+
Join Filter: (length((z.a || z1.a)) < 5)
1322+
CTE z
1323+
-> WorkTable Scan on x x_1
1324+
Output: x_1.a
1325+
-> CTE Scan on z
1326+
Output: z.a
1327+
-> CTE Scan on z z1
1328+
Output: z1.a
1329+
(16 rows)
1330+
1331+
with recursive x(a) as
1332+
((values ('a'), ('b'))
1333+
union all
1334+
(with z as not materialized (select * from x)
1335+
select z.a || z1.a as a from z cross join z as z1
1336+
where length(z.a || z1.a) < 5))
1337+
select * from x;
1338+
a
1339+
------
1340+
a
1341+
b
1342+
aa
1343+
ab
1344+
ba
1345+
bb
1346+
aaaa
1347+
aaab
1348+
aaba
1349+
aabb
1350+
abaa
1351+
abab
1352+
abba
1353+
abbb
1354+
baaa
1355+
baab
1356+
baba
1357+
babb
1358+
bbaa
1359+
bbab
1360+
bbba
1361+
bbbb
1362+
(22 rows)
1363+
1364+
explain (verbose, costs off)
1365+
with recursive x(a) as
1366+
((values ('a'), ('b'))
1367+
union all
1368+
(with z as not materialized (select * from x)
1369+
select z.a || z.a as a from z
1370+
where length(z.a || z.a) < 5))
1371+
select * from x;
1372+
QUERY PLAN
1373+
--------------------------------------------------------
1374+
CTE Scan on x
1375+
Output: x.a
1376+
CTE x
1377+
-> Recursive Union
1378+
-> Values Scan on "*VALUES*"
1379+
Output: "*VALUES*".column1
1380+
-> WorkTable Scan on x x_1
1381+
Output: (x_1.a || x_1.a)
1382+
Filter: (length((x_1.a || x_1.a)) < 5)
1383+
(9 rows)
1384+
1385+
with recursive x(a) as
1386+
((values ('a'), ('b'))
1387+
union all
1388+
(with z as not materialized (select * from x)
1389+
select z.a || z.a as a from z
1390+
where length(z.a || z.a) < 5))
1391+
select * from x;
1392+
a
1393+
------
1394+
a
1395+
b
1396+
aa
1397+
bb
1398+
aaaa
1399+
bbbb
1400+
(6 rows)
1401+
13021402
-- Check handling of outer references
13031403
explain (verbose, costs off)
13041404
with x as (select * from int4_tbl)

‎src/test/regress/sql/subselect.sql

Lines changed: 35 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -690,6 +690,41 @@ explain (verbose, costs off)
690690
with xas not materialized (select*from (select f1, now()as nfrom subselect_tbl) ss)
691691
select*from x, x x2wherex.n=x2.n;
692692

693+
-- Multiply-referenced CTEs can't be inlined if they contain outer self-refs
694+
explain (verbose, costs off)
695+
with recursive x(a)as
696+
((values ('a'), ('b'))
697+
union all
698+
(with zas not materialized (select*from x)
699+
selectz.a||z1.aas afrom zcross join zas z1
700+
where length(z.a||z1.a)<5))
701+
select*from x;
702+
703+
with recursive x(a)as
704+
((values ('a'), ('b'))
705+
union all
706+
(with zas not materialized (select*from x)
707+
selectz.a||z1.aas afrom zcross join zas z1
708+
where length(z.a||z1.a)<5))
709+
select*from x;
710+
711+
explain (verbose, costs off)
712+
with recursive x(a)as
713+
((values ('a'), ('b'))
714+
union all
715+
(with zas not materialized (select*from x)
716+
selectz.a||z.aas afrom z
717+
where length(z.a||z.a)<5))
718+
select*from x;
719+
720+
with recursive x(a)as
721+
((values ('a'), ('b'))
722+
union all
723+
(with zas not materialized (select*from x)
724+
selectz.a||z.aas afrom z
725+
where length(z.a||z.a)<5))
726+
select*from x;
727+
693728
-- Check handling of outer references
694729
explain (verbose, costs off)
695730
with xas (select*from int4_tbl)

0 commit comments

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