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)