Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-5257

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

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • 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';

        Attachments

          Activity

            People

            • Assignee:
              psergey Sergei Petrunia
              Reporter:
              ccalender Chris Calender
            • Votes:
              1 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: