MySQL Bugs: #115176: Inconsistent Results When Using Date and FROM

Bug #115176	Inconsistent Results When Using Date and FROM_DAYS Conversion
Submitted:	30 May 13:41	Modified:	30 May 14:25
Reporter:	Wenqian Deng	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
There is an inconsistency in the results when using c_1 (a date column) and (FROM_DAYS(c_0)) (a conversion of a bigint to a date) in SQL queries. Although both expressions should yield the same value, the queries return different results under certain conditions.

How to repeat:
Create a table and insert values:

CREATE TABLE t0 (c_0 bigint, c_1 date);
INSERT INTO t0 VALUES (2211469, '6054-10-18');

Verify that c_1 and (FROM_DAYS(c_0)) have the same value:

SELECT c_1, (FROM_DAYS(c_0)) FROM t0;
+------------+------------------+
| c_1        | (FROM_DAYS(c_0)) |
+------------+------------------+
| 6054-10-18 | 6054-10-18       |
+------------+------------------+

Execute the following query:

SELECT t0.c_1 FROM t0 WHERE (t0.c_1 BETWEEN '9827-01-06 04:56:45.000000' AND 1477699892 AND (EXISTS(SELECT t0.c_0 FROM t0 WHERE t0.c_0 <= 1296920099))) AND NOT t0.c_0 IS NULL;

Returns empty set

Replace c_1 with (FROM_DAYS(c_0)) and execute the query:

SELECT (FROM_DAYS(t0.c_0)) FROM t0 WHERE ((FROM_DAYS(t0.c_0)) BETWEEN '9827-01-06 04:56:45.000000' AND 1477699892 AND (EXISTS(SELECT t0.c_0 FROM t0 WHERE t0.c_0 <= 1296920099))) AND NOT t0.c_0 IS NULL;

+---------------------+
| (FROM_DAYS(t0.c_0)) |
+---------------------+
| 6054-10-18          |
+---------------------+

Expected Result:

Both queries should return the same results, either both returning the same date or both returning an empty set.

Hello Wenqian Deng,

Thank you for the report and test case.
Verified as described.

regards,
Umesh

Hi Mr. Deng,

Thank you for your bug report.

However, this is not a bug.

These are our results:

c_1	(FROM_DAYS(c_0))
6054-10-18	6054-10-18
First   query

Second  query

Both returned empty results .......

We have your two queries differently. We just replaced a date function with FROM_DAYS conversion.

The rest remained the same.

I am sure these sqls provided returns different results on MySQL 8.0. see: https://dbfiddle.uk/NLvo8qJf

Hi Mr. Deng,

Actually, we found the error in your queries ........

You have used the following condition:

BETWEEN '9827-01-06 04:56:45.000000' AND 1477699892

If you mix different data types, you will get very inconsistent results. In this case, you mixed DATETIME type and an integer. SQL Standard is clear that an error should be returned straightforward.

However, MySQL tries to do more ....... so in this expression that uses three values, it tried to find a common denominator.

When we have removed that condition, we got the correct result:

c_1	(FROM_DAYS(c_0))
6054-10-18	6054-10-18

First   query

c_1
6054-10-18

Second  query
FROM_DAYS(t0.c_0)
6054-10-18

Not a bug.