Bug #109365 Value change caused by comparison + ALL

• Submitted: 13 Dec 2022 13:44
• Modified: 13 Dec 2022 14:49
• Reporter: ZongYin Hao
• Status: Not a Bug
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0.3, 8.0.31
• OS: Any
• CPU Architecture: Any
• Tags: ALL

[13 Dec 2022 13:44] ZongYin Hao

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)

[13 Dec 2022 13:56] MySQL Verification Team

Hi Mr. Hao,

Thank you very much for your bug report.

However, it is not a bug.

If you read our Manual, you will find out that HAVING can be used only in aggregated queries, like the ones with GROUP BY. It also says that results of using HAVING without aggregating results is unknown.

That follows strictly current SQL standard .....

Not a bug.

[13 Dec 2022 14:49] ZongYin Hao

I'm sorry, I didn't notice this rule. 
I will check the documentation more carefully!