Bug #107663 Assertion `keyparts > 0' failed in MySQL 8.0.29
Submitted: 26 Jun 2022 14:56 Modified: 26 Jun 2022 15:30
Reporter: Wang Ke Email Updates:
Status: Verified 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

[26 Jun 2022 14:56] Wang Ke
Description:
A test case triggered an assertion failure in mysql-8.0.29-debug-asan:

```
CREATE TABLE t1 ( a INT ) ENGINE = innodb ;
INSERT INTO t1 VALUES ( 0 ) , ( 1 ) ;
CREATE TABLE t2 ( b TEXT , c INT , PRIMARY KEY ( b ( 1 ) ) ) ENGINE = innodb ;
INSERT INTO t2 VALUES ( 'a' , 0 ) ;
SELECT 2147483647 ca6 FROM ( ( ( SELECT ( SELECT COUNT( * ) ca1 FROM t1 ra3 WHERE ra3 . a = 0 ) AS ca3 FROM t1 ra1 WHERE ra1 . a IN ( SELECT ra2 . a FROM t1 ra2 WHERE ra1 . a = 1 ) ) UNION ( ( SELECT 1 ca5 FROM t2 ra4 ) ) ) ) ra0 WHERE EXISTS ( SELECT ra5 . a FROM t1 ra5 WHERE ra0 . ca3 = ( SELECT DEFAULT ( ra5 . a ) ca2 FROM t2 ra6 WHERE ra5 . a = 1 OR ra6 . b AND - 0 = 1 ) GROUP BY ra0 . ca3 ) ;
```

Log file:

```
2022-06-26T14:50:42.474169Z 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/sql_select.cc:2103: void calc_length_and_keyparts(Key_use *, JOIN_TAB *, const uint, table_map, Key_use **, uint *, uint *, table_map *, bool *): Assertion `keyparts > 0' failed.
14:51:36 UTC - mysqld got signal 6 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
Thread pointer: 0x62700021e900
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 = 7fd9c467ab20 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) [0x7fd9e5d04390]
/lib/x86_64-linux-gnu/libc.so.6(gsignal+0x38) [0x7fd9e41f8438]
/lib/x86_64-linux-gnu/libc.so.6(abort+0x16a) [0x7fd9e41fa03a]
/lib/x86_64-linux-gnu/libc.so.6(+0x2dbe7) [0x7fd9e41f0be7]
/lib/x86_64-linux-gnu/libc.so.6(+0x2dc92) [0x7fd9e41f0c92]
/home/mysql-server/mysql-8.0.29-origin-dev/bin/mysqld(calc_length_and_keyparts(Key_use*, JOIN_TAB*, unsigned int, unsigned long, Key_use**, unsigned int*, unsigned int*, unsigned long*, bool*)+0x877) [0x7b771b7]
/home/mysql-server/mysql-8.0.29-origin-dev/bin/mysqld(create_ref_for_key(JOIN*, JOIN_TAB*, Key_use*, unsigned long)+0x465) [0x7b743a5]
/home/mysql-server/mysql-8.0.29-origin-dev/bin/mysqld(JOIN::init_ref_access()+0x4a3) [0x7b73a13]
/home/mysql-server/mysql-8.0.29-origin-dev/bin/mysqld(JOIN::optimize(bool)+0x63de) [0x772feae]
/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) [0x7fd9e5cfa6ba]
/lib/x86_64-linux-gnu/libc.so.6(clone+0x6d) [0x7fd9e42ca51d]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (61400001e270): SELECT 2147483647 ca6 FROM ( ( ( SELECT ( SELECT COUNT( * ) ca1 FROM t1 ra3 WHERE ra3 . a = 0 ) AS ca3 FROM t1 ra1 WHERE ra1 . a IN ( SELECT ra2 . a FROM t1 ra2 WHERE ra1 . a = 1 ) ) UNION ( ( SELECT 1 ca5 FROM t2 ra4 ) ) ) ) ra0 WHERE EXISTS ( SELECT ra5 . a FROM t1 ra5 WHERE ra0 . ca3 = ( SELECT DEFAULT ( ra5 . a ) ca2 FROM t2 ra6 WHERE ra5 . a = 1 OR ra6 . b AND - 0 = 1 ) GROUP BY ra0 . ca3 )
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.
```

Observed that release build is not affected.

How to repeat:
Run the test case:

```
CREATE TABLE t1 ( a INT ) ENGINE = innodb ;
INSERT INTO t1 VALUES ( 0 ) , ( 1 ) ;
CREATE TABLE t2 ( b TEXT , c INT , PRIMARY KEY ( b ( 1 ) ) ) ENGINE = innodb ;
INSERT INTO t2 VALUES ( 'a' , 0 ) ;
SELECT 2147483647 ca6 FROM ( ( ( SELECT ( SELECT COUNT( * ) ca1 FROM t1 ra3 WHERE ra3 . a = 0 ) AS ca3 FROM t1 ra1 WHERE ra1 . a IN ( SELECT ra2 . a FROM t1 ra2 WHERE ra1 . a = 1 ) ) UNION ( ( SELECT 1 ca5 FROM t2 ra4 ) ) ) ) ra0 WHERE EXISTS ( SELECT ra5 . a FROM t1 ra5 WHERE ra0 . ca3 = ( SELECT DEFAULT ( ra5 . a ) ca2 FROM t2 ra6 WHERE ra5 . a = 1 OR ra6 . b AND - 0 = 1 ) GROUP BY ra0 . ca3 ) ;
```
[26 Jun 2022 15:30] 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
[26 Jun 2022 15:32] MySQL Verification Team
- 5.7.38 release/debug build not affected