MySQL Bugs: #14522: STR_TO_DATE functionality has changed from 4.1->5.1

Bug #14522	STR_TO_DATE functionality has changed from 4.1->5.1
Submitted:	31 Oct 2005 21:02	Modified:	3 Nov 2005 1:49
Reporter:	Irwin Rommel	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.15-nt	OS:	Windows (Windows)
Assigned to:		CPU Architecture:	Any

Description:
In MySQL 4.1 the following sql executed without error using the .Net data provider
and MySql 4.1

update html set dtpubdate=STR_TO_DATE(vchDate,'%Y-%m-%d') 
where dtPubdate is null

The vchDate field contained data like this: 2005-10-10 1:00 PM

Once the MySQL server was updated to 5.1 the statement generated string truncation errors and the statement did not execute.

This 4.1 functionality was critical to my application and I have had to downgrade back to 4.1 from 5.1.

How to repeat:
Creat a table with two fields in it(dtPubdate Date and vchHtml varchar(50))

Populate the vchHtml field with the following data:

10/31/2005 01:00:00 AM
10/31/2005 01:00:00 AM
10/31/2005 01:00:00 AM

Run the following command via a .Net MySQLCommand object using MySQL 4.1 server:

update html set dtpubdate=STR_TO_DATE(vchDate,'%m/%d/%Y') 
where dtPubdate is null

Result: dtPubdate is populated with the correct date value

Run the same command via a .Net MySQLCommand object using MySQL 5.1 server:

Result: Statement does not execute and error message comes up.

Thank you for a bug report. Do you really use 5.1.x (development) version of MySQL? I hope it is a mistake, so, I have tried with 5.0.15-nt, officially released:

mysql> select str_to_date('10/31/2005 01:00:00 AM', '%m/%d/%Y');
+---------------------------------------------------+
| str_to_date('10/31/2005 01:00:00 AM', '%m/%d/%Y') |
+---------------------------------------------------+
| 2005-10-31                                        |
+---------------------------------------------------+
1 row in set, 1 warning (0.08 sec)

mysql> show warnings;
+---------+------+----------------------------------------------------------+
| Level   | Code | Message                                                  |
+---------+------+----------------------------------------------------------+
| Warning | 1292 | Truncated incorrect date value: '10/31/2005 01:00:00 AM' |
+---------+------+----------------------------------------------------------+
1 row in set (0.00 sec)

mysql> create table td(dtPubdate date, vchHtml varchar(50));
Query OK, 0 rows affected (0.22 sec)

mysql> insert into td(vchHtml) values ('10/31/2005 01:00:00 AM'), ('10/31/2005 01:00:00 AM'), ('10/31/2005 01:00:00 AM');
Query OK, 3 rows affected (0.06 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> update td set dtpubdate=STR_TO_DATE(vchHtml,'%m/%d/%Y') where dtPubdate is null;
ERROR 1292 (22007): Truncated incorrect date value: '10/31/2005 01:00:00 AM'
mysql> select * from td;
+-----------+------------------------+
| dtPubdate | vchHtml                |
+-----------+------------------------+
| NULL      | 10/31/2005 01:00:00 AM |
| NULL      | 10/31/2005 01:00:00 AM |
| NULL      | 10/31/2005 01:00:00 AM |
+-----------+------------------------+
3 rows in set (0.01 sec)

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

mysql> select * from td;
+-----------+------------------------+
| dtPubdate | vchHtml                |
+-----------+------------------------+
| NULL      | 10/31/2005 01:00:00 AM |
| NULL      | 10/31/2005 01:00:00 AM |
| NULL      | 10/31/2005 01:00:00 AM |
+-----------+------------------------+
3 rows in set (0.01 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.15-nt |
+-----------+
1 row in set (0.01 sec)

Looks like these results are intended, documented behaviour of My SQL 5.0.x. See the manual for details (http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html):

"The date, time, or datetime values contained in str should be given in the format indicated by format.
...
Starting from MySQL 5.0.3, an illegal value also produces a warning."

You data are in the different format than specified. So, simple select truncates them and produces a warning. This is correct. 

The behaviour of update is incorrect, though. And it is changed in the forthcoming 5.0.16 (verified on 5.0.16-BK, ChangeSet@1.1957, 2005-10-29 13:11:34+04:00, konstantin@mysql.com, on Linux):

mysql> create table td(dtPubdate date, vchHtml varchar(50));
Query OK, 0 rows affected (0,01 sec)

mysql> insert into td(vchHtml) values ('10/31/2005 01:00:00 AM');
Query OK, 1 row affected (0,00 sec)

mysql> update td set dtpubdate=STR_TO_DATE(vchHtml,'%m/%d/%Y') where dtPubdate is null;
Query OK, 1 row affected, 1 warning (0,04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> show warnings;
+---------+------+----------------------------------------------------------+
| Level   | Code | Message                                                  |
+---------+------+----------------------------------------------------------+
| Warning | 1292 | Truncated incorrect date value: '10/31/2005 01:00:00 AM' |
+---------+------+----------------------------------------------------------+
1 row in set (0,00 sec)

mysql> select * from td;
+------------+------------------------+
| dtPubdate  | vchHtml                |
+------------+------------------------+
| 2005-10-31 | 10/31/2005 01:00:00 AM |
+------------+------------------------+
1 row in set (0,00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.16    |
+-----------+
1 row in set (0,00 sec)

So, please, wait for 5.0.16 to be officially released.