Description:
MySQL returns a row that must be filtered according to SQL three-valued logic when a CTE containing a `LEFT JOIN` is merged into the outer query.
The right side of the `LEFT JOIN` is empty, so the join produces a NULL-complemented row. The outer predicate is:
```sql
a <> 'sample'
```
For the NULL-complemented row, `a` is `NULL`. The predicate therefore evaluates to `UNKNOWN`, and the `WHERE` clause must reject the row.
With the default optimizer settings, MySQL incorrectly returns:
```text
NULL | NULL
```
When `derived_merge` is disabled, the same query correctly returns an empty result. The result therefore changes solely because of an optimizer transformation.
Expected result:
```text
Empty set
```
Actual result with `derived_merge=on`:
```text
+------+------+
| a | b |
+------+------+
| NULL | NULL |
+------+------+
```
The execution plans also demonstrate the behavioral difference.
With `derived_merge=on`:
```text
Nested loop left join
-> Rows fetched before execution
-> Zero rows (Impossible filter)
```
With `derived_merge=off`:
```text
Zero rows (no matching row in const table)
```
This is a logic bug because merging a derived table or CTE must not change the query result, and a null-rejecting `WHERE` predicate must not retain a NULL-complemented outer-join row.
How to repeat:
Run the following query using the default optimizer settings:
```sql
SET optimizer_switch = 'default';
WITH cut AS (
SELECT r.a, r.b
FROM (SELECT 1 AS x) AS l
LEFT JOIN (
SELECT VERSION() AS a, NULL AS b
FROM (SELECT 1 AS z) AS e
WHERE FALSE
) AS r ON TRUE
)
SELECT *
FROM cut
WHERE a <> 'sample';
```
Actual result:
```text
+------+------+
| a | b |
+------+------+
| NULL | NULL |
+------+------+
```
Disable derived-table merging and execute the same query:
```sql
SET optimizer_switch = 'derived_merge=off';
WITH cut AS (
SELECT r.a, r.b
FROM (SELECT 1 AS x) AS l
LEFT JOIN (
SELECT VERSION() AS a, NULL AS b
FROM (SELECT 1 AS z) AS e
WHERE FALSE
) AS r ON TRUE
)
SELECT *
FROM cut
WHERE a <> 'sample';
```
Expected and actual result with merging disabled:
```text
Empty set
```
The predicate can also be evaluated directly to confirm the expected SQL semantics:
```sql
SELECT NULL <> 'sample';
```
Result:
```text
NULL
```
The issue was originally detected in VECT pair 37. In that query, the unmodified query returned 2 rows, while three independently generated CTE cuts returned 9,992 rows. The affected candidates were two `join` candidates and one `atomic_predicate` candidate. All three differences disappeared when `derived_merge` was disabled.
© 2026, Oracle Corporation and/or its affiliates
Description: MySQL returns a row that must be filtered according to SQL three-valued logic when a CTE containing a `LEFT JOIN` is merged into the outer query. The right side of the `LEFT JOIN` is empty, so the join produces a NULL-complemented row. The outer predicate is: ```sql a <> 'sample' ``` For the NULL-complemented row, `a` is `NULL`. The predicate therefore evaluates to `UNKNOWN`, and the `WHERE` clause must reject the row. With the default optimizer settings, MySQL incorrectly returns: ```text NULL | NULL ``` When `derived_merge` is disabled, the same query correctly returns an empty result. The result therefore changes solely because of an optimizer transformation. Expected result: ```text Empty set ``` Actual result with `derived_merge=on`: ```text +------+------+ | a | b | +------+------+ | NULL | NULL | +------+------+ ``` The execution plans also demonstrate the behavioral difference. With `derived_merge=on`: ```text Nested loop left join -> Rows fetched before execution -> Zero rows (Impossible filter) ``` With `derived_merge=off`: ```text Zero rows (no matching row in const table) ``` This is a logic bug because merging a derived table or CTE must not change the query result, and a null-rejecting `WHERE` predicate must not retain a NULL-complemented outer-join row. How to repeat: Run the following query using the default optimizer settings: ```sql SET optimizer_switch = 'default'; WITH cut AS ( SELECT r.a, r.b FROM (SELECT 1 AS x) AS l LEFT JOIN ( SELECT VERSION() AS a, NULL AS b FROM (SELECT 1 AS z) AS e WHERE FALSE ) AS r ON TRUE ) SELECT * FROM cut WHERE a <> 'sample'; ``` Actual result: ```text +------+------+ | a | b | +------+------+ | NULL | NULL | +------+------+ ``` Disable derived-table merging and execute the same query: ```sql SET optimizer_switch = 'derived_merge=off'; WITH cut AS ( SELECT r.a, r.b FROM (SELECT 1 AS x) AS l LEFT JOIN ( SELECT VERSION() AS a, NULL AS b FROM (SELECT 1 AS z) AS e WHERE FALSE ) AS r ON TRUE ) SELECT * FROM cut WHERE a <> 'sample'; ``` Expected and actual result with merging disabled: ```text Empty set ``` The predicate can also be evaluated directly to confirm the expected SQL semantics: ```sql SELECT NULL <> 'sample'; ``` Result: ```text NULL ``` The issue was originally detected in VECT pair 37. In that query, the unmodified query returned 2 rows, while three independently generated CTE cuts returned 9,992 rows. The affected candidates were two `join` candidates and one `atomic_predicate` candidate. All three differences disappeared when `derived_merge` was disabled.