Bug #107602 Assertion `keyparts > 0 at calc_length_and_keyparts

Submitted: 20 Jun 2022 7:24 Modified: 20 Jun 2022 8:01
Reporter: zhijun long Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S6 (Debug Builds)
Version:5.7.38 OS:Any
Assigned to: CPU Architecture:Any

[20 Jun 2022 7:24] zhijun long
Description:
mysqld: ../mysql-server-5.7/sql/sql_select.cc:1209: void calc_length_and_keyparts(Key_use*, JOIN_TAB*, uint, table_map, Key_use**, uint*, uint*, table_map*, bool*): Assertion `keyparts > 0' failed.

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 = 7fffe825cd00 thread_stack 0x40000
./bin/mysqld(my_print_stacktrace+0x35)[0x1883ebd]
./bin/mysqld(handle_fatal_signal+0x3e4)[0xecf825]
/lib64/libpthread.so.0(+0xf630)[0x7ffff7bce630]
/lib64/libc.so.6(gsignal+0x37)[0x7ffff5ee1387]
/lib64/libc.so.6(abort+0x148)[0x7ffff5ee2a78]
/lib64/libc.so.6(+0x2f1a6)[0x7ffff5eda1a6]
/lib64/libc.so.6(+0x2f252)[0x7ffff5eda252]
./bin/mysqld(_Z24calc_length_and_keypartsP7Key_useP8JOIN_TABjyPS0_PjS4_PyPb+0x211)[0x15a02a7]
./bin/mysqld(_Z18create_ref_for_keyP4JOINP8JOIN_TABP7Key_usey+0x171)[0x15a042d]
./bin/mysqld(_ZN4JOIN15init_ref_accessEv+0x134)[0x159feca]
./bin/mysqld(_ZN4JOIN8optimizeEv+0x155c)[0x152876e]
./bin/mysqld(_ZN13st_select_lex8optimizeEP3THD+0xd2)[0x159fab6]
./bin/mysqld(_Z12handle_queryP3THDP3LEXP12Query_resultyy+0x2f3)[0x159e23c]
./bin/mysqld[0x1554131]
./bin/mysqld(_Z21mysql_execute_commandP3THDb+0xd15)[0x154d93f]
./bin/mysqld(_Z11mysql_parseP3THDP12Parser_state+0x6b1)[0x1555230]
./bin/mysqld(_Z16dispatch_commandP3THDPK8COM_DATA19enum_server_command+0xcb9)[0x154a764]
./bin/mysqld(_Z10do_commandP3THD+0x4ba)[0x15495af]
./bin/mysqld(handle_connection+0x1ee)[0x167a5b9]
./bin/mysqld(pfs_spawn_thread+0x173)[0x1d3c07a]
/lib64/libpthread.so.0(+0x7ea5)[0x7ffff7bc6ea5]
/lib64/libc.so.6(clone+0x6d)[0x7ffff5fa9b0d]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (7fff9c005890): EXPLAIN SELECT count(1) AS count FROM (     SELECT substring(fa, getByteLength('11') + 2) AS ide,         ti     FROM t1     WHERE cl = 1     ) AS TEMP WHERE TEMP.ide IN (         SELECT /*+ SEMIJOIN(materialization) */ ap.Ide         FROM t2 AS ap         JOIN t3 AS ex ON ap.Ide = ex.Ide         WHERE ap.ctime > 0         )     AND TEMP.ti IN (         SELECT orderid         FROM t4         WHERE ts IN (                 'running',                 'completed',                 'cancel'                 )         )
Connection ID (thread ID): 2
Status: NOT_KILLED

How to repeat:
Step1:create tables

CREATE TABLE `t1` (
  `cl` text COLLATE utf8_bin,
  `fa` varchar(250) COLLATE utf8_bin DEFAULT NULL,
  `ti` text COLLATE utf8_bin,
  `id` varchar(250) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `key1` (`fa`),
  KEY `IDX_1` (`ti`(250))
) ENGINE=InnoDB;

CREATE TABLE `t2` (
  `ID` varchar(250) COLLATE utf8_bin NOT NULL,
  `ide` varchar(128) COLLATE utf8_bin NOT NULL,
  `ctime` bigint(20) DEFAULT '0',
  PRIMARY KEY (`ide`),
  KEY `Index1` (`ctime`)
) ENGINE=InnoDB;

CREATE TABLE `t3` (
  `ID` varchar(250) COLLATE utf8_bin NOT NULL,
  `ide` varchar(128) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`ide`)
) ENGINE=InnoDB;

CREATE TABLE `t4` (
  `ts` text COLLATE utf8_bin,
  `orderid` varchar(100) COLLATE utf8_bin DEFAULT NULL,
  UNIQUE KEY `key_1` (`orderid`)) ENGINE=InnoDB;

SET GLOBAL log_bin_trust_function_creators=1;
DELIMITER //
CREATE FUNCTION getByteLength(inputText text)
RETURNS INT
BEGIN
	RETURN(length(inputText));
END//
DELIMITER ;

Step2: Load data
LOAD DATA INFILE '../t1_data.sql' INTO TABLE t1;
LOAD DATA INFILE '../t2_data.sql' INTO TABLE t2;
LOAD DATA INFILE '../t3_data.sql' INTO TABLE t3;
LOAD DATA INFILE '../t4_data.sql' INTO TABLE t4;

[zhijun@localhost]$ cat t1_data.sql
1       aaa111111111aaaaaaaaaaaaaaaa_ddff       AAA-20220620    001
[zhijun@localhost]$ cat t2_data.sql
222222222222222bbbbbbbbbbbbbbb  111111111aaaaaaaaaaaaaaaa_ddff  1
[zhijun@localhost]$ cat t3_data.sql
222222222222222bbbbbbbbbbbbbbb  111111111aaaaaaaaaaaaaaaa_ddff
[zhijun@localhost]$ cat t4_data.sql
completed       AAA-20220620

Step3: 
EXPLAIN SELECT count(1) AS count
FROM (
    SELECT substring(fa, getByteLength('11') + 2) AS ide,
        ti
    FROM t1
    WHERE cl = 1
    ) AS TEMP
WHERE TEMP.ide IN (
        SELECT /*+ SEMIJOIN(materialization) */ ap.Ide
        FROM t2 AS ap
        JOIN t3 AS ex ON ap.Ide = ex.Ide
        WHERE ap.ctime > 0
        )
    AND TEMP.ti IN (
        SELECT orderid
        FROM t4
        WHERE ts IN (
                'running',
                'completed',
                'cancel'
                )
        );
[20 Jun 2022 8:01] MySQL Verification Team
Hello zhijun long,

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

regards,
Umesh
[20 Jun 2022 8:02] MySQL Verification Team
- 5.7.38 release build - not affected

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

mysql> use test1
Database changed
mysql> CREATE TABLE `t1` (
    ->   `cl` text COLLATE utf8_bin,
    ->   `fa` varchar(250) COLLATE utf8_bin DEFAULT NULL,
    ->   `ti` text COLLATE utf8_bin,
    ->   `id` varchar(250) COLLATE utf8_bin NOT NULL,
    ->   PRIMARY KEY (`id`),
    ->   UNIQUE KEY `key1` (`fa`),
    ->   KEY `IDX_1` (`ti`(250))
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.02 sec)

mysql>
mysql> CREATE TABLE `t2` (
    ->   `ID` varchar(250) COLLATE utf8_bin NOT NULL,
    ->   `ide` varchar(128) COLLATE utf8_bin NOT NULL,
    ->   `ctime` bigint(20) DEFAULT '0',
    ->   PRIMARY KEY (`ide`),
    ->   KEY `Index1` (`ctime`)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> CREATE TABLE `t3` (
    ->   `ID` varchar(250) COLLATE utf8_bin NOT NULL,
    ->   `ide` varchar(128) COLLATE utf8_bin NOT NULL,
    ->   PRIMARY KEY (`ide`)
    -> ) ENGINE=InnoDB;
DELIMITER ;
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> CREATE TABLE `t4` (
    ->   `ts` text COLLATE utf8_bin,
    ->   `orderid` varchar(100) COLLATE utf8_bin DEFAULT NULL,
    ->   UNIQUE KEY `key_1` (`orderid`)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> SET GLOBAL log_bin_trust_function_creators=1;
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER //
mysql> CREATE FUNCTION getByteLength(inputText text)
    -> RETURNS INT
    -> BEGIN
    -> RETURN(length(inputText));
    -> END//
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql>
mysql> LOAD DATA INFILE '/export/home/tmp/ushastry/mysql-8.0.29/t1_data.sql' INTO TABLE t1;
ERROR 1261 (01000): Row 1 doesn't contain data for all columns
mysql> set sql_mode='';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> LOAD DATA INFILE '/export/home/tmp/ushastry/mysql-8.0.29/t1_data.sql' INTO TABLE t1;
Query OK, 1 row affected, 3 warnings (0.00 sec)
Records: 1  Deleted: 0  Skipped: 0  Warnings: 3

mysql> LOAD DATA INFILE '/export/home/tmp/ushastry/mysql-8.0.29/t2_data.sql' INTO TABLE t2;
Query OK, 1 row affected, 2 warnings (0.00 sec)
Records: 1  Deleted: 0  Skipped: 0  Warnings: 2

mysql> LOAD DATA INFILE '/export/home/tmp/ushastry/mysql-8.0.29/t3_data.sql' INTO TABLE t3;
Query OK, 1 row affected, 1 warning (0.00 sec)
Records: 1  Deleted: 0  Skipped: 0  Warnings: 1

mysql> LOAD DATA INFILE '/export/home/tmp/ushastry/mysql-8.0.29/t4_data.sql' INTO TABLE t4;
Query OK, 1 row affected, 1 warning (0.00 sec)
Records: 1  Deleted: 0  Skipped: 0  Warnings: 1

mysql> EXPLAIN SELECT count(1) AS count
    -> FROM (
    ->     SELECT substring(fa, getByteLength('11') + 2) AS ide,
        WHERE ts IN (
    ->         ti
    ->     FROM t1
    ->     WHERE cl = 1
    ->     ) AS TEMP
    -> WHERE TEMP.ide IN (
    ->         SELECT /*+ SEMIJOIN(materialization) */ ap.Ide
    ->         FROM t2 AS ap
    ->         JOIN t3 AS ex ON ap.Ide = ex.Ide
    ->         WHERE ap.ctime > 0
    ->         )
    ->     AND TEMP.ti IN (
    ->         SELECT orderid
    ->         FROM t4
    ->         WHERE ts IN (
    ->                 'running',
    ->                 'completed',
    ->                 'cancel'
    ->                 )
    ->         );
+----+--------------+-------------+------------+--------+----------------+---------+---------+--------------+------+----------+------------------------------------+
| id | select_type  | table       | partitions | type   | possible_keys  | key     | key_len | ref          | rows | filtered | Extra                              |
+----+--------------+-------------+------------+--------+----------------+---------+---------+--------------+------+----------+------------------------------------+
|  1 | SIMPLE       | t1          | NULL       | ALL    | IDX_1          | NULL    | NULL    | NULL         |    1 |   100.00 | Using where                        |
|  1 | SIMPLE       | <subquery3> | NULL       | ref    | <auto_key>     | NULL    | NULL    | NULL         |    1 |   100.00 | NULL                               |
|  1 | SIMPLE       | t4          | NULL       | ref    | key_1          | key_1   | 303     | func         |    1 |   100.00 | Using index condition; Using where |
|  3 | MATERIALIZED | ap          | NULL       | index  | PRIMARY,Index1 | Index1  | 9       | NULL         |    1 |   100.00 | Using where; Using index           |
|  3 | MATERIALIZED | ex          | NULL       | eq_ref | PRIMARY        | PRIMARY | 386     | test1.ap.ide |    1 |   100.00 | Using index                        |
+----+--------------+-------------+------------+--------+----------------+---------+---------+--------------+------+----------+------------------------------------+
5 rows in set, 1 warning (0.00 sec)
[20 Jun 2022 8:03] MySQL Verification Team
- 5.7.38 debug build

 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> use test1
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> EXPLAIN SELECT count(1) AS count
FROM (
    SELECT substring(fa, getByteLength('11') + 2) AS ide,
        ti
    FROM t1
    WHERE cl = 1
    ) AS TEMP
WHERE TEMP.ide IN (
        SELECT /*+ SEMIJOIN(materialization) */ ap.Ide
        FROM t2 AS ap
        JOIN t3 AS ex ON ap.Ide = ex.Ide
        WHERE ap.ctime > 0
        )
    AND TEMP.ti IN (
        SELECT orderid
        FROM t4
        WHERE ts IN (
    -> FROM (
    ->     SELECT substring(fa, getByteLength('11') + 2) AS ide,
    ->         ti
    ->     FROM t1
    ->     WHERE cl = 1
    ->     ) AS TEMP
    -> WHERE TEMP.ide IN (
    ->         SELECT /*+ SEMIJOIN(materialization) */ ap.Ide
    ->         FROM t2 AS ap
    ->         JOIN t3 AS ex ON ap.Ide = ex.Ide
    ->         WHERE ap.ctime > 0
    ->         )
    ->     AND TEMP.ti IN (
    ->         SELECT orderid
    ->         FROM t4
    ->         WHERE ts IN (
    ->                 'running',
    ->                 'completed',
    ->                 'cancel'
    ->                 )
    ->         );
ERROR 2013 (HY000): Lost connection to MySQL server during query

(gdb) bt
#0  0x00007f811066baa1 in pthread_kill () from /lib64/libpthread.so.0
#1  0x000000000191acf5 in my_write_core (sig=6) at /var/lib/pb2/sb_1-6473437-1647886122.65/mysql-5.7.38/mysys/stacktrace.c:261
#2  0x0000000000f068c1 in handle_fatal_signal (sig=6) at /var/lib/pb2/sb_1-6473437-1647886122.65/mysql-5.7.38/sql/signal_handler.cc:227
#3  <signal handler called>
#4  0x00007f810f056387 in raise () from /lib64/libc.so.6
#5  0x00007f810f057a78 in abort () from /lib64/libc.so.6
#6  0x00007f810f04f1a6 in __assert_fail_base () from /lib64/libc.so.6
#7  0x00007f810f04f252 in __assert_fail () from /lib64/libc.so.6
#8  0x000000000161b40c in calc_length_and_keyparts (keyuse=0x7f80a49deae0, tab=0x7f80a49db7a0, key=0, used_tables=4611686018427387921, chosen_keyuses=0x7f81042ec670, length_out=0x7f81042ec718, keyparts_out=0x7f81042ec71c,
    dep_map=0x0, maybe_null=0x0) at /var/lib/pb2/sb_1-6473437-1647886122.65/mysql-5.7.38/sql/sql_select.cc:1209
#9  0x000000000161b5a1 in create_ref_for_key (join=0x7f80a49d8180, j=0x7f80a49db7a0, org_keyuse=0x7f80a49dea78, used_tables=4611686018427387921) at /var/lib/pb2/sb_1-6473437-1647886122.65/mysql-5.7.38/sql/sql_select.cc:1267
#10 0x000000000161b006 in JOIN::init_ref_access (this=0x7f80a49d8180) at /var/lib/pb2/sb_1-6473437-1647886122.65/mysql-5.7.38/sql/sql_select.cc:1106
#11 0x00000000015a023a in JOIN::optimize (this=0x7f80a49d8180) at /var/lib/pb2/sb_1-6473437-1647886122.65/mysql-5.7.38/sql/sql_optimizer.cc:472
#12 0x000000000161ac03 in st_select_lex::optimize (this=0x7f80a4005b20, thd=0x7f80a4000b70) at /var/lib/pb2/sb_1-6473437-1647886122.65/mysql-5.7.38/sql/sql_select.cc:1016
#13 0x0000000001619317 in handle_query (thd=0x7f80a4000b70, lex=0x7f80a4002d10, result=0x7f80a494ec08, added_options=0, removed_options=0) at /var/lib/pb2/sb_1-6473437-1647886122.65/mysql-5.7.38/sql/sql_select.cc:171
#14 0x00000000015cdc3b in execute_sqlcom_select (thd=0x7f80a4000b70, all_tables=0x7f80a494a070) at /var/lib/pb2/sb_1-6473437-1647886122.65/mysql-5.7.38/sql/sql_parse.cc:5152
#15 0x00000000015c6a9c in mysql_execute_command (thd=0x7f80a4000b70, first_level=true) at /var/lib/pb2/sb_1-6473437-1647886122.65/mysql-5.7.38/sql/sql_parse.cc:2829
#16 0x00000000015cee9a in mysql_parse (thd=0x7f80a4000b70, parser_state=0x7f81042ee5f0) at /var/lib/pb2/sb_1-6473437-1647886122.65/mysql-5.7.38/sql/sql_parse.cc:5600
#17 0x00000000015c35e8 in dispatch_command (thd=0x7f80a4000b70, com_data=0x7f81042eedc0, command=COM_QUERY) at /var/lib/pb2/sb_1-6473437-1647886122.65/mysql-5.7.38/sql/sql_parse.cc:1493
#18 0x00000000015c228a in do_command (thd=0x7f80a4000b70) at /var/lib/pb2/sb_1-6473437-1647886122.65/mysql-5.7.38/sql/sql_parse.cc:1032
#19 0x00000000016fc0d5 in handle_connection (arg=0x46c6790) at /var/lib/pb2/sb_1-6473437-1647886122.65/mysql-5.7.38/sql/conn_handler/connection_handler_per_thread.cc:313
#20 0x0000000001f58ef8 in pfs_spawn_thread (arg=0x46bd910) at /var/lib/pb2/sb_1-6473437-1647886122.65/mysql-5.7.38/storage/perfschema/pfs.cc:2197
#21 0x00007f8110666ea5 in start_thread () from /lib64/libpthread.so.0
#22 0x00007f810f11eb0d in clone () from /lib64/libc.so.6
(gdb)

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.044 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.