Description:
The QUOTE function does not properly handle NULL values when used in a RIGHT OUTER JOIN. The first query correctly returns a row with w_1 = 1, indicating that QUOTE(subq_1.c_0) IS NOT NULL. However, when the same condition is applied in the WHERE clause of the second query, it incorrectly filters out all rows, returning an empty set.
How to repeat:
Create Test Tables and Insert Sample Data:
CREATE TABLE `t1` (
`c1` int
);
INSERT INTO `t1` VALUES (1);
Execute the Following Queries
Query 1: QUOTE Function Used in SELECT Clause
SELECT
QUOTE(subq_1.c_0) IS NOT NULL AS w_1
FROM
((SELECT
ref_1.c1 AS c_0
FROM
t1 AS ref_1
) AS subq_1
RIGHT OUTER JOIN t1 AS ref_2
ON FALSE);
Result:
+-----+
| w_1 |
+-----+
| 1 |
+-----+
1 row in set (0.00 sec)
Query 2: QUOTE Function Used in WHERE Clause
select
QUOTE(subq_1.c_0) is not null as w_1
from
((select
ref_1.c1 as c_0
from
t1 as ref_1
) as subq_1
right outer join t1 as ref_2
on false)
where QUOTE(subq_1.c_0) is not null
Expected Result:
+-----+
| w_1 |
+-----+
| 1 |
+-----+
1 row in set (0.00 sec)
Actual Result:
Empty 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 QUOTE function does not properly handle NULL values when used in a RIGHT OUTER JOIN. The first query correctly returns a row with w_1 = 1, indicating that QUOTE(subq_1.c_0) IS NOT NULL. However, when the same condition is applied in the WHERE clause of the second query, it incorrectly filters out all rows, returning an empty set. How to repeat: Create Test Tables and Insert Sample Data: CREATE TABLE `t1` ( `c1` int ); INSERT INTO `t1` VALUES (1); Execute the Following Queries Query 1: QUOTE Function Used in SELECT Clause SELECT QUOTE(subq_1.c_0) IS NOT NULL AS w_1 FROM ((SELECT ref_1.c1 AS c_0 FROM t1 AS ref_1 ) AS subq_1 RIGHT OUTER JOIN t1 AS ref_2 ON FALSE); Result: +-----+ | w_1 | +-----+ | 1 | +-----+ 1 row in set (0.00 sec) Query 2: QUOTE Function Used in WHERE Clause select QUOTE(subq_1.c_0) is not null as w_1 from ((select ref_1.c1 as c_0 from t1 as ref_1 ) as subq_1 right outer join t1 as ref_2 on false) where QUOTE(subq_1.c_0) is not null Expected Result: +-----+ | w_1 | +-----+ | 1 | +-----+ 1 row in set (0.00 sec) Actual Result: Empty 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