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

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

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

          ccalender Chris Calender (Inactive) added a comment - Additionally, it looks like MariaDB supports ISO8601 (RFC 3339 is a profile extension of ISO8601) as seen here: https://github.com/MariaDB/server/blob/bb2c1a52c61706dde8c525a8887f2d364c0db1eb/sql-common/my_time.c#L269 and here: https://github.com/MariaDB/server/blob/bb2c1a52c61706dde8c525a8887f2d364c0db1eb/sql/item_timefunc.h#L1103

          Also reproducible with MySQL 5.5, but not 5.6/5.7.

          elenst Elena Stepanova added a comment - Also reproducible with MySQL 5.5, but not 5.6/5.7.

          MariaDB does not support "datetime with time zone" literals. If you modify your last query not to include a time zone:

          mysql> EXPLAIN PARTITIONS SELECT * FROM `t1` WHERE `GMTDataTime` BETWEEN '2016-12-10T10:10:10' AND '2016-12-11T11:11:11';
          

          then it will use partition pruning.

          serg Sergei Golubchik added a comment - MariaDB does not support "datetime with time zone" literals. If you modify your last query not to include a time zone: mysql> EXPLAIN PARTITIONS SELECT * FROM `t1` WHERE `GMTDataTime` BETWEEN '2016-12-10T10:10:10' AND '2016-12-11T11:11:11' ; then it will use partition pruning.
          mariadb-rh@p4u.ch Ralf Hauser added a comment -

          We have a similar problem with inserts that are rejected into a syslog db as we migrated from mysql:

          db error (1292): Incorrect datetime value: '2017-09-08T11:25:42.692921+02:00' for column 'ReportedTime' at row 1

          would it be useful to be able to insert with the same sql statement as in mysql (kind of "backward compatibility")?

          mariadb-rh@p4u.ch Ralf Hauser added a comment - We have a similar problem with inserts that are rejected into a syslog db as we migrated from mysql: db error (1292): Incorrect datetime value: '2017-09-08T11:25:42.692921+02:00' for column 'ReportedTime' at row 1 would it be useful to be able to insert with the same sql statement as in mysql (kind of "backward compatibility")?

          People

            serg Sergei Golubchik
            ccalender Chris Calender (Inactive)
            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.