MySQL Bugs: #107636: Assertion in ha_index_init() failed in MySQL 8.0.29

Bug #107636	Assertion in ha_index_init() failed in MySQL 8.0.29
Submitted:	22 Jun 2022 16:33	Modified:	23 Jun 2022 5:59
Reporter:	Wang Ke	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S6 (Debug Builds)
Version:	8.0.29, 5.7.38	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	assertion failure

Description:
An assertion failure occurred in function ha_index_init() of mysql server(8.0.29-debug-asan).

Test case:

```
CREATE TABLE t1 ( k INT , KEY ( k ) ) ENGINE = MyISAM ;
INSERT INTO t1 VALUES ( 1 ) ;
CREATE TABLE t2 ( k INT , KEY ( k ) ) ENGINE = MyISAM ;
INSERT INTO t2 VALUES ( 1 ) ;
SELECT ra0 . k FROM t2 ra0 WHERE ( SELECT ra0 . k FROM t1 ra3 HAVING MIN( ra3 . k ) < MAX( ra0 . k ) ) < 1 ;
```

Log file:

```
2022-06-22T16:26:22.914099Z 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/handler.cc:2901: int handler::ha_index_init(uint, bool): Assertion `table_share->tmp_table != NO_TMP_TABLE || m_lock_type != F_UNLCK' failed.
16:26:50 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 = 7f0e45e21b20 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) [0x7f0e7846b390]
/lib/x86_64-linux-gnu/libc.so.6(gsignal+0x38) [0x7f0e7695f438]
/lib/x86_64-linux-gnu/libc.so.6(abort+0x16a) [0x7f0e7696103a]
/lib/x86_64-linux-gnu/libc.so.6(+0x2dbe7) [0x7f0e76957be7]
/lib/x86_64-linux-gnu/libc.so.6(+0x2dc92) [0x7f0e76957c92]
/home/mysql-server/mysql-8.0.29-origin-dev/bin/mysqld(handler::ha_index_init(unsigned int, bool)+0x619) [0x66427f9]
/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*)+0x18a9) [0x70a8879]
/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_singlerow_subselect::val_int()+0x12b) [0x683b00b]
/home/mysql-server/mysql-8.0.29-origin-dev/bin/mysqld(Arg_comparator::compare_int_signed()+0xa4) [0x6783924]
/home/mysql-server/mysql-8.0.29-origin-dev/bin/mysqld(Item_func_lt::val_int()+0x9d) [0x679793d]
/home/mysql-server/mysql-8.0.29-origin-dev/bin/mysqld() [0x7741890]
/home/mysql-server/mysql-8.0.29-origin-dev/bin/mysqld(JOIN::optimize(bool)+0x661e) [0x77300ee]
/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) [0x7f0e784616ba]
/lib/x86_64-linux-gnu/libc.so.6(clone+0x6d) [0x7f0e76a3151d]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (60e000078490): SELECT ra0 . k FROM t2 ra0 WHERE ( SELECT ra0 . k FROM t1 ra3 HAVING MIN( ra3 . k ) < MAX( ra0 . k ) ) < 1
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.
```
I didn't verify the test case in release build yet.

How to repeat:
Run the test case:

```
CREATE TABLE t1 ( k INT , KEY ( k ) ) ENGINE = MyISAM ;
INSERT INTO t1 VALUES ( 1 ) ;
CREATE TABLE t2 ( k INT , KEY ( k ) ) ENGINE = MyISAM ;
INSERT INTO t2 VALUES ( 1 ) ;
SELECT ra0 . k FROM t2 ra0 WHERE ( SELECT ra0 . k FROM t1 ra3 HAVING MIN( ra3 . k ) < MAX( ra0 . k ) ) < 1 ;
```

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

- 8.0.29 release build - not affected

rm -rf 107636/
bin/mysqld --no-defaults --initialize-insecure --basedir=$PWD --datadir=$PWD/107636 --log-error-verbosity=3
bin/mysqld_safe --no-defaults --basedir=$PWD --datadir=$PWD/107636 --core-file --socket=/tmp/mysql.sock  --port=3306 --log-error=$PWD/107636/log.err --mysqlx-port=33330 --mysqlx-socket=/tmp/mysql_x_ushastry.sock --log-error-verbosity=3  --secure-file-priv="" --local-infile=1 2>&1 &

 bin/mysql -uroot -S /tmp/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.29 MySQL Community Server - GPL

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 test;
Query OK, 1 row affected (0.00 sec)

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

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

mysql> CREATE TABLE t2 ( k INT , KEY ( k ) ) ENGINE = MyISAM ;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t2 VALUES ( 1 ) ;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT ra0 . k FROM t2 ra0 WHERE ( SELECT ra0 . k FROM t1 ra3 HAVING MIN( ra3 . k ) < MAX( ra0 . k ) ) < 1 ;
Empty set (0.00 sec)

mysql> SELECT ra0 . k FROM t2 ra0 WHERE ( SELECT ra0 . k FROM t1 ra3 HAVING MIN( ra3 . k ) < MAX( ra0 . k ) ) < 1 ;
Empty set (0.00 sec)

mysql> SELECT ra0 . k FROM t2 ra0 WHERE ( SELECT ra0 . k FROM t1 ra3 HAVING MIN( ra3 . k ) < MAX( ra0 . k ) ) < 1 ;
Empty set (0.00 sec)

-- 8.0.29 debug build - affected

rm -rf 107636/
bin/mysqld --no-defaults --initialize-insecure --basedir=$PWD --datadir=$PWD/107636 --log-error-verbosity=3
bin/mysqld_safe --no-defaults --mysqld-version=debug --basedir=$PWD --datadir=$PWD/107636 --core-file --socket=/tmp/mysql.sock  --port=3306 --log-error=$PWD/107636/log.err --mysqlx-port=33330 --mysqlx-socket=/tmp/mysql_x_ushastry.sock --log-error-verbosity=3  --secure-file-priv="" --local-infile=1 2>&1 &

 bin/mysql -uroot -S /tmp/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.29-debug MySQL Community Server - GPL - Debug

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 test;
Query OK, 1 row affected (0.01 sec)

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

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

mysql> CREATE TABLE t2 ( k INT , KEY ( k ) ) ENGINE = MyISAM ;
Query OK, 0 rows affected (0.01 sec)

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

mysql> SELECT ra0 . k FROM t2 ra0 WHERE ( SELECT ra0 . k FROM t1 ra3 HAVING MIN( ra3 . k ) < MAX( ra0 . k ) ) < 1 ;
ERROR 2013 (HY000): Lost connection to MySQL server during query

- 5.7.38 release build not affected

rm -rf 107638/
bin/mysqld --initialize-insecure --basedir=$PWD --datadir=$PWD/107638 --log-error-verbosity=3
bin/mysqld --no-defaults --basedir=$PWD --datadir=$PWD/107638 --core-file --socket=/tmp/mysql.sock --port=3306 --log-error=$PWD/107638/log.err --log-error-verbosity=3 --secure-file-priv=""  --performance-schema=ON 2>&1 &

 bin/mysql -uroot -S /tmp/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.38 MySQL Community Server (GPL)

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 test;
Query OK, 1 row affected (0.00 sec)

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

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

mysql> CREATE TABLE t2 ( k INT , KEY ( k ) ) ENGINE = MyISAM ;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t2 VALUES ( 1 ) ;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT ra0 . k FROM t2 ra0 WHERE ( SELECT ra0 . k FROM t1 ra3 HAVING MIN( ra3 . k ) < MAX( ra0 . k ) ) < 1 ;
Empty set (0.00 sec)
- 5.7.38 debug build affected

rm -rf 107638/
bin/mysqld --initialize-insecure --basedir=$PWD --datadir=$PWD/107638 --log-error-verbosity=3
bin/mysqld-debug --no-defaults --basedir=$PWD --datadir=$PWD/107638 --core-file --socket=/tmp/mysql.sock --port=3306 --log-error=$PWD/107638/log.err --log-error-verbosity=3 --secure-file-priv=""  --performance-schema=ON 2>&1 &

 bin/mysql -uroot -S /tmp/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.38-debug MySQL Community Server - Debug (GPL)

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> drop database test;
Query OK, 2 rows affected (0.00 sec)

mysql> drop database test1;
Query OK, 1 row affected (0.01 sec)

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

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

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

mysql> CREATE TABLE t2 ( k INT , KEY ( k ) ) ENGINE = MyISAM ;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t2 VALUES ( 1 ) ;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT ra0 . k FROM t2 ra0 WHERE ( SELECT ra0 . k FROM t1 ra3 HAVING MIN( ra3 . k ) < MAX( ra0 . k ) ) < 1 ;
ERROR 2013 (HY000): Lost connection to MySQL server during query