Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.4, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10, 10.11, 11.0, 11.1, 11.2, 11.3, 11.4, 11.5
-
None
Description
If in strict mode I insert the value '1970-01-01 00:00:00' directly to a TIMESTAMP column, it's rejected as expected, because the value of
{tv_sec=0, tv_usec=0} is reserved for zero datetimes:
SET sql_mode='STRICT_TRANS_TABLES,STRICT_ALL_TABLES'; |
SET time_zone='+00:00'; |
CREATE OR REPLACE TABLE t1 (a TIMESTAMP) ENGINE=MyISAM; |
INSERT INTO t1 VALUES ('1970-01-01 00:00:00'); |
ERROR 1292 (22007): Incorrect datetime value: '1970-01-01 00:00:00' for column `test`.`t1`.`a` at row 1
|
I I insert the same value but with some microseconds, it also gets rejected for the same reason (the value of {tv_sec=0, tv_usec=0}
is reserved for zero datetimes):
SET sql_mode='STRICT_TRANS_TABLES,STRICT_ALL_TABLES'; |
SET time_zone='+00:00'; |
CREATE OR REPLACE TABLE t1 (a TIMESTAMP) ENGINE=MyISAM; |
INSERT INTO t1 VALUES ('1970-01-01 00:00:00.1'); |
ERROR 1292 (22007): Incorrect datetime value: '1970-01-01 00:00:00.1' for column `test`.`t1`.`a` at row 1
|
If I however insert the same value (with some microseconds) from another table:
SET sql_mode='STRICT_TRANS_TABLES,STRICT_ALL_TABLES'; |
SET time_zone='+00:00'; |
CREATE OR REPLACE TABLE t1 (a TIMESTAMP) ENGINE=MyISAM; |
CREATE OR REPLACE TABLE t2 (a TIMESTAMP(1)) ENGINE=MyISAM; |
INSERT INTO t2 VALUES ('1970-01-01 00:00:00.1'); |
INSERT INTO t1 SELECT a FROM t2; |
SELECT * FROM t1; |
+---------------------+
|
| a |
|
+---------------------+
|
| 0000-00-00 00:00:00 |
|
+---------------------+
|
it gets inserted and re-interpreted as zero datetime. This is not correct. The expected behavior would be to reject this value like in the previous two scripts.
Attachments
Issue Links
- relates to
-
-
- Closed
-
-
-
- Closed
-
-
-
- In Review
-