Bug #109407 Extra line caused by NOT comparsion + ALL
Submitted: 16 Dec 2022 12:31 Modified: 16 Dec 2022 12:37
Reporter: ZongYin Hao Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:5.7.5, 8.0.31 OS:Any
Assigned to: CPU Architecture:Any
Tags: ALL

[16 Dec 2022 12:31] ZongYin Hao
Description:
In theory, the result of sql2 ⊆ the result of sql1:

SELECT 1 FROM t WHERE (NOT (FROM_DAYS(1)=ALL (SELECT c1 FROM t))); -- sql1
SELECT 1 FROM t WHERE (NOT (FROM_DAYS(1)>=ALL (SELECT c1 FROM t))); -- sql2

First, the condition of sql2 (>=ALL) is more relaxed than the condition of sql1 (=ALL).
Then, NOT will reverse this relationship.
Therefore, the result of sql2 ⊆ the result of sql1.

However, there is an extra line in the result of sql2, seems like a logical bug:

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.31    |
+-----------+
1 row in set (0.00 sec)

mysql> SELECT 1 FROM t WHERE (NOT (FROM_DAYS(1)=ALL (SELECT c1 FROM t)));
Empty set, 1 warning (0.00 sec)

mysql> SELECT 1 FROM t WHERE (NOT (FROM_DAYS(1)>=ALL (SELECT c1 FROM t)));
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

How to repeat:
drop table if exists t;
CREATE TABLE t (c1 DECIMAL(40,20) UNSIGNED);
INSERT INTO t VALUES (1);

SELECT 1 FROM t WHERE (NOT (FROM_DAYS(1)=ALL (SELECT c1 FROM t)));
SELECT 1 FROM t WHERE (NOT (FROM_DAYS(1)>=ALL (SELECT c1 FROM t)));

Suggested fix:
The following list may be helpful for your debugging:
1. Interestingly, the bug cannot be reproduced after changing NOT = ALL(NOT >= ALL) to != ALL(< ALL):
mysql> SELECT 1 FROM t WHERE ((FROM_DAYS(1)!=ALL (SELECT c1 FROM t)));
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set, 1 warning (0.00 sec)

mysql> SELECT 1 FROM t WHERE ((FROM_DAYS(1)<ALL (SELECT c1 FROM t)));
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

2. We look for the first version of the bug in all docker images (with format x.x.x) of https://hub.docker.com/_/mysql/tags 
We found that the bug first occurred in mysql:5.7.5, it cannot be reproduced in mysql:5.7.4:

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.4-m14 |
+-----------+
1 row in set (0.00 sec)

mysql> SELECT 1 FROM t WHERE (NOT (FROM_DAYS(1)=ALL (SELECT c1 FROM t)));
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

mysql> SELECT 1 FROM t WHERE (NOT (FROM_DAYS(1)>=ALL (SELECT c1 FROM t)));
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)
[16 Dec 2022 12:37] MySQL Verification Team
Hello ZongYin Hao,

Thank you for the report and test case.

regards,
Umesh