Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.5, 10.6, 10.11, 11.1(EOL), 11.2, 11.4, 10.4(EOL), 10.7(EOL), 10.8(EOL), 10.9(EOL), 10.10(EOL), 11.0(EOL), 11.3(EOL), 11.5(EOL)
-
None
Description
SET time_zone='Europe/Moscow'; |
CREATE OR REPLACE TABLE t1 (a TIMESTAMP); |
SET timestamp=1288477526; -- this is summer time |
INSERT INTO t1 VALUES (NOW()); |
SET timestamp=1288477526+3600; -- this is winter time |
INSERT INTO t1 VALUES (NOW()); |
SELECT a, UNIX_TIMESTAMP(a) FROM t1; |
DELIMITER $$
|
BEGIN NOT ATOMIC |
DECLARE ts0 TIMESTAMP DEFAULT (SELECT MIN(a) FROM t1); |
DECLARE ts1 TIMESTAMP DEFAULT (SELECT MAX(a) FROM t1); |
SELECT a, UNIX_TIMESTAMP(a) FROM t1 WHERE a=ts0; |
SELECT a, UNIX_TIMESTAMP(a) FROM t1 WHERE a=ts1; |
EXECUTE IMMEDIATE 'SELECT a, UNIX_TIMESTAMP(a) FROM t1 WHERE a=?' USING ts0; |
EXECUTE IMMEDIATE 'SELECT a, UNIX_TIMESTAMP(a) FROM t1 WHERE a=?' USING ts1; |
END; |
$$
|
DELIMITER ;
|
+---------------------+-------------------+
|
| a | UNIX_TIMESTAMP(a) |
|
+---------------------+-------------------+
|
| 2010-10-31 02:25:26 | 1288477526 | <-- correct, this is the minumum value
|
+---------------------+-------------------+
|
1 row in set (0.001 sec)
|
|
+---------------------+-------------------+
|
| a | UNIX_TIMESTAMP(a) |
|
+---------------------+-------------------+
|
| 2010-10-31 02:25:26 | 1288481126 | <-- correct, this is the maximum value
|
+---------------------+-------------------+
|
1 row in set (0.001 sec)
|
|
+---------------------+-------------------+
|
| a | UNIX_TIMESTAMP(a) |
|
+---------------------+-------------------+
|
| 2010-10-31 02:25:26 | 1288477526 | <-- correct, this is the mininum value
|
+---------------------+-------------------+
|
1 row in set (0.001 sec)
|
|
+---------------------+-------------------+
|
| a | UNIX_TIMESTAMP(a) |
|
+---------------------+-------------------+
|
| 2010-10-31 02:25:26 | 1288477526 | <-- wrong, this is NOT the maximum value
|
+---------------------+-------------------+
|
1 row in set (0.001 sec)
|
The problem happens because the TIMESTAMP data type stores actually its DATETIME representation into Item_param when binding.
Attachments
Issue Links
- relates to
-
MDEV-13995 MAX(timestamp) returns a wrong result near DST change
- Closed
-
MDEV-14271 Dynamic SQL: TIMESTAMP parameter value is not preserved on timezone change
- Open
-
MDEV-15654 ADDTIME creates invalid timestamp value near DST changes
- Closed
-
MDEV-16422 DST and unix_timestamp(now())
- Confirmed
-
MDEV-22828 Bad results near DST change: UNIX_TIMESTAMP(timestamp_column)=<integer literal>
- Open
-
MDEV-32152 Wrong results near DST for `WHERE indexed_timestamp_column='2010-10-31 02:30:00'`
- Open