Bug #82642 insert string-format value to datetime and timestamp return different status

• Submitted: 19 Aug 2016 3:58
• Modified: 23 Apr 21:00
• Reporter: 帅 Bang
• Status: Not a Bug
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 5.6, 5.5.51, 5.6.32
• OS: Any
• CPU Architecture: Any

[19 Aug 2016 3:58] 帅 Bang

Description:
mysql> drop table if exists sb;
Query OK, 0 rows affected (0.00 sec)

mysql> create table sb(a datetime);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into sb values("9-01-01 12:34:56");
Query OK, 1 row affected (0.01 sec) ;

Yeah, insertion succeed. But, when we change datetime to timestamp，things get weird. 

mysql> drop table sb;
Query OK, 0 rows affected (0.01 sec)

mysql> create table sb(a timestamp);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into sb values("9-01-01 12:34:56");
ERROR 1292 (22007): Incorrect datetime value: '9-01-01 12:34:56' for column 'a' at row 1

It failed !

mysql> select @@sql_mode;
+-------------------+
| @@sql_mode        |
+-------------------+
| STRICT_ALL_TABLES |
+-------------------+
1 row in set (0.00 sec)

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

How to repeat:
drop table if exists sb;
create table sb(a datetime);
insert into sb values("9-01-01 12:34:56");
drop table sb;
create table sb(a timestamp);
insert into sb values("9-01-01 12:34:56");

Suggested fix:
both failed or both succeed.

[19 Aug 2016 7:56] MySQL Verification Team

Hi Bang,

Thank you for the report.

Thanks,
Umesh

[23 Apr 21:00] Roy Lyseng

Posted by developer:
 
This is not a bug.
Year 9 is acceptable for type DATETIME, but it is out of the range supported for TIMESTAMP.