Description:
Silent wrong result in the **default** configuration (`derived_condition_pushdown=on` is a default flag of `optimizer_switch`). Adding a `WHERE` clause to a derived table that returned no rows makes it return a row.
`derived_condition_pushdown`, which is enabled by default, copies an outer `WHERE`
predicate into **every branch** of a set operation inside a derived table. A set
operation matches and deduplicates rows using the **column collation**
(case-insensitive here), while the pushed-down predicate may use a **finer notion
of equality** — byte equality via `HEX()`, `CAST(... AS BINARY)`, or an explicit
`COLLATE ..._bin`. Evaluated inside a branch, such a predicate removes rows that
the set operation would have merged (`INTERSECT`) or subtracted with (`EXCEPT`),
so the result gains or loses rows. No error and no warning is raised.
```sql
DROP DATABASE IF EXISTS bugrep_mysql3;
CREATE DATABASE bugrep_mysql3;
USE bugrep_mysql3;
CREATE TABLE p1 (s VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci);
CREATE TABLE p2 (s VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci);
INSERT INTO p1 VALUES ('ABC'),('abc');
INSERT INTO p2 VALUES ('abc');
-- Step A: the derived table is empty. Under a case-insensitive collation both
-- rows of p1 are equal to the single row of p2, so EXCEPT removes both.
SELECT COUNT(*) AS n_unfiltered
FROM (SELECT s FROM p1 EXCEPT SELECT s FROM p2) q;
-- actual: 0 expected: 0
-- Step B: the same derived table with a WHERE clause on top -- BUG
SELECT COUNT(*) AS n_filtered
FROM (SELECT s FROM p1 EXCEPT SELECT s FROM p2) q
WHERE HEX(q.s) = HEX('ABC');
-- actual: 1 expected: 0 <== filtering increased the row count
SELECT q.s AS s, HEX(q.s) AS hex_s
FROM (SELECT s FROM p1 EXCEPT SELECT s FROM p2) q
WHERE HEX(q.s) = HEX('ABC');
-- actual: ('ABC', '414243') -- a row produced out of an empty relation
-- Step C: disabling the pushdown restores the correct answer
SET SESSION optimizer_switch = 'derived_condition_pushdown=off';
SELECT COUNT(*) AS n_filtered_pushdown_off
FROM (SELECT s FROM p1 EXCEPT SELECT s FROM p2) q
WHERE HEX(q.s) = HEX('ABC');
-- actual: 0 expected: 0
SET SESSION optimizer_switch = DEFAULT;
-- Step D: confirm the failing configuration is the default one
SELECT @@optimizer_switch LIKE '%derived_condition_pushdown=on%' AS default_is_on;
-- actual: 1
-- Step E: the INTERSECT direction loses a row (secondary evidence, see note)
SELECT HEX(s) AS intersect_rep
FROM (SELECT s FROM p1 INTERSECT SELECT s FROM p2) q;
-- actual: 414243 -- the engine's own representative for the matched pair
SELECT COUNT(*) AS n_intersect_filtered
FROM (SELECT s FROM p1 INTERSECT SELECT s FROM p2) q
WHERE HEX(q.s) = HEX('ABC');
-- actual: 0 expected: 1, given the representative reported above
```
Why this particular test is unambiguous:
Under a case-insensitive collation, MySQL does not specify **which** of two equal
strings a set operation returns as its representative. That makes "the
representative is `'ABC'` rather than `'abc'`" unusable as a correctness criterion
on its own — a reviewer could reasonably call the choice implementation-defined.
Step E is therefore listed as secondary evidence only.
Steps A–C deliberately avoid the ambiguity. `EXCEPT` here must produce the
**empty set**, because every row of `p1` is equal to the row of `p2` under
`utf8mb4_general_ci`, so there is no representative left to choose; the engine
agrees and returns 0 in step A. And applying a `WHERE` clause to an empty relation
can only yield the empty relation, for any predicate whatsoever. Observing the
count go from 0 to 1 when a filter is added violates predicate monotonicity, which
holds regardless of how representative values are chosen. There is no reading of
the standard or of the MySQL manual under which 1 is an acceptable answer to
step B.
Predicate dependence — only predicates with a finer equality notion break. All
measured on the step-A/B `EXCEPT` query, where the correct answer is 0 in every
row of the table:
| Predicate | pushdown on (default) | pushdown off |
|---|---|---|
| `HEX(q.s) = HEX('ABC')` | **1** | 0 |
| `CAST(q.s AS BINARY) = _binary'ABC'` | **1** | 0 |
| `q.s COLLATE utf8mb4_bin = 'ABC'` | **1** | 0 |
| `q.s = 'ABC'` (same ci collation as the column) | 0 | 0 |
A predicate that uses the same equality notion as the set operation is safe;
predicates that distinguish values the set operation treats as equal are not.
How to repeat:
Measured on MySQL 9.7.1 (Homebrew, macOS 26.4, arm64), default server configuration and default `optimizer_switch`. The script above is self-contained;
run it with `mysql -h 127.0.0.1 -u root --table < file`. Failure observation: step A returns 0 and step B returns 1 — adding a `WHERE` clause to a query that returned no rows produced a row. Step C shows that the single change `derived_condition_pushdown=off` restores 0. `SHOW WARNINGS` is empty throughout.
Suggested fix:
We suspect the pushdown decision does not check whether the predicate's notion of equality agrees with the equality used by the layer it crosses. A set operation groups by the column collation (`utf8mb4_general_ci`), whereas `HEX(...)`, `CAST(... AS BINARY)` and `COLLATE utf8mb4_bin` discriminate at byte level, so the
predicate is not constant on a collation-equality class and cannot be evaluated before the grouping. A predicate would appear safe to push across a set operation only if it is invariant under that operation's equality. This matches the safety condition PostgreSQL adopted in commit 44fb59fc60 (2026-07-06, back-patched to v18): a qual may be pushed through a grouping layer only if its notion of equality agrees with the layer's. This is an inference from the plans and the observed results, not from source inspection.
Description: Silent wrong result in the **default** configuration (`derived_condition_pushdown=on` is a default flag of `optimizer_switch`). Adding a `WHERE` clause to a derived table that returned no rows makes it return a row. `derived_condition_pushdown`, which is enabled by default, copies an outer `WHERE` predicate into **every branch** of a set operation inside a derived table. A set operation matches and deduplicates rows using the **column collation** (case-insensitive here), while the pushed-down predicate may use a **finer notion of equality** — byte equality via `HEX()`, `CAST(... AS BINARY)`, or an explicit `COLLATE ..._bin`. Evaluated inside a branch, such a predicate removes rows that the set operation would have merged (`INTERSECT`) or subtracted with (`EXCEPT`), so the result gains or loses rows. No error and no warning is raised. ```sql DROP DATABASE IF EXISTS bugrep_mysql3; CREATE DATABASE bugrep_mysql3; USE bugrep_mysql3; CREATE TABLE p1 (s VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci); CREATE TABLE p2 (s VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci); INSERT INTO p1 VALUES ('ABC'),('abc'); INSERT INTO p2 VALUES ('abc'); -- Step A: the derived table is empty. Under a case-insensitive collation both -- rows of p1 are equal to the single row of p2, so EXCEPT removes both. SELECT COUNT(*) AS n_unfiltered FROM (SELECT s FROM p1 EXCEPT SELECT s FROM p2) q; -- actual: 0 expected: 0 -- Step B: the same derived table with a WHERE clause on top -- BUG SELECT COUNT(*) AS n_filtered FROM (SELECT s FROM p1 EXCEPT SELECT s FROM p2) q WHERE HEX(q.s) = HEX('ABC'); -- actual: 1 expected: 0 <== filtering increased the row count SELECT q.s AS s, HEX(q.s) AS hex_s FROM (SELECT s FROM p1 EXCEPT SELECT s FROM p2) q WHERE HEX(q.s) = HEX('ABC'); -- actual: ('ABC', '414243') -- a row produced out of an empty relation -- Step C: disabling the pushdown restores the correct answer SET SESSION optimizer_switch = 'derived_condition_pushdown=off'; SELECT COUNT(*) AS n_filtered_pushdown_off FROM (SELECT s FROM p1 EXCEPT SELECT s FROM p2) q WHERE HEX(q.s) = HEX('ABC'); -- actual: 0 expected: 0 SET SESSION optimizer_switch = DEFAULT; -- Step D: confirm the failing configuration is the default one SELECT @@optimizer_switch LIKE '%derived_condition_pushdown=on%' AS default_is_on; -- actual: 1 -- Step E: the INTERSECT direction loses a row (secondary evidence, see note) SELECT HEX(s) AS intersect_rep FROM (SELECT s FROM p1 INTERSECT SELECT s FROM p2) q; -- actual: 414243 -- the engine's own representative for the matched pair SELECT COUNT(*) AS n_intersect_filtered FROM (SELECT s FROM p1 INTERSECT SELECT s FROM p2) q WHERE HEX(q.s) = HEX('ABC'); -- actual: 0 expected: 1, given the representative reported above ``` Why this particular test is unambiguous: Under a case-insensitive collation, MySQL does not specify **which** of two equal strings a set operation returns as its representative. That makes "the representative is `'ABC'` rather than `'abc'`" unusable as a correctness criterion on its own — a reviewer could reasonably call the choice implementation-defined. Step E is therefore listed as secondary evidence only. Steps A–C deliberately avoid the ambiguity. `EXCEPT` here must produce the **empty set**, because every row of `p1` is equal to the row of `p2` under `utf8mb4_general_ci`, so there is no representative left to choose; the engine agrees and returns 0 in step A. And applying a `WHERE` clause to an empty relation can only yield the empty relation, for any predicate whatsoever. Observing the count go from 0 to 1 when a filter is added violates predicate monotonicity, which holds regardless of how representative values are chosen. There is no reading of the standard or of the MySQL manual under which 1 is an acceptable answer to step B. Predicate dependence — only predicates with a finer equality notion break. All measured on the step-A/B `EXCEPT` query, where the correct answer is 0 in every row of the table: | Predicate | pushdown on (default) | pushdown off | |---|---|---| | `HEX(q.s) = HEX('ABC')` | **1** | 0 | | `CAST(q.s AS BINARY) = _binary'ABC'` | **1** | 0 | | `q.s COLLATE utf8mb4_bin = 'ABC'` | **1** | 0 | | `q.s = 'ABC'` (same ci collation as the column) | 0 | 0 | A predicate that uses the same equality notion as the set operation is safe; predicates that distinguish values the set operation treats as equal are not. How to repeat: Measured on MySQL 9.7.1 (Homebrew, macOS 26.4, arm64), default server configuration and default `optimizer_switch`. The script above is self-contained; run it with `mysql -h 127.0.0.1 -u root --table < file`. Failure observation: step A returns 0 and step B returns 1 — adding a `WHERE` clause to a query that returned no rows produced a row. Step C shows that the single change `derived_condition_pushdown=off` restores 0. `SHOW WARNINGS` is empty throughout. Suggested fix: We suspect the pushdown decision does not check whether the predicate's notion of equality agrees with the equality used by the layer it crosses. A set operation groups by the column collation (`utf8mb4_general_ci`), whereas `HEX(...)`, `CAST(... AS BINARY)` and `COLLATE utf8mb4_bin` discriminate at byte level, so the predicate is not constant on a collation-equality class and cannot be evaluated before the grouping. A predicate would appear safe to push across a set operation only if it is invariant under that operation's equality. This matches the safety condition PostgreSQL adopted in commit 44fb59fc60 (2026-07-06, back-patched to v18): a qual may be pushed through a grouping layer only if its notion of equality agrees with the layer's. This is an inference from the plans and the observed results, not from source inspection.