Description:
The REPEAT function, when used within a WHERE clause, unexpectedly filters out rows that should be included based on the logical condition.
How to repeat:
Create Test Tables and Insert Sample Data:
CREATE TABLE `t1` (
`c1` int,
KEY `i1` (`c1`)
);
INSERT INTO `t1` VALUES (0),(7),(9);
Execute the Following Queries
Query 1: Evaluate Condition in SELECT Clause
select
ref_1.c1 as c_0,
not (CHAR_LENGTH(REPEAT(LOWER('A'),ref_1.c1)) > (ref_1.c1)) as w_1
from
t1 as ref_1;
Result:
+------+------+
| c_0 | w_1 |
+------+------+
| 0 | 1 |
| 7 | 1 |
| 9 | 1 |
+------+------+
3 rows in set (0.00 sec)
Query 2: Apply Condition in WHERE Clause
select
ref_1.c1 as c_0,
not (CHAR_LENGTH(REPEAT(LOWER('A'),ref_1.c1)) > (ref_1.c1)) as w_1
from
t1 as ref_1
where not (CHAR_LENGTH(REPEAT(LOWER('A'),ref_1.c1)) > (ref_1.c1));
Expected Result:
+------+------+
| c_0 | w_1 |
+------+------+
| 0 | 1 |
| 7 | 1 |
| 9 | 1 |
+------+------+
3 rows in set (0.00 sec)
Actual Result:
+------+------+
| c_0 | w_1 |
+------+------+
| 0 | 1 |
| 7 | 1 |
+------+------+
2 rows in set (0.00 sec)
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: The REPEAT function, when used within a WHERE clause, unexpectedly filters out rows that should be included based on the logical condition. How to repeat: Create Test Tables and Insert Sample Data: CREATE TABLE `t1` ( `c1` int, KEY `i1` (`c1`) ); INSERT INTO `t1` VALUES (0),(7),(9); Execute the Following Queries Query 1: Evaluate Condition in SELECT Clause select ref_1.c1 as c_0, not (CHAR_LENGTH(REPEAT(LOWER('A'),ref_1.c1)) > (ref_1.c1)) as w_1 from t1 as ref_1; Result: +------+------+ | c_0 | w_1 | +------+------+ | 0 | 1 | | 7 | 1 | | 9 | 1 | +------+------+ 3 rows in set (0.00 sec) Query 2: Apply Condition in WHERE Clause select ref_1.c1 as c_0, not (CHAR_LENGTH(REPEAT(LOWER('A'),ref_1.c1)) > (ref_1.c1)) as w_1 from t1 as ref_1 where not (CHAR_LENGTH(REPEAT(LOWER('A'),ref_1.c1)) > (ref_1.c1)); Expected Result: +------+------+ | c_0 | w_1 | +------+------+ | 0 | 1 | | 7 | 1 | | 9 | 1 | +------+------+ 3 rows in set (0.00 sec) Actual Result: +------+------+ | c_0 | w_1 | +------+------+ | 0 | 1 | | 7 | 1 | +------+------+ 2 rows in set (0.00 sec) 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