MySQL Bugs: #45004: Date Value

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:	None
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

Description:
Hy 
When I select a incorrect date type with
select  STR_TO_DATE('xxx' ,'%Y%m%d')
function return null and this is ok

In select 'xxx' + interval 0 day return null

But when i update a table with
 update  abc  set  `Date`=  STR_TO_DATE('xxx' ,'%Y%m%d')

I get error
Incorrect datetime value: 'xxx' for function str_to_time

Same is with 
update  abc  set  `Date`=  'xxx' + interval 0 day

My wish is to control the validity of date

How to repeat:

CREATE TABLE `abc` (
  `Date` date default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

insert into abc set Date=null

select 'xxx' + interval 0 day return null
select  STR_TO_DATE('xxx' ,'%Y%m%d')

update  abc  set  `Date`=  STR_TO_DATE('xxx' ,'%Y%m%d')
update  abc  set  `Date`=  'xxx' + interval 0 day

Thank you for the report.

Please provide output of SELECT @@sql_mode;

Result of 
SELECT @@sql_mode
is
STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

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.