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
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