Bug #45004 | Date Value | ||
---|---|---|---|
Submitted: | 21 May 2009 8:46 | Modified: | 21 May 2009 14:36 |
Reporter: | Fiala Vjero | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: Data Types | Severity: | S2 (Serious) |
Version: | 5.0.81, 5.0.83-bzr | OS: | Any |
Assigned to: | CPU Architecture: | Any | |
Tags: | incorrect datetime value, interval 0 day, STR_TO_DATE |
[21 May 2009 8:46]
Fiala Vjero
[21 May 2009 9:01]
Sveta Smirnova
Thank you for the report. Please provide output of SELECT @@sql_mode;
[21 May 2009 10:49]
Fiala Vjero
Result of SELECT @@sql_mode is STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
[21 May 2009 14:36]
Valeriy Kravchuk
Verified with recent 5.0.83 from bzr (I've got warning, not error): valeriy-kravchuks-macbook-pro:5.0 openxs$ bin/mysql -uroot test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.0.83-debug Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> set @@sql_mode=''; Query OK, 0 rows affected (0.00 sec) mysql> CREATE TABLE `abc` ( -> `Date` date default NULL -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1; Query OK, 0 rows affected (0.01 sec) mysql> insert into abc set Date=null; Query OK, 1 row affected (0.00 sec) mysql> select 'xxx' + interval 0 day; +------------------------+ | 'xxx' + interval 0 day | +------------------------+ | NULL | +------------------------+ 1 row in set, 1 warning (0.00 sec) mysql> select STR_TO_DATE('xxx' ,'%Y%m%d'); +------------------------------+ | STR_TO_DATE('xxx' ,'%Y%m%d') | +------------------------------+ | NULL | +------------------------------+ 1 row in set, 1 warning (0.00 sec) mysql> update abc set Date = 'xxx' + interval 0 day; Query OK, 0 rows affected, 1 warning (0.00 sec) Rows matched: 1 Changed: 0 Warnings: 0 mysql> show warnings\G *************************** 1. row *************************** Level: Warning Code: 1292 Message: Truncated incorrect datetime value: 'xxx' 1 row in set (0.00 sec) mysql> select * from abc; +------+ | Date | +------+ | NULL | +------+ 1 row in set (0.00 sec) mysql> update abc set Date = STR_TO_DATE('xxx' ,'%Y%m%d') -> ; Query OK, 0 rows affected, 1 warning (0.00 sec) Rows matched: 1 Changed: 0 Warnings: 0 mysql> show warnings\G *************************** 1. row *************************** Level: Error Code: 1411 Message: Incorrect datetime value: 'xxx' for function str_to_time 1 row in set (0.00 sec) Still, I think this is inconsistent to NOT give even a warning on SELECT, but give it on a valid (NULL is valid value for a column) UPDATE.