Description

Double negation is an identity transformation: `NOT NOT P` must select exactly the rows that `P` selects, for any predicate `P`. For quantified comparison predicates (`<op> ANY|SOME|ALL (subquery)`) MariaDB evaluates the un-negated form and the singly negated form correctly, but not the doubly negated form.

For `ALL`, `NOT NOT P` returns the rows of `NOT P` — the second negation is simply lost. For `ANY` the outcome is worse than a sign error: `NOT NOT c > ANY (SELECT c FROM qo)` returns *every* row of the outer table, so the predicate has degenerated into a tautology. In a `WHERE` clause on a large table that means the query silently returns the whole table.

Minimal Reproduction

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

CREATE TABLE q (c INT);
CREATE TABLE qo (c INT);
INSERT INTO q VALUES (1),(2),(3);
INSERT INTO qo VALUES (2),(5);

– ALL form. c > ALL (2) <=> c > 2 <=>

{3}
SELECT 'baseline ' AS f, GROUP_CONCAT(c ORDER BY c) AS r FROM q WHERE c > ALL (SELECT c FROM qo WHERE c < 5);
SELECT 'NOT ' AS f, GROUP_CONCAT(c ORDER BY c) AS r FROM q WHERE NOT c > ALL (SELECT c FROM qo WHERE c < 5);
SELECT 'NOT NOT ' AS f, GROUP_CONCAT(c ORDER BY c) AS r FROM q WHERE NOT NOT c > ALL (SELECT c FROM qo WHERE c < 5);
SELECT 'NOT(NOT(...)) ' AS f, GROUP_CONCAT(c ORDER BY c) AS r FROM q WHERE NOT (NOT ( c > ALL (SELECT c FROM qo WHERE c < 5)));
SELECT 'NOT NOT NOT ' AS f, GROUP_CONCAT(c ORDER BY c) AS r FROM q WHERE NOT NOT NOT c > ALL (SELECT c FROM qo WHERE c < 5);
SELECT 'NOT NOT NOT NOT' AS f, GROUP_CONCAT(c ORDER BY c) AS r FROM q WHERE NOT NOT NOT NOT c > ALL (SELECT c FROM qo WHERE c < 5);

– ANY form. c > ANY (2,5) <=> c > 2 <=> {3}

SELECT 'ANY baseline ' AS f, GROUP_CONCAT(c ORDER BY c) AS r FROM q WHERE c > ANY (SELECT c FROM qo);
SELECT 'ANY NOT ' AS f, GROUP_CONCAT(c ORDER BY c) AS r FROM q WHERE NOT c > ANY (SELECT c FROM qo);
SELECT 'ANY NOT NOT ' AS f, GROUP_CONCAT(c ORDER BY c) AS r FROM q WHERE NOT NOT c > ANY (SELECT c FROM qo);
SELECT 'SOME NOT NOT ' AS f, GROUP_CONCAT(c ORDER BY c) AS r FROM q WHERE NOT NOT c > SOME (SELECT c FROM qo);

– other operators
SELECT '< ALL baseline' AS f, GROUP_CONCAT(c ORDER BY c) AS r FROM q WHERE c < ALL (SELECT c FROM qo);
SELECT '< ALL NOT NOT ' AS f, GROUP_CONCAT(c ORDER BY c) AS r FROM q WHERE NOT NOT c < ALL (SELECT c FROM qo);
SELECT '>= ALL baseline' AS f, GROUP_CONCAT(c ORDER BY c) AS r FROM q WHERE c >= ALL (SELECT c FROM qo WHERE c < 5);
SELECT '>= ALL NOT NOT ' AS f, GROUP_CONCAT(c ORDER BY c) AS r FROM q WHERE NOT NOT c >= ALL (SELECT c FROM qo WHERE c < 5);

– reference-free oracle: the standard aggregate rewrites of ALL / ANY
SELECT '> MAX (= > ALL)' AS f, GROUP_CONCAT(c ORDER BY c) AS r FROM q WHERE c > (SELECT MAX(c) FROM qo WHERE c < 5);
SELECT '> MIN (= > ANY)' AS f, GROUP_CONCAT(c ORDER BY c) AS r FROM q WHERE c > (SELECT MIN(c) FROM qo);

EXPLAIN EXTENDED SELECT c FROM q WHERE NOT NOT c > ALL (SELECT c FROM qo WHERE c < 5);
SHOW WARNINGS;
EXPLAIN EXTENDED SELECT c FROM q WHERE NOT NOT c > ANY (SELECT c FROM qo);
SHOW WARNINGS;
```

Expected Result

`NOT NOT P` must equal `P`, and `NOT NOT NOT P` must equal `NOT P`:

```
baseline 3 NOT NOT 3
NOT 1,2 NOT NOT NOT 1,2
NOT NOT NOT NOT 3
ANY baseline 3 ANY NOT NOT 3 SOME NOT NOT 3
< ALL baseline 1 < ALL NOT NOT 1
>= ALL baseline 2,3 >= ALL NOT NOT 2,3
```

Actual Result

Measured on MariaDB 12.3.2 (`NULL` in the `r` column means the query returned no rows):

```

The last two lines are a reference-free oracle: `c > ALL S` is equivalent to `c > (SELECT MAX(c) FROM S)` and `c > ANY S` to `c > (SELECT MIN(c) FROM S)`. MariaDB evaluates both aggregate forms as `3`, so it contradicts its own `NOT NOT` results without any reference engine being involved.

Applying a third `NOT` returns `1,2` again — the answer stops changing after the first negation. This is consistent with the negation being folded into the predicate in a way that is not idempotent-safe rather than with a simple parity mistake.

1. Cross-Engine Comparison

Same data, same statements, measured on the same machine (PostgreSQL and DuckDB use `string_agg`/row lists rather than `GROUP_CONCAT`, results transcribed to the same notation):

MariaDB is the only engine of the five that is wrong on any of these. Entries marked `—` were not run on that engine.

Plan / Activation Evidence

`EXPLAIN EXTENDED` + `SHOW WARNINGS` prints the rewritten predicate, which shows the transformation directly.

`NOT NOT c > ALL (SELECT c FROM qo WHERE c < 5)` is rewritten to:

```
select `q`.`c` from `q`
where <not>(<in_optimizer>(`q`.`c`,
<min>(select `qo`.`c` from `qo` where `qo`.`c` < 5) < <cache>(`q`.`c`)))
```

The correct un-negated form of `c > ALL S` is `MAX(S) < c`. The rewritten predicate has `MIN(S) < c` (the quantifier was flipped `ALL` → `ANY`) and still carries a `<not>` wrapper — i.e. one negation was consumed by flipping the quantifier and the other was left in place, so the two `NOT`s do not cancel. Evaluating it: `NOT (2 < c)` = `c <= 2` = `

{1,2}

`, which matches the observed output.

`NOT NOT c > ANY (SELECT c FROM qo)` is rewritten to:

```
select `q`.`c` from `q`
where <nop>(<in_optimizer>(`q`.`c`,
(select max(`qo`.`c`) from `qo`) >= <cache>(`q`.`c`)))
```

Here the comparison operator itself has been changed from `>` to `>=` and the quantifier from `ANY` to `ALL` (`max`), while the outer wrapper became `<nop>` (no-op). Evaluating it: `MAX(qo.c) >= c` = `5 >= c`, true for every row of `q` — exactly the observed `1,2,3`. This is why the `ANY` form degenerates into a tautology rather than merely returning the complement.

The bug reproduces in the default configuration; we did not find any `optimizer_switch` setting that avoids it.

Root Cause Analysis

Hypothesis. When `NOT` is applied to a quantified comparison predicate, the negation is pushed into the predicate by a truth transformer. The correct transformation flips only the *quantification polarity* (`ANY` ↔ `ALL`, i.e. the `not_all` flag and the `MIN`/`MAX` choice), because `NOT (c > ALL S)` is `c <= ANY S` — the comparison operator is negated as a consequence of, not in addition to, the polarity flip. MariaDB's implementation appears to flip both the quantification polarity and the comparison operator, which makes the transformation self-inverse in a way that the second `NOT` cannot undo: applying it twice returns to the once-negated state instead of the original, which is exactly what the `EXPLAIN EXTENDED` output above shows and exactly what the third-`NOT` result confirms.

MySQL's fix for the same symptom removed a duplicated call to the comparison-function creator inside the `truth_transformer()` of the `Item_func_nop_all` / `Item_func_not_all` classes; the `<nop>` and `<not>` wrappers visible in MariaDB's rewritten predicates above are the same class pair.

Upstream Reference

• *MySQL Bug#37004689* — "Quantified comparison predicate and NOT NOT gives wrong result", fixed by commit [12c74d2bc4](https://github.com/mysql/mysql-server/commit/12c74d2bc4) (commit title verified: `Bug#37004689: Quantified comparison predicate and NOT NOT gives wrong…`). MySQL 9.7.1 on this machine is correct on every form in the table above.

Fixed-status contrast measured on this machine:

Workaround

Rewrite the quantified predicate using the equivalent aggregate subquery, which is evaluated correctly:

• `c > ALL (SELECT c FROM qo WHERE c < 5)` → `c > (SELECT MAX(c) FROM qo WHERE c < 5)`
• `c > ANY (SELECT c FROM qo)` → `c > (SELECT MIN(c) FROM qo)`

(These rewrites are only equivalent when the subquery is non-empty and NULL-free; the general forms need `IS NOT NULL` handling.) Alternatively, hand-simplify the double negation out of the query text before sending it — but that is not available to applications that build predicates programmatically, which is the realistic way a `NOT NOT` ends up in a statement. There is no configuration-level workaround.

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
optimizer_switch: server default, unmodified
```