Bug #112470 Assertion Failure in /mysql-8.0.34/sql/item_sum.cc:642
Submitted: 26 Sep 9:30 Modified: 26 Sep 9:32
Reporter: xin wen Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DDL Severity:S6 (Debug Builds)
Version:8.0.34 OS:Ubuntu (20.04)
Assigned to: CPU Architecture:Any

[26 Sep 9:30] xin wen 
Description:
Run these queries:

CREATE TABLE t0 ( CONSTRAINT cc0 CHECK ( c52 > REPEAT ( -77 , COS ( -5093762040897600327 ) ) + TRIM( -112 ) REGEXP ( SELECT STDDEV_SAMP( QUOTE ( 10 ) AND REVERSE ( -6564566756798818168 ) OR RAND ( ) ) OVER ( ORDER BY 4310251258742839724 ) AS c14 GROUP BY 1 LIMIT 1 ) ) , c52 INT ) REPLACE AS SELECT 81 AS c44 WHERE -48 IS NULL ;

Will trigger assertion failure:
/home/wx/mysql-8.0.34/sql/item_sum.cc:642: virtual bool Item_sum::aggregate_check_group(uchar*): Assertion `fixed' failed.

GDB info:
#0  __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50
#1  0x00007ffff6af2859 in __GI_abort () at abort.c:79
#2  0x00007ffff6af2729 in __assert_fail_base (fmt=0x7ffff6c88588 "%s%s%s:%u: %s%sAssertion `%s' failed.\n%n", assertion=0x55555dfdb880 "fixed", file=0x55555dfdbc60 "/home/wx/mysql-8.0.34/sql/item_sum.cc", line=642, function=<optimized out>) at assert.c:92
#3  0x00007ffff6b03fd6 in __GI___assert_fail (assertion=assertion@entry=0x55555dfdb880 "fixed", file=file@entry=0x55555dfdbc60 "/home/wx/mysql-8.0.34/sql/item_sum.cc", line=line@entry=642, function=function@entry=0x55555dfddbc0 "virtual bool Item_sum::aggregate_check_group(uchar*)") at assert.c:101
#4  0x0000555559e42890 in Item_sum::aggregate_check_group (this=0x61400015c270, arg=0x7fffc707acb0 "") at /home/wx/mysql-8.0.34/sql/item_sum.cc:648
#5  0x0000555559d3e0b0 in Item_func::walk (this=<optimized out>, processor=&virtual table offset 792, walk=<optimized out>, argument=<optimized out>) at /home/wx/mysql-8.0.34/sql/item_func.cc:613
#6  0x0000555559b8e725 in Group_check::check_expression (this=this@entry=0x7fffc707acb0, thd=thd@entry=0x6270002bf900, expr=0x61400015c270, in_select_list=in_select_list@entry=true) at /home/wx/mysql-8.0.34/sql/aggregate_check.cc:291
#7  0x0000555559b8f730 in Group_check::check_query (this=this@entry=0x7fffc707acb0, thd=thd@entry=0x6270002bf900) at /home/wx/mysql-8.0.34/sql/aggregate_check.cc:187
#8  0x000055555956ed06 in Query_block::check_only_full_group_by (this=this@entry=0x61900052dab0, thd=thd@entry=0x6270002bf900) at /home/wx/mysql-8.0.34/sql/sql_resolver.cc:4594
#9  0x0000555559578216 in Query_block::apply_local_transforms (this=<optimized out>, thd=<optimized out>, prune=<optimized out>) at /home/wx/mysql-8.0.34/sql/sql_resolver.cc:830
#10 0x0000555559578003 in Query_block::apply_local_transforms (this=this@entry=0x61900052b2b0, thd=thd@entry=0x6270002bf900, prune=prune@entry=true) at /home/wx/mysql-8.0.34/sql/sql_resolver.cc:761
#11 0x000055555958e6f1 in Query_block::prepare (this=this@entry=0x61900052b2b0, thd=thd@entry=0x6270002bf900, insert_field_list=insert_field_list@entry=0x0) at /home/wx/mysql-8.0.34/sql/sql_resolver.cc:589
#12 0x000055555970ff37 in Query_expression::prepare (this=this@entry=0x6120002eda70, thd=thd@entry=0x6270002bf900, sel_result=sel_result@entry=0x61400015c470, insert_field_list=insert_field_list@entry=0x0, added_options=added_options@entry=268435456, removed_options=removed_options@entry=0) at /home/wx/mysql-8.0.34/sql/sql_union.cc:753
#13 0x000055555a1a92c1 in Sql_cmd_create_table::execute (this=<optimized out>, thd=0x6270002bf900) at /home/wx/mysql-8.0.34/sql/sql_cmd_ddl_table.cc:372
#14 0x00005555594b8caf in mysql_execute_command (thd=thd@entry=0x6270002bf900, first_level=first_level@entry=true) at /home/wx/mysql-8.0.34/sql/sql_parse.cc:3688
#15 0x00005555594c0bfe in dispatch_sql_command (thd=0x6270002bf900, parser_state=parser_state@entry=0x7fffc707ec00) at /home/wx/mysql-8.0.34/sql/sql_parse.cc:5368
#16 0x00005555594c38e2 in dispatch_command (thd=thd@entry=0x6270002bf900, com_data=com_data@entry=0x7fffc7080200, command=<optimized out>) at /home/wx/mysql-8.0.34/sql/sql_parse.cc:2054
#17 0x00005555594c7392 in do_command (thd=thd@entry=0x6270002bf900) at /home/wx/mysql-8.0.34/sql/sql_parse.cc:1439
#18 0x000055555989b7be in handle_connection (arg=arg@entry=0x60300016b070) at /home/wx/mysql-8.0.34/sql/conn_handler/connection_handler_per_thread.cc:302
#19 0x000055555cc724e9 in pfs_spawn_thread (arg=0x614000140a60) at /home/wx/mysql-8.0.34/storage/perfschema/pfs.cc:3042
#20 0x00007ffff7568609 in start_thread (arg=<optimized out>) at pthread_create.c:477
#21 0x00007ffff6bef133 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

Observed that only debug build is affected.

How to repeat:
Run the queries above.
[26 Sep 9:32] MySQL Verification Team 
Hello xin wen,

Thank you for the report and test case.
Observed that 8.0.34 debug build is affected.

regards,
Umesh
[26 Sep 9:33] MySQL Verification Team 
- release build 

 ./mtr --nocheck-testcases bug112470
Logging: ./mtr  --nocheck-testcases bug112470
MySQL Version 8.0.34
Checking supported features
Using 'all' suites
Collecting tests
Checking leftover processes
Removing old var directory
Creating var directory '/export/home/tmp/ushastry/mysql-8.0.34/mysql-test/var'
Installing system database
Using parallel: 1

==============================================================================
                  TEST NAME                       RESULT  TIME (ms) COMMENT
------------------------------------------------------------------------------
CREATE TABLE t0 ( CONSTRAINT cc0 CHECK ( c52 > REPEAT ( -77 , COS ( -5093762040897600327 ) ) + TRIM( -112 ) REGEXP ( SELECT STDDEV_SAMP( QUOTE ( 10 ) AND REVERSE ( -6564566756798818168 ) OR RAND ( ) ) OVER ( ORDER BY 4310251258742839724 ) AS c14 GROUP BY 1 LIMIT 1 ) ) , c52 INT ) REPLACE AS SELECT 81 AS c44 WHERE -48 IS NULL ;
[ 50%] main.bug112470                            [ fail ]
        Test ended at 2023-09-26 11:32:01

CURRENT_TEST: main.bug112470
mysqltest: At line 1: Query 'CREATE TABLE t0 ( CONSTRAINT cc0 CHECK ( c52 > REPEAT ( -77 , COS ( -5093762040897600327 ) ) + TRIM( -112 ) REGEXP ( SELECT STDDEV_SAMP( QUOTE ( 10 ) AND REVERSE ( -6564566756798818168 ) OR RAND ( ) ) OVER ( ORDER BY 4310251258742839724 ) AS c14 GROUP BY 1 LIMIT 1 ) ) , c52 INT ) REPLACE AS SELECT 81 AS c44 WHERE -48 IS NULL ' failed.
ERROR 3814 (HY000): An expression of a check constraint 'cc0' contains disallowed function: rand.
safe_process[24137]: Child process: 24138, exit: 1

-- debug build

 ./mtr --nocheck-testcases bug112470 --debug-server
Logging: ./mtr  --nocheck-testcases bug112470 --debug-server
MySQL Version 8.0.34
Checking supported features
 - Binaries are debug compiled
Using 'all' suites
Collecting tests
Checking leftover processes
Removing old var directory
Creating var directory '/export/home/tmp/ushastry/mysql-8.0.34/mysql-test/var'
Installing system database
Using parallel: 1

==============================================================================
                  TEST NAME                       RESULT  TIME (ms) COMMENT
------------------------------------------------------------------------------
CREATE TABLE t0 ( CONSTRAINT cc0 CHECK ( c52 > REPEAT ( -77 , COS ( -5093762040897600327 ) ) + TRIM( -112 ) REGEXP ( SELECT STDDEV_SAMP( QUOTE ( 10 ) AND REVERSE ( -6564566756798818168 ) OR RAND ( ) ) OVER ( ORDER BY 4310251258742839724 ) AS c14 GROUP BY 1 LIMIT 1 ) ) , c52 INT ) REPLACE AS SELECT 81 AS c44 WHERE -48 IS NULL ;
[ 50%] main.bug112470                            [ fail ]
        Test ended at 2023-09-26 11:32:40

CURRENT_TEST: main.bug112470
mysqltest: At line 1: Query 'CREATE TABLE t0 ( CONSTRAINT cc0 CHECK ( c52 > REPEAT ( -77 , COS ( -5093762040897600327 ) ) + TRIM( -112 ) REGEXP ( SELECT STDDEV_SAMP( QUOTE ( 10 ) AND REVERSE ( -6564566756798818168 ) OR RAND ( ) ) OVER ( ORDER BY 4310251258742839724 ) AS c14 GROUP BY 1 LIMIT 1 ) ) , c52 INT ) REPLACE AS SELECT 81 AS c44 WHERE -48 IS NULL ' failed.
ERROR 2013 (HY000): Lost connection to MySQL server during query