Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.0.12, 10.3(EOL), 10.4(EOL), 10.5(EOL), 10.6, 10.7(EOL), 10.8(EOL), 10.9(EOL), 10.10(EOL)
-
None
Description
Notes:
Encountered while running tests for MDEV-6454, but unrelated.
The data dump is attached.
Table structure:
CREATE TABLE `table20000_innodb_int_autoinc` ( |
`col_varchar_64` varchar(64) DEFAULT NULL, |
`col_varchar_64_key` varchar(64) DEFAULT NULL, |
`col_bigint_key` bigint(20) DEFAULT NULL, |
`col_varchar_10` varchar(10) DEFAULT NULL, |
`col_smallint_key` smallint(6) DEFAULT NULL, |
`col_smallint` smallint(6) DEFAULT NULL, |
`col_varchar_10_key` varchar(10) DEFAULT NULL, |
`col_bigint` bigint(20) DEFAULT NULL, |
`pk` int(11) NOT NULL AUTO_INCREMENT, |
PRIMARY KEY (`pk`), |
KEY `col_varchar_64_key` (`col_varchar_64_key`), |
KEY `col_bigint_key` (`col_bigint_key`), |
KEY `col_smallint_key` (`col_smallint_key`), |
KEY `col_varchar_10_key` (`col_varchar_10_key`), |
KEY `key1` (`col_smallint`,`col_bigint_key`) |
) ENGINE=InnoDB AUTO_INCREMENT=20001 DEFAULT CHARSET=latin1; |
Query:
SELECT col_smallint_key, MAX(col_bigint_key) FROM table20000_innodb_int_autoinc |
WHERE ( pk = 6 OR col_smallint_key = 255 ) AND col_varchar_64_key <> 'y' OR col_smallint_key IS NULL GROUP BY 1; |
Execution time and plans:
"Good" plan and time:
SELECT col_smallint_key, MAX(col_bigint_key) FROM table20000_innodb_int_autoinc |
WHERE ( pk = 6 OR col_smallint_key = 255 ) AND col_varchar_64_key <> 'y' OR col_smallint_key IS NULL GROUP BY 1; |
+------------------+---------------------+ |
| col_smallint_key | MAX(col_bigint_key) | |
+------------------+---------------------+ |
| NULL | 9222527611924643840 | |
| 0 | 28088 |
|
| 255 | 6826049660210118656 |
|
+------------------+---------------------+ |
3 rows in set (0.16 sec) |
+------+-------------+-------------------------------+-------------+---------------------------------------------+-------------------------------------------+---------+------+------+----------+------------------------------------------------------------------------------------------------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | |
+------+-------------+-------------------------------+-------------+---------------------------------------------+-------------------------------------------+---------+------+------+----------+------------------------------------------------------------------------------------------------------+ |
| 1 | SIMPLE | table20000_innodb_int_autoinc | index_merge | PRIMARY,col_varchar_64_key,col_smallint_key | PRIMARY,col_smallint_key,col_smallint_key | 4,3,3 | NULL | 3403 | 100.00 | Using union(PRIMARY,col_smallint_key,col_smallint_key); Using where; Using temporary; Using filesort | |
+------+-------------+-------------------------------+-------------+---------------------------------------------+-------------------------------------------+---------+------+------+----------+------------------------------------------------------------------------------------------------------+ |
|
|
| Note | 1003 | select `test`.`table20000_innodb_int_autoinc`.`col_smallint_key` AS `col_smallint_key`,max(`test`.`table20000_innodb_int_autoinc`.`col_bigint_key`) AS `MAX(col_bigint_key)` from `test`.`table20000_innodb_int_autoinc` where ((((`test`.`table20000_innodb_int_autoinc`.`pk` = 6) or (`test`.`table20000_innodb_int_autoinc`.`col_smallint_key` = 255)) and (`test`.`table20000_innodb_int_autoinc`.`col_varchar_64_key` <> 'y')) or isnull(`test`.`table20000_innodb_int_autoinc`.`col_smallint_key`)) group by 1 | |
Bad plan and time:
SET use_stat_tables = PREFERABLY; |
ANALYZE TABLE table20000_innodb_int_autoinc; |
+------------------------------------+---------+----------+-----------------------------------------+ |
| Table | Op | Msg_type | Msg_text | |
+------------------------------------+---------+----------+-----------------------------------------+ |
| test.table20000_innodb_int_autoinc | analyze | status | Engine-independent statistics collected | |
| test.table20000_innodb_int_autoinc | analyze | status | OK |
|
+------------------------------------+---------+----------+-----------------------------------------+ |
SELECT col_smallint_key, MAX(col_bigint_key) FROM table20000_innodb_int_autoinc WHERE ( pk = 6 OR col_smallint_key = 255 ) AND col_varchar_64_key <> 'y' OR col_smallint_key IS NULL GROUP BY 1; |
+------------------+---------------------+ |
| col_smallint_key | MAX(col_bigint_key) | |
+------------------+---------------------+ |
| NULL | 9222527611924643840 | |
| 0 | 28088 |
|
| 255 | 6826049660210118656 |
|
+------------------+---------------------+ |
3 rows in set (0.65 sec) |
EXPLAIN EXTENDED SELECT col_smallint_key, MAX(col_bigint_key) FROM table20000_innodb_int_autoinc WHERE ( pk = 6 OR col_smallint_key = 255 ) AND col_varchar_64_key <> 'y' OR col_smallint_key IS NULL GROUP BY 1; |
+------+-------------+-------------------------------+-------+---------------------------------------------+------------------+---------+------+-------+----------+-------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | |
+------+-------------+-------------------------------+-------+---------------------------------------------+------------------+---------+------+-------+----------+-------------+ |
| 1 | SIMPLE | table20000_innodb_int_autoinc | index | PRIMARY,col_varchar_64_key,col_smallint_key | col_smallint_key | 3 | NULL | 20000 | 100.00 | Using where | |
+------+-------------+-------------------------------+-------+---------------------------------------------+------------------+---------+------+-------+----------+-------------+ |
1 row in set, 1 warning (0.00 sec) |
|
|
| Note | 1003 | select `test`.`table20000_innodb_int_autoinc`.`col_smallint_key` AS `col_smallint_key`,max(`test`.`table20000_innodb_int_autoinc`.`col_bigint_key`) AS `MAX(col_bigint_key)` from `test`.`table20000_innodb_int_autoinc` where ((((`test`.`table20000_innodb_int_autoinc`.`pk` = 6) or (`test`.`table20000_innodb_int_autoinc`.`col_smallint_key` = 255)) and (`test`.`table20000_innodb_int_autoinc`.`col_varchar_64_key` <> 'y')) or isnull(`test`.`table20000_innodb_int_autoinc`.`col_smallint_key`)) group by 1 | |