MySQL Bugs: #96125: Using index for skip scan, Mysql server core down

Bug #96125	Using index for skip scan, Mysql server core down
Submitted:	8 Jul 2019 14:02	Modified:	8 Jul 2019 15:25
Reporter:	juncai meng	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S6 (Debug Builds)
Version:	8.0.16	OS:	CentOS
Assigned to:		CPU Architecture:	Any

Description:
exec sql statement: select count(*) from STRG_PARTRANGE_TBL_001 where c_id<=600 
mysql server core down：

#8  0x000000000314c2a9 in key_rec_cmp (key=0x7f70fcf90380, first_rec=0x7f70fcfff5c2 "", second_rec=0x7f70fd0009ff "")
    at /data/taurus/mysql-8.0.16/sql/key.cc:594
#9  0x0000000002b40b6b in Key_rec_less::operator() (this=0x7f70fcf619e0, first=0x7f70fcfff5c0 "", second=0x7f70fd0009fd "\001")
    at /data/taurus/mysql-8.0.16/sql/partitioning/partition_handler.h:363
#10 0x0000000002b434d8 in Priority_queue<unsigned char*, std::vector<unsigned char*, std::allocator<unsigned char*> >, Key_rec_less>::heapify (
    this=0x7f70fcf619e0, i=0, last=3) at /data/taurus/mysql-8.0.16/include/priority_queue.h:133
#11 0x0000000002b429c6 in Priority_queue<unsigned char*, std::vector<unsigned char*, std::allocator<unsigned char*> >, Key_rec_less>::heapify (
    this=0x7f70fcf619e0, i=0) at /data/taurus/mysql-8.0.16/include/priority_queue.h:147
#12 0x0000000002b4278b in Priority_queue<unsigned char*, std::vector<unsigned char*, std::allocator<unsigned char*> >, Key_rec_less>::build_heap (
    this=0x7f70fcf619e0) at /data/taurus/mysql-8.0.16/include/priority_queue.h:199
#13 0x0000000002b419b1 in Priority_queue<unsigned char*, std::vector<unsigned char*, std::allocator<unsigned char*> >, Key_rec_less>::assign (
    this=0x7f70fcf619e0, container=...) at /data/taurus/mysql-8.0.16/include/priority_queue.h:187
#14 0x0000000002b3f4c0 in Partition_helper::handle_ordered_index_scan (this=0x7f70fcf902e8, buf=0x7f70fcff0778 "")
    at /data/taurus/mysql-8.0.16/sql/partitioning/partition_handler.cc:2638
#15 0x0000000002b3dc4f in Partition_helper::common_index_read (this=0x7f70fcf902e8, buf=0x7f70fcff0778 "", have_start_key=true)
    at /data/taurus/mysql-8.0.16/sql/partitioning/partition_handler.cc:1972
#16 0x0000000002b3e498 in Partition_helper::ph_read_range_first (this=0x7f70fcf902e8, start_key=0x7f70fcf535d0, end_key=0x7f70fcf535f0, eq_range_arg=false, 
    sorted=true) at /data/taurus/mysql-8.0.16/sql/partitioning/partition_handler.cc:2265
#17 0x0000000004318df3 in ha_innopart::read_range_first (this=0x7f70fcf8ec58, start_key=0x7f70fcf535d0, end_key=0x7f70fcf535f0, eq_range_arg=false, 
    sorted=true) at /data/taurus/mysql-8.0.16/storage/innobase/handler/ha_innopart.h:523
#18 0x0000000002fb68bf in handler::ha_read_range_first (this=0x7f70fcf8ec58, start_key=0x7f70fcf535d0, end_key=0x7f70fcf535f0, eq_range=false, sorted=true)
    at /data/taurus/mysql-8.0.16/sql/handler.cc:7088
#19 0x0000000002b250f7 in QUICK_SKIP_SCAN_SELECT::get_next (this=0x7f70fcf534d0) at /data/taurus/mysql-8.0.16/sql/opt_range.cc:14771
#20 0x0000000002b5972f in IndexRangeScanIterator::Read (this=0x7f70fcfee4d0) at /data/taurus/mysql-8.0.16/sql/records.cc:349
#21 0x0000000002f3c855 in FilterIterator::Read (this=0x7f70fcfeeab0) at /data/taurus/mysql-8.0.16/sql/composite_iterators.cc:70
#22 0x0000000002f3cebf in AggregateIterator::Read (this=0x7f70fcfeead8) at /data/taurus/mysql-8.0.16/sql/composite_iterators.cc:182
#23 0x0000000002bfbe60 in ExecuteIteratorQuery (join=0x7f70fcfedac8) at /data/taurus/mysql-8.0.16/sql/sql_executor.cc:1930
#24 0x0000000002bfc0cb in do_select (join=0x7f70fcfedac8) at /data/taurus/mysql-8.0.16/sql/sql_executor.cc:1986
#25 0x0000000002bf5d60 in JOIN::exec (this=0x7f70fcfedac8) at /data/taurus/mysql-8.0.16/sql/sql_executor.cc:313
#26 0x0000000002cc6b4e in Sql_cmd_dml::execute_inner (this=0x7f70fcfed990, thd=0x7f70fcdaf580) at /data/taurus/mysql-8.0.16/sql/sql_select.cc:903
#27 0x0000000002cc61d4 in Sql_cmd_dml::execute (this=0x7f70fcfed990, thd=0x7f70fcdaf580) at /data/taurus/mysql-8.0.16/sql/sql_select.cc:699
#28 0x0000000002c658c4 in mysql_execute_command (thd=0x7f70fcdaf580, first_level=true) at /data/taurus/mysql-8.0.16/sql/sql_parse.cc:4400
#29 0x0000000002c680f5 in mysql_parse (thd=0x7f70fcdaf580, parser_state=0x7f71a8141c20) at /data/taurus/mysql-8.0.16/sql/sql_parse.cc:5194

How to repeat:
exec sqls follow：

DELIMITER $$

CREATE PROCEDURE test()
begin
declare num_temp int;
set num_temp=0;

while num_temp<2000 do
INSERT INTO STRG_PARTRANGE_TBL_001 VALUES (
num_temp+1,
num_temp+1,
234213542352134,
100.2,
123523.2132,
'wevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbds',
'q2',
'wevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbds',
'dfecddfecddfecddfecd',
'dfecddfecddfecddfecd',
'wevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbds',
'sw',
'ewfxejdsk',
'wedjkswikilsdcnr',
curtime(),
'21',
12342124.23,
0.2,
2323423.56,
2342324.3454,
1200234.235,
true,
now(),
'wevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbds',
'wevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbds',
'wevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbds',
'wevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbds',
'wevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbds',
'wevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbds'
);
set num_temp=num_temp+1;
end while;
end;
$$

DELIMITER ;

create table STRG_PARTRANGE_TBL_001(
c_id INT,
C_D_ID INTEGER NOT NULL,
C_W_ID BIGINT NOT NULL,
C_DOUBLE DOUBLE NOT NULL,
C_DECIMAL DECIMAL NOT NULL,
C_FIRST VARCHAR(64) NOT NULL,
C_MIDDLE CHAR(2),
C_LAST VARCHAR(64) NOT NULL,
C_STREET_1 VARCHAR(20) NOT NULL,
C_STREET_2 VARCHAR(20),
C_CITY VARCHAR(64) NOT NULL,
C_STATE CHAR(2) NOT NULL,
C_ZIP CHAR(9) NOT NULL,
C_PHONE CHAR(16) NOT NULL,
C_SINCE TIMESTAMP,
C_CREDIT CHAR(2) NOT NULL,
C_CREDIT_LIM NUMERIC(12,2),
C_DISCOUNT NUMERIC(4,4),
C_BALANCE NUMERIC(12,2),
C_YTD_PAYMENT REAL NOT NULL,
C_PAYMENT_CNT FLOAT NOT NULL,
C_DELIVERY_CNT BOOLEAN NOT NULL,
C_END DATE NOT NULL,
C_VCHAR VARCHAR(1000),
C_DATA TEXT,
C_TEXT BLOB,
C_TINYTEXT TINYTEXT,
C_MEDIUMBLOB MEDIUMBLOB,
C_LONGBLOB LONGBLOB,
primary key (C_ID))
partition by range(c_id)
(
partition part_1 values less than (201),
partition part_2 values less than (401),
partition part_3 values less than (601),
partition part_4 values less than (801),
partition part_5 values less than (1001),
partition part_6 values less than (1201),
partition part_7 values less than (1401),
partition part_8 values less than (1601),
partition part_9 values less than (1801),
partition part_10 values less than (2001),
partition part_11 values less than (maxvalue)
);

call test();

CREATE UNIQUE INDEX STRG_RANGE_INDEX_001_1 ON STRG_PARTRANGE_TBL_001(c_id);
CREATE INDEX STRG_RANGE_INDEX_001_3 ON STRG_PARTRANGE_TBL_001(C_FIRST);

delete from STRG_PARTRANGE_TBL_001 WHERE (c_id - 2*(floor(c_id/2)))=0;

explain select count(*) from STRG_PARTRANGE_TBL_001 where c_id<=600;

select count(*) from STRG_PARTRANGE_TBL_001 where c_id<=600;

Suggested fix:
skip_scan problem.

this problem is always appear

Release built version aren't affected, will test debug binary:

mysql> create database c;
Query OK, 1 row affected (0.02 sec)

mysql> use c
Database changed
mysql> DELIMITER $$
mysql>
mysql> CREATE PROCEDURE test()
    -> begin
    -> declare num_temp int;
    -> set num_temp=0;
    ->
    -> while num_temp<2000 do
    -> INSERT INTO STRG_PARTRANGE_TBL_001 VALUES (
    -> num_temp+1,
    -> num_temp+1,
    -> 234213542352134,
    -> 100.2,
    -> 123523.2132,
    -> 'wevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbds',
    -> 'q2',
    -> 'wevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbds',
    -> 'dfecddfecddfecddfecd',
    -> 'dfecddfecddfecddfecd',
    -> 'wevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbds',
    -> 'sw',
    -> 'ewfxejdsk',
    -> 'wedjkswikilsdcnr',
    -> curtime(),
    -> '21',
    -> 12342124.23,
    -> 0.2,
    -> 2323423.56,
    -> 2342324.3454,
    -> 1200234.235,
    -> true,
    -> now(),
    -> 'wevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbds',
    -> 'wevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbds',
    -> 'wevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbds',
    -> 'wevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbds',
    -> 'wevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbds',
    -> 'wevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbds'
    -> );
    -> set num_temp=num_temp+1;
    -> end while;
    -> end;
    -> $$
Query OK, 0 rows affected (0.02 sec)

mysql>
mysql> DELIMITER ;
mysql>
mysql> create table STRG_PARTRANGE_TBL_001(
    -> c_id INT,
    -> C_D_ID INTEGER NOT NULL,
    -> C_W_ID BIGINT NOT NULL,
    -> C_DOUBLE DOUBLE NOT NULL,
    -> C_DECIMAL DECIMAL NOT NULL,
    -> C_FIRST VARCHAR(64) NOT NULL,
    -> C_MIDDLE CHAR(2),
    -> C_LAST VARCHAR(64) NOT NULL,
    -> C_STREET_1 VARCHAR(20) NOT NULL,
    -> C_STREET_2 VARCHAR(20),
    -> C_CITY VARCHAR(64) NOT NULL,
    -> C_STATE CHAR(2) NOT NULL,
    -> C_ZIP CHAR(9) NOT NULL,
    -> C_PHONE CHAR(16) NOT NULL,
    -> C_SINCE TIMESTAMP,
    -> C_CREDIT CHAR(2) NOT NULL,
    -> C_CREDIT_LIM NUMERIC(12,2),
    -> C_DISCOUNT NUMERIC(4,4),
    -> C_BALANCE NUMERIC(12,2),
    -> C_YTD_PAYMENT REAL NOT NULL,
    -> C_PAYMENT_CNT FLOAT NOT NULL,
    -> C_DELIVERY_CNT BOOLEAN NOT NULL,
    -> C_END DATE NOT NULL,
    -> C_VCHAR VARCHAR(1000),
    -> C_DATA TEXT,
    -> C_TEXT BLOB,
    -> C_TINYTEXT TINYTEXT,
    -> C_MEDIUMBLOB MEDIUMBLOB,
    -> C_LONGBLOB LONGBLOB,
    -> primary key (C_ID))
    -> partition by range(c_id)
    -> (
    -> partition part_1 values less than (201),
    -> partition part_2 values less than (401),
    -> partition part_3 values less than (601),
    -> partition part_4 values less than (801),
    -> partition part_5 values less than (1001),
    -> partition part_6 values less than (1201),
    -> partition part_7 values less than (1401),
    -> partition part_8 values less than (1601),
    -> partition part_9 values less than (1801),
    -> partition part_10 values less than (2001),
    -> partition part_11 values less than (maxvalue)
    -> );
Query OK, 0 rows affected (0.21 sec)

mysql>
mysql> call test();
Query OK, 1 row affected (6.05 sec)

mysql> CREATE UNIQUE INDEX STRG_RANGE_INDEX_001_1 ON STRG_PARTRANGE_TBL_001(c_id);
Query OK, 0 rows affected (0.38 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> CREATE INDEX STRG_RANGE_INDEX_001_3 ON STRG_PARTRANGE_TBL_001(C_FIRST);
Query OK, 0 rows affected (0.40 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>
mysql> delete from STRG_PARTRANGE_TBL_001 WHERE (c_id - 2*(floor(c_id/2)))=0;
Query OK, 1000 rows affected (0.04 sec)

mysql>
mysql> explain select count(*) from STRG_PARTRANGE_TBL_001 where c_id<=600;
+----+-------------+------------------------+----------------------+-------+-------------------------------------------------------+---------+---------+------+------+----------+--------------------------+
| id | select_type | table                  | partitions           | type  | possible_keys                                         | key     | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+------------------------+----------------------+-------+-------------------------------------------------------+---------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | STRG_PARTRANGE_TBL_001 | part_1,part_2,part_3 | range | PRIMARY,STRG_RANGE_INDEX_001_1,STRG_RANGE_INDEX_001_3 | PRIMARY | 4       | NULL |  150 |   100.00 | Using where; Using index |
+----+-------------+------------------------+----------------------+-------+-------------------------------------------------------+---------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

mysql>
mysql> select count(*) from STRG_PARTRANGE_TBL_001 where c_id<=600;
+----------+
| count(*) |
+----------+
|      300 |
+----------+
1 row in set (0.00 sec)

mysql> show variables like "%version%";
+--------------------------+------------------------------+
| Variable_name            | Value                        |
+--------------------------+------------------------------+
| immediate_server_version | 999999                       |
| innodb_version           | 8.0.16                       |
| original_server_version  | 999999                       |
| protocol_version         | 10                           |
| slave_type_conversions   |                              |
| tls_version              | TLSv1,TLSv1.1,TLSv1.2        |
| version                  | 8.0.16                       |
| version_comment          | MySQL Community Server - GPL |
| version_compile_machine  | x86_64                       |
| version_compile_os       | Win64                        |
| version_compile_zlib     | 1.2.11                       |
+--------------------------+------------------------------+
11 rows in set (0.00 sec)

mysql>

Thank you for the bug report, debug built affected:

d:\dbs>d:\dbs\8.0\bin\mysqld --defaults-file=d:\dbs\my80.ini --standalone --console
2019-07-08T15:19:35.733643Z 0 [System] [MY-010116] [Server] d:\dbs\8.0\bin\mysqld (mysqld 8.0.18-debug) starting as process 10888
2019-07-08T15:19:43.465967Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2019-07-08T15:19:43.886116Z 0 [System] [MY-010931] [Server] d:\dbs\8.0\bin\mysqld: ready for connections. Version: '8.0.18-debug'  socket: ''  port: 3580  Source distribution BUILT: 2019-JUL-05.
2019-07-08T15:19:44.097745Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060
Assertion failed: bitmap_is_set(key_info->table->read_set, key_info->key_part->field->field_index), file D:\build\2019JUL05\mysql-8.0\sql\key.cc, line 593
abort() has been called15:19:58 UTC - mysqld got exception 0x80000003 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
Thread pointer: 0x1ff22283050
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...
7ff7d639bc85    mysqld.exe!my_sigabrt_handler()[my_thr_init.cc:372]
7ffd3b75c3e1    ucrtbased.dll!raise()
7ffd3b75e039    ucrtbased.dll!abort()
7ffd3b763c65    ucrtbased.dll!_get_wide_winmain_command_line()
7ffd3b7637d7    ucrtbased.dll!_get_wide_winmain_command_line()
7ffd3b761868    ucrtbased.dll!_get_wide_winmain_command_line()
7ffd3b7641cf    ucrtbased.dll!_wassert()
7ff7d46a11fa    mysqld.exe!key_rec_cmp()[key.cc:592]
7ff7d4306774    mysqld.exe!Key_rec_less::operator()()[partition_handler.h:371]
7ff7d4309e61    mysqld.exe!Priority_queue<unsigned char *,std::vector<unsigned char *,std::allocator<unsigned char *> >,Key_rec_less>::heapify()[priority_queue.h:133]
7ff7d430971a    mysqld.exe!Priority_queue<unsigned char *,std::vector<unsigned char *,std::allocator<unsigned char *> >,Key_rec_less>::build_heap()[priority_queue.h:201]
7ff7d4309138    mysqld.exe!Priority_queue<unsigned char *,std::vector<unsigned char *,std::allocator<unsigned char *> >,Key_rec_less>::assign()[priority_queue.h:188]
7ff7d430160e    mysqld.exe!Partition_helper::handle_ordered_index_scan()[partition_handler.cc:2633]
7ff7d4302f02    mysqld.exe!Partition_helper::common_index_read()[partition_handler.cc:1966]
7ff7d42fc895    mysqld.exe!Partition_helper::ph_read_range_first()[partition_handler.cc:2259]
7ff7d6a6f119    mysqld.exe!ha_innopart::read_range_first()[ha_innopart.h:485]
7ff7d40d104a    mysqld.exe!handler::ha_read_range_first()[handler.cc:7161]
7ff7d42c580a    mysqld.exe!QUICK_SKIP_SCAN_SELECT::get_next()[opt_range.cc:14865]
7ff7d46a5e16    mysqld.exe!IndexRangeScanIterator::Read()[records.cc:353]
7ff7d4aff72f    mysqld.exe!FilterIterator::Read()[composite_iterators.cc:77]
7ff7d4b00614    mysqld.exe!AggregateIterator::Read()[composite_iterators.cc:271]
7ff7d46f1835    mysqld.exe!SELECT_LEX_UNIT::ExecuteIteratorQuery()[sql_union.cc:1499]
7ff7d46f2214    mysqld.exe!SELECT_LEX_UNIT::execute()[sql_union.cc:1571]
7ff7d472c323    mysqld.exe!Sql_cmd_dml::execute_inner()[sql_select.cc:898]
7ff7d472bbe0    mysqld.exe!Sql_cmd_dml::execute()[sql_select.cc:703]
7ff7d43e07d1    mysqld.exe!mysql_execute_command()[sql_parse.cc:4437]
7ff7d43d9520    mysqld.exe!mysql_parse()[sql_parse.cc:5241]
7ff7d43e4149    mysqld.exe!dispatch_command()[sql_parse.cc:1760]
7ff7d43e1f79    mysqld.exe!do_command()[sql_parse.cc:1272]
7ff7d3f739da    mysqld.exe!handle_connection()[connection_handler_per_thread.cc:302]
7ff7d6d84685    mysqld.exe!pfs_spawn_thread()[pfs.cc:2856]
7ff7d639a7f7    mysqld.exe!win_thread_start()[my_thread.cc:52]
7ffd3b76542c    ucrtbased.dll!_register_onexit_function()
7ffd96a27bd4    KERNEL32.DLL!BaseThreadInitThunk()
7ffd9864ce71    ntdll.dll!RtlUserThreadStart()

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (1ff224678d0): select count(*) from STRG_PARTRANGE_TBL_001 where c_id<=600
Connection ID (thread ID): 8
Status: NOT_KILLED