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
          View 4 older comments
          elenst Elena Stepanova added a comment -

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

          elenst Elena Stepanova added a comment - To reproduce with MTR, SET NAMES utf8 should be added to the test.
          pomyk Patryk Pomykalski added a comment -

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

          pomyk Patryk Pomykalski added a comment - Caused by this fix: http://bazaar.launchpad.net/~maria-captains/maria/5.5/revision/2502.528.13
          psergei Sergei Petrunia added a comment -

          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.

          psergei Sergei Petrunia added a comment - 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.
          pomyk Patryk Pomykalski added a comment -

          I think max_length check should be limited to string columns.

          pomyk Patryk Pomykalski added a comment - I think max_length check should be limited to string columns.
          psergei Sergei Petrunia added a comment -

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

          psergei Sergei Petrunia added a comment - Agree. I've also changed max_length to compare characters, not bytes.

          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.