MySQL Bugs: #79259: Get inconsistent result for invalid date in column and const,compared w/ date

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:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.7.8	OS:	Any
Assigned to:	MySQL Verification Team	CPU Architecture:	Any

Description:
Output:
======
mysql> set sql_mode='ALLOW_INVALID_DATES';
Query OK, 0 rows affected (0.00 sec)

mysql> drop table t3; create table t3(a date, b varchar(100));
insert into Query OK, 0 rows affected (0.00 sec)

t3 values ('2015-11-10', '2015-13-10');
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t3 values ('2015-11-10', '2015-13-10');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t3 values ('0000-00-00', '2015-13-10');
Query OK, 1 row affected (0.00 sec)

mysql> select a,b, a=b,a = '2015-13-10', a < '2015-13-10' from t3;
+------------+------------+------+------------------+------------------+
| a          | b          | a=b  | a = '2015-13-10' | a < '2015-13-10' |
+------------+------------+------+------------------+------------------+
| 2015-11-10 | 2015-13-10 |    0 |                0 |                1 |
| 0000-00-00 | 2015-13-10 |    1 |                0 |                1 |
+------------+------------+------+------------------+------------------+
2 rows in set, 4 warnings (0.00 sec)

Problem:
========
When a is '0000-00-00', b is string '2015-13-10', a=b and a='2015-13-10' should return the same result, while in fact inconsistent result is returned.

There is same problem for datetime and timestamp type.

How to repeat:

set sql_mode='ALLOW_INVALID_DATES';
drop table t3; create table t3(a date, b varchar(100));
insert into t3 values ('2015-11-10', '2015-13-10');
insert into t3 values ('0000-00-00', '2015-13-10');
select a,b, a=b,a = '2015-13-10', a < '2015-13-10' from t3;

drop table t1; create table t1(a datetime, b varchar(100));
insert into t1 values ('2015-11-10', '2015-13-10');
insert into t1 values ('0000-00-00', '2015-13-10');
select a,b, a=b,a = '2015-13-10', a < '2015-13-10' from t1;

drop table t2; create table t2(a timestamp, b varchar(100));
insert into t2 values ('2015-11-10', '2015-13-10');
insert into t2 values ('0000-00-00', '2015-13-10');
select a,b, a=b,a = '2015-13-10', a < '2015-13-10' from t2;

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.

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.

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)

Is there any progress on analysis of this issue?

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.