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

Inconsistent handling of RFC 3339 times for datetimes, especially for Partition Pruning

    XMLWordPrintable

    Details

      Description

      RFC 3339 (RFC3339 time) times are not handled consistently, and specifically when it comes to partition pruning.

      For reference, more on RFC 3339 can be found here:
      https://www.ietf.org/rfc/rfc3339.txt

      A normal datetime looks like:

      2016-12-12 11:10:09

      A RFC 3339 time might look like:

      2016-12-14T11:09:09-05:00

      The main problem I see right now is with partition pruning, but perhaps it applies to other areas.

      Test case:

      DROP TABLE IF EXISTS t1;
      CREATE TABLE `t1` (
      `id` int,
      `GMTDataTime` datetime NOT NULL,
      PRIMARY KEY (`GMTDataTime`,`id`),
      KEY `id` (`id`)
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1 
      PARTITION BY LIST ( TO_DAYS(GMTDataTime))
      (PARTITION p1 VALUES IN (736673),
      PARTITION p2 VALUES IN (736674),
      PARTITION p3 VALUES IN (736675));
      INSERT INTO `t1` VALUES (1,'2016-12-10 10:10:10');
      INSERT INTO `t1` VALUES (2,'2016-12-11 11:11:11');
      INSERT INTO `t1` VALUES (3,'2016-12-12 12:12:12');
      SELECT * FROM `t1`;
      EXPLAIN PARTITIONS SELECT * FROM `t1` WHERE `GMTDataTime` BETWEEN '2016-12-10 10:10:10' AND '2016-12-11 11:11:11';
      EXPLAIN PARTITIONS SELECT * FROM `t1` WHERE `GMTDataTime` BETWEEN '2016-12-10T10:10:10-05:00' AND '2016-12-11T11:11:11-05:00';
      

      Note how the latter, using RFC 3339 time, does not use partition pruning (it should have excluded "p3" in "partitions" column):

      mysql> EXPLAIN PARTITIONS SELECT * FROM `t1` WHERE `GMTDataTime` BETWEEN '2016-12-10 10:10:10' AND '2016-12-11 11:11:11';
      +------+-------------+-------+------------+-------+---------------+---------+---------+------+------+--------------------------+
      | id   | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
      +------+-------------+-------+------------+-------+---------------+---------+---------+------+------+--------------------------+
      |    1 | SIMPLE      | t1    | p1,p2      | index | PRIMARY       | PRIMARY | 9       | NULL |    2 | Using where; Using index |
      +------+-------------+-------+------------+-------+---------------+---------+---------+------+------+--------------------------+
      1 row in set (0.00 sec)
       
      mysql> EXPLAIN PARTITIONS SELECT * FROM `t1` WHERE `GMTDataTime` BETWEEN '2016-12-10T10:10:10-05:00' AND '2016-12-11T11:11:11-05:00';
      +------+-------------+-------+------------+-------+---------------+---------+---------+------+------+--------------------------+
      | id   | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
      +------+-------------+-------+------------+-------+---------------+---------+---------+------+------+--------------------------+
      |    1 | SIMPLE      | t1    | p1,p2,p3   | index | PRIMARY       | PRIMARY | 9       | NULL |    3 | Using where; Using index |
      +------+-------------+-------+------------+-------+---------------+---------+---------+------+------+--------------------------+
      1 row in set (0.00 sec)
      

      There are a couple of hacky work-arounds where you essentially re-convert the RFC 3339 time to a 'typical' datetime, which back allows partition pruning to take place:

      mysql> EXPLAIN PARTITIONS SELECT * FROM `t1` WHERE `GMTDataTime` BETWEEN DATE_FORMAT('2016-12-10T10:10:10-05:00','%Y-%m-%d %H:%i:%s') AND DATE_FORMAT('2016-12-11T11:11:11-05:00','%Y-%m-%d %H:%i:%s');
      +------+-------------+-------+------------+-------+---------------+---------+---------+------+------+--------------------------+
      | id   | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
      +------+-------------+-------+------------+-------+---------------+---------+---------+------+------+--------------------------+
      |    1 | SIMPLE      | t1    | p1,p2      | index | PRIMARY       | PRIMARY | 9       | NULL |    2 | Using where; Using index |
      +------+-------------+-------+------------+-------+---------------+---------+---------+------+------+--------------------------+
      1 row in set, 6 warnings (0.00 sec)
       
      mysql> EXPLAIN PARTITIONS SELECT * FROM `t1` WHERE `GMTDataTime` BETWEEN FROM_UNIXTIME(UNIX_TIMESTAMP('2016-12-10T10:10:10-05:00')) AND FROM_UNIXTIME(UNIX_TIMESTAMP('2016-12-11T11:11:11-05:00'));
      +------+-------------+-------+------------+-------+---------------+---------+---------+------+------+--------------------------+
      | id   | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
      +------+-------------+-------+------------+-------+---------------+---------+---------+------+------+--------------------------+
      |    1 | SIMPLE      | t1    | p1,p2      | index | PRIMARY       | PRIMARY | 9       | NULL |    2 | Using where; Using index |
      +------+-------------+-------+------------+-------+---------------+---------+---------+------+------+--------------------------+
      1 row in set, 6 warnings (0.00 sec)
      

      But this is quite hacky, not always possible for queries on-the-fly, and still returns [many] warnings.

        Attachments

          Activity

            People

            Assignee:
            serg Sergei Golubchik
            Reporter:
            ccalender Chris Calender
            Votes:
            1 Vote for this issue
            Watchers:
            5 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.