Bug #121147 QUOTE() incorrectly treated as null-rejecting in outer joins
Description:
Per the manual / implementation notes, `QUOTE(NULL)` returns the **string** `'NULL'` (four letters), not SQL NULL.
Therefore predicates such as `QUOTE(inner_col) IS NOT NULL` or `QUOTE(inner_col) > ''` are **true** for NULL-complemented outer-join rows and must not be used to convert a LEFT JOIN into an INNER JOIN.
On MySQL 9.7.1, `QUOTE(inner_col)` in WHERE is still treated as null-rejecting:
- `EXPLAIN` shows an **Inner hash join** instead of a left join.
- `COUNT(*)` with `WHERE QUOTE(t2.b) IS NOT NULL` returns **1**.
- The same predicate evaluated via `QUOTE(CASE WHEN FALSE THEN t2.b ELSE t2.b END)` returns **2** (correct).
- Filtering the predicate in an outer subquery also returns **2**.
How to repeat:
```sql
CREATE TABLE t1 (a INT, b INT);
INSERT INTO t1 VALUES (1,1),(2,3);
-- Semantics: QUOTE(NULL) is not SQL NULL
SELECT QUOTE(NULL) IS NOT NULL; -- 1
SELECT CONCAT('X', QUOTE(NULL)); -- 'XNULL'
-- SELECT-list: NULL-extended row has QUOTE(...) IS NOT NULL = 1
SELECT t1.a, t2.b, QUOTE(t2.b) IS NOT NULL AS p, QUOTE(t2.b) AS q
FROM t1 LEFT JOIN t1 t2 ON t1.a = t2.b;
-- 1 | 1 | 1 | '1'
-- 2 | NULL | 1 | NULL << string NULL (IS NOT NULL = 1), not SQL NULL
-- WHERE (wrong)
SELECT COUNT(*) AS c_where
FROM t1 LEFT JOIN t1 t2 ON t1.a = t2.b
WHERE QUOTE(t2.b) IS NOT NULL;
-- Actual: 1
-- Expected: 2
-- CASE wrapper (correct) — same pattern as Bug #112397
SELECT COUNT(*) AS c_case
FROM t1 LEFT JOIN t1 t2 ON t1.a = t2.b
WHERE QUOTE(CASE WHEN FALSE THEN t2.b ELSE t2.b END) IS NOT NULL;
-- Actual: 2
-- Expected: 2
-- Outer filter oracle (correct)
SELECT COUNT(*) AS c_subq FROM (
SELECT t1.a, QUOTE(t2.b) IS NOT NULL AS p
FROM t1 LEFT JOIN t1 t2 ON t1.a = t2.b
) s WHERE p;
-- Actual: 2
-- Expected: 2
EXPLAIN FORMAT=TREE
SELECT * FROM t1 LEFT JOIN t1 t2 ON t1.a = t2.b
WHERE QUOTE(t2.b) IS NOT NULL;
-- Shows: Inner hash join ... (should remain Left join)
DROP TABLE t1;
```
Also reproduces with:
```sql
WHERE QUOTE(t2.b) > '';
-- Actual COUNT: 1; Expected: 2
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.040 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.
Description: Per the manual / implementation notes, `QUOTE(NULL)` returns the **string** `'NULL'` (four letters), not SQL NULL. Therefore predicates such as `QUOTE(inner_col) IS NOT NULL` or `QUOTE(inner_col) > ''` are **true** for NULL-complemented outer-join rows and must not be used to convert a LEFT JOIN into an INNER JOIN. On MySQL 9.7.1, `QUOTE(inner_col)` in WHERE is still treated as null-rejecting: - `EXPLAIN` shows an **Inner hash join** instead of a left join. - `COUNT(*)` with `WHERE QUOTE(t2.b) IS NOT NULL` returns **1**. - The same predicate evaluated via `QUOTE(CASE WHEN FALSE THEN t2.b ELSE t2.b END)` returns **2** (correct). - Filtering the predicate in an outer subquery also returns **2**. How to repeat: ```sql CREATE TABLE t1 (a INT, b INT); INSERT INTO t1 VALUES (1,1),(2,3); -- Semantics: QUOTE(NULL) is not SQL NULL SELECT QUOTE(NULL) IS NOT NULL; -- 1 SELECT CONCAT('X', QUOTE(NULL)); -- 'XNULL' -- SELECT-list: NULL-extended row has QUOTE(...) IS NOT NULL = 1 SELECT t1.a, t2.b, QUOTE(t2.b) IS NOT NULL AS p, QUOTE(t2.b) AS q FROM t1 LEFT JOIN t1 t2 ON t1.a = t2.b; -- 1 | 1 | 1 | '1' -- 2 | NULL | 1 | NULL << string NULL (IS NOT NULL = 1), not SQL NULL -- WHERE (wrong) SELECT COUNT(*) AS c_where FROM t1 LEFT JOIN t1 t2 ON t1.a = t2.b WHERE QUOTE(t2.b) IS NOT NULL; -- Actual: 1 -- Expected: 2 -- CASE wrapper (correct) — same pattern as Bug #112397 SELECT COUNT(*) AS c_case FROM t1 LEFT JOIN t1 t2 ON t1.a = t2.b WHERE QUOTE(CASE WHEN FALSE THEN t2.b ELSE t2.b END) IS NOT NULL; -- Actual: 2 -- Expected: 2 -- Outer filter oracle (correct) SELECT COUNT(*) AS c_subq FROM ( SELECT t1.a, QUOTE(t2.b) IS NOT NULL AS p FROM t1 LEFT JOIN t1 t2 ON t1.a = t2.b ) s WHERE p; -- Actual: 2 -- Expected: 2 EXPLAIN FORMAT=TREE SELECT * FROM t1 LEFT JOIN t1 t2 ON t1.a = t2.b WHERE QUOTE(t2.b) IS NOT NULL; -- Shows: Inner hash join ... (should remain Left join) DROP TABLE t1; ``` Also reproduces with: ```sql WHERE QUOTE(t2.b) > ''; -- Actual COUNT: 1; Expected: 2