Description:
When using the INTERVAL function in a WHERE clause, MySQL unexpectedly returns an empty result set, despite the same function returning valid values in the SELECT clause.
The expected behavior is that rows where INTERVAL(a, b, c) > 0 should be included in the result. However, MySQL incorrectly filters out all rows, leading to an empty result set. This inconsistency suggests that the INTERVAL function is not being evaluated correctly in the WHERE clause.
How to repeat:
Create Test Tables and Insert Sample Data:
CREATE TABLE `t1` (
`c1` int,
`c2` int
);
INSERT INTO `t1` VALUES (10,1);
CREATE TABLE `t2` (
`c3` int
);
INSERT INTO `t2` VALUES (2);
Execute the Following Queries:
Query 1: Checking INTERVAL in SELECT Clause
select distinct
ref_0.c1 as c_1,
ref_2.c2 as c_2,
ref_0.c2 as c_3,
(INTERVAL(ref_0.c1,
ref_2.c2,
ref_0.c2)) as w_1
from
((t1 as ref_0
cross join t2 as ref_1
) left outer join t1 as ref_2
on (ref_1.c3 = ref_2.c2 ))
Result:
+------+------+------+-----+
| c_1 | c_2 | c_3 | w_1 |
+------+------+------+-----+
| 10 | NULL | 1 | 2 |
+------+------+------+-----+
1 row in set (0.01 sec)
The INTERVAL function correctly returns 2 in column w_1.
Query 2: Filtering with INTERVAL in WHERE Clause
ref_0.c1 as c_1,
ref_2.c2 as c_2,
ref_0.c2 as c_3,
(INTERVAL(ref_0.c1,
ref_2.c2,
ref_0.c2)) as w_1
from
((t1 as ref_0
cross join t2 as ref_1
) left outer join t1 as ref_2
on (ref_1.c3 = ref_2.c2 ))
where INTERVAL(ref_0.c1,
ref_2.c2,
ref_0.c2) > 0;
Expected Result:
+------+------+------+-----+
| c_1 | c_2 | c_3 | w_1 |
+------+------+------+-----+
| 10 | NULL | 1 | 2 |
+------+------+------+-----+
1 row in set (0.01 sec)
Since INTERVAL(10, NULL, 1) = 2, the condition INTERVAL(...) > 0 should be TRUE, so the row should be included.
Actual Result:
Empty set (0.01 sec)
MySQL incorrectly filters out all rows, even though INTERVAL(...) is greater than 0 in Query 1.
mysql version:
github commit: 61a3a1d8ef15512396b4c2af46e922a19bf2b174
version: 9.1.0
os version:
Linux ubuntu 5.15.0-134-generic #145~20.04.1-Ubuntu SMP Mon Feb 17 13:27:16 UTC 2025 x86_64 x86_64 x86_64 GNU/Linux
Description: When using the INTERVAL function in a WHERE clause, MySQL unexpectedly returns an empty result set, despite the same function returning valid values in the SELECT clause. The expected behavior is that rows where INTERVAL(a, b, c) > 0 should be included in the result. However, MySQL incorrectly filters out all rows, leading to an empty result set. This inconsistency suggests that the INTERVAL function is not being evaluated correctly in the WHERE clause. How to repeat: Create Test Tables and Insert Sample Data: CREATE TABLE `t1` ( `c1` int, `c2` int ); INSERT INTO `t1` VALUES (10,1); CREATE TABLE `t2` ( `c3` int ); INSERT INTO `t2` VALUES (2); Execute the Following Queries: Query 1: Checking INTERVAL in SELECT Clause select distinct ref_0.c1 as c_1, ref_2.c2 as c_2, ref_0.c2 as c_3, (INTERVAL(ref_0.c1, ref_2.c2, ref_0.c2)) as w_1 from ((t1 as ref_0 cross join t2 as ref_1 ) left outer join t1 as ref_2 on (ref_1.c3 = ref_2.c2 )) Result: +------+------+------+-----+ | c_1 | c_2 | c_3 | w_1 | +------+------+------+-----+ | 10 | NULL | 1 | 2 | +------+------+------+-----+ 1 row in set (0.01 sec) The INTERVAL function correctly returns 2 in column w_1. Query 2: Filtering with INTERVAL in WHERE Clause ref_0.c1 as c_1, ref_2.c2 as c_2, ref_0.c2 as c_3, (INTERVAL(ref_0.c1, ref_2.c2, ref_0.c2)) as w_1 from ((t1 as ref_0 cross join t2 as ref_1 ) left outer join t1 as ref_2 on (ref_1.c3 = ref_2.c2 )) where INTERVAL(ref_0.c1, ref_2.c2, ref_0.c2) > 0; Expected Result: +------+------+------+-----+ | c_1 | c_2 | c_3 | w_1 | +------+------+------+-----+ | 10 | NULL | 1 | 2 | +------+------+------+-----+ 1 row in set (0.01 sec) Since INTERVAL(10, NULL, 1) = 2, the condition INTERVAL(...) > 0 should be TRUE, so the row should be included. Actual Result: Empty set (0.01 sec) MySQL incorrectly filters out all rows, even though INTERVAL(...) is greater than 0 in Query 1. mysql version: github commit: 61a3a1d8ef15512396b4c2af46e922a19bf2b174 version: 9.1.0 os version: Linux ubuntu 5.15.0-134-generic #145~20.04.1-Ubuntu SMP Mon Feb 17 13:27:16 UTC 2025 x86_64 x86_64 x86_64 GNU/Linux