Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Critical
-
Resolution: Fixed
-
Affects Version/s: 5.5.33a
-
Fix Version/s: 5.5.34
-
Component/s: None
-
Labels:None
-
Environment: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';
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Well, I think you might need to test it again.
I just downloaded another 5.5.33a (Windows 64-bit), re-installed, and copy/pasted the above test case, and I see the issue immediately.
mysql> CREATE TABLE `t1` (
-> `a` int(11) NOT NULL AUTO_INCREMENT,
-> `b` datetime DEFAULT NULL,
-> PRIMARY KEY (`a`),
-> KEY `idx_b` (`b`)
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.03 sec)
mysql> INSERT INTO `t1` (b) VALUES ('2013-01-06 23:59:59');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO `t1` (b) VALUES ('2013-02-06 23:59:59');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO `t1` (b) VALUES ('2013-03-06 23:59:59');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO `t1` (b) VALUES ('2013-04-06 23:59:59');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO `t1` (b) VALUES ('2013-05-06 23:59:59');
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO `t1` (b) VALUES ('2013-06-06 23:59:59');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO `t1` (b) VALUES ('2013-07-06 23:59:59');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO `t1` (b) VALUES ('2013-08-06 23:59:59');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO `t1` (b) VALUES ('2013-09-06 23:59:59');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO `t1` (b) VALUES ('2013-10-06 23:59:59');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO `t1` (b) VALUES ('2013-11-06 23:59:59');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO `t1` (b) VALUES ('2013-12-06 23:59:59');
Query OK, 1 row affected (0.00 sec)
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 |
------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select version();
-----------------
| version() |
-----------------
| 5.5.33a-MariaDB |
-----------------
1 row in set (0.00 sec)
mysql>
I can even see the issue with NO data:
mysql> CREATE TABLE `t1` (
-> `a` int(11) NOT NULL AUTO_INCREMENT,
-> `b` datetime DEFAULT NULL,
-> PRIMARY KEY (`a`),
-> KEY `idx_b` (`b`)
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.06 sec)
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 | index | idx_b | idx_b | 9 | NULL | 1 | Using where; Using index |
------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
mysql> select version();
-----------------
| version() |
-----------------
| 5.5.33a-MariaDB |
-----------------
1 row in set (0.00 sec)
Ok, some more info 
When I use mariadb compiled from source I don't see this bug (in mtr). When I used package from mariadb.org I can see it.
For me, it's reproducible all right on a source build as well, just not via MTR. If I run the provided test from MySQL client on a server with all defaults, it's reproducible. Same test via MTR gives "optimized away". MTR sets lots of non-default parameters, apparently they change the picture.
To reproduce with MTR, SET NAMES utf8 should be added to the test.
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.
I think max_length check should be limited to string columns.
Agree. I've also changed max_length to compare characters, not bytes.
I couldn't reproduce it with 5.5.33a and latest bzr version.