BUG #17835: Two assertions failed in nodeAgg.c and execExprInterp.c with the same SQL - Mailing list pgsql-bugs

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