Bug #64247 Abnormal behaviour of str_to_date() function
Submitted: 7 Feb 2012 10:08 Modified: 15 Feb 2012 14:49
Reporter: Prince Munjal Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S2 (Serious)
Version:5.5.18, 5.5.21 OS:Any
Assigned to: CPU Architecture:Any
Tags: regression
Triage: Needs Triage: D3 (Medium)

[7 Feb 2012 10:08] Prince Munjal
Description:
Team,

I have come across an abnormal behavior of 'str_to_date()' function while sql_mode contains 'NO_ZERO_DATE'.

mysql> select @@sql_mode;
+---------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                  |
+---------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> create table test_strtodate
    -> (
    ->   col1    decimal(4) not null,
    ->   col2    date not null,
    ->   col3    char(4) not null,
    ->   col4    date default null
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.14 sec)

mysql> insert into test_strtodate values(1000,'2012-01-01','ABCD','2012-02-01'),(1000,'2012-01-02','ABCD','2012-02-02'),(1000,'2012-01-03','UVWX','2012-02-02'),(1001,'2012-01-01','UVWX','2012-02-01');
Query OK, 4 rows affected (0.03 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from test_strtodate;
+------+------------+------+------------+
| col1 | col2       | col3 | col4       |
+------+------------+------+------------+
| 1000 | 2012-01-01 | ABCD | 2012-02-01 |
| 1000 | 2012-01-02 | ABCD | 2012-02-02 |
| 1000 | 2012-01-03 | UVWX | 2012-02-02 |
| 1001 | 2012-01-01 | UVWX | 2012-02-01 |
+------+------------+------+------------+
4 rows in set (0.00 sec)

mysql> select * from test_strtodate where col3='ABCD' and col2=str_to_date('00000000','%Y%m%d'); 
Empty set (0.00 sec)

mysql> select * from test_strtodate where col3='ABCD' and col2>str_to_date('00000000','%Y%m%d'); 
+------+------------+------+------------+
| col1 | col2       | col3 | col4       |
+------+------------+------+------------+
| 1000 | 2012-01-01 | ABCD | 2012-02-01 |
| 1000 | 2012-01-02 | ABCD | 2012-02-02 |
+------+------------+------+------------+
2 rows in set (0.00 sec)

mysql> Alter table test_strtodate add primary key p_key(col1,col2,col3);
Query OK, 0 rows affected (0.23 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from test_strtodate where col3='ABCD' and col2>str_to_date('00000000','%Y%m%d'); 
+------+------------+------+------------+
| col1 | col2       | col3 | col4       |
+------+------------+------+------------+
| 1000 | 2012-01-01 | ABCD | 2012-02-01 |
| 1000 | 2012-01-02 | ABCD | 2012-02-02 |
+------+------------+------+------------+
2 rows in set (0.00 sec)

mysql> Alter table test_strtodate drop primary key;
Query OK, 4 rows affected (0.25 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> Alter table test_strtodate add primary key p_key(col3,col1,col2);
Query OK, 0 rows affected (0.25 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from test_strtodate where col3='ABCD' and col2>str_to_date('00000000','%Y%m%d'); 
Empty set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+---------------------------------------------------------------+
| Level   | Code | Message                                                       |
+---------+------+---------------------------------------------------------------+
| Warning | 1411 | Incorrect datetime value: '00000000' for function str_to_date |
+---------+------+---------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from test_strtodate where col2>str_to_date('00000000','%Y%m%d'); 
+------+------------+------+------------+
| col1 | col2       | col3 | col4       |
+------+------------+------+------------+
| 1000 | 2012-01-01 | ABCD | 2012-02-01 |
| 1000 | 2012-01-02 | ABCD | 2012-02-02 |
| 1000 | 2012-01-03 | UVWX | 2012-02-02 |
| 1001 | 2012-01-01 | UVWX | 2012-02-01 |
+------+------------+------+------------+
4 rows in set (0.00 sec)

mysql> select * from test_strtodate where col2>str_to_date('00000000','%Y%m%d') and col1=1000; 
+------+------------+------+------------+
| col1 | col2       | col3 | col4       |
+------+------------+------+------------+
| 1000 | 2012-01-01 | ABCD | 2012-02-01 |
| 1000 | 2012-01-02 | ABCD | 2012-02-02 |
| 1000 | 2012-01-03 | UVWX | 2012-02-02 |
+------+------------+------+------------+
3 rows in set (0.00 sec)

mysql> notee;

Abnormal Behavior of 'str_to_date()':

The above test case is clearly showing that whenever i am creating PRIMARY KEY Index using (col3, col1, col2) instead of ( col1 , col2, col3) on table, the query start giving warning for incorrect datetime value.

The above Testcase is working fine when I am removing 'NO_ZERO_DATE' from sql_mode

How to repeat:
This is the often issue faced by the application used by my client with MySQL which needs to resolve.
[7 Feb 2012 10:12] Prince Munjal
To Repeat this issue,

Follow the test case by setting sql_mode to 'NO_ZERO_DATE'.
[7 Feb 2012 10:17] Prince Munjal
Test case for str_to_date function without 'NO_ZERO_DATE' in sql_mode

Attachment: strtodate_bug_without_nozerodate.txt (text/plain), 4.28 KiB.

[7 Feb 2012 10:18] Prince Munjal
Test case for str_to_date function with 'NO_ZERO_DATE' in sql_mode

Attachment: strtodate_bug_with_nozerodate.txt (text/plain), 4.20 KiB.

[14 Feb 2012 19:58] Sveta Smirnova
Thank you for the report.

Verified as described in version 5.5. In version 5.1 query "select * from test_strtodate where col3='ABCD' and col2>str_to_date('00000000','%Y%m%d'); " returns 2 rows for both primary keys and in version 5.6 same query returns no result both times.
[15 Feb 2012 14:49] Prince Munjal
Team,

Correct !!!... I have recently faced this issue in my application after upgradion of MySQL from 5.1 to 5.5