MySQL Bugs: #114988: WHERE ({date} < zeroDate OR zeroDate is NULL) is incorrect in MySQL 8

Bug #114988	WHERE ({date} < zeroDate OR zeroDate is NULL) is incorrect in MySQL 8
Submitted:	13 May 17:02	Modified:	14 May 22:50
Reporter:	Damian Shaw	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	8.0.36	OS:	Fedora
Assigned to:		CPU Architecture:	x86

Description:
There is a bug when a zero date (or datetime) is checked in a where statement like:

WHERE ({date} < zeroDate OR zeroDate is NULL) 

MySql  filters out rows which pass the condition, specifically when the following circumstances are true:

* NOT NULLABLE date or date column
* There is a zero date value in that column
Comparison on date column that involves an OR (and maybe other predicate logic)
* Comparison on date includes a "IS NULL"
* Comparison on date includes an inequality operator that points AWAY from datetime column

How to repeat:
I found this when migrating from 5.7 to 8.0, so I give a series of queries that are the same and then the final query which is different and is a bug in 8.0.36:

CREATE TABLE `testZeroDates` (
  `dateNotNull` date NOT NULL
); 

INSERT INTO testZeroDates (dateNotNull)
VALUES ('0000-00-00');
 
 
-- Returns 1 row in MySQL 5.7 and 8.0
SELECT 1
FROM testZeroDates 
WHERE dateNotNull IS  NULL;
 

-- Returns 1 row in MySQL 5.7 and 8.0
SELECT 1
FROM testZeroDates 
WHERE ('2000-01-01' > dateNotNull) OR (dateNotNull IS NULL);
 
 
-- Returns 1 row in MySQL 5.7 and 8.0
SELECT 1
FROM testZeroDates 
WHERE ('2000-01-01' < dateNotNull) OR (dateNotNull = '0000-00-00');
 

-- Returns 1 row in MySQL 5.7, but 0 rows in MySQL 8.0 (bug!)
SELECT 1
FROM testZeroDates 
WHERE ('2000-01-01' < dateNotNull) OR (dateNotNull IS NULL);

Suggested fix:
dateNotNull IS NULL is TRUE, so MySQL 8.0.36 should not filter rows based on WHERE ({expression}) OR TRUE.

The behavior should change to the same as MySQL 5.7.

I've realized there is a further simplification of the SQL that shows the bug in MySQL 8.0.36:

-- Returns 1 row in MySQL 5.7 and 8.0
SELECT 1
FROM testZeroDates 
WHERE dateNotNull IS  NULL;

-- Returns 1 row in MySQL 5.7 and 8.0
SELECT 1
FROM testZeroDates 
WHERE TRUE OR (dateNotNull IS NULL);

-- Returns 1 row in MySQL 5.7 and 8.0
SELECT 1
FROM testZeroDates 
WHERE FALSE OR (dateNotNull = '0000-00-00');

-- Returns 1 row in MySQL 5.7, but 0 rows in MySQL 8.0
SELECT 1
FROM testZeroDates 
WHERE FALSE OR (dateNotNull IS NULL);

Hi Mr. Shaw,

Thank you very much for your bug report.

We cannot repeat your report, with 8.0.37,  since "0000-00-00' is NOT allowed as a value for the data type DATE.

Since no such row can be inserted, none of the SELECTs return any rows.

This is all documented in our Reference Manual.

Can't repeat.

The problem can be reproduced when sql_mode NO_ZERO_DATE is not in effect.

It occurs due to a special semantic that was implemented for a certain ODBC function: IS NULL when applied to a zero date will return true (see documentation of operator IS NULL). But this is only applicable when the IS NULL operator is the only predicate in a WHERE clause. Versions before 8.0 did not implement this restriction.

So this is not a bug.

Hi Roy,

Thanks for your confirmation, is this documented between 5.7 and 8.0? Either in the documentation or as a migration guide?

I had looked up the same documentation that zero date "IS NULL" evaluates to True, but the documentation itself did not change between 5.7 and 8.0:

https://dev.mysql.com/doc/refman/8.0/en/comparison-operators.html#operator_is-null:

> For DATE and DATETIME columns that are declared as NOT NULL, you can find the special date '0000-00-00' by using a statement like this:
> SELECT * FROM tbl_name WHERE date_column IS NULL

https://dev.mysql.com/doc/refman/5.7/en/comparison-operators.html#operator_is-null:

> For DATE and DATETIME columns that are declared as NOT NULL, you can find the special date '0000-00-00' by using a statement like this:
> SELECT * FROM tbl_name WHERE date_column IS NULL

So it was surprising to see a change in behavior, especially one that breaks predicate logic ("expr1" evaluates to True but "False OR expr1" evaluates to False). It would be good to know if we are missing any other documentation as we attempt to migrates.

Thanks and Regards,
Damian

Notice the documentation says this is made specially for ODBC drivers.
It is also documented with the statement

  SELECT * FROM tbl_name WHERE date_column IS NULL

It is not supposed to be used in composite conditions, thus it was considered a bug and not a change in behaviour. But the documentation should probably warn about not using this feature outside of the ODBC driver.

> Notice the documentation says this is made specially for ODBC drivers.
It is also documented with the statement

I had not interpreted the documentation in the way you are presenting.

I read the documentation as the specialness and specificity of behavior related to a zero date being treated as null even though it's not.

I did not read it as this behavior only being limited to non-composite logic, because I don't think it clearly indicates that, and the behavior is consistent on MySQL 5.6 and 5.7.

> It is also documented with the statement
>
>  SELECT * FROM tbl_name WHERE date_column IS NULL

I had always interpreted this as a minimum reproducible example, the use of a simple example does not mean that you would expect different behavior in more complicated examples.

In fact, I would expect in documenting behavior that if that behavior was special to only simple cases it would be explicitly called out.

> It is not supposed to be used in composite conditions, thus it was considered a bug and not a change in behaviour. But the documentation should probably warn about not using this feature outside of the ODBC driver.

Bug fixes that change behaviors are still changes in behavior, going through all the release notes I am still unable to find where this changed: https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-0.html

I guess this is an example of Hyrum's Law, and we're about 6 years too late to be discussing if this change is valid it or not.

We will just have to much more carefully test our application and logic on MySQL 8.0 than expected.

Thanks again for confirming, please feel free to close this bug report.