Description:
When using REPEAT with a CASE statement inside a subquery, MySQL returns an incorrect result.
Expected behavior:
The output column c_1 should be equal to c_0, as the REPEAT function should generate a string of length c_0, and LENGTH(subq_0.c_1) should match c_0.
Actual behavior:
The returned value of LENGTH(subq_0.c_1) is incorrect and does not match c_0.
How to repeat:
Create Test Table and Insert Sample Data:
CREATE TABLE `t0` (
`c1` int NOT NULL,
`c2` int DEFAULT NULL,
PRIMARY KEY (`c1`)
);
INSERT INTO `t0` VALUES (44,2);
Execute the Following Query:
select
subq_0.c_0 as c_0,
length(subq_0.c_1) as c_1_len
from
(select
ref_0.c1 as c_0,
REPEAT(case when (false) then ('w') else ('w') end,
ref_0.c1) as c_1
from
t0 as ref_0
where (ref_0.c2) is not null) as subq_0
where (not ((subq_0.c_1) = ('abcd')));
Expected Result:
+-----+---------+
| c_0 | c_1_len |
+-----+---------+
| 44 | 44|
+-----+---------+
1 row in set (0.00 sec)
The output column c_1 should be equal to c_0, as the REPEAT function should generate a string of length c_0, and LENGTH(subq_0.c_1) should match c_0.
Actual Result:
+-----+---------+
| c_0 | c_1_len |
+-----+---------+
| 44 | 1936 |
+-----+---------+
1 row in set (0.00 sec)
The returned value of LENGTH(subq_0.c_1) is incorrect and does not match c_0.
Description: When using REPEAT with a CASE statement inside a subquery, MySQL returns an incorrect result. Expected behavior: The output column c_1 should be equal to c_0, as the REPEAT function should generate a string of length c_0, and LENGTH(subq_0.c_1) should match c_0. Actual behavior: The returned value of LENGTH(subq_0.c_1) is incorrect and does not match c_0. How to repeat: Create Test Table and Insert Sample Data: CREATE TABLE `t0` ( `c1` int NOT NULL, `c2` int DEFAULT NULL, PRIMARY KEY (`c1`) ); INSERT INTO `t0` VALUES (44,2); Execute the Following Query: select subq_0.c_0 as c_0, length(subq_0.c_1) as c_1_len from (select ref_0.c1 as c_0, REPEAT(case when (false) then ('w') else ('w') end, ref_0.c1) as c_1 from t0 as ref_0 where (ref_0.c2) is not null) as subq_0 where (not ((subq_0.c_1) = ('abcd'))); Expected Result: +-----+---------+ | c_0 | c_1_len | +-----+---------+ | 44 | 44| +-----+---------+ 1 row in set (0.00 sec) The output column c_1 should be equal to c_0, as the REPEAT function should generate a string of length c_0, and LENGTH(subq_0.c_1) should match c_0. Actual Result: +-----+---------+ | c_0 | c_1_len | +-----+---------+ | 44 | 1936 | +-----+---------+ 1 row in set (0.00 sec) The returned value of LENGTH(subq_0.c_1) is incorrect and does not match c_0.