Description

`condition_pushdown_for_derived` pushes an outer `WHERE` predicate into each branch of a set operation inside a derived table. The set operation itself deduplicates and matches rows using the *column collation, but the pushed-down predicate is evaluated *before the set operation using its own, byte-exact semantics. When the column has a case-insensitive collation, the two disagree about which rows are "the same row", so pushing the predicate below the set operation is not semantics-preserving.

*Why the test uses `EXCEPT` and not `INTERSECT`.* Under a case-insensitive collation, `

{'ABC','abc'} INTERSECT {'abc'}` is a one-row result whose representative byte string is not specified by the standard — an implementation may legitimately return either `'ABC'` or `'abc'`. So an unexpected representative cannot by itself be called a bug. `EXCEPT` avoids that entirely:

- Under `utf8mb4_general_ci`, both rows of `p1` are equal to the single row of `p2`, so `{'ABC','abc'}

EXCEPT

{'abc'}

` is the *empty set* — there is no representative to choose, and MariaDB agrees (it returns 0 rows).

• Applying *any* `WHERE` clause to the empty set must still yield the empty set. This is monotonicity of selection, and it holds for every predicate, every collation and every implementation.

MariaDB violates that: the unfiltered derived table has 0 rows, and adding `WHERE HEX(q.s) = HEX('ABC')` makes it return 1 row.

Minimal Reproduction

```sql
DROP DATABASE IF EXISTS bugrep_mariadb;
CREATE DATABASE bugrep_mariadb;
USE bugrep_mariadb;

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');

– (1) The EXCEPT result is empty, which is correct.
SELECT 'unfiltered' AS q, COUNT AS n
FROM (SELECT s FROM p1 EXCEPT SELECT s FROM p2) q;

– (2) Applying a WHERE clause to an empty set must still yield an empty set.
SELECT 'WHERE HEX(s)=HEX(''ABC'')' AS q, COUNT AS n
FROM (SELECT s FROM p1 EXCEPT SELECT s FROM p2) q
WHERE HEX(q.s) = HEX('ABC');

SELECT 'WHERE HEX(s)=HEX(''abc'')' AS q, COUNT AS n
FROM (SELECT s FROM p1 EXCEPT SELECT s FROM p2) q
WHERE HEX(q.s) = HEX('abc');

– (3) Disabling the pushdown restores the correct answer.
SET SESSION optimizer_switch = 'condition_pushdown_for_derived=off';
SELECT 'pushdown off' AS q, COUNT AS n
FROM (SELECT s FROM p1 EXCEPT SELECT s FROM p2) q
WHERE HEX(q.s) = HEX('ABC');
SET SESSION optimizer_switch = DEFAULT;

EXPLAIN FORMAT=JSON
SELECT COUNT FROM (SELECT s FROM p1 EXCEPT SELECT s FROM p2) q
WHERE HEX(q.s) = HEX('ABC')\G
```

Expected Result

All four counts are 0. Query (1) establishes that the derived table is empty, and the filtered queries can only ever return a subset of it.

Actual Result

Measured on MariaDB 12.3.2:

```
-------------+

-------------+

--------------------------+

--------------------------+

--------------------------+
---------------+

---------------+

`WHERE 1 = 1` on the same derived table returns 0, as it must; the anomaly requires a predicate that is actually pushed down.

The `INTERSECT` form is affected by the same mechanism, although (per the note above) it is not on its own a sound test: `SELECT HEX(s) FROM (SELECT s FROM p1 INTERSECT SELECT s FROM p2) q` returns `414243` (`'ABC'`), yet `... WHERE HEX(q.s)=HEX('ABC')` returns 0 rows and `... WHERE HEX(q.s)=HEX('abc')` returns 1 row.

Cross-Engine Comparison

The `EXCEPT` monotonicity test above, run on the same machine. PostgreSQL uses a non-deterministic ICU collation (`provider = icu, locale = 'und-u-ks-level2', deterministic = false`) and `s COLLATE "C" = 'ABC'` as the byte-exact predicate; DuckDB uses `COLLATE NOCASE` and `hex(s)`.

PostgreSQL 18.4 does not push the predicate below `EXCEPT` (`EXPLAIN` shows `Filter` on a `Subquery Scan` above `HashSetOp Except`), so the monotonicity test passes. It does still push into the branches of `INTERSECT`: on the same data, `SELECT s COLLATE "C"` from the `INTERSECT` derived table returns `ABC`, while filtering on `s COLLATE "C" = 'ABC'` returns 0 rows and `= 'abc'` returns 1, and `EXPLAIN` shows `Filter: ((s)::text = 'ABC'::text)` under both `Seq Scan`s. That is the same semantic constraint, and it is addressed upstream by PostgreSQL commit [44fb59fc60](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=44fb59fc60) ("Fix qual pushdown past grouping with mismatched equivalence", Richard Guo, 2026-07-06), which our local 18.4 build predates. That commit's rationale applies directly here: a qual must not be moved across a grouping boundary when "the moved clause's equivalence relation disagrees with the grouping's", and it explicitly names non-deterministic collations as one of the two forms of disagreement.

Related prior art on the MariaDB side, same family (collation-aware equality versus byte-wise equality inside an optimization): MDEV-34417(https://jira.mariadb.org/browse/MDEV-34417) "Wrong result set with utf8mb4_danish_ci and BNLH join" (closed, fixed). The present report concerns a different optimization (derived-table condition pushdown across a set operation), not BNLH join buffering.

Plan / Activation Evidence

`EXPLAIN FORMAT=JSON` for the failing query shows the predicate attached to *both* branches of the `EXCEPT`, in addition to the derived table itself:

```json
"table_name": "<derived2>",
"attached_condition": "hex(q.s) = <cache>(hex('ABC'))",
"materialized": { "query_block": { "union_result": {
"table_name": "<except2,3>",
"query_specifications": [
{ "query_block": { "select_id": 2,
"table":

{ "table_name": "p1", "attached_condition": "hex(p1.s) = <cache>(hex('ABC'))" }

} },
{ "query_block": { "select_id": 3, "operation": "EXCEPT",
"table":

{ "table_name": "p2", "attached_condition": "hex(p2.s) = <cache>(hex('ABC'))" }

} }
] } } }
```

With the predicate applied inside branch 2, `p1` yields only `'ABC'`; inside branch 3, `p2` yields nothing; so `EXCEPT` produces one row instead of none.

L1 switch differential (measured):

So on MariaDB the effective switch is *`condition_pushdown_for_derived`*; `condition_pushdown_from_having` has no effect on this case.

Root Cause Analysis

Hypothesis. The pushdown check that decides whether an outer predicate may be moved into the branches of a set operation verifies that the predicate depends only on columns of the derived table's select list, but does not verify that the predicate's notion of equality agrees with the collation used by the set operation to identify duplicate rows. Under a case-insensitive collation, `EXCEPT` and `INTERSECT` treat `'ABC'` and `'abc'` as the same row, while a byte-sensitive predicate such as `HEX(s) = HEX('ABC')` distinguishes them. Filtering before the set operation therefore removes rows that would have participated in the duplicate-matching, changing which rows survive. The safe condition is the one PostgreSQL's fix states: the predicate may cross the set-operation boundary only if its equivalence relation is at least as coarse as the one the set operation groups by.

Note that the pushed predicate need not mention the collation explicitly to be unsafe — any predicate that can distinguish two values the collation considers equal (here, via `HEX()`) is enough.

Workaround

• `SET SESSION optimizer_switch = 'condition_pushdown_for_derived=off';` — verified to restore the correct answer for this case. This is a broad switch and will disable a generally useful optimization.
• Interpose an optimization barrier around the set operation, e.g. `... FROM (SELECT s FROM (<set operation>) q0 ORDER BY s LIMIT <large>) q WHERE ...`, which returned 0 (correct) in our test.
• A plain (non-materialized) CTE is *not* a workaround: `WITH q AS (SELECT s FROM p1 EXCEPT SELECT s FROM p2) SELECT COUNT FROM q WHERE HEX(q.s)=HEX('ABC')` still returns 1.

Environment

```
Server: MariaDB 12.3.2-MariaDB (Homebrew), 127.0.0.1:3307
OS: macOS (darwin 25.5.0, arm64)
sql_mode: STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
Column collation: utf8mb4_general_ci (also the server default here)
optimizer_switch: server default, unmodified (condition_pushdown_for_derived=on)
```