Bug #79259 | Get inconsistent result for invalid date in column and const,compared w/ date | ||
---|---|---|---|
Submitted: | 13 Nov 2015 3:47 | Modified: | 4 Nov 2019 18:33 |
Reporter: | Su Dylan | Email Updates: | |
Status: | Not a Bug | Impact on me: | |
Category: | MySQL Server: DML | Severity: | S3 (Non-critical) |
Version: | 5.7.8 | OS: | Any |
Assigned to: | MySQL Verification Team | CPU Architecture: | Any |
[13 Nov 2015 3:47]
Su Dylan
[13 Nov 2015 7:57]
Peter Laursen
A maybe (?) related test case (with MySQL 5.6, BTW) not involving any table storage at all. SET sql_mode = 'ALLOW_INVALID_DATES'; SELECT ((SELECT CAST('0000-00-00' AS DATE)) = (SELECT CAST('2015-13-10' AS CHAR))); -- returns "1" -- and also sql_mode does not seem to matter for the result at all: SET sql_mode = ''; SELECT ((SELECT CAST('0000-00-00' AS DATE)) = (SELECT CAST('2015-13-10' AS CHAR))); -- returns "1" -- and also same result with data types reverted (and in any sql-mode) SELECT ((SELECT CAST('0000-00-00' AS CHAR)) = (SELECT CAST('2015-13-10' AS DATE))); -- returns "1" -- Peter -- not an Oracle/MySQL person.
[13 Nov 2015 8:10]
Peter Laursen
Sorry. I did a mistake. I was on MariaDB 10.1 - not MySQL 5.6 as I thought. -- 1) SELECT VERSION(); -- 10.1.2-MariaDB-log SET sql_mode = 'ALLOW_INVALID_DATES'; SELECT ((SELECT CAST('0000-00-00' AS DATE)) = (SELECT CAST('2015-13-10' AS CHAR))); -- returns "1" -- and also sql_mode does not seem to matter for the result at all: SET sql_mode = ''; SELECT ((SELECT CAST('0000-00-00' AS DATE)) = (SELECT CAST('2015-13-10' AS CHAR))); -- returns "1" -- and also same result with data types reverted (in any sql-mode) SELECT ((SELECT CAST('0000-00-00' AS CHAR)) = (SELECT CAST('2015-13-10' AS DATE))); -- returns "1" -- 2) SELECT VERSION(); -- 5.6.13-log SET sql_mode = 'ALLOW_INVALID_DATES'; SELECT ((SELECT CAST('0000-00-00' AS DATE)) = (SELECT CAST('2015-13-10' AS CHAR))); -- returns "0" SET sql_mode = ''; SELECT ((SELECT CAST('0000-00-00' AS DATE)) = (SELECT CAST('2015-13-10' AS CHAR))); -- returns "0" -- datatypes reversed SELECT ((SELECT CAST('0000-00-00' AS CHAR)) = (SELECT CAST('2015-13-10' AS DATE))); -- returns NULL - because subquery "SELECT CAST('2015-13-10' AS DATE)" does in this sql_mode. So Su seems right that some bug sneaked in recently (in 5.7, possibly late 5.6.x) that has also already been merged into MariaDB 10.1.
[13 Nov 2015 16:04]
Su Dylan
Hi Peter, Thank you for the reply and I appreciate it very much. In my original description, I didn't describe the expected result clearly enough. Actually, when comparing a date to a string( no matter in column or as a constant string), I expected string be converted to date (invalid date convert to '0000-00-00'). In other words, I expected "1" returned for the examples you provided. Please let me know if there is any misunderstanding of MySQL for me. Here are result based on MySQL 5.7.8: ==================================== mysql> select version(); +-----------+ | version() | +-----------+ | 5.7.8-rc | +-----------+ 1 row in set (0.00 sec) mysql> SET sql_mode = 'ALLOW_INVALID_DATES'; Query OK, 0 rows affected (0.00 sec) mysql> SELECT ((SELECT CAST('0000-00-00' AS DATE)) = (SELECT CAST('2015-13-10' AS CHAR))); +-----------------------------------------------------------------------------+ | ((SELECT CAST('0000-00-00' AS DATE)) = (SELECT CAST('2015-13-10' AS CHAR))) | +-----------------------------------------------------------------------------+ | 0 | +-----------------------------------------------------------------------------+ 1 row in set, 1 warning (0.00 sec) mysql> SET sql_mode = ''; Query OK, 0 rows affected (0.00 sec) mysql> SELECT ((SELECT CAST('0000-00-00' AS DATE)) = (SELECT CAST('2015-13-10' AS CHAR))); +-----------------------------------------------------------------------------+ | ((SELECT CAST('0000-00-00' AS DATE)) = (SELECT CAST('2015-13-10' AS CHAR))) | +-----------------------------------------------------------------------------+ | 0 | +-----------------------------------------------------------------------------+ 1 row in set, 1 warning (0.00 sec) mysql> SELECT ((SELECT CAST('0000-00-00' AS CHAR)) = (SELECT CAST('2015-13-10' AS DATE))); +-----------------------------------------------------------------------------+ | ((SELECT CAST('0000-00-00' AS CHAR)) = (SELECT CAST('2015-13-10' AS DATE))) | +-----------------------------------------------------------------------------+ | NULL | +-----------------------------------------------------------------------------+ 1 row in set, 1 warning (0.00 sec)
[19 Nov 2015 7:16]
Su Dylan
Is there any progress on analysis of this issue?
[4 Nov 2019 18:33]
MySQL Verification Team
This is not a bug. Basically when you do: SET sql_mode = 'ALLOW_INVALID_DATES'; you cannot expect anything good after it. CAST('2015-13-10' AS DATE) is NULL irrelevant if sql_mode is allowing or not invalid dates in modern MySQL and it is proper behavior, expecting that to return '0000-00-00' is a wrong expectation.