Description:
In theory, the result of sql1(=) ⊆ the result of sql2(>=):
SELECT (c1+ADDTIME('2005-02-13 06:24:30', '06:14:38')) AS `f1` FROM t HAVING (100=ALL (SELECT 1)) OR f1 != 1; -- sql1
SELECT (c1+ADDTIME('2005-02-13 06:24:30', '06:14:38')) AS `f1` FROM t HAVING (100>=ALL (SELECT 1)) OR f1 != 1; -- sql2
Because the condition of sql1(100 = ALL (SELECT 1)) → the condition of sql2(100 >= ALL (SELECT 1))
However, the value 2005 changed to 20050213123908 after changing = to >=, seems like a logical bug:
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.31 |
+-----------+
1 row in set (0.00 sec)
mysql> SELECT (c1+ADDTIME('2005-02-13 06:24:30', '06:14:38')) AS `f1` FROM t HAVING (100=ALL (SELECT 1)) OR f1 != 1;
+------+
| f1 |
+------+
| 2005 |
+------+
1 row in set, 2 warnings (0.01 sec)
mysql> SELECT (c1+ADDTIME('2005-02-13 06:24:30', '06:14:38')) AS `f1` FROM t HAVING (100>=ALL (SELECT 1)) OR f1 != 1;
+----------------+
| f1 |
+----------------+
| 20050213123908 |
+----------------+
1 row in set, 1 warning (0.00 sec)
How to repeat:
drop table if exists t;
CREATE TABLE t (c1 VARCHAR(20));
INSERT INTO t VALUES ('a');
SELECT (c1+ADDTIME('2005-02-13 06:24:30', '06:14:38')) AS `f1` FROM t HAVING (100=ALL (SELECT 1)) OR f1 != 1;
SELECT (c1+ADDTIME('2005-02-13 06:24:30', '06:14:38')) AS `f1` FROM t HAVING (100>=ALL (SELECT 1)) OR f1 != 1;
Suggested fix:
The following list may be helpful for your debugging:
1. 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:8.0.3, it cannot be reproduced in mysql:8.0.2:
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.2-dmr |
+-----------+
1 row in set (0.00 sec)
mysql> SELECT (c1+ADDTIME('2005-02-13 06:24:30', '06:14:38')) AS `f1` FROM t HAVING (100=ALL (SELECT 1)) OR f1 != 1;
+------+
| f1 |
+------+
| 2005 |
+------+
1 row in set, 2 warnings (0.00 sec)
mysql> SELECT (c1+ADDTIME('2005-02-13 06:24:30', '06:14:38')) AS `f1` FROM t HAVING (100>=ALL (SELECT 1)) OR f1 != 1;
+------+
| f1 |
+------+
| 2005 |
+------+
1 row in set, 1 warning (0.00 sec)
2. Interestingly, the bug can also be reproduced on 8.0.2 after changing sql2 to ... HAVING 1:
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.2-dmr |
+-----------+
1 row in set (0.00 sec)
mysql> SELECT (c1+ADDTIME('2005-02-13 06:24:30', '06:14:38')) AS `f1` FROM t HAVING (100=ALL (SELECT 1)) OR f1 != 1;
+------+
| f1 |
+------+
| 2005 |
+------+
1 row in set, 2 warnings (0.00 sec)
mysql> SELECT (c1+ADDTIME('2005-02-13 06:24:30', '06:14:38')) AS `f1` FROM t HAVING 1;
+----------------+
| f1 |
+----------------+
| 20050213123908 |
+----------------+
1 row in set, 1 warning (0.00 sec)