Bug #117060 changing a constant TRUE to a condition involving TO_DAYS leads to a different result set
Submitted: 27 Dec 2024 13:28 Modified: 27 Dec 2024 13:38
Reporter: Aaditya Dubey Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:8.4.3, 8.0.40 OS:Any
Assigned to: CPU Architecture:Any

[27 Dec 2024 13:28] Aaditya Dubey
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:
1. Set up the table

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

2. 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

Suggested fix:
The expected result is that both queries should return the same result set.
[27 Dec 2024 13:38] MySQL Verification Team
Hello Aaditya,

Thank you for the report and feedback.
Verified as described.

regards,
Umesh