Bug #115146 Inconsistent Results in SQL Query
Submitted: 28 May 2:38 Modified: 28 May 10:07
Reporter: Wenqian Deng Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:8.0 OS:Any
Assigned to: CPU Architecture:Any

[28 May 2:38] Wenqian Deng
Description:
The results vary unexpectedly based on a conditional statement within the query. Specifically, changing a constant TRUE to a condition involving TO_DAYS leads to a different result set.

How to repeat:
Set up the table:

CREATE TABLE t1 (c_0 DATE, c_1 MEDIUMINT);
INSERT INTO t1 VALUES ('8124-01-25', 2967254);

Execute the following SQL query:

SELECT t1.c_0 
FROM t1 
WHERE (
    EXISTS (
        SELECT (TO_DAYS(t1.c_0)) 
        FROM t1 
        WHERE (
            (TO_DAYS(t1.c_0)) <> -2.5698771209691616e+38 
            AND t1.c_0 BETWEEN -2.8264431170269555e+38 AND 
            (CASE WHEN (TRUE AND TRUE) THEN ('6263-05-09 13:00:11.000000') 
            ELSE ('6263-05-09 13:00:11.000000') END)
        )
    ) 
    AND t1.c_0 BETWEEN -9223372036854775808 AND '0001-01-01 00:00:00'
);

Returns:
+------------+
| c_0        |
+------------+
| 8124-01-25 |
+------------+

Modify the SQL query by replacing the TRUE condition in line 10 with (TO_DAYS(t1.c_0) = TO_DAYS(t1.c_0)):

SELECT t1.c_0 
FROM t1 
WHERE (
    EXISTS (
        SELECT (TO_DAYS(t1.c_0)) 
        FROM t1 
        WHERE (
            (TO_DAYS(t1.c_0)) <> -2.5698771209691616e+38 
            AND t1.c_0 BETWEEN -2.8264431170269555e+38 AND 
            (CASE WHEN (TRUE AND (TO_DAYS(t1.c_0) = TO_DAYS(t1.c_0))) THEN ('6263-05-09 13:00:11.000000') 
            ELSE ('6263-05-09 13:00:11.000000') END)
        )
    ) 
    AND t1.c_0 BETWEEN -9223372036854775808 AND '0001-01-01 00:00:00'
);

The modified query returns:
Empty set

What I expected is that both queries should return the same result set.
[28 May 10:07] MySQL Verification Team
HI Mr. Deng,

Thank you for your bug report.

Indeed, we managed to repeat your bug report:

First query
c_0
8124-01-25
second query

This is a very small bug in our Optimiser, but it is still a bug.

Thank you for your contribution.

This is now a fully verified bug report for versions 8.0 and 8.4.