MySQL Bugs: #111497: Assertion `m_deque == other.m

Bug #111497	Assertion `m_deque == other.m_deque' failed
Submitted:	20 Jun 2023 15:51	Modified:	21 Jun 2023 7:19
Reporter:	Yu Liang	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S6 (Debug Builds)
Version:	8.0.33	OS:	Ubuntu (20.04 LTS)
Assigned to:		CPU Architecture:	x86 (Intel(R) Core(TM) i7-10700 CPU)

Description:
The latest version of the MySQL Server (version 8.0.33 debug build) (git commit hash: ea7087d8850) crashes with Assertion Failure when executing the following query:

Config from "/etc/mysql/conf.d/mysql.cnf":

```
[mysqld]
sql_mode = "NO_ENGINE_SUBSTITUTION"
```

```sql
drop database if exists test123;
create database test123;
use test123;
CREATE TABLE v0 ( c1 INT) ;
WITH RECURSIVE v0 AS ( WITH v0 AS ( WITH v0 AS ( WITH RECURSIVE v0 AS ( select 'any_text' ) TABLE v0 UNION ALL TABLE v0 ) TABLE v0 ) TABLE v0 ) VALUES ROW ( NULL IN ( WITH v0 AS ( TABLE v0 ) TABLE v0 ) );
```

In the debug build, the server crashes with the following stack trace: 

```
mysql> WITH RECURSIVE v0 AS ( WITH v0 AS ( WITH v0 AS ( WITH RECURSIVE v0 AS ( select 'any_text' ) TABLE v0 UNION ALL TABLE v0 ) TABLE v0 ) TABLE v0 ) VALUES ROW ( NULL IN ( WITH v0 AS ( TABLE v0 ) TABLE v0 ) );
mbind: Operation not permitted
mysqld: /home/mysql/mysql-server/include/mem_root_deque.h:336: bool mem_root_deque<Table_ref *>::Iterator<Table_ref *>::operator==(const Iterator<Iterator_element_type> &) const [T = Table_ref *, Iterator_element_type = Table_ref *]: Assertion `m_deque == other.m_deque' failed.
2023-06-20T15:47:43Z UTC - mysqld got signal 6 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
BuildID[sha1]=90a6d529c89400b4547f04097580d294cf5ca17b
Thread pointer: 0xfffef0001040
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 = ffff9464f538 thread_stack 0x100000
/home/mysql/mysql-server/bld/runtime_output_directory/mysqld(my_print_stacktrace(unsigned char const*, unsigned long)+0x60) [0x4e63e10]
/home/mysql/mysql-server/bld/runtime_output_directory/mysqld(print_fatal_signal(int)+0x348) [0x1a844ec]
/home/mysql/mysql-server/bld/runtime_output_directory/mysqld(handle_fatal_signal+0x16c) [0x1a848ac]
linux-vdso.so.1(__kernel_rt_sigreturn+0) [0xffff994f37a0]
/lib/aarch64-linux-gnu/libc.so.6(gsignal+0xe0) [0xffff98b67d78]
/lib/aarch64-linux-gnu/libc.so.6(abort+0x114) [0xffff98b54aac]
/lib/aarch64-linux-gnu/libc.so.6(+0x2d490) [0xffff98b61490]
/lib/aarch64-linux-gnu/libc.so.6(+0x2d4f4) [0xffff98b614f4]
/home/mysql/mysql-server/bld/runtime_output_directory/mysqld() [0x1602668]
/home/mysql/mysql-server/bld/runtime_output_directory/mysqld(JOIN::make_join_plan()+0x1230) [0x15bfc3c]
/home/mysql/mysql-server/bld/runtime_output_directory/mysqld(JOIN::optimize(bool)+0x2500) [0x15b52c0]
/home/mysql/mysql-server/bld/runtime_output_directory/mysqld(Query_block::optimize(THD*, bool)+0x26c) [0x173c148]
/home/mysql/mysql-server/bld/runtime_output_directory/mysqld(Query_expression::optimize(THD*, TABLE*, bool, bool)+0x280) [0x18a943c]
/home/mysql/mysql-server/bld/runtime_output_directory/mysqld(Query_block::optimize(THD*, bool)+0x3ec) [0x173c2c8]
/home/mysql/mysql-server/bld/runtime_output_directory/mysqld(Query_expression::optimize(THD*, TABLE*, bool, bool)+0x280) [0x18a943c]
/home/mysql/mysql-server/bld/runtime_output_directory/mysqld(Sql_cmd_dml::execute_inner(THD*)+0x54) [0x17369ec]
/home/mysql/mysql-server/bld/runtime_output_directory/mysqld(Sql_cmd_dml::execute(THD*)+0x7d8) [0x1734a88]
/home/mysql/mysql-server/bld/runtime_output_directory/mysqld(mysql_execute_command(THD*, bool)+0x279c) [0x1628e64]
/home/mysql/mysql-server/bld/runtime_output_directory/mysqld(dispatch_sql_command(THD*, Parser_state*)+0x1170) [0x16224a8]
/home/mysql/mysql-server/bld/runtime_output_directory/mysqld(dispatch_command(THD*, COM_DATA const*, enum_server_command)+0x548c) [0x161b3e0]
/home/mysql/mysql-server/bld/runtime_output_directory/mysqld(do_command(THD*)+0xbe0) [0x161fab4]
/home/mysql/mysql-server/bld/runtime_output_directory/mysqld() [0x1a54f18]
/home/mysql/mysql-server/bld/runtime_output_directory/mysqld() [0x65594d0]
/lib/aarch64-linux-gnu/libpthread.so.0(+0x7624) [0xffff99499624]
/lib/aarch64-linux-gnu/libc.so.6(+0xd149c) [0xffff98c0549c]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (fffef00243c0): WITH RECURSIVE v0 AS ( WITH v0 AS ( WITH v0 AS ( WITH RECURSIVE v0 AS ( select 'any_text' ) TABLE v0 UNION ALL TABLE v0 ) TABLE v0 ) TABLE v0 ) VALUES ROW ( NULL IN ( WITH v0 AS ( TABLE v0 ) TABLE v0 ) )
Connection ID (thread ID): 8
Status: NOT_KILLED
```

How to repeat:
Steps to repeat the Assertion Failure:
1. Download the MySQL Server source code from the official github repo: `https://github.com/mysql/mysql-server`
2. Checkout to the latest mysql released version: 8.0.33 (hash: `ea7087d8850`)
3. Compile MySQL using the command: 

```
mkdir -p bld
cd bld
cmake .. -DDOWNLOAD_BOOST=1 -DWITH_BOOST=../boost -DWITH_UNIT_TESTS=OFF -DWITH_DEBUG=1
make
```

4. Run the MySQL Server with command: 

```
./bin/mysqld --basedir=$(pwd) --datadir=$(pwd)/data_all/ori_data --port=7000  --socket=/tmp/mysql_0.sock --mysqlx=OFF --performance_schema=OFF
```

5. Setup the MySQL Server config in the path: "/etc/mysql/conf.d/mysql.cnf"

```
[mysqld]
sql_mode = "NO_ENGINE_SUBSTITUTION"
```

6. Run the MySQL Client with the PoC:

```
./bin/mysql --port=7000 --user=root --socket=/tmp/mysql_0.sock < poc_0.sql
```

where `poc_0.sql` is:

```sql
drop database if exists test123;
create database test123;
use test123;
CREATE TABLE v0 ( c1 INT) ;
WITH RECURSIVE v0 AS ( WITH v0 AS ( WITH v0 AS ( WITH RECURSIVE v0 AS ( select 'any_text' ) TABLE v0 UNION ALL TABLE v0 ) TABLE v0 ) TABLE v0 ) VALUES ROW ( NULL IN ( WITH v0 AS ( TABLE v0 ) TABLE v0 ) );
```

Suggested fix:
The server should continue running instead of crashing by Assertion Failure.

Only debug mode is affected.

Hello Yu Liang,

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

regards,
Umesh

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

==============================================================================
                  TEST NAME                       RESULT  TIME (ms) COMMENT
------------------------------------------------------------------------------
drop database if exists test123;
Warnings:
Note    1008    Can't drop database 'test123'; database doesn't exist
create database test123;
use test123;
CREATE TABLE v0 ( c1 INT) ;
WITH RECURSIVE v0 AS ( WITH v0 AS ( WITH v0 AS ( WITH RECURSIVE v0 AS ( select 'any_text' ) TABLE v0 UNION ALL TABLE v0 ) TABLE v0 ) TABLE v0 ) VALUES ROW ( NULL IN ( WITH v0 AS ( TABLE v0 ) TABLE v0 ) );
column_0
NULL
[ 50%] main.bug111497                            [ pass ]      5
[100%] shutdown_report                           [ pass ]

--debug build - affected

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

==============================================================================
                  TEST NAME                       RESULT  TIME (ms) COMMENT
------------------------------------------------------------------------------
drop database if exists test123;
Warnings:
Note    1008    Can't drop database 'test123'; database doesn't exist
create database test123;
use test123;
CREATE TABLE v0 ( c1 INT) ;
WITH RECURSIVE v0 AS ( WITH v0 AS ( WITH v0 AS ( WITH RECURSIVE v0 AS ( select 'any_text' ) TABLE v0 UNION ALL TABLE v0 ) TABLE v0 ) TABLE v0 ) VALUES ROW ( NULL IN ( WITH v0 AS ( TABLE v0 ) TABLE v0 ) );
[ 50%] main.bug111497                            [ fail ]
        Test ended at 2023-06-21 09:19:32

CURRENT_TEST: main.bug111497
mysqltest: At line 5: Query 'WITH RECURSIVE v0 AS ( WITH v0 AS ( WITH v0 AS ( WITH RECURSIVE v0 AS ( select 'any_text' ) TABLE v0 UNION ALL TABLE v0 ) TABLE v0 ) TABLE v0 ) VALUES ROW ( NULL IN ( WITH v0 AS ( TABLE v0 ) TABLE v0 ) )' failed.
ERROR 2013 (HY000): Lost connection to MySQL server during query
.
-bt

#0  0x00007f6cd6091aa1 in pthread_kill () from /lib64/libpthread.so.0
#1  0x0000000003f9296e in my_write_core(int) ()
#2  0x00000000032365f5 in handle_fatal_signal ()
#3  <signal handler called>
#4  0x00007f6cd42cb387 in raise () from /lib64/libc.so.6
#5  0x00007f6cd42cca78 in abort () from /lib64/libc.so.6
#6  0x00007f6cd42c41a6 in __assert_fail_base () from /lib64/libc.so.6
#7  0x00007f6cd42c4252 in __assert_fail () from /lib64/libc.so.6
#8  0x000000000302192f in mem_root_deque<Table_ref*>::Iterator<Table_ref*>::operator==(mem_root_deque<Table_ref*>::Iterator<Table_ref*> const&) const ()
#9  0x0000000003021939 in mem_root_deque<Table_ref*>::Iterator<Table_ref*>::operator!=(mem_root_deque<Table_ref*>::Iterator<Table_ref*> const&) const ()
#10 0x00000000030b2d62 in add_key_fields_for_nj(THD*, JOIN*, Table_ref*, Key_field**, unsigned int*, SARGABLE_PARAM**) ()
#11 0x00000000030b3216 in update_ref_and_keys(THD*, Mem_root_array<Key_use>*, JOIN_TAB*, unsigned int, Item*, unsigned long, Query_block*, SARGABLE_PARAM**) ()
#12 0x00000000030b36b0 in JOIN::make_join_plan() ()
#13 0x00000000030b4860 in JOIN::optimize(bool) ()
#14 0x000000000311a4b1 in Query_block::optimize(THD*, bool) ()
#15 0x000000000319107f in Query_expression::optimize(THD*, TABLE*, bool, bool) ()
#16 0x000000000311a51d in Query_block::optimize(THD*, bool) ()
#17 0x000000000319107f in Query_expression::optimize(THD*, TABLE*, bool, bool) ()
#18 0x000000000311a0d5 in Sql_cmd_dml::execute_inner(THD*) ()
#19 0x0000000003123873 in Sql_cmd_dml::execute(THD*) ()
#20 0x00000000030c4964 in mysql_execute_command(THD*, bool) ()
#21 0x00000000030c5dc9 in dispatch_sql_command(THD*, Parser_state*) ()
#22 0x00000000030c752b in dispatch_command(THD*, COM_DATA const*, enum_server_command) ()
#23 0x00000000030c926e in do_command(THD*) ()
#24 0x0000000003227bb7 in handle_connection ()
#25 0x0000000004743af9 in pfs_spawn_thread ()
#26 0x00007f6cd608cea5 in start_thread () from /lib64/libpthread.so.0
#27 0x00007f6cd4393b2d in clone () from /lib64/libc.so.6