[MDEV-5257] MIN/MAX Optimization (Select tables optimized away) does not work for DateTime - Jira

MIN/MAX Optimization (Select tables optimized away) does not work for DateTime.

This is similar to bug #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';

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server