Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.5, 10.6, 10.11, 11.1(EOL), 11.2, 10.4(EOL), 10.7(EOL), 10.8(EOL), 10.9(EOL), 10.10(EOL), 11.0(EOL), 11.3(EOL)
-
None
Description
Wrong results near DST for `WHERE indexed_timestamp_column='2010-10-31 02:30:00'`
I create and populate a table with an indexed TIMESTAMP column, the insert values near DST change (one hour fall back):
SET time_zone='Europe/Moscow'; |
CREATE OR REPLACE TABLE t1 (a TIMESTAMP NULL, KEY(a)); |
|
# Add special values near DST change (one hour fall back) |
SET @first_second_after_dst_fall_back=1288479600; |
SET timestamp=@first_second_after_dst_fall_back-1800; |
INSERT INTO t1 VALUES (NOW()); |
SET timestamp=@first_second_after_dst_fall_back+1800; |
INSERT INTO t1 VALUES (NOW()); |
|
# Add some more data |
INSERT INTO t1 VALUES ('2001-01-01 10:20:30'),('2001-01-01 10:20:31'); |
INSERT INTO t1 VALUES ('2001-01-01 10:20:32'),('2001-01-01 10:20:33'); |
INSERT INTO t1 VALUES ('2001-01-01 10:20:34'),('2001-01-01 10:20:35'); |
INSERT INTO t1 VALUES ('2001-01-01 10:20:36'),('2001-01-01 10:20:37'); |
INSERT INTO t1 VALUES ('2001-01-01 10:20:38'),('2001-01-01 10:20:39'); |
Now let's check the values near the DST change
SELECT UNIX_TIMESTAMP(a), a FROM t1 WHERE a>DATE'2010-01-01'; |
+-------------------+---------------------+
|
| UNIX_TIMESTAMP(a) | a |
|
+-------------------+---------------------+
|
| 1288477800 | 2010-10-31 02:30:00 |
|
| 1288481400 | 2010-10-31 02:30:00 |
|
+-------------------+---------------------+
|
Notice, there are two different UNIX_TIMESTAMP values with equal "YYYY-MM-DD hh:mm:ss" representation. So far so good.
Now let's query these values:
SELECT UNIX_TIMESTAMP(a), a FROM t1 WHERE a='2010-10-31 02:30:00'; |
+-------------------+---------------------+
|
| UNIX_TIMESTAMP(a) | a |
|
+-------------------+---------------------+
|
| 1288477800 | 2010-10-31 02:30:00 |
|
+-------------------+---------------------+
|
Opps. Only one value was found. Looks wrong.
Now let's query these values without the index:
SELECT UNIX_TIMESTAMP(a), a FROM t1 IGNORE KEY(a) WHERE a='2010-10-31 02:30:00'; |
+-------------------+---------------------+
|
| UNIX_TIMESTAMP(a) | a |
|
+-------------------+---------------------+
|
| 1288477800 | 2010-10-31 02:30:00 |
|
| 1288481400 | 2010-10-31 02:30:00 |
|
+-------------------+---------------------+
|
Now it looks good, both values where found.
Attachments
Issue Links
- relates to
-
MDEV-16422 DST and unix_timestamp(now())
- Confirmed
-
MDEV-16423 DST and timestamp comparison
- Confirmed
-
MDEV-19121 optimizer fails to optimize query on timestamp column if where condition falls in the DST hour that do not exist
- Confirmed
-
MDEV-22828 Bad results near DST change: UNIX_TIMESTAMP(timestamp_column)=<integer literal>
- Open
-
MDEV-32148 Inefficient WHERE timestamp_column=datetime_const_expr
- Closed
-
MDEV-34028 Bad results near DST change with a dynamic SQL TIMESTAMP parameter
- Open