[MDEV-5257] MIN/MAX Optimization (Select tables optimized away) does not work for DateTime Created: 2013-11-07  Updated: 2013-11-13  Resolved: 2013-11-13

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 5.5.33a
Fix Version/s: 5.5.34

Type: Bug Priority: Critical
Reporter: Chris Calender (Inactive) Assignee: Sergei Petrunia
Resolution: Fixed Votes: 1
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';



 Comments   
Comment by Patryk Pomykalski [ 2013-11-08 ]

I couldn't reproduce it with 5.5.33a and latest bzr version.

Comment by Chris Calender (Inactive) [ 2013-11-08 ]

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)

Comment by Patryk Pomykalski [ 2013-11-08 ]

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.

Comment by Elena Stepanova [ 2013-11-08 ]

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.

Comment by Elena Stepanova [ 2013-11-08 ]

To reproduce with MTR, SET NAMES utf8 should be added to the test.

Comment by Patryk Pomykalski [ 2013-11-08 ]

Caused by this fix:
http://bazaar.launchpad.net/~maria-captains/maria/5.5/revision/2502.528.13

Comment by Sergei Petrunia [ 2013-11-12 ]

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.

Comment by Patryk Pomykalski [ 2013-11-12 ]

I think max_length check should be limited to string columns.

Comment by Sergei Petrunia [ 2013-11-12 ]

Agree. I've also changed max_length to compare characters, not bytes.

Generated at Thu Feb 08 07:02:53 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.