Details
Description
Zero datetime reinterprets as '1970-01-01 00:00:00' on field_datetime=field_timestamp
SET sql_mode=''; |
SET time_zone='+00:00'; |
CREATE OR REPLACE TABLE t1 (a TIMESTAMP); |
INSERT INTO t1 VALUES ('0000-00-00 00:00:00'); |
SELECT * FROM t1; |
+---------------------+
|
| a |
|
+---------------------+
|
| 0000-00-00 00:00:00 |
|
+---------------------+
|
CREATE OR REPLACE TABLE t2 (a datetime); |
INSERT INTO t2 SELECT a FROM t1; |
SELECT * FROM t2; |
+---------------------+
|
| a |
|
+---------------------+
|
| 1970-01-01 00:00:00 |
|
+---------------------+
|
Looks wrong. The zero datetime was erroneously re-interpreted as '1970-01-01 00:00:00'.
Note, if I wrap the column t2.a into COALESCE(), it works OK:
INSERT INTO t2 SELECT COALESCE(a) FROM t1; |
SELECT * FROM t2; |
+---------------------+
|
| a |
|
+---------------------+
|
| 1970-01-01 00:00:00 |
|
| 0000-00-00 00:00:00 |
|
+---------------------+
|
The problem resides in this code:
int Field_timestamp::save_in_field(Field *to) |
{
|
ulong sec_part;
|
my_time_t ts= get_timestamp(&sec_part);
|
return to->store_timestamp_dec(Timeval(ts, sec_part), decimals()); |
}
|
It should not call store_timestamp_dec() for zero datetime, as this method knows nothing about zero datetime. It can store only real timestamp values.
A proposed fix:
int Field_timestamp::save_in_field(Field *to) |
{
|
ulong sec_part;
|
my_time_t ts= get_timestamp(&sec_part);
|
if (!ts && ! sec_part) |
return to->store_time_dec(Datetime::zero().get_mysql_time(), decimals()); |
return to->store_timestamp_dec(Timeval(ts, sec_part), decimals()); |
}
|
Attachments
Issue Links
- relates to
-
MDEV-34037 DATETIME <-> TIMESTAMP conversion in a virtual column corrups the table on @@time_zone change
-
- In Review
-
-
-
- Closed
-
-
-
- Closed
-