Description:
When nested string operation functions (e.g., SUBSTRING, REPEAT) have their innermost function parameters as conditional expressions (NULLIF, COALESCE, CASE), MySQL produces inconsistent query results.
Specifically, when the same expression is used in both the SELECT and WHERE clauses, MySQL incorrectly evaluates the filtering condition, leading to unexpected row retention or elimination. This suggests that nested string operations with conditional expressions in inner parameters are not consistently evaluated across query execution phases.
How to repeat:
Create Test Tables and Insert Sample Data:
CREATE TABLE `t1` (
`c1` int,
`c2` int
);
INSERT INTO `t1` VALUES (6,55);
CREATE TABLE `t2` (
`c3` int,
`c4` int
);
INSERT INTO `t2` VALUES (1,2),(3,4);
Execute the Following Queries:
Query 1: Checking Computed Column Values in SELECT Clause
select
ref_0.c1 as c_0,
ref_0.c2 as c_1,
ref_1.c3 as c_2,
ref_1.c4 as c_3,
('b'< (SUBSTRING(REPEAT(case when (true) then (ifnull('March','March')) else (case when ( (((ref_0.c1) <> (
select
ref_2.c3 as c_0
from
t2 as ref_2
where ( ((ref_1.c3) <= (ref_1.c4)))
order by c_0 desc
limit 1))
)) then ('March') else ('March') end
) end
,
ref_0.c1),
ref_0.c2,
ref_0.c2))) as w_1
from
(t1 as ref_0
cross join t2 as ref_1
);
Result:
+------+------+------+------+------+
| c_0 | c_1 | c_2 | c_3 | w_1 |
+------+------+------+------+------+
| 6 | 55 | 1 | 2 | 0 |
| 6 | 55 | 3 | 4 | 0 |
+------+------+------+------+------+
2 rows in set (0.00 sec)
Query 2: Filtering with the Same Expression in WHERE Clause
select
ref_0.c1 as c_0,
ref_0.c2 as c_1,
ref_1.c3 as c_2,
ref_1.c4 as c_3,
('b'< (SUBSTRING(REPEAT(case when (true) then (ifnull('March','March')) else (case when ( (((ref_0.c1) <> (
select
ref_2.c3 as c_0
from
t2 as ref_2
where ( ((ref_1.c3) <= (ref_1.c4)))
order by c_0 desc
limit 1))
)) then ('March') else ('March') end
) end
,
ref_0.c1),
ref_0.c2,
ref_0.c2))) as w_1
from
(t1 as ref_0
cross join t2 as ref_1
)
where ('b'< (SUBSTRING(REPEAT(case when (true) then (ifnull('March','March')) else (case when ( (((ref_0.c1) <> (
select
ref_2.c3 as c_0
from
t2 as ref_2
where ( ((ref_1.c3) <= (ref_1.c4)))
order by c_0 desc
limit 1))
)) then ('March') else ('March') end
) end
,
ref_0.c1),
ref_0.c2,
ref_0.c2))) ;
Expected Result:
Empty set (0.00 sec)
Since w_1 = 0, the condition 'b' < ... should evaluate to FALSE, filtering out all rows.
Actual Result:
+------+------+------+------+------+
| c_0 | c_1 | c_2 | c_3 | w_1 |
+------+------+------+------+------+
| 6 | 55 | 3 | 4 | 0 |
+------+------+------+------+------+
1 row in set (0.00 sec)
One row is incorrectly retained, even though w_1 = 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 nested string operation functions (e.g., SUBSTRING, REPEAT) have their innermost function parameters as conditional expressions (NULLIF, COALESCE, CASE), MySQL produces inconsistent query results. Specifically, when the same expression is used in both the SELECT and WHERE clauses, MySQL incorrectly evaluates the filtering condition, leading to unexpected row retention or elimination. This suggests that nested string operations with conditional expressions in inner parameters are not consistently evaluated across query execution phases. How to repeat: Create Test Tables and Insert Sample Data: CREATE TABLE `t1` ( `c1` int, `c2` int ); INSERT INTO `t1` VALUES (6,55); CREATE TABLE `t2` ( `c3` int, `c4` int ); INSERT INTO `t2` VALUES (1,2),(3,4); Execute the Following Queries: Query 1: Checking Computed Column Values in SELECT Clause select ref_0.c1 as c_0, ref_0.c2 as c_1, ref_1.c3 as c_2, ref_1.c4 as c_3, ('b'< (SUBSTRING(REPEAT(case when (true) then (ifnull('March','March')) else (case when ( (((ref_0.c1) <> ( select ref_2.c3 as c_0 from t2 as ref_2 where ( ((ref_1.c3) <= (ref_1.c4))) order by c_0 desc limit 1)) )) then ('March') else ('March') end ) end , ref_0.c1), ref_0.c2, ref_0.c2))) as w_1 from (t1 as ref_0 cross join t2 as ref_1 ); Result: +------+------+------+------+------+ | c_0 | c_1 | c_2 | c_3 | w_1 | +------+------+------+------+------+ | 6 | 55 | 1 | 2 | 0 | | 6 | 55 | 3 | 4 | 0 | +------+------+------+------+------+ 2 rows in set (0.00 sec) Query 2: Filtering with the Same Expression in WHERE Clause select ref_0.c1 as c_0, ref_0.c2 as c_1, ref_1.c3 as c_2, ref_1.c4 as c_3, ('b'< (SUBSTRING(REPEAT(case when (true) then (ifnull('March','March')) else (case when ( (((ref_0.c1) <> ( select ref_2.c3 as c_0 from t2 as ref_2 where ( ((ref_1.c3) <= (ref_1.c4))) order by c_0 desc limit 1)) )) then ('March') else ('March') end ) end , ref_0.c1), ref_0.c2, ref_0.c2))) as w_1 from (t1 as ref_0 cross join t2 as ref_1 ) where ('b'< (SUBSTRING(REPEAT(case when (true) then (ifnull('March','March')) else (case when ( (((ref_0.c1) <> ( select ref_2.c3 as c_0 from t2 as ref_2 where ( ((ref_1.c3) <= (ref_1.c4))) order by c_0 desc limit 1)) )) then ('March') else ('March') end ) end , ref_0.c1), ref_0.c2, ref_0.c2))) ; Expected Result: Empty set (0.00 sec) Since w_1 = 0, the condition 'b' < ... should evaluate to FALSE, filtering out all rows. Actual Result: +------+------+------+------+------+ | c_0 | c_1 | c_2 | c_3 | w_1 | +------+------+------+------+------+ | 6 | 55 | 3 | 4 | 0 | +------+------+------+------+------+ 1 row in set (0.00 sec) One row is incorrectly retained, even though w_1 = 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