Description:
A comparison predicate against a quantified subquery (`>= ALL`, `<= ALL`, `> ALL`,
`< ANY`, `> ANY`) that evaluates to TRUE returns no rows when it is wrapped in an
even number of `NOT` operators in a WHERE clause.
`NOT(NOT P)` is a strict identity under SQL three-valued logic:
| P | `NOT(NOT P)` |
|---|---|
| TRUE | TRUE |
| FALSE | FALSE |
| NULL | NULL (WHERE treats both as non-matching) |
So `WHERE P` and `WHERE NOT(NOT P)` must select the same rows. They do not when P
is a quantified comparison subquery.
The subquery column contains no NULL values, so three-valued logic is not involved.
No error or warning is raised — the query silently returns the wrong result set.
Scope observed:
- Affected operators: `>= ALL`, `<= ALL`, `> ALL`, `< ANY`, `> ANY`
- NOT affected: `<> ALL` (which is rewritten via the NOT IN path)
- Wrapping without negation (`TRUE AND P`, `FALSE OR P`) behaves correctly
- Replacing the quantified subquery with an equivalent scalar `MAX()` subquery
behaves correctly
Reproduced on 8.0.13, 8.0.18, 8.0.45 and 8.0.46 (all official community builds).
How to repeat:
```sql
CREATE DATABASE bugtest;
USE bugtest;
CREATE TABLE a (x int);
INSERT INTO a VALUES (1),(2),(3);
CREATE TABLE b (y int);
INSERT INTO b VALUES (5),(6);
-- The predicate evaluates to TRUE (10 is >= every value in b)
SELECT (10 >= ALL (SELECT y FROM b)) AS p;
-- returns 1
-- Bare predicate in WHERE: correct, matches all 3 rows
SELECT COUNT(*) FROM a WHERE 10 >= ALL (SELECT y FROM b);
-- returns 3 (expected 3)
-- Same predicate under double negation: returns 0
SELECT COUNT(*) FROM a WHERE NOT(NOT (10 >= ALL (SELECT y FROM b)));
-- returns 0 (expected 3) <-- WRONG
```
### Other affected operators (each returns 0 instead of 3)
```sql
SELECT COUNT(*) FROM a WHERE NOT(NOT (10 >= ALL (SELECT y FROM b))); -- expected 3, got 0
SELECT COUNT(*) FROM a WHERE NOT(NOT ( 1 <= ALL (SELECT y FROM b))); -- expected 3, got 0
SELECT COUNT(*) FROM a WHERE NOT(NOT (10 > ALL (SELECT y FROM b))); -- expected 3, got 0
SELECT COUNT(*) FROM a WHERE NOT(NOT ( 1 < ANY (SELECT y FROM b))); -- expected 3, got 0
SELECT COUNT(*) FROM a WHERE NOT(NOT (10 > ANY (SELECT y FROM b))); -- expected 3, got 0
```
### Controls that behave correctly
```sql
-- <> ALL is not affected
SELECT COUNT(*) FROM a WHERE 10 <> ALL (SELECT y FROM b); -- 3
SELECT COUNT(*) FROM a WHERE NOT(NOT (10 <> ALL (SELECT y FROM b))); -- 3 correct
-- Equivalent scalar MAX() subquery is not affected
SELECT COUNT(*) FROM a WHERE 10 >= (SELECT MAX(y) FROM b); -- 3
SELECT COUNT(*) FROM a WHERE NOT(NOT (10 >= (SELECT MAX(y) FROM b))); -- 3 correct
-- Wrapping without negation is not affected
SELECT COUNT(*) FROM a WHERE TRUE AND (10 >= ALL (SELECT y FROM b)); -- 3 correct
SELECT COUNT(*) FROM a WHERE FALSE OR (10 >= ALL (SELECT y FROM b)); -- 3 correct
-- Deeper wrappings that contain an even number of NOTs are also affected
SELECT COUNT(*) FROM a WHERE NOT(FALSE OR NOT (10 >= ALL (SELECT y FROM b))); -- expected 3, got 0
SELECT COUNT(*) FROM a WHERE NOT(FALSE OR NOT(TRUE AND (10 >= ALL (SELECT y FROM b)))); -- expected 3, got 0
-- No NULLs in the subquery column
SELECT COUNT(*) AS total, COUNT(y) AS nonnull FROM b; -- 2, 2
```
Suggested fix:
Not verified against source, offered only as a starting point.
A quantified comparison is rewritten internally into a form with special NULL
handling (roughly `x >= ALL (subq)` → `NOT (x < ANY (subq))`, then handled by
`Item_in_optimizer` / `Item_allany_subselect`). That rewrite depends on whether
the predicate sits at the top level of the WHERE clause, where FALSE and NULL are
interchangeable, versus nested under NOT where they are not.
The symptom is consistent with the top-level flag, or the parity of the enclosing
negations, being tracked incorrectly when the predicate is nested under NOT — the
rewritten result appears to be negated one extra time.
Note that Bug #51070 ("Query with a NOT IN subquery predicate returns a wrong
result set", fixed in 5.5) describes the same mechanism for a different predicate:
"When applying the NOT, the subquery was wrapped inside a NOT node ... Fixed by
making a NOT node forward the notification to its child node." The fix there
covered `NOT IN` with NULLs; the comparison-based ALL/ANY path appears not to be
covered. That patch may be a useful reference.
