MySQL Bugs: #98047: MIN/MAX issue with NOT EXISTS subquery

Bug #98047	MIN/MAX issue with NOT EXISTS subquery
Submitted:	21 Dec 2019 19:26	Modified:	28 Jan 2020 18:54
Reporter:	Attila Molnár	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S2 (Serious)
Version:	8.0.18	OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
The MIN and the MAX retrieve wrong value when a NOT EXISTS subquery is added.

How to repeat:
CREATE TABLE numbers (
  n INT NOT NULL,
  PRIMARY KEY (n));
INSERT INTO numbers (n) VALUES(1);
INSERT INTO numbers (n) VALUES(2);
INSERT INTO numbers (n) VALUES(3);
INSERT INTO numbers (n) VALUES(4);
CREATE TABLE ids (
  id INT NOT NULL,
  PRIMARY KEY (id));
INSERT INTO ids (id) VALUES(1);
INSERT INTO ids (id) VALUES(2);
INSERT INTO ids (id) VALUES(4);

When runs the following SELECT:
SELECT max(n), min(n)
  FROM numbers
 WHERE NOT EXISTS (SELECT 1 
                     FROM ids 
                    WHERE id = n);
expects that the result is:
+--------+--------+
| min(n) | max(n) |
+--------+--------+
| 3      | 3      |
+--------+--------+

but it returns with:
+--------+--------+
| min(n) | max(n) |
+--------+--------+
| 1      | 4      |
+--------+--------+

Thank you for the bug report. Server version 5.7 not affected by this bug, only 8.0 server.

Posted by developer:
 
Fixed in 8.0.20.

MIN() and MAX() could return an incorrect value for some queries if a
WHERE clause was added.

Posted by developer:
 
Corrected changelog entry:

MIN() and MAX() could return an incorrect value for some queries if a
WHERE clause containing an IN () subquery was added.