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)
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.
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' ) );