MySQL Bugs: #115962: Quantified comparison predicate and NOT NOT gives wrong result

Bug #115962	Quantified comparison predicate and NOT NOT gives wrong result
Submitted:	29 Aug 2024 14:21	Modified:	1 Oct 2024 16:49
Reporter:	Roy Lyseng	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.7	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Adding NOT in front of a quantified comparison predicate (e.g. v >ALL (<subquery>)
properly inverts the result (and makes it equivalent to v <ANY (<subquery>)).

However, extending this to NOT NOT gives a wrong result.

How to repeat:
CREATE TABLE it
 (pk    INTEGER PRIMARY KEY,
  low   INTEGER NOT NULL,
  lown  INTEGER,
  mid   INTEGER NOT NULL,
  midn  INTEGER,
  hig   INTEGER NOT NULL,
  hign  INTEGER,
  nulls INTEGER,
  eql   INTEGER NOT NULL,
  eqln  INTEGER
);

CREATE TABLE ot(
  v INTEGER NOT NULL,
  vn INTEGER);

INSERT INTO it(pk, low, lown, mid, midn, hig, hign, nulls, eql, eqln) VALUES
 (1, 1,    1, 4,    4, 7,    7, NULL, 11,    11),
 (2, 2,    2, 5,    5, 8,    8, NULL, 11,    11),
 (3, 3, NULL, 5, NULL, 9, NULL, NULL, 11, NULL);

INSERT INTO ot VALUES (1, 1), (2, NULL);

SELECT * FROM ot WHERE v >ALL (SELECT eql FROM it);
Empty set (0.00 sec)

SELECT * FROM ot WHERE not v >ALL (SELECT eql FROM it);
+---+------+
| v | vn   |
+---+------+
| 1 |    1 |
| 2 | NULL |
+---+------+
2 rows in set (0.00 sec)

SELECT * FROM ot WHERE not not v >ALL (SELECT eql FROM it);
+---+------+
| v | vn   |
+---+------+
| 1 |    1 |
| 2 | NULL |
+---+------+
2 rows in set (0.00 sec)

Suggested fix:
Fix the transformations.

Documented fix as follows in the MySQL 9.1.0 changelog:

    NOT worked correctly with a quantified comparison such as < ALL, 
    > ANY, and so on, but NOT NOT did not.

Closed.