Description:
## Version
MySQL **9.7.2** (`mysql:9.7.2` Docker, `SELECT VERSION()` = `9.7.2`). Reproduced 3/3.
## What's Wrong?
`2 IN (1, NULL, 3)` is SQL **UNKNOWN** (no equal match, but the set contains NULL). The value-list form is correct. The same predicate with a **table subquery**, evaluated as a **projection column**, is rewritten as a dependent `EXISTS` with
```
(outer ⊕ inner) OR (inner IS NULL)
```
`EXISTS` is two-valued, so a NULL in the subquery makes the predicate **TRUE** for every non-NULL outer value that does not match. The WHERE / `in_optimizer` path uses equality-only `EXISTS` (NULL treated like FALSE for filtering), so it drops those rows.
DQR relocates `WHERE col IN (SELECT…)` into a derived-table boolean column. The two equivalent queries disagree. Paper NoREC does not: `SUM((p) IS TRUE)` uses a different subquery plan and agrees with WHERE.
| Form | `2 IN (SELECT c0 FROM t1)` with `t1 = {1, NULL, 3}` | Verdict |
|---|---|---|
| Value list `2 IN (1, NULL, 3)` | NULL | correct |
| `WHERE c0 IN (SELECT…)` | row `2` dropped | coincidentally same as UNKNOWN-in-WHERE |
| Projection `(c0 IN (SELECT…)) AS ref1 … WHERE ref1` | row `2` **kept** | **wrong** (UNKNOWN became TRUE) |
| Paper NoREC `SUM((p) IS TRUE)` | 1 | agrees with WHERE |
The same projection rewrite (EXPLAIN: `OR (i.c0 is null)`) is used for `= ANY`, `< ANY`, `> ANY`, `<= ANY`, `>= ANY`. `<op> ALL` / `<> ANY` share the rewrite; they do not always *diverge* on this three-row INT example (ANY-of-unequal is already TRUE; ALL-of-NULL is already FALSE in WHERE).
How to repeat:
## How to Reproduce?
```sql
CREATE TABLE t0(c0 INT);
CREATE TABLE t1(c0 INT);
INSERT INTO t0 VALUES (1), (2), (NULL);
INSERT INTO t1 VALUES (1), (NULL), (3);
-- Q1 WHERE: one row (the matching 1)
SELECT c0 FROM t0 WHERE c0 IN (SELECT c0 FROM t1);
-- 1
-- Q2 derived-table boolean (semantically equivalent): two rows
SELECT ref0 FROM (
SELECT c0 AS ref0,
(c0 IN (SELECT c0 FROM t1)) AS ref1
FROM t0
) AS s WHERE ref1;
-- 1
-- 2 <-- WRONG
-- Controls
SELECT (2 IN (1, NULL, 3)); -- NULL (correct)
SELECT COUNT(*) FROM t0 WHERE (c0 IN (SELECT c0 FROM t1)); -- 1
SELECT COALESCE(SUM(c), 0) FROM (
SELECT ((c0 IN (SELECT c0 FROM t1)) IS TRUE) AS c FROM t0
) s; -- 1 (NoREC agrees)
-- Removing the inner NULL makes Q1 and Q2 agree (both 1).
```
`EXPLAIN` of Q2 (abridged):
```
Filter: (0 <> s.ref1)
Materialize
Select #3 (subquery in projection; dependent)
Filter: ((<cache>(t0.c0) = t1.c0) OR (t1.c0 IS NULL))
```
`EXPLAIN` of Q1 uses `<in_optimizer>` / equality-only `EXISTS`, without `OR inner IS NULL`.
© 2026, Oracle Corporation and/or its affiliates
Description: ## Version MySQL **9.7.2** (`mysql:9.7.2` Docker, `SELECT VERSION()` = `9.7.2`). Reproduced 3/3. ## What's Wrong? `2 IN (1, NULL, 3)` is SQL **UNKNOWN** (no equal match, but the set contains NULL). The value-list form is correct. The same predicate with a **table subquery**, evaluated as a **projection column**, is rewritten as a dependent `EXISTS` with ``` (outer ⊕ inner) OR (inner IS NULL) ``` `EXISTS` is two-valued, so a NULL in the subquery makes the predicate **TRUE** for every non-NULL outer value that does not match. The WHERE / `in_optimizer` path uses equality-only `EXISTS` (NULL treated like FALSE for filtering), so it drops those rows. DQR relocates `WHERE col IN (SELECT…)` into a derived-table boolean column. The two equivalent queries disagree. Paper NoREC does not: `SUM((p) IS TRUE)` uses a different subquery plan and agrees with WHERE. | Form | `2 IN (SELECT c0 FROM t1)` with `t1 = {1, NULL, 3}` | Verdict | |---|---|---| | Value list `2 IN (1, NULL, 3)` | NULL | correct | | `WHERE c0 IN (SELECT…)` | row `2` dropped | coincidentally same as UNKNOWN-in-WHERE | | Projection `(c0 IN (SELECT…)) AS ref1 … WHERE ref1` | row `2` **kept** | **wrong** (UNKNOWN became TRUE) | | Paper NoREC `SUM((p) IS TRUE)` | 1 | agrees with WHERE | The same projection rewrite (EXPLAIN: `OR (i.c0 is null)`) is used for `= ANY`, `< ANY`, `> ANY`, `<= ANY`, `>= ANY`. `<op> ALL` / `<> ANY` share the rewrite; they do not always *diverge* on this three-row INT example (ANY-of-unequal is already TRUE; ALL-of-NULL is already FALSE in WHERE). How to repeat: ## How to Reproduce? ```sql CREATE TABLE t0(c0 INT); CREATE TABLE t1(c0 INT); INSERT INTO t0 VALUES (1), (2), (NULL); INSERT INTO t1 VALUES (1), (NULL), (3); -- Q1 WHERE: one row (the matching 1) SELECT c0 FROM t0 WHERE c0 IN (SELECT c0 FROM t1); -- 1 -- Q2 derived-table boolean (semantically equivalent): two rows SELECT ref0 FROM ( SELECT c0 AS ref0, (c0 IN (SELECT c0 FROM t1)) AS ref1 FROM t0 ) AS s WHERE ref1; -- 1 -- 2 <-- WRONG -- Controls SELECT (2 IN (1, NULL, 3)); -- NULL (correct) SELECT COUNT(*) FROM t0 WHERE (c0 IN (SELECT c0 FROM t1)); -- 1 SELECT COALESCE(SUM(c), 0) FROM ( SELECT ((c0 IN (SELECT c0 FROM t1)) IS TRUE) AS c FROM t0 ) s; -- 1 (NoREC agrees) -- Removing the inner NULL makes Q1 and Q2 agree (both 1). ``` `EXPLAIN` of Q2 (abridged): ``` Filter: (0 <> s.ref1) Materialize Select #3 (subquery in projection; dependent) Filter: ((<cache>(t0.c0) = t1.c0) OR (t1.c0 IS NULL)) ``` `EXPLAIN` of Q1 uses `<in_optimizer>` / equality-only `EXISTS`, without `OR inner IS NULL`.