Description:
`A INTERSECT empty` is always empty. MySQL recognizes the `WHERE FALSE` branch
as `Zero rows`, but still scans and deduplicates the other 100,000-row input.
### Expected behaviour
The complete `INTERSECT` should be replaced with an empty result without
scanning `lhs`.
### Actual behaviour
```text
Intersect materialize with deduplication
├─ Table scan on lhs
└─ Zero rows (Impossible WHERE)
```
Seven alternating executions after warm-up:
```text
Original (`A INTERSECT empty`): 48.619 ms
Mutated (`A WHERE FALSE`): 0.431 ms
Original/mutated: 112.73x
```
MySQL does correctly fold `empty EXCEPT B` to a zero-row result. The missed
optimization reported here is specifically the absorbing empty input of
`INTERSECT`.
### Execution-plan evidence and decision
Both forms return `COUNT(*) = 0`, and the timing difference remained stable
over seven alternating executions. The plans prove that the original performs
work which cannot affect the result:
```text
Original Mutated
Aggregate Zero input rows (Impossible WHERE),
`- Intersect materialize aggregated into one output row
|- Table scan on lhs (~100000 rows)
`- Zero rows (Impossible WHERE)
```
Thus the 112.73x difference is backed by an unnecessary base-table scan, not
timing alone. `empty EXCEPT B` is already optimized to a zero-input aggregate
and is not part of this report.
How to repeat:
DROP DATABASE IF EXISTS mysql_empty_setop;
CREATE DATABASE mysql_empty_setop;
USE mysql_empty_setop;
CREATE TABLE digits(d INT PRIMARY KEY);
INSERT INTO digits VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
CREATE TABLE lhs(id INT);
CREATE TABLE rhs(id INT);
INSERT INTO lhs SELECT a.d+b.d*10+c.d*100+d.d*1000+e.d*10000+1
FROM digits a,digits b,digits c,digits d,digits e;
INSERT INTO rhs SELECT a.d+b.d*10+c.d*100+d.d*1000+e.d*10000+1
FROM digits a,digits b,digits c,digits d,digits e;
ANALYZE TABLE lhs,rhs;
EXPLAIN FORMAT=TREE
SELECT COUNT(*) FROM (
(SELECT id FROM lhs)
INTERSECT
(SELECT id FROM rhs WHERE FALSE)
) s;
EXPLAIN FORMAT=TREE
SELECT COUNT(*) FROM (SELECT id FROM lhs WHERE FALSE) s;
SELECT COUNT(*) FROM (
(SELECT id FROM lhs)
INTERSECT
(SELECT id FROM rhs WHERE FALSE)
) s;
-- Mutated query produced by removing the whole INTERSECT operation.
SELECT COUNT(*) FROM (SELECT id FROM lhs WHERE FALSE) s;
