Bug #107647 Assertion `!table->file->inited' failed in MySQL 8.0.29
Submitted: 24 Jun 2022 7:58 Modified: 20 Dec 2022 13:50
Reporter: Wang Ke Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S6 (Debug Builds)
Version:8.0.29 OS:Any
Assigned to: CPU Architecture:Any
Tags: assertion failure

[24 Jun 2022 7:58] Wang Ke
Description:
An assertion failure occurred in the process of fuzzing with mysql-8.0.29-debug-asan:

Test case:

```
CREATE TABLE t1 ( k INT , KEY ( k ) ) ENGINE = MyISAM ; 
INSERT INTO t1 VALUES ( 1 ) ; 
SELECT * FROM t1 ra0 LEFT JOIN t1 ra1 ON ra0 . k IN ( SELECT MAX( ra0 . k ) FROM t1 ) ;
```

Log file:

```
2022-06-24T07:48:11.333649Z 0 [System] [MY-010931] [Server] /home/mysql-server/mysql-8.0.29-origin-dev/bin/mysqld: ready for connections. Version: '8.0.29-debug-asan'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution.
mysqld: /home/mysql-server/mysql-8.0.29-origin/sql/opt_sum.cc:483: bool optimize_aggregated_query(THD *, Query_block *, const mem_root_deque<Item *> &, Item *, aggregate_evaluated *): Assertion `!table->file->inited' failed.
07:48:31 UTC - mysqld got signal 6 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
Thread pointer: 0x62700028e900
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 = 7f5e130bab20 thread_stack 0x100000
/home/mysql-server/mysql-8.0.29-origin-dev/bin/mysqld(__interceptor_backtrace+0x5b) [0x5e4e34b]
/home/mysql-server/mysql-8.0.29-origin-dev/bin/mysqld(my_print_stacktrace(unsigned char const*, unsigned long)+0x10d) [0xb6ab1cd]
/home/mysql-server/mysql-8.0.29-origin-dev/bin/mysqld(print_fatal_signal(int)+0x389) [0x8149469]
/home/mysql-server/mysql-8.0.29-origin-dev/bin/mysqld(handle_fatal_signal+0x175) [0x8149ab5]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x11390) [0x7f5e456f2390]
/lib/x86_64-linux-gnu/libc.so.6(gsignal+0x38) [0x7f5e43be6438]
/lib/x86_64-linux-gnu/libc.so.6(abort+0x16a) [0x7f5e43be803a]
/lib/x86_64-linux-gnu/libc.so.6(+0x2dbe7) [0x7f5e43bdebe7]
/lib/x86_64-linux-gnu/libc.so.6(+0x2dc92) [0x7f5e43bdec92]
/home/mysql-server/mysql-8.0.29-origin-dev/bin/mysqld(optimize_aggregated_query(THD*, Query_block*, mem_root_deque<Item*> const&, Item*, aggregate_evaluated*)+0x3688) [0x70aa658]
/home/mysql-server/mysql-8.0.29-origin-dev/bin/mysqld(JOIN::optimize(bool)+0x2ff0) [0x772cac0]
/home/mysql-server/mysql-8.0.29-origin-dev/bin/mysqld(Query_block::optimize(THD*, bool)+0x330) [0x7b71280]
/home/mysql-server/mysql-8.0.29-origin-dev/bin/mysqld(Query_expression::optimize(THD*, TABLE*, bool, bool)+0x404) [0x7e176c4]
/home/mysql-server/mysql-8.0.29-origin-dev/bin/mysqld(Item_subselect::exec(THD*)+0x841) [0x6830c81]
/home/mysql-server/mysql-8.0.29-origin-dev/bin/mysqld(Item_in_subselect::exec(THD*)+0x67d) [0x68324dd]
/home/mysql-server/mysql-8.0.29-origin-dev/bin/mysqld(Item_in_subselect::val_bool_naked()+0xeb) [0x684125b]
/home/mysql-server/mysql-8.0.29-origin-dev/bin/mysqld(Item_in_optimizer::val_int()+0x310) [0x6791cd0]
/home/mysql-server/mysql-8.0.29-origin-dev/bin/mysqld(join_read_const_table(JOIN_TAB*, POSITION*)+0xd0f) [0x760fbdf]
/home/mysql-server/mysql-8.0.29-origin-dev/bin/mysqld(JOIN::extract_func_dependent_tables()+0x1792) [0x777fbc2]
/home/mysql-server/mysql-8.0.29-origin-dev/bin/mysqld(JOIN::make_join_plan()+0x994) [0x773dc14]
/home/mysql-server/mysql-8.0.29-origin-dev/bin/mysqld(JOIN::optimize(bool)+0x47b1) [0x772e281]
/home/mysql-server/mysql-8.0.29-origin-dev/bin/mysqld(Query_block::optimize(THD*, bool)+0x330) [0x7b71280]
/home/mysql-server/mysql-8.0.29-origin-dev/bin/mysqld(Query_expression::optimize(THD*, TABLE*, bool, bool)+0x404) [0x7e176c4]
/home/mysql-server/mysql-8.0.29-origin-dev/bin/mysqld(Sql_cmd_dml::execute_inner(THD*)+0x9a) [0x7b66eba]
/home/mysql-server/mysql-8.0.29-origin-dev/bin/mysqld(Sql_cmd_dml::execute(THD*)+0xe1d) [0x7b6423d]
/home/mysql-server/mysql-8.0.29-origin-dev/bin/mysqld(mysql_execute_command(THD*, bool)+0x3583) [0x794fbc3]
/home/mysql-server/mysql-8.0.29-origin-dev/bin/mysqld(dispatch_sql_command(THD*, Parser_state*)+0x1b5d) [0x7945c8d]
/home/mysql-server/mysql-8.0.29-origin-dev/bin/mysqld(dispatch_command(THD*, COM_DATA const*, enum_server_command)+0x756f) [0x793a43f]
/home/mysql-server/mysql-8.0.29-origin-dev/bin/mysqld(do_command(THD*)+0xf9c) [0x794149c]
/home/mysql-server/mysql-8.0.29-origin-dev/bin/mysqld() [0x80d00a2]
/home/mysql-server/mysql-8.0.29-origin-dev/bin/mysqld() [0xd6ac2da]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x76ba) [0x7f5e456e86ba]
/lib/x86_64-linux-gnu/libc.so.6(clone+0x6d) [0x7f5e43cb851d]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (60d000a929e0): SELECT * FROM t1 ra0 LEFT JOIN t1 ra1 ON ra0 . k IN ( SELECT MAX( ra0 . k ) FROM t1 )
Connection ID (thread ID): 8
Status: NOT_KILLED

The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
```

No test in release build has been conducted due to my poor disk space, so please verify it for yourself.

How to repeat:
Run the test case:

```
CREATE TABLE t1 ( k INT , KEY ( k ) ) ENGINE = MyISAM ; 
INSERT INTO t1 VALUES ( 1 ) ; 
SELECT * FROM t1 ra0 LEFT JOIN t1 ra1 ON ra0 . k IN ( SELECT MAX( ra0 . k ) FROM t1 ) ;
```
[24 Jun 2022 8:22] Wang Ke
Observed that mysql-8.0.29 release build not affected:

```
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.29 Source distribution

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database mytest;
Query OK, 1 row affected (0.02 sec)

mysql> use mytest;
Database changed
mysql> CREATE TABLE t1 ( k INT , KEY ( k ) ) ENGINE = MyISAM ; 
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO t1 VALUES ( 1 ) ; 
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM t1 ra0 LEFT JOIN t1 ra1 ON ra0 . k IN ( SELECT MAX( ra0 . k ) FROM t1 ) ;
+------+------+
| k    | k    |
+------+------+
|    1 |    1 |
+------+------+
1 row in set (0.00 sec)

mysql> 

```
[24 Jun 2022 9:16] MySQL Verification Team
Hello Wang Ke,

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

regards,
Umesh
[20 Dec 2022 13:50] Jon Stephens
Fixed in MySQL 8.0.33.

Did not affect release builds.

Closed.