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

Commitb0cc0a7

Browse files
committed
Calculate agglevelsup correctly when Aggref contains a CTE.
If an aggregate function call contains a sub-select that hasan RTE referencing a CTE outside the aggregate, we must treatthat reference like a Var referencing the CTE's query levelfor purposes of determining the aggregate's level. Otherwisewe might reach the nonsensical conclusion that the aggregateshould be evaluated at some query level higher than the CTE,ending in a planner error or a broken plan tree that causesexecutor failures.Bug: #19055Reported-by: BugForge <dllggyx@outlook.com>Author: Tom Lane <tgl@sss.pgh.pa.us>Discussion:https://postgr.es/m/19055-6970cfa8556a394d@postgresql.orgBackpatch-through: 13
1 parent0951942 commitb0cc0a7

File tree

3 files changed

+75
-1
lines changed

3 files changed

+75
-1
lines changed

‎src/backend/parser/parse_agg.c‎

Lines changed: 27 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -791,6 +791,32 @@ check_agg_arguments_walker(Node *node,
791791
parser_errposition(context->pstate,
792792
((WindowFunc*)node)->location)));
793793
}
794+
795+
if (IsA(node,RangeTblEntry))
796+
{
797+
/*
798+
* CTE references act similarly to Vars of the CTE's level. Without
799+
* this we might conclude that the Agg can be evaluated above the CTE,
800+
* leading to trouble.
801+
*/
802+
RangeTblEntry*rte= (RangeTblEntry*)node;
803+
804+
if (rte->rtekind==RTE_CTE)
805+
{
806+
intctelevelsup=rte->ctelevelsup;
807+
808+
/* convert levelsup to frame of reference of original query */
809+
ctelevelsup-=context->sublevels_up;
810+
/* ignore local CTEs of subqueries */
811+
if (ctelevelsup >=0)
812+
{
813+
if (context->min_varlevel<0||
814+
context->min_varlevel>ctelevelsup)
815+
context->min_varlevel=ctelevelsup;
816+
}
817+
}
818+
return false;/* allow range_table_walker to continue */
819+
}
794820
if (IsA(node,Query))
795821
{
796822
/* Recurse into subselects */
@@ -800,7 +826,7 @@ check_agg_arguments_walker(Node *node,
800826
result=query_tree_walker((Query*)node,
801827
check_agg_arguments_walker,
802828
context,
803-
0);
829+
QTW_EXAMINE_RTES_BEFORE);
804830
context->sublevels_up--;
805831
returnresult;
806832
}

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

Lines changed: 34 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2297,6 +2297,40 @@ from int4_tbl;
22972297
-2147483647
22982298
(5 rows)
22992299

2300+
--
2301+
-- test for bug #19055: interaction of WITH with aggregates
2302+
--
2303+
-- The reference to cte1 must determine the aggregate's level,
2304+
-- even though it contains no Vars referencing cte1
2305+
explain (verbose, costs off)
2306+
select f1, (with cte1(x,y) as (select 1,2)
2307+
select count((select i4.f1 from cte1))) as ss
2308+
from int4_tbl i4;
2309+
QUERY PLAN
2310+
--------------------------------------------
2311+
Seq Scan on public.int4_tbl i4
2312+
Output: i4.f1, (SubPlan 2)
2313+
SubPlan 2
2314+
-> Aggregate
2315+
Output: count((InitPlan 1).col1)
2316+
InitPlan 1
2317+
-> Result
2318+
Output: i4.f1
2319+
-> Result
2320+
(9 rows)
2321+
2322+
select f1, (with cte1(x,y) as (select 1,2)
2323+
select count((select i4.f1 from cte1))) as ss
2324+
from int4_tbl i4;
2325+
f1 | ss
2326+
-------------+----
2327+
0 | 1
2328+
123456 | 1
2329+
-123456 | 1
2330+
2147483647 | 1
2331+
-2147483647 | 1
2332+
(5 rows)
2333+
23002334
--
23012335
-- test for nested-recursive-WITH bug
23022336
--

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

Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1097,6 +1097,20 @@ select ( with cte(foo) as ( values(f1) )
10971097
values((select foofrom cte)) )
10981098
from int4_tbl;
10991099

1100+
--
1101+
-- test for bug #19055: interaction of WITH with aggregates
1102+
--
1103+
-- The reference to cte1 must determine the aggregate's level,
1104+
-- even though it contains no Vars referencing cte1
1105+
explain (verbose, costs off)
1106+
select f1, (with cte1(x,y)as (select1,2)
1107+
selectcount((selecti4.f1from cte1)))as ss
1108+
from int4_tbl i4;
1109+
1110+
select f1, (with cte1(x,y)as (select1,2)
1111+
selectcount((selecti4.f1from cte1)))as ss
1112+
from int4_tbl i4;
1113+
11001114
--
11011115
-- test for nested-recursive-WITH bug
11021116
--

0 commit comments

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