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