[MDEV-11341] STR_TO_DATE does not return NULL for invalid dates Created: 2016-11-23  Updated: 2019-04-22

Status: Open
Project: MariaDB Server
Component/s: Temporal Types
Affects Version/s: 5.5, 10.0, 10.1, 10.2, 10.3, 10.4
Fix Version/s: 10.4

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Alexander Barkov
Resolution: Unresolved Votes: 1
Labels: upstream


 Description   

MariaDB 10.1

 
MariaDB [test]> SELECT STR_TO_DATE('1949-02-30','%Y-%m-%d');
 
+--------------------------------------+
 
| STR_TO_DATE('1949-02-30','%Y-%m-%d') |
 
+--------------------------------------+
 
| 1949-02-30                           |
 
+--------------------------------------+
 
1 row in set (0.00 sec)

According to specification, it should be NULL for invalid date values.
Reproducible with MySQL 5.5/5.6, but fixed in MySQL 5.7 (only it produces duplicate warnings, let's not do it):

MySQL 5.7

 
MySQL [test]> SELECT STR_TO_DATE('1949-02-30','%Y,%m,%d');
 
+--------------------------------------+
 
| STR_TO_DATE('1949-02-30','%Y,%m,%d') |
 
+--------------------------------------+
 
| NULL                                 |
 
+--------------------------------------+
 
1 row in set, 2 warnings (0.00 sec)
 
 
 
MySQL [test]> show warnings;
 
+---------+------+-----------------------------------------------------------------+
 
| Level   | Code | Message                                                         |
 
+---------+------+-----------------------------------------------------------------+
 
| Warning | 1411 | Incorrect datetime value: '1949-02-30' for function str_to_date |
 
| Warning | 1411 | Incorrect datetime value: '1949-02-30' for function str_to_date |
 
+---------+------+-----------------------------------------------------------------+
 
2 rows in set (0.00 sec)

Found on stackoverflow.

 Comments   
Comment by Alexander Barkov [ 2019-04-22 ]

Just tested with MySQL-5.7.25:

SELECT
 
  STR_TO_DATE('1949-02-30','%Y-%m-%d'),
 
  STR_TO_DATE('1949-02-30','%Y,%m,%d');


+--------------------------------------+--------------------------------------+
 
| STR_TO_DATE('1949-02-30','%Y-%m-%d') | STR_TO_DATE('1949-02-30','%Y,%m,%d') |
 
+--------------------------------------+--------------------------------------+
 
| 1949-02-30                           | NULL                                 |
 
+--------------------------------------+--------------------------------------+
 
1 row in set, 2 warnings (0.000 sec)

It returns NULL, but not because if an invalid date: the reason is a wrong delimiter.

With a correct delimiter it still returns an invalid date '1949-02-30'.

Comment by Elena Stepanova [ 2019-04-22 ]

My bad, I mixed up different SQL statements while converting the StackOverflow question into a bug report. There was a claim that some hackish sequence of ALTER/UPDATE statements was working differently on 5.7 comparing to 10.1, most likely i was trying to simplify that one but took a wrong turn.

Generated at Thu Feb 08 07:49:12 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.