Обсуждение: BUG #17835: Two assertions failed in nodeAgg.c and execExprInterp.c with the same SQL

The following bug has been logged on the website:

Bug reference:      17835
Logged by:          Anban Company
Email address:      xinwen@stu.scu.edu.cn
PostgreSQL version: 15.2
Operating system:   Ubuntu 20.04
Description:

When executing the following query with CTE:

WITH table1 ( column25 ) AS ( SELECT 1 ) SELECT FROM ( SELECT column25
column12 FROM table1 ) AS alias0 GROUP BY column12 HAVING AVG ( ( SELECT 1
FROM table1 JOIN ( SELECT AVG ( column25 ORDER BY CASE 1 WHEN column12 THEN
( SELECT AVG ( column12 ) FROM table1 ) END ) column14 FROM table1 ) AS
alias3 ON alias3 . column14 = 1 ) ) = 1 ;

I get a failed assertion with the following stacktrace:

Core was generated by `postgres: postgres postgres [local] SELECT
'.
Program terminated with signal SIGABRT, Aborted.
#0  __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50
#1  0x00007fe5a271d859 in __GI_abort () at abort.c:79
#2  0x0000560d08a00598 in ExceptionalCondition
(conditionName=conditionName@entry=0x560d08b58efd "!context->is_aggref",
errorType=errorType@entry=0x560d08a5e4a0 "FailedAssertion",
fileName=fileName@entry=0x560d08b589c0
"/home/postgres/postgresql-15.2/original_bin-15.2/../src/backend/executor/nodeAgg.c",
lineNumber=lineNumber@entry=1424) at
/home/postgres/postgresql-15.2/original_bin-15.2/../src/backend/utils/error/assert.c:69
#3  0x0000560d087579ba in find_cols_walker (node=<optimized out>,
context=0x7ffe58af50b0) at
/home/postgres/postgresql-15.2/original_bin-15.2/../src/backend/executor/nodeAgg.c:1424
#4  0x0000560d087b12a2 in expression_tree_walker (node=0x560d09681c38,
walker=0x560d087578f0 <find_cols_walker>, context=0x7ffe58af50b0) at
/home/postgres/postgresql-15.2/original_bin-15.2/../src/backend/nodes/nodeFuncs.c:2235
#5  0x0000560d087b12a2 in expression_tree_walker (node=0x560d09681ce8,
walker=0x560d087578f0 <find_cols_walker>, context=0x7ffe58af50b0) at
/home/postgres/postgresql-15.2/original_bin-15.2/../src/backend/nodes/nodeFuncs.c:2235
#6  0x0000560d087b1472 in expression_tree_walker (node=0x560d09681860,
walker=0x560d087578f0 <find_cols_walker>, context=0x7ffe58af50b0) at
/home/postgres/postgresql-15.2/original_bin-15.2/../src/backend/nodes/nodeFuncs.c:1958
#7  0x0000560d08757929 in find_cols_walker (node=<optimized out>,
context=0x7ffe58af50b0) at
/home/postgres/postgresql-15.2/original_bin-15.2/../src/backend/executor/nodeAgg.c:1426
#8  0x0000560d087b12a2 in expression_tree_walker (node=0x560d09681d40,
walker=0x560d087578f0 <find_cols_walker>, context=0x7ffe58af50b0) at
/home/postgres/postgresql-15.2/original_bin-15.2/../src/backend/nodes/nodeFuncs.c:2235
#9  0x0000560d087b12a2 in expression_tree_walker (node=0x560d09681df0,
walker=0x560d087578f0 <find_cols_walker>, context=0x7ffe58af50b0) at
/home/postgres/postgresql-15.2/original_bin-15.2/../src/backend/nodes/nodeFuncs.c:2235
#10 0x0000560d0875ae01 in find_cols (aggstate=0x560d09698cc0,
unaggregated=<synthetic pointer>, aggregated=<synthetic pointer>) at
/home/postgres/postgresql-15.2/original_bin-15.2/../src/backend/executor/nodeAgg.c:1391
#11 find_hash_columns (aggstate=0x560d09698cc0) at
/home/postgres/postgresql-15.2/original_bin-15.2/../src/backend/executor/nodeAgg.c:1557
#12 ExecInitAgg (node=node@entry=0x560d0967f8c8,
estate=estate@entry=0x560d0966fc50, eflags=eflags@entry=16) at
/home/postgres/postgresql-15.2/original_bin-15.2/../src/backend/executor/nodeAgg.c:3601
#13 0x0000560d0874b069 in ExecInitNode (node=node@entry=0x560d0967f8c8,
estate=estate@entry=0x560d0966fc50, eflags=eflags@entry=16) at
/home/postgres/postgresql-15.2/original_bin-15.2/../src/backend/executor/execProcnode.c:341
#14 0x0000560d08744572 in InitPlan (eflags=16, queryDesc=0x560d095a1030) at
/home/postgres/postgresql-15.2/original_bin-15.2/../src/backend/executor/execMain.c:938
#15 standard_ExecutorStart (queryDesc=0x560d095a1030, eflags=16) at
/home/postgres/postgresql-15.2/original_bin-15.2/../src/backend/executor/execMain.c:265
#16 0x0000560d088d8339 in PortalStart (portal=portal@entry=0x560d095ec560,
params=params@entry=0x0, eflags=eflags@entry=0, snapshot=snapshot@entry=0x0)
at
/home/postgres/postgresql-15.2/original_bin-15.2/../src/backend/tcop/pquery.c:517
#17 0x0000560d088d4cb5 in exec_simple_query ( query_string=0x560d0957f010
"WITH table1 ( column25 ) AS ( SELECT 1 ) SELECT FROM ( SELECT column25
column12 FROM table1 ) AS alias0 GROUP BY column12 HAVING AVG ( ( SELECT 1
FROM table1 JOIN ( SELECT AVG ( column25 ORDER BY CASE"...) at
/home/postgres/postgresql-15.2/original_bin-15.2/../src/backend/tcop/postgres.c:1211
#18 0x0000560d088d67fc in PostgresMain (dbname=<optimized out>,
username=<optimized out>) at
/home/postgres/postgresql-15.2/original_bin-15.2/../src/backend/tcop/postgres.c:4593
#19 0x0000560d088435aa in BackendRun (port=<optimized out>, port=<optimized
out>) at
/home/postgres/postgresql-15.2/original_bin-15.2/../src/backend/postmaster/postmaster.c:4511
#20 BackendStartup (port=<optimized out>) at
/home/postgres/postgresql-15.2/original_bin-15.2/../src/backend/postmaster/postmaster.c:4239
#21 ServerLoop () at
/home/postgres/postgresql-15.2/original_bin-15.2/../src/backend/postmaster/postmaster.c:1806
#22 0x0000560d0884471b in PostmasterMain (argc=<optimized out>,
argv=0x560d09579310) at
/home/postgres/postgresql-15.2/original_bin-15.2/../src/backend/postmaster/postmaster.c:1478
#23 0x0000560d0856e510 in main (argc=3, argv=0x560d09579310) at
/home/postgres/postgresql-15.2/original_bin-15.2/../src/backend/main/main.c:202

And when executing the same query with table:

CREATE TABLE table1 ( column25 INT );
INSERT INTO table1 VALUES ( 1 ) ;
SELECT FROM ( SELECT column25 column12 FROM table1 ) AS alias0 GROUP BY
column12 HAVING AVG ( ( SELECT 1 FROM table1 JOIN ( SELECT AVG ( column25
ORDER BY CASE 1 WHEN column12 THEN ( SELECT AVG ( column12 ) FROM table1 )
END ) column14 FROM table1 ) AS alias3 ON alias3 . column14 = 1 ) ) = 1 ;

I get another failed assertion with the following stacktrace:

Core was generated by `postgres: postgres postgres [local] SELECT
'.
Program terminated with signal SIGABRT, Aborted.
#0  __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50
#1  0x00007fb0d6f40859 in __GI_abort () at abort.c:79
#2  0x0000556d20e60598 in ExceptionalCondition
(conditionName=conditionName@entry=0x556d20fb4ae8 "econtext->ecxt_aggvalues
!= NULL", errorType=errorType@entry=0x556d20ebe4a0 "FailedAssertion",
fileName=fileName@entry=0x556d20fb4360
"/home/postgres/postgresql-15.2/original_bin-15.2/../src/backend/executor/execExprInterp.c",
lineNumber=lineNumber@entry=1532) at
/home/postgres/postgresql-15.2/original_bin-15.2/../src/backend/utils/error/assert.c:69
#3  0x0000556d20b9f867 in ExecInterpExpr (state=0x556d2326f530,
econtext=0x556d2326a230, isnull=<optimized out>) at
/home/postgres/postgresql-15.2/original_bin-15.2/../src/backend/executor/execExprInterp.c:1532
#4  0x0000556d20bdbc52 in ExecEvalExprSwitchContext (isNull=0x556d2324a5e8,
econtext=0x556d2326a230, state=<optimized out>) at
/home/postgres/postgresql-15.2/original_bin-15.2/../src/include/executor/executor.h:341
#5  ExecScanSubPlan (isNull=0x556d2326ef48, econtext=0x556d2326a230,
node=0x556d2326f418) at
/home/postgres/postgresql-15.2/original_bin-15.2/../src/backend/executor/nodeSubplan.c:288
#6  ExecSubPlan (node=node@entry=0x556d2326f418,
econtext=econtext@entry=0x556d2326a230, isNull=0x556d2326ef48) at
/home/postgres/postgresql-15.2/original_bin-15.2/../src/backend/executor/nodeSubplan.c:89
#7  0x0000556d20b9dbe0 in ExecEvalSubPlan (state=state@entry=0x556d2326ef68,
op=op@entry=0x556d2326f040, econtext=econtext@entry=0x556d2326a230) at
/home/postgres/postgresql-15.2/original_bin-15.2/../src/backend/executor/execExprInterp.c:3961
#8  0x0000556d20b9ec96 in ExecInterpExpr (state=0x556d2326ef68,
econtext=0x556d2326a230, isnull=<optimized out>) at
/home/postgres/postgresql-15.2/original_bin-15.2/../src/backend/executor/execExprInterp.c:1566
#9  0x0000556d20bb969f in ExecEvalExprSwitchContext (isNull=0x7fff04ffdea7,
econtext=<optimized out>, state=<optimized out>) at
/home/postgres/postgresql-15.2/original_bin-15.2/../src/include/executor/executor.h:341
#10 advance_aggregates (aggstate=<optimized out>, aggstate=<optimized out>)
at
/home/postgres/postgresql-15.2/original_bin-15.2/../src/backend/executor/nodeAgg.c:824
#11 agg_retrieve_direct (aggstate=<optimized out>) at
/home/postgres/postgresql-15.2/original_bin-15.2/../src/backend/executor/nodeAgg.c:2436
#12 ExecAgg (pstate=<optimized out>) at
/home/postgres/postgresql-15.2/original_bin-15.2/../src/backend/executor/nodeAgg.c:2161
#13 0x0000556d20ba3b33 in ExecProcNode (node=0x556d2326c798) at
/home/postgres/postgresql-15.2/original_bin-15.2/../src/include/executor/executor.h:259
#14 ExecutePlan (execute_once=<optimized out>, dest=0x556d23258ed8,
direction=<optimized out>, numberTuples=0, sendTuples=<optimized out>,
operation=CMD_SELECT, use_parallel_mode=<optimized out>,
planstate=0x556d2326c798, estate=0x556d2324a3c0) at
/home/postgres/postgresql-15.2/original_bin-15.2/../src/backend/executor/execMain.c:1636
#15 standard_ExecutorRun (queryDesc=0x556d2317ddf0, direction=<optimized
out>, count=0, execute_once=<optimized out>) at
/home/postgres/postgresql-15.2/original_bin-15.2/../src/backend/executor/execMain.c:363
#16 0x0000556d20d376df in PortalRunSelect (portal=0x556d231c7560,
forward=<optimized out>, count=0, dest=<optimized out>) at
/home/postgres/postgresql-15.2/original_bin-15.2/../src/backend/tcop/pquery.c:924
#17 0x0000556d20d38cb1 in PortalRun (portal=portal@entry=0x556d231c7560,
count=count@entry=9223372036854775807, isTopLevel=isTopLevel@entry=true,
run_once=run_once@entry=true, dest=dest@entry=0x556d23258ed8,
altdest=altdest@entry=0x556d23258ed8, qc=0x7fff04ffe150) at
/home/postgres/postgresql-15.2/original_bin-15.2/../src/backend/tcop/pquery.c:768
#18 0x0000556d20d34a72 in exec_simple_query ( query_string=0x556d2315a010
"SELECT FROM ( SELECT column25 column12 FROM table1 ) AS alias0 GROUP BY
column12 HAVING AVG ( ( SELECT 1 FROM table1 JOIN ( SELECT AVG ( column25
ORDER BY CASE 1 WHEN column12 THEN ( SELECT AVG ( colu"...) at
/home/postgres/postgresql-15.2/original_bin-15.2/../src/backend/tcop/postgres.c:1250
#19 0x0000556d20d367fc in PostgresMain (dbname=<optimized out>,
username=<optimized out>) at
/home/postgres/postgresql-15.2/original_bin-15.2/../src/backend/tcop/postgres.c:4593
#20 0x0000556d20ca35aa in BackendRun (port=<optimized out>, port=<optimized
out>) at
/home/postgres/postgresql-15.2/original_bin-15.2/../src/backend/postmaster/postmaster.c:4511
#21 BackendStartup (port=<optimized out>) at
/home/postgres/postgresql-15.2/original_bin-15.2/../src/backend/postmaster/postmaster.c:4239
#22 ServerLoop () at
/home/postgres/postgresql-15.2/original_bin-15.2/../src/backend/postmaster/postmaster.c:1806
#23 0x0000556d20ca471b in PostmasterMain (argc=<optimized out>,
argv=0x556d23154310) at
/home/postgres/postgresql-15.2/original_bin-15.2/../src/backend/postmaster/postmaster.c:1478
#24 0x0000556d209ce510 in main (argc=3, argv=0x556d23154310) at
/home/postgres/postgresql-15.2/original_bin-15.2/../src/backend/main/main.c:202

PG Bug reporting form <noreply@postgresql.org> writes:
> When executing the following query with CTE:

> WITH table1 ( column25 ) AS ( SELECT 1 ) SELECT FROM ( SELECT column25
> column12 FROM table1 ) AS alias0 GROUP BY column12 HAVING AVG ( ( SELECT 1
> FROM table1 JOIN ( SELECT AVG ( column25 ORDER BY CASE 1 WHEN column12 THEN
> ( SELECT AVG ( column12 ) FROM table1 ) END ) column14 FROM table1 ) AS
> alias3 ON alias3 . column14 = 1 ) ) = 1 ;

> I get a failed assertion with the following stacktrace:

Simplifying a bit, we get

WITH table1 ( col1 ) AS ( SELECT 1 )
SELECT AVG((
  SELECT AVG ( a1.col1 ORDER BY ( SELECT AVG ( a2.col2 ) FROM table1 ) )
  FROM table1 a1
))
FROM table1 AS a2(col2);

PG v10 says
    ERROR:  aggregate function calls cannot be nested
which seems correct: per spec, the innermost AVG actually should belong
to the outer query level that sources a2.col2.  Later versions fail to
detect that the query is nonsensical and end up with nonsensical
executor state instead.  I bisected this to

69c3936a1499b772a749ae629fc59b2d72722332 is the first bad commit
commit 69c3936a1499b772a749ae629fc59b2d72722332
Author: Andres Freund <andres@anarazel.de>
Date:   Tue Jan 9 13:25:38 2018 -0800

    Expression evaluation based aggregate transition invocation.

So that commit broke something about the nested-aggregate detection
logic.  It's not completely gone: if we simplify this to

WITH table1 ( col1 ) AS ( SELECT 1 )
SELECT AVG((
  SELECT AVG ( ( SELECT AVG ( a2.col2 ) FROM table1 ) )
  FROM table1 a1
))
FROM table1 AS a2(col2);

we still get

ERROR:  aggregate function calls cannot be nested
LINE 3:   SELECT AVG ( ( SELECT AVG ( a2.col2 ) FROM table1 ) )
                                ^

v10 detected this in ExecInitAgg, while later versions are trying to
do it in the parser, but evidently there's some gap there ...

            regards, tom lane

I wrote:
> v10 detected this in ExecInitAgg, while later versions are trying to
> do it in the parser, but evidently there's some gap there ...

Looking closer, 69c3936a1 didn't really break this, it just exposed
that it was already broken.  check_agg_arguments() has never gotten
this case right, but it was back-stopped by the check in ExecInitAgg,
which 69c3936a1 removed because it was no longer easy to check there.

The problem is simple: check_agg_arguments supposes that an inner
(lower level) aggregate cannot contain aggregates of outer levels,
which is just wrong on its face.  So we can't skip descending into
an aggregate's arguments.  Somebody (probably Gierth) recognized
that this was true for grouping functions, but didn't see that the
adjacent code was buggy.

            regards, tom lane

diff --git a/src/backend/parser/parse_agg.c b/src/backend/parser/parse_agg.c
index 4fbf80c271..85cd47b7ae 100644
--- a/src/backend/parser/parse_agg.c
+++ b/src/backend/parser/parse_agg.c
@@ -736,8 +736,7 @@ check_agg_arguments_walker(Node *node,
                 context->min_agglevel > agglevelsup)
                 context->min_agglevel = agglevelsup;
         }
-        /* no need to examine args of the inner aggregate */
-        return false;
+        /* Continue and descend into subtree */
     }
     if (IsA(node, GroupingFunc))
     {
diff --git a/src/test/regress/expected/aggregates.out b/src/test/regress/expected/aggregates.out
index e074cb71bf..f0517f95b6 100644
--- a/src/test/regress/expected/aggregates.out
+++ b/src/test/regress/expected/aggregates.out
@@ -1282,6 +1282,12 @@ select (select max(min(unique1)) from int8_tbl) from tenk1;
 ERROR:  aggregate function calls cannot be nested
 LINE 1: select (select max(min(unique1)) from int8_tbl) from tenk1;
                            ^
+select avg((select avg(a1.col1 order by (select avg(a2.col2) from tenk1 a3))
+            from tenk1 a1(col1)))
+from tenk1 a2(col2);
+ERROR:  aggregate function calls cannot be nested
+LINE 1: select avg((select avg(a1.col1 order by (select avg(a2.col2)...
+                                                        ^
 --
 -- Test removal of redundant GROUP BY columns
 --
diff --git a/src/test/regress/sql/aggregates.sql b/src/test/regress/sql/aggregates.sql
index 616ef38c25..1783d19bd5 100644
--- a/src/test/regress/sql/aggregates.sql
+++ b/src/test/regress/sql/aggregates.sql
@@ -439,6 +439,9 @@ drop table minmaxtest cascade;
 -- check for correct detection of nested-aggregate errors
 select max(min(unique1)) from tenk1;
 select (select max(min(unique1)) from int8_tbl) from tenk1;
+select avg((select avg(a1.col1 order by (select avg(a2.col2) from tenk1 a3))
+            from tenk1 a1(col1)))
+from tenk1 a2(col2);

 --
 -- Test removal of redundant GROUP BY columns