Description:
`UNION`, `INTERSECT`, and `EXCEPT` use set semantics, so duplicates inside an
individual input branch cannot affect the final result. Nevertheless, MySQL
retains `DISTINCT` in both branches and performs temporary-table deduplication
before the set operation performs its own deduplication.
### Expected behaviour
Branch-level `DISTINCT` should be removed or absorbed into the set operation.
Queries with and without the branch-level `DISTINCT` should have equivalent
plans and comparable execution times.
### Actual behaviour
The plan contains a `Temporary table with deduplication` for each branch in
addition to `Union/Intersect/Except materialize with deduplication`.
Five alternating executions after warm-up produced:
| Operation | Without branch DISTINCT | With branch DISTINCT | Slowdown |
|---|---:|---:|---:|
| UNION | 54.44 ms | 86.00 ms | 1.58x |
| INTERSECT | 73.36 ms | 102.97 ms | 1.40x |
| EXCEPT | 74.64 ms | 103.12 ms | 1.38x |
How to repeat:
DROP DATABASE IF EXISTS mysql_set_branch_distinct;
CREATE DATABASE mysql_set_branch_distinct;
USE mysql_set_branch_distinct;
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, v INT);
CREATE TABLE rhs(id INT, v INT);
INSERT INTO lhs
SELECT n, MOD(n,1000) FROM (
SELECT a.d+b.d*10+c.d*100+d.d*1000+e.d*10000+1 AS n
FROM digits a,digits b,digits c,digits d,digits e
) numbers;
INSERT INTO rhs
SELECT n, MOD(n,1000) FROM (
SELECT a.d+b.d*10+c.d*100+d.d*1000+e.d*10000+50001 AS n
FROM digits a,digits b,digits c,digits d,digits e
) numbers;
ANALYZE TABLE lhs,rhs;
EXPLAIN FORMAT=TREE SELECT COUNT(*) FROM
((SELECT DISTINCT id FROM lhs) UNION (SELECT DISTINCT id FROM rhs)) s;
EXPLAIN FORMAT=TREE SELECT COUNT(*) FROM
((SELECT DISTINCT id FROM lhs) INTERSECT (SELECT DISTINCT id FROM rhs)) s;
EXPLAIN FORMAT=TREE SELECT COUNT(*) FROM
((SELECT DISTINCT id FROM lhs) EXCEPT (SELECT DISTINCT id FROM rhs)) s;
-- Compare each statement above with the corresponding form below.
SELECT COUNT(*) FROM ((SELECT id FROM lhs) UNION (SELECT id FROM rhs)) s;
SELECT COUNT(*) FROM
((SELECT DISTINCT id FROM lhs) UNION (SELECT DISTINCT id FROM rhs)) s;
SELECT COUNT(*) FROM ((SELECT id FROM lhs) INTERSECT (SELECT id FROM rhs)) s;
SELECT COUNT(*) FROM
((SELECT DISTINCT id FROM lhs) INTERSECT (SELECT DISTINCT id FROM rhs)) s;
SELECT COUNT(*) FROM ((SELECT id FROM lhs) EXCEPT (SELECT id FROM rhs)) s;
SELECT COUNT(*) FROM
((SELECT DISTINCT id FROM lhs) EXCEPT (SELECT DISTINCT id FROM rhs)) s;
```
