MySQL Bugs: #112951: Assertion `fixed' failed in MySQL 8.1.0

Bug #112951	Assertion `fixed' failed in MySQL 8.1.0
Submitted:	3 Nov 2023 10:05	Modified:	3 Nov 2023 10:15
Reporter:	Wang Ke	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S6 (Debug Builds)
Version:	8.1.0, 8.0.35, 8.2.0	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	assertion failure

Description:
Hello, an assertion failure occurred in MySQL 8.1.0-debug:

```
mysqld: /home/mysql-8.1.0/sql/item_sum.cc:646: virtual bool Item_sum::aggregate_check_group(uchar *): Assertion `fixed' failed.
2023-11-03T09:58:38Z UTC - mysqld got signal 6 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
BuildID[sha1]=d41d0359075d9acaf807cea3acc3b128cdec5219
Thread pointer: 0x6270002bc100
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 7ff84e1f0a20 thread_stack 0x100000
/home/mysql-8.1.0-origin/bin/mysqld(__interceptor_backtrace+0x5b) [0x649134b]
/home/mysql-8.1.0-origin/bin/mysqld(my_print_stacktrace(unsigned char const*, unsigned long)+0x10d) [0xbfde8ed]
/home/mysql-8.1.0-origin/bin/mysqld(print_fatal_signal(int)+0x552) [0x8979ee2]
/home/mysql-8.1.0-origin/bin/mysqld(handle_fatal_signal+0x175) [0x897a5c5]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x11390) [0x7ff87fb72390]
/lib/x86_64-linux-gnu/libc.so.6(gsignal+0x38) [0x7ff87de5b438]
/lib/x86_64-linux-gnu/libc.so.6(abort+0x16a) [0x7ff87de5d03a]
/lib/x86_64-linux-gnu/libc.so.6(+0x2dbe7) [0x7ff87de53be7]
/lib/x86_64-linux-gnu/libc.so.6(+0x2dc92) [0x7ff87de53c92]
/home/mysql-8.1.0-origin/bin/mysqld(Item_sum::aggregate_check_group(unsigned char*)+0x36d) [0x702d28d]
/home/mysql-8.1.0-origin/bin/mysqld(Item_func::walk(bool (Item::*)(unsigned char*), enum_walk, unsigned char*)+0x168) [0x68c9778]
/home/mysql-8.1.0-origin/bin/mysqld(Group_check::check_expression(THD*, Item*, bool)+0x711) [0x70b4d91]
/home/mysql-8.1.0-origin/bin/mysqld(Group_check::check_query(THD*)+0x718) [0x70b38a8]
/home/mysql-8.1.0-origin/bin/mysqld(Query_block::check_only_full_group_by(THD*)+0x239) [0x822cc79]
/home/mysql-8.1.0-origin/bin/mysqld(Query_block::apply_local_transforms(THD*, bool)+0xa1e) [0x8223e4e]
/home/mysql-8.1.0-origin/bin/mysqld(Query_block::apply_local_transforms(THD*, bool)+0x4e5) [0x8223915]
/home/mysql-8.1.0-origin/bin/mysqld(Query_block::prepare(THD*, mem_root_deque<Item*>*)+0x3d40) [0x820adb0]
/home/mysql-8.1.0-origin/bin/mysqld(Query_expression::prepare(THD*, Query_result*, mem_root_deque<Item*>*, unsigned long long, unsigned long long)+0xdbe) [0x857038e]
/home/mysql-8.1.0-origin/bin/mysqld(Sql_cmd_create_table::execute(THD*)+0x3255) [0x7c56015]
/home/mysql-8.1.0-origin/bin/mysqld(mysql_execute_command(THD*, bool)+0x5331) [0x80a0a91]
/home/mysql-8.1.0-origin/bin/mysqld(dispatch_sql_command(THD*, Parser_state*)+0x1b28) [0x8094e68]
/home/mysql-8.1.0-origin/bin/mysqld(dispatch_command(THD*, COM_DATA const*, enum_server_command)+0x377a) [0x808522a]
/home/mysql-8.1.0-origin/bin/mysqld(do_command(THD*)+0x12ee) [0x80902be]
/home/mysql-8.1.0-origin/bin/mysqld() [0x88ff832]
/home/mysql-8.1.0-origin/bin/mysqld() [0xeb1629a]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x76ba) [0x7ff87fb686ba]
/lib/x86_64-linux-gnu/libc.so.6(clone+0x6d) [0x7ff87df2d51d]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (6110000f0570): CREATE TABLE t0 ( c0 INT ZEROFILL CHECK ( t0.ca3 < ( ( SELECT 1 ca0 FROM DUAL GROUP BY 1 HAVING 1 ORDER BY COUNT( 1 ) ) > RAND( ) / 100 ) ) ) SELECT true AS ca3
Connection ID (thread ID): 8
Status: NOT_KILLED
```

Observed that release build is not affected:

```
mysql> CREATE TABLE t0 ( c0 INT ZEROFILL CHECK ( t0.ca3 < ( ( SELECT 1 ca0 FROM DUAL GROUP BY 1 HAVING 1 ORDER BY COUNT( 1 ) ) > RAND( ) / 100 ) ) ) SELECT true AS ca3;
ERROR 3813 (HY000): Column check constraint 't0_chk_1' references other column.
```

How to repeat:
Test case:

```
CREATE TABLE t0 ( c0 INT ZEROFILL CHECK ( t0.ca3 < ( ( SELECT 1 ca0 FROM DUAL GROUP BY 1 HAVING 1 ORDER BY COUNT( 1 ) ) > RAND( ) / 100 ) ) ) SELECT true AS ca3;
```

Hello Wang Ke,

Thank you for the report and test case.
Observed that 8.0.35 debug build is affected with provided test case.

regards,
Umesh

-
./mtr --nocheck-testcases bug112951 --debug-server
Logging: ./mtr  --nocheck-testcases bug112951 --debug-server
MySQL Version 8.0.35
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.35/mysql-test/var'
Installing system database
Using parallel: 1

==============================================================================
                  TEST NAME                       RESULT  TIME (ms) COMMENT
------------------------------------------------------------------------------
CREATE TABLE t0 ( c0 INT ZEROFILL CHECK ( t0.ca3 < ( ( SELECT 1 ca0 FROM DUAL GROUP BY 1 HAVING 1 ORDER BY COUNT( 1 ) ) > RAND( ) / 100 ) ) ) SELECT true AS ca3;
[ 50%] main.bug112951                            [ fail ]
        Test ended at 2023-11-03 11:14:41

CURRENT_TEST: main.bug112951
mysqltest: At line 1: Query 'CREATE TABLE t0 ( c0 INT ZEROFILL CHECK ( t0.ca3 < ( ( SELECT 1 ca0 FROM DUAL GROUP BY 1 HAVING 1 ORDER BY COUNT( 1 ) ) > RAND( ) / 100 ) ) ) SELECT true AS ca3' failed.
ERROR 2013 (HY000): Lost connection to MySQL server during query

-bt

#0  0x00007f1ffd792aa1 in pthread_kill () from /lib64/libpthread.so.0
#1  0x0000000003fbbfa6 in my_write_core(int) ()
#2  0x00000000032595e3 in handle_fatal_signal ()
#3  <signal handler called>
#4  0x00007f1ffb857387 in raise () from /lib64/libc.so.6
#5  0x00007f1ffb858a78 in abort () from /lib64/libc.so.6
#6  0x00007f1ffb8501a6 in __assert_fail_base () from /lib64/libc.so.6
#7  0x00007f1ffb850252 in __assert_fail () from /lib64/libc.so.6
#8  0x000000000345cd34 in Item_sum::aggregate_check_group(unsigned char*) ()
#9  0x0000000003400084 in Item_func::walk(bool (Item::*)(unsigned char*), enum_walk, unsigned char*) ()
#10 0x00000000033649a6 in Group_check::check_expression(THD*, Item*, bool) ()
#11 0x0000000003364fca in Group_check::check_query(THD*) ()
#12 0x000000000312642e in Query_block::check_only_full_group_by(THD*) ()
#13 0x0000000003128641 in Query_block::apply_local_transforms(THD*, bool) [clone .localalias.10] ()
#14 0x0000000003128561 in Query_block::apply_local_transforms(THD*, bool) [clone .localalias.10] ()
#15 0x0000000003130589 in Query_block::prepare(THD*, mem_root_deque<Item*>*) ()
#16 0x00000000031b1e8a in Query_expression::prepare(THD*, Query_result*, mem_root_deque<Item*>*, unsigned long long, unsigned long long) ()
#17 0x000000000359df50 in Sql_cmd_create_table::execute(THD*) ()
#18 0x00000000030e497b in mysql_execute_command(THD*, bool) ()
#19 0x00000000030e8295 in dispatch_sql_command(THD*, Parser_state*) ()
#20 0x00000000030e99f9 in dispatch_command(THD*, COM_DATA const*, enum_server_command) ()
#21 0x00000000030eb737 in do_command(THD*) ()
#22 0x000000000324ab8b in handle_connection ()
#23 0x0000000004776b03 in pfs_spawn_thread ()
#24 0x00007f1ffd78dea5 in start_thread () from /lib64/libpthread.so.0
#25 0x00007f1ffb91fb2d in clone () from /lib64/libc.so.6

- 8.1.0 debug build

./mtr --nocheck-testcases bug112951 --debug-server
Logging: ./mtr  --nocheck-testcases bug112951 --debug-server
MySQL Version 8.1.0
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.1.0/mysql-test/var'
Installing system database
Using parallel: 1

==============================================================================
                  TEST NAME                       RESULT  TIME (ms) COMMENT
------------------------------------------------------------------------------
CREATE TABLE t0 ( c0 INT ZEROFILL CHECK ( t0.ca3 < ( ( SELECT 1 ca0 FROM DUAL GROUP BY 1 HAVING 1 ORDER BY COUNT( 1 ) ) > RAND( ) / 100 ) ) ) SELECT true AS ca3;
[ 50%] main.bug112951                            [ fail ]
        Test ended at 2023-11-03 11:21:24

CURRENT_TEST: main.bug112951
mysqltest: At line 1: Query 'CREATE TABLE t0 ( c0 INT ZEROFILL CHECK ( t0.ca3 < ( ( SELECT 1 ca0 FROM DUAL GROUP BY 1 HAVING 1 ORDER BY COUNT( 1 ) ) > RAND( ) / 100 ) ) ) SELECT true AS ca3' failed.
ERROR 2013 (HY000): Lost connection to MySQL server during query

-bt
#0  0x00007f4e1fbefaa1 in pthread_kill () from /lib64/libpthread.so.0
#1  0x0000000004076b77 in my_write_core(int) ()
#2  0x000000000330766e in handle_fatal_signal ()
#3  <signal handler called>
#4  0x00007f4e1df3a387 in raise () from /lib64/libc.so.6
#5  0x00007f4e1df3ba78 in abort () from /lib64/libc.so.6
#6  0x00007f4e1df331a6 in __assert_fail_base () from /lib64/libc.so.6
#7  0x00007f4e1df33252 in __assert_fail () from /lib64/libc.so.6
#8  0x000000000350be80 in Item_sum::aggregate_check_group(unsigned char*) ()
#9  0x00000000034ad8ab in Item_func::walk(bool (Item::*)(unsigned char*), enum_walk, unsigned char*) ()
#10 0x000000000340fa06 in Group_check::check_expression(THD*, Item*, bool) ()
#11 0x0000000003410030 in Group_check::check_query(THD*) ()
#12 0x00000000031d0c48 in Query_block::check_only_full_group_by(THD*) ()
#13 0x00000000031d305d in Query_block::apply_local_transforms(THD*, bool) [clone .localalias] ()
#14 0x00000000031d2f7d in Query_block::apply_local_transforms(THD*, bool) [clone .localalias] ()
#15 0x00000000031da319 in Query_block::prepare(THD*, mem_root_deque<Item*>*) ()
#16 0x000000000325ab63 in Query_expression::prepare(THD*, Query_result*, mem_root_deque<Item*>*, unsigned long long, unsigned long long) ()
#17 0x0000000003655b62 in Sql_cmd_create_table::execute(THD*) ()
#18 0x000000000318eb63 in mysql_execute_command(THD*, bool) ()
#19 0x0000000003192579 in dispatch_sql_command(THD*, Parser_state*) ()
#20 0x0000000003193b7f in dispatch_command(THD*, COM_DATA const*, enum_server_command) ()
#21 0x0000000003195763 in do_command(THD*) ()
#22 0x00000000032f967c in handle_connection ()
#23 0x000000000482e4e4 in pfs_spawn_thread ()
#24 0x00007f4e1fbeaea5 in start_thread () from /lib64/libpthread.so.0
#25 0x00007f4e1e002b2d in clone () from /lib64/libc.so.6

- 8.2.0 debug build

 ./mtr --nocheck-testcases bug112951 --debug-server
Logging: ./mtr  --nocheck-testcases bug112951 --debug-server
MySQL Version 8.2.0
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.2.0/mysql-test/var'
Installing system database
Using parallel: 1

==============================================================================
                  TEST NAME                       RESULT  TIME (ms) COMMENT
------------------------------------------------------------------------------
CREATE TABLE t0 ( c0 INT ZEROFILL CHECK ( t0.ca3 < ( ( SELECT 1 ca0 FROM DUAL GROUP BY 1 HAVING 1 ORDER BY COUNT( 1 ) ) > RAND( ) / 100 ) ) ) SELECT true AS ca3;
[ 50%] main.bug112951                            [ fail ]
        Test ended at 2023-11-03 11:22:24

CURRENT_TEST: main.bug112951
mysqltest: At line 1: Query 'CREATE TABLE t0 ( c0 INT ZEROFILL CHECK ( t0.ca3 < ( ( SELECT 1 ca0 FROM DUAL GROUP BY 1 HAVING 1 ORDER BY COUNT( 1 ) ) > RAND( ) / 100 ) ) ) SELECT true AS ca3' failed.
ERROR 2013 (HY000): Lost connection to MySQL server during query

-bt
#0  0x00007f5424c2eaa1 in pthread_kill () from /lib64/libpthread.so.0
#1  0x00000000040c60c1 in my_write_core(int) ()
#2  0x000000000334a9c2 in handle_fatal_signal ()
#3  <signal handler called>
#4  0x00007f5422f78387 in raise () from /lib64/libc.so.6
#5  0x00007f5422f79a78 in abort () from /lib64/libc.so.6
#6  0x00007f5422f711a6 in __assert_fail_base () from /lib64/libc.so.6
#7  0x00007f5422f71252 in __assert_fail () from /lib64/libc.so.6
#8  0x0000000003550e74 in Item_sum::aggregate_check_group(unsigned char*) ()
#9  0x00000000034f2ff5 in Item_func::walk(bool (Item::*)(unsigned char*), enum_walk, unsigned char*) ()
#10 0x0000000003454764 in Group_check::check_expression(THD*, Item*, bool) ()
#11 0x0000000003454d8e in Group_check::check_query(THD*) ()
#12 0x0000000003211434 in Query_block::check_only_full_group_by(THD*) ()
#13 0x0000000003213861 in Query_block::apply_local_transforms(THD*, bool) [clone .localalias] ()
#14 0x0000000003213781 in Query_block::apply_local_transforms(THD*, bool) [clone .localalias] ()
#15 0x000000000321b37b in Query_block::prepare(THD*, mem_root_deque<Item*>*) ()
#16 0x000000000329d3f1 in Query_expression::prepare(THD*, Query_result*, mem_root_deque<Item*>*, unsigned long long, unsigned long long) ()
#17 0x000000000369c353 in Sql_cmd_create_table::execute(THD*) ()
#18 0x00000000031cedc8 in mysql_execute_command(THD*, bool) ()
#19 0x00000000031d284f in dispatch_sql_command(THD*, Parser_state*) ()
#20 0x00000000031d3f63 in dispatch_command(THD*, COM_DATA const*, enum_server_command) ()
#21 0x00000000031d5c7b in do_command(THD*) ()
#22 0x000000000333c9b6 in handle_connection ()
#23 0x00000000048a7ae8 in pfs_spawn_thread ()
#24 0x00007f5424c29ea5 in start_thread () from /lib64/libpthread.so.0
#25 0x00007f5423040b2d in clone () from /lib64/libc.so.6