Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
N/A
-
None
Description
Observed on 10.0-mdev6384 development tree (10.0 with the patch for MDEV-6384 applied manually)
Query:
SELECT `pk` , MAX( `col_bigint_key` ) FROM `table10000_innodb_int_autoinc` WHERE ( `col_smallint_key` IN ( 255 , 255 ) OR ( `pk` = 144 ) ) AND ( `col_bigint_key` IN ( 3 , 155 ) AND `col_bigint_key` IN ( 255 , 8 , 0 ) ) AND ( `col_bigint_key` IS NULL OR ( `col_bigint_key` <> 1 ) ) OR ( `col_smallint_key` IS NULL AND `pk` BETWEEN 121 AND 4 + 255 ) GROUP BY 1; |
Table:
CREATE TABLE `table10000_innodb_int_autoinc` ( |
`col_smallint_key` smallint(6) DEFAULT NULL, |
`col_bigint_key` bigint(20) DEFAULT NULL, |
`col_varchar_64_key` varchar(64) DEFAULT NULL, |
`col_varchar_10` varchar(10) DEFAULT NULL, |
`col_varchar_10_key` varchar(10) DEFAULT NULL, |
`col_varchar_64` varchar(64) DEFAULT NULL, |
`col_bigint` bigint(20) DEFAULT NULL, |
`pk` int(11) NOT NULL AUTO_INCREMENT, |
`col_smallint` smallint(6) DEFAULT NULL, |
PRIMARY KEY (`pk`), |
KEY `col_smallint_key` (`col_smallint_key`), |
KEY `col_bigint_key` (`col_bigint_key`), |
KEY `col_varchar_64_key` (`col_varchar_64_key`), |
KEY `col_varchar_10_key` (`col_varchar_10_key`) |
) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=latin1 |
(data dump is attached)
10.0
+------+-------------+-------------------------------+-------+-----------------------------------------+------------------+---------+------+------+----------+---------------------------------------------------------------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | |
+------+-------------+-------------------------------+-------+-----------------------------------------+------------------+---------+------+------+----------+---------------------------------------------------------------------+ |
| 1 | SIMPLE | table10000_innodb_int_autoinc | range | PRIMARY,col_smallint_key,col_bigint_key | col_smallint_key | 7 | NULL | 21 | 100.00 | Using index condition; Using where; Using temporary; Using filesort | |
+------+-------------+-------------------------------+-------+-----------------------------------------+------------------+---------+------+------+----------+---------------------------------------------------------------------+ |
Execution:
21 rows in set (0.00 sec)
+----------------------------+-------+
|
| Variable_name | Value |
|
+----------------------------+-------+
|
| Handler_commit | 1 |
|
| Handler_delete | 0 |
|
| Handler_discover | 0 |
|
| Handler_external_lock | 0 |
|
| Handler_icp_attempts | 21 |
|
| Handler_icp_match | 21 |
|
| Handler_mrr_init | 0 |
|
| Handler_mrr_key_refills | 0 |
|
| Handler_mrr_rowid_refills | 0 |
|
| Handler_prepare | 0 |
|
| Handler_read_first | 0 |
|
| Handler_read_key | 22 |
|
| Handler_read_last | 0 |
|
| Handler_read_next | 21 |
|
| Handler_read_prev | 0 |
|
| Handler_read_rnd | 21 |
|
| Handler_read_rnd_deleted | 0 |
|
| Handler_read_rnd_next | 22 |
|
| Handler_rollback | 0 |
|
| Handler_savepoint | 0 |
|
| Handler_savepoint_rollback | 0 |
|
| Handler_tmp_update | 0 |
|
| Handler_tmp_write | 21 |
|
| Handler_update | 0 |
|
| Handler_write | 0 |
|
+----------------------------+-------+
|
10.0-mdev6384
-----------
+------+-------------+-------------------------------+-------+-----------------------------------------+---------+---------+------+-------+----------+-------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | |
+------+-------------+-------------------------------+-------+-----------------------------------------+---------+---------+------+-------+----------+-------------+ |
| 1 | SIMPLE | table10000_innodb_int_autoinc | index | PRIMARY,col_smallint_key,col_bigint_key | PRIMARY | 4 | NULL | 10000 | 0.21 | Using where | |
+------+-------------+-------------------------------+-------+-----------------------------------------+---------+---------+------+-------+----------+-------------+ |
1 row in set, 1 warning (0.00 sec) |
Execution:
21 rows in set (0.06 sec)
+----------------------------+-------+
|
| Variable_name | Value |
|
+----------------------------+-------+
|
| Handler_commit | 1 |
|
| Handler_delete | 0 |
|
| Handler_discover | 0 |
|
| Handler_external_lock | 0 |
|
| Handler_icp_attempts | 0 |
|
| Handler_icp_match | 0 |
|
| Handler_mrr_init | 0 |
|
| Handler_mrr_key_refills | 0 |
|
| Handler_mrr_rowid_refills | 0 |
|
| Handler_prepare | 0 |
|
| Handler_read_first | 1 |
|
| Handler_read_key | 0 |
|
| Handler_read_last | 0 |
|
| Handler_read_next | 10000 |
|
| Handler_read_prev | 0 |
|
| Handler_read_rnd | 0 |
|
| Handler_read_rnd_deleted | 0 |
|
| Handler_read_rnd_next | 0 |
|
| Handler_rollback | 0 |
|
| Handler_savepoint | 0 |
|
| Handler_savepoint_rollback | 0 |
|
| Handler_tmp_update | 0 |
|
| Handler_tmp_write | 0 |
|
| Handler_update | 0 |
|
| Handler_write | 0 |
|
+----------------------------+-------+
|
The effect seems to be stable. Persistent statistics doesn't help.
