MySQL Bugs: #111306: AccessPath* MoveCompositeIteratorsFromTablePath(AccessPath*, const Query

Bug #111306	AccessPath* MoveCompositeIteratorsFromTablePath(AccessPath*, const Query_block&
Submitted:	6 Jun 2023 22:04	Modified:	1 Mar 2024 17:49
Reporter:	Yu Liang	Email Updates:
Status:	Closed	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)

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

```
mysql> CREATE TABLE v0 ( c1 INT);

Query OK, 0 rows affected (0.02 sec)

mysql> ( TABLE v0 LIMIT 100 ) ORDER BY c1 MEMBER OF ( c1 )  < c1 MEMBER ( ROW_NUMBER ( ) OVER ( ) ) ;

mbind: Operation not permitted
mysqld: /home/mysql/mysql-server/sql/sql_executor.cc:1612: AccessPath *MoveCompositeIteratorsFromTablePath(AccessPath *, const Query_block &): Assertion `false' failed.
2023-06-06T22:01:27Z UTC - mysqld got signal 6 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
BuildID[sha1]=1d55d4671b7469352fc317efb0121b604c26443d
Thread pointer: 0xfffef8001040
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 = ffff84166538 thread_stack 0x100000
/home/mysql/mysql-server/bld/runtime_output_directory/mysqld(my_print_stacktrace(unsigned char const*, unsigned long)+0x60) [0x4e6bc30]
/home/mysql/mysql-server/bld/runtime_output_directory/mysqld(print_fatal_signal(int)+0x340) [0x1a89320]
/home/mysql/mysql-server/bld/runtime_output_directory/mysqld(handle_fatal_signal+0x16c) [0x1a896dc]
linux-vdso.so.1(__kernel_rt_sigreturn+0) [0xffff9f6b47a0]
/lib/aarch64-linux-gnu/libc.so.6(gsignal+0xe0) [0xffff9ed28d78]
/lib/aarch64-linux-gnu/libc.so.6(abort+0x114) [0xffff9ed15aac]
/lib/aarch64-linux-gnu/libc.so.6(+0x2d490) [0xffff9ed22490]
/lib/aarch64-linux-gnu/libc.so.6(+0x2d4f4) [0xffff9ed224f4]
/home/mysql/mysql-server/bld/runtime_output_directory/mysqld(MoveCompositeIteratorsFromTablePath(AccessPath*, Query_block const&)+0xb4c) [0x154e46c]
/home/mysql/mysql-server/bld/runtime_output_directory/mysqld() [0x18b3c04]
/home/mysql/mysql-server/bld/runtime_output_directory/mysqld(make_set_op_access_path(THD*, Query_term_set_op*, Query_term*, Mem_root_array<AppendPathParameters>*, bool)+0xab8) [0x18b2e8c]
/home/mysql/mysql-server/bld/runtime_output_directory/mysqld(make_set_op_access_path(THD*, Query_term_set_op*, Query_term*, Mem_root_array<AppendPathParameters>*, bool)+0x468) [0x18b283c]
/home/mysql/mysql-server/bld/runtime_output_directory/mysqld(Query_expression::create_access_paths(THD*)+0x4c0) [0x18b0dfc]
/home/mysql/mysql-server/bld/runtime_output_directory/mysqld(Query_expression::optimize(THD*, TABLE*, bool, bool)+0x8a8) [0x18aebb0]
/home/mysql/mysql-server/bld/runtime_output_directory/mysqld(Sql_cmd_dml::execute_inner(THD*)+0x54) [0x1739d98]
/home/mysql/mysql-server/bld/runtime_output_directory/mysqld(Sql_cmd_dml::execute(THD*)+0x7a0) [0x1737e1c]
/home/mysql/mysql-server/bld/runtime_output_directory/mysqld(mysql_execute_command(THD*, bool)+0x281c) [0x162bcd8]
/home/mysql/mysql-server/bld/runtime_output_directory/mysqld(dispatch_sql_command(THD*, Parser_state*)+0x1124) [0x16252c0]
/home/mysql/mysql-server/bld/runtime_output_directory/mysqld(dispatch_command(THD*, COM_DATA const*, enum_server_command)+0x5434) [0x161e224]
/home/mysql/mysql-server/bld/runtime_output_directory/mysqld(do_command(THD*)+0xb8c) [0x16228f4]
/home/mysql/mysql-server/bld/runtime_output_directory/mysqld() [0x1a59a64]
/home/mysql/mysql-server/bld/runtime_output_directory/mysqld() [0x655c1a0]
/lib/aarch64-linux-gnu/libpthread.so.0(+0x7624) [0xffff9f65a624]
/lib/aarch64-linux-gnu/libc.so.6(+0xd149c) [0xffff9edc649c]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (fffef80243c0): ( TABLE v0 LIMIT 100 ) ORDER BY c1 MEMBER OF ( c1 )  < c1 MEMBER ( ROW_NUMBER ( ) OVER ( ) )
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 -DUSE_LD_GOLD=1 -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. 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);
( TABLE v0 LIMIT 100 ) ORDER BY c1 MEMBER OF ( c1 )  < c1 MEMBER ( ROW_NUMBER ( ) OVER ( ) ) ;
```

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

Hello Yu Liang,

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

regards,
Umesh

- 8.0.33 release build - not affected

./mtr bug111306 --nocheck-testcases
Logging: ./mtr  bug111306 --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);
( TABLE v0 LIMIT 100 ) ORDER BY c1 MEMBER OF ( c1 )  < c1 MEMBER ( ROW_NUMBER ( ) OVER ( ) ) ;
c1
[ 50%] main.bug111306                            [ pass ]      7
[100%] shutdown_report                           [ pass ]
------------------------------------------------------------------------------
The servers were restarted 0 times
The servers were reinitialized 0 times
Spent 0.007 of 6 seconds executing testcases

- 8.0.33 release build - not affected

 ./mtr bug111306 --nocheck-testcases --debug-server
Logging: ./mtr  bug111306 --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);
( TABLE v0 LIMIT 100 ) ORDER BY c1 MEMBER OF ( c1 )  < c1 MEMBER ( ROW_NUMBER ( ) OVER ( ) ) ;
[ 50%] main.bug111306                            [ fail ]
        Test ended at 2023-06-07 07:26:27

CURRENT_TEST: main.bug111306
mysqltest: At line 5: Query '( TABLE v0 LIMIT 100 ) ORDER BY c1 MEMBER OF ( c1 )  < c1 MEMBER ( ROW_NUMBER ( ) OVER ( ) ) ' failed.
ERROR 2013 (HY000): Lost connection to MySQL server during query

.
#0  0x00007f071938faa1 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  0x00007f07175c9387 in raise () from /lib64/libc.so.6
#5  0x00007f07175caa78 in abort () from /lib64/libc.so.6
#6  0x00007f07175c21a6 in __assert_fail_base () from /lib64/libc.so.6
#7  0x00007f07175c2252 in __assert_fail () from /lib64/libc.so.6
#8  0x0000000003079cd2 in MoveCompositeIteratorsFromTablePath(AccessPath*, Query_block const&) ()
#9  0x000000000318dd17 in add_materialized_access_path(THD*, Query_term*, Mem_root_array<MaterializePathParameters::QueryBlock>&, TABLE*, unsigned long long) ()
#10 0x000000000319004b in make_set_op_access_path(THD*, Query_term_set_op*, Query_term*, Mem_root_array<AppendPathParameters>*, bool) [clone .localalias.3] ()
#11 0x000000000318ff72 in make_set_op_access_path(THD*, Query_term_set_op*, Query_term*, Mem_root_array<AppendPathParameters>*, bool) [clone .localalias.3] ()
#12 0x000000000319043e in Query_expression::create_access_paths(THD*) ()
#13 0x0000000003191299 in Query_expression::optimize(THD*, TABLE*, bool, bool) ()
#14 0x000000000311a0d5 in Sql_cmd_dml::execute_inner(THD*) ()
#15 0x0000000003123873 in Sql_cmd_dml::execute(THD*) ()
#16 0x00000000030c4964 in mysql_execute_command(THD*, bool) ()
#17 0x00000000030c5dc9 in dispatch_sql_command(THD*, Parser_state*) ()
#18 0x00000000030c752b in dispatch_command(THD*, COM_DATA const*, enum_server_command) ()
#19 0x00000000030c926e in do_command(THD*) ()
#20 0x0000000003227bb7 in handle_connection ()
#21 0x0000000004743af9 in pfs_spawn_thread ()
#22 0x00007f071938aea5 in start_thread () from /lib64/libpthread.so.0
#23 0x00007f0717691b2d in clone () from /lib64/libc.so.6

I was able to reproduce this today with:

CREATE TABLE t0 (c0 INT);
CREATE VIEW v0 (c0, c1) AS ((SELECT 1,2 FROM t0) INTERSECT (SELECT 1,2));
SELECT 1 FROM v0 JOIN t0 ON t0.c0 = v0.c1 WHERE (t0.c0 + t0.c0) > v0.c0;

Documented fix as follows in the MySQL 8.0.36 and 8.3.0 changelogs:

    Ordering a nested block where the ORDER BY contained a window
    function raised an assert in sql/sql_executor.cc.

Closed.