Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
5.5.33a
-
None
-
None
-
All OS
Description
MIN/MAX Optimization (Select tables optimized away) does not work for DateTime.
This is similar to bug #3855:
https://mariadb.atlassian.net/browse/MDEV-3855
However, that bug is specifically about inet_aton.
This one is regarding datetime values, and they are not optimized away, thus the query can take much longer than expected.
MariaDB:
mysql> EXPLAIN SELECT MIN(b) FROM t1 WHERE b <= '2013-11-06 23:59:59'; |
+------+-------------+-------+-------+---------------+-------+---------+------+------+--------------------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
+------+-------------+-------+-------+---------------+-------+---------+------+------+--------------------------+ |
| 1 | SIMPLE | t1 | range | idx_b | idx_b | 9 | NULL | 11 | Using where; Using index | |
+------+-------------+-------+-------+---------------+-------+---------+------+------+--------------------------+ |
MySQL:
mysql> EXPLAIN SELECT MIN(b) FROM t1 WHERE b <= '2013-11-06 23:59:59'; |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+ |
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away | |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+ |
The following illustrates this:
CREATE TABLE `t1` ( |
`a` int(11) NOT NULL AUTO_INCREMENT, |
`b` datetime DEFAULT NULL, |
PRIMARY KEY (`a`), |
KEY `idx_b` (`b`) |
) ENGINE=InnoDB;
|
|
|
INSERT INTO `t1` (b) VALUES ('2013-01-06 23:59:59'); |
INSERT INTO `t1` (b) VALUES ('2013-02-06 23:59:59'); |
INSERT INTO `t1` (b) VALUES ('2013-03-06 23:59:59'); |
INSERT INTO `t1` (b) VALUES ('2013-04-06 23:59:59'); |
INSERT INTO `t1` (b) VALUES ('2013-05-06 23:59:59'); |
INSERT INTO `t1` (b) VALUES ('2013-06-06 23:59:59'); |
INSERT INTO `t1` (b) VALUES ('2013-07-06 23:59:59'); |
INSERT INTO `t1` (b) VALUES ('2013-08-06 23:59:59'); |
INSERT INTO `t1` (b) VALUES ('2013-09-06 23:59:59'); |
INSERT INTO `t1` (b) VALUES ('2013-10-06 23:59:59'); |
INSERT INTO `t1` (b) VALUES ('2013-11-06 23:59:59'); |
INSERT INTO `t1` (b) VALUES ('2013-12-06 23:59:59'); |
|
|
EXPLAIN SELECT MIN(b) FROM t1 WHERE b <= '2013-11-06 23:59:59'; |
Attachments
Activity
Caused by this fix:
http://bazaar.launchpad.net/~maria-captains/maria/5.5/revision/2502.528.13
I was looking at the code of this optimization in opt_sum_query() and co., and it looks very odd. Indeed, a short experimentation shows that opt-sum optimization doesn't correctly take datatypes into account. I've created an example and filed it as MDEV-5283. MySQL is also affected.
It will require substantial amount of effort to fix the problem completely. I think, we should fix this MDEV by providing adequate handling for unambigous cases like the one in this MDEV.
Agree. I've also changed max_length to compare characters, not bytes.
To reproduce with MTR, SET NAMES utf8 should be added to the test.