| 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: | |
| Category: | MySQL Server: Optimizer | Severity: | S6 (Debug Builds) |
| Version: | 5.7.38 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[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)
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' ) );