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

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Fixed
    • 5.5.33a
    • 5.5.34
    • 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

          Ascending order - Click to sort in descending order
          ccalender Chris Calender (Inactive) created issue -
          elenst Elena Stepanova made changes -
          Field Original Value New Value
          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';
          		 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:

          {code:sql}
          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 |
          +------+-------------+-------+-------+---------------+-------+---------+------+------+--------------------------+
          {code}

          MySQL:

          {code:sql}
          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 |
          +----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
          {code}

          The following illustrates this:

          {code:sql}
          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';
          {code}
          serg Sergei Golubchik made changes -
          Assignee Sergei Petrunia [ psergey ]
          serg Sergei Golubchik made changes -
          Fix Version/s 5.5.34 [ 13700 ]
          serg Sergei Golubchik made changes -
          Priority Major [ 3 ] Critical [ 2 ]
          psergei Sergei Petrunia made changes -
          Resolution Fixed [ 1 ]
          Status Open [ 1 ] Closed [ 6 ]
          serg Sergei Golubchik made changes -
          Workflow defaullt [ 29630 ] MariaDB v2 [ 44238 ]
          ratzpo Rasmus Johansson (Inactive) made changes -
          Workflow MariaDB v2 [ 44238 ] MariaDB v3 [ 63533 ]
          serg Sergei Golubchik made changes -
          Workflow MariaDB v3 [ 63533 ] MariaDB v4 [ 147207 ]

          People

            psergei Sergei Petrunia
            ccalender Chris Calender (Inactive)
            Votes:
            1 Vote for this issue
            Watchers:
            6 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

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