Description:
The query involving the MAKE_SET function, combined with a RIGHT JOIN and a WHERE clause, is returning incorrect results.
How to repeat:
Create Test Table and Insert Sample Data:
CREATE TABLE `t1` (
`c1` int
);
INSERT INTO `t1` VALUES (1);
Execute the Following Queries:
Query 1: Without WHERE Clause
select
ref_1.c1,
((MAKE_SET(1,BIN(ref_1.c1),BIN(ref_1.c1))) not like '%_6') as w_1
from
t1 as ref_1 right join t1 as ref_2 on false;
Result:
+------+------+
| c1 | w_1 |
+------+------+
| NULL | 1 |
+------+------+
1 row in set (0.00 sec)
Query 2: With WHERE Clause
select
ref_1.c1,
((MAKE_SET(1,BIN(ref_1.c1),BIN(ref_1.c1))) not like '%_6') as w_1
from
t1 as ref_1 right join t1 as ref_2 on false
where ((MAKE_SET(1,BIN(ref_1.c1),BIN(ref_1.c1))) not like '%_6');
Expected Result:
+------+------+
| c1 | w_1 |
+------+------+
| NULL | 1 |
+------+------+
1 row in set (0.00 sec)
Actual Result:
Empty set (0.00 sec)
The second query, which uses the WHERE clause with the MAKE_SET function, should return the same result as the first query but instead returns an empty set.
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
