MySQL Bugs: #64247: Abnormal behaviour of str_to

Bug #64247	Abnormal behaviour of str_to_date() function
Submitted:	7 Feb 2012 10:08	Modified:	6 Nov 2019 21:28
Reporter:	Prince Munjal	Email Updates:
Status:	Closed	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

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.

To Repeat this issue,

Follow the test case by setting sql_mode to 'NO_ZERO_DATE'.

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.

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.

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.

Team,

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

Posted by developer:
 
In 5.7 and later, str_to_date('00000000', '%Y%m%d') consistently returns a NULL value, since the string argument does not represent a valid date.