Bug #68832 Daylight Saving time and interval
Submitted: 2 Apr 2013 11:04 Modified: 26 Apr 2013 13:58
Reporter: Vincent Keravec Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Parser Severity:S2 (Serious)
Version:5.1.70 OS:Linux (RHEL6)
Assigned to: CPU Architecture:Any

[2 Apr 2013 11:04] Vincent Keravec
Description:
When using interval it seems that mysql is returning an incorrect date/time. When combine with a partition table this block the partition pruning algorithm.

How to repeat:
mysql> select '2013-03-31 03:10:00' - interval 1 hour;
+-----------------------------------------+
| '2013-03-31 03:10:00' - interval 1 hour |
+-----------------------------------------+
| 2013-03-31 02:10:00                     |
+-----------------------------------------+
1 row in set (0.00 sec)

This should have return 2013-03-31 01:10:00.

mysql> select '2013-03-31 03:10:00' - interval 2 hour;
+-----------------------------------------+
| '2013-03-31 03:10:00' - interval 2 hour |
+-----------------------------------------+
| 2013-03-31 01:10:00                     |
+-----------------------------------------+
1 row in set (0.00 sec)
This should have return 2013-03-31 00:10:00.

CREATE TABLE `timezonetest` (
  `dato` timestamp DEFAULT 0 NOT NULL,
  PRIMARY KEY (`dato`)
)  ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE (UNIX_TIMESTAMP(dato))
(PARTITION p2012 VALUES LESS THAN (UNIX_TIMESTAMP('2013-01-01 00:00:00')) ENGINE = InnoDB,
PARTITION p2013 VALUES LESS THAN (UNIX_TIMESTAMP('2014-01-01 00:00:00')) ENGINE = InnoDB)
;

insert into timezonetest (dato) values 
('2012-03-31 00:00:00'),('2012-03-31 00:30:00'),('2012-03-31 01:00:00'),('2012-03-31 01:30:00'),('2012-03-31 03:00:00'),('2012-03-31 03:30:00'),
('2013-03-31 00:00:00'),('2013-03-31 00:30:00'),('2013-03-31 01:00:00'),('2013-03-31 01:30:00'),('2013-03-31 03:00:00'),('2013-03-31 03:30:00');

mysql> explain partitions select * from timezonetest where dato >= '2013-03-31 03:10:00';                  
+----+-------------+--------------+------------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table        | partitions | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+--------------+------------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | timezonetest | p2013      | range | PRIMARY       | PRIMARY | 4       | NULL |    1 | Using where; Using index |
+----+-------------+--------------+------------+-------+---------------+---------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

mysql> explain partitions select * from timezonetest where dato >= '2013-03-31 03:10:00' - interval 1 hour;
+----+-------------+--------------+-------------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table        | partitions  | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+--------------+-------------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | timezonetest | p2012,p2013 | index | PRIMARY       | PRIMARY | 4       | NULL |   12 | Using where; Using index |
+----+-------------+--------------+-------------+-------+---------------+---------+---------+------+------+--------------------------+
1 row in set, 2 warnings (0.00 sec)

mysql> show warnings;
+---------+------+----------------------------------------------------------------------------+
| Level   | Code | Message                                                                    |
+---------+------+----------------------------------------------------------------------------+
| Warning | 1292 | Incorrect datetime value: '2013-03-31 02:10:00' for column 'dato' at row 1 |
| Warning | 1292 | Incorrect datetime value: '2013-03-31 02:10:00' for column 'dato' at row 1 |
+---------+------+----------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> explain partitions select * from timezonetest where dato >= '2013-03-31 03:10:00' - interval 2 hour;
+----+-------------+--------------+------------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table        | partitions | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+--------------+------------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | timezonetest | p2013      | range | PRIMARY       | PRIMARY | 4       | NULL |    3 | Using where; Using index |
+----+-------------+--------------+------------+-------+---------------+---------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

Suggested fix:
The interval function should return valid date based on the timezone.

mysql> select '2013-03-31 03:10:00' - interval 1 hour;
+-----------------------------------------+
| '2013-03-31 03:10:00' - interval 1 hour |
+-----------------------------------------+
| 2013-03-31 02:10:00                     |
+-----------------------------------------+
1 row in set (0.00 sec)

Should return 2013-03-31 01:10:00.

mysql> select '2013-03-31 03:10:00' - interval 2 hour;
+-----------------------------------------+
| '2013-03-31 03:10:00' - interval 2 hour |
+-----------------------------------------+
| 2013-03-31 01:10:00                     |
+-----------------------------------------+
1 row in set (0.00 sec)
Should return 2013-03-31 00:10:00.
[26 Apr 2013 13:58] Erlend Dahl
Thank you for the bug report. It seems to be a long-standing problem, since I can repeat it in latest 5.1.70:

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.1.70    |
+-----------+
1 row in set (0.00 sec)

mysql> select @@global.time_zone, @@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| Europe/Oslo        | Europe/Oslo         |
+--------------------+---------------------+
1 row in set (0.00 sec)

mysql> select '2013-03-31 03:10:00' - interval 1 hour; 
+-----------------------------------------+
| '2013-03-31 03:10:00' - interval 1 hour |
+-----------------------------------------+
| 2013-03-31 02:10:00                     |
+-----------------------------------------+
1 row in set (0.00 sec)

mysql> -- Expected 2013-03-31 01:10:00.
mysql> select '2013-03-31 03:10:00' - interval 2 hour; 
+-----------------------------------------+
| '2013-03-31 03:10:00' - interval 2 hour |
+-----------------------------------------+
| 2013-03-31 01:10:00                     |
+-----------------------------------------+
1 row in set (0.00 sec)

mysql> -- Expected 2013-03-31 00:10:00.
mysql> use test;
Database changed
mysql> drop table if exists `timezonetest`;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE `timezonetest` (
    ->   `dato` timestamp DEFAULT 0 NOT NULL,
    ->   PRIMARY KEY (`dato`)
    -> )  ENGINE=InnoDB DEFAULT CHARSET=utf8
    -> PARTITION BY RANGE (UNIX_TIMESTAMP(dato))
    -> (PARTITION p2012 VALUES LESS THAN (UNIX_TIMESTAMP('2013-01-01 00:00:00')) ENGINE = InnoDB,
    -> PARTITION p2013 VALUES LESS THAN (UNIX_TIMESTAMP('2014-01-01 00:00:00')) ENGINE = InnoDB)
    -> ;
Query OK, 0 rows affected, 4 warnings (0.01 sec)

mysql> 
mysql> insert into timezonetest (dato) values 
    -> ('2012-03-31 00:00:00'),('2012-03-31 00:30:00'),('2012-03-31 01:00:00'),('2012-03-31 01:30:00'),('2012-03-31 03:00:00'),('2012-03-31 03:30:00'),
    -> ('2013-03-31 00:00:00'),('2013-03-31 00:30:00'),('2013-03-31 01:00:00'),('2013-03-31 01:30:00'),('2013-03-31 03:00:00'),('2013-03-31 03:30:00');
Query OK, 12 rows affected (0.00 sec)
Records: 12  Duplicates: 0  Warnings: 0

mysql> explain partitions select * from timezonetest where dato >= '2013-03-31 03:10:00';                  
+----+-------------+--------------+-------------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table        | partitions  | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+--------------+-------------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | timezonetest | p2012,p2013 | range | PRIMARY       | PRIMARY | 4       | NULL |    3 | Using where; Using index |
+----+-------------+--------------+-------------+-------+---------------+---------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

mysql> explain partitions select * from timezonetest where dato >= '2013-03-31 03:10:00' - interval 1 hour;
+----+-------------+--------------+-------------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table        | partitions  | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+--------------+-------------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | timezonetest | p2012,p2013 | index | PRIMARY       | PRIMARY | 4       | NULL |   12 | Using where; Using index |
+----+-------------+--------------+-------------+-------+---------------+---------+---------+------+------+--------------------------+
1 row in set, 2 warnings (0.00 sec)

mysql> show warnings;
+---------+------+----------------------------------------------------------------------------+
| Level   | Code | Message                                                                    |
+---------+------+----------------------------------------------------------------------------+
| Warning | 1292 | Incorrect datetime value: '2013-03-31 02:10:00' for column 'dato' at row 1 |
| Warning | 1292 | Incorrect datetime value: '2013-03-31 02:10:00' for column 'dato' at row 1 |
+---------+------+----------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> explain partitions select * from timezonetest where dato >= '2013-03-31 03:10:00' - interval 2 hour;
+----+-------------+--------------+-------------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table        | partitions  | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+--------------+-------------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | timezonetest | p2012,p2013 | range | PRIMARY       | PRIMARY | 4       | NULL |    5 | Using where; Using index |
+----+-------------+--------------+-------------+-------+---------------+---------+---------+------+------+--------------------------+
1 row in set (0.00 sec)