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.