Bug #121147 QUOTE() incorrectly treated as null-rejecting in outer joins

Submitted: 21 Aug 4:11 Modified: 21 Aug 20:40
Reporter: mu mu Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:9.7.1 OS:Ubuntu (22.04)
Assigned to: CPU Architecture:Any

[21 Aug 4:11] mu mu
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
[21 Aug 20:40] Roy Lyseng
Thank you for the bug report.
Verified as described.

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.