Bug #113578 AVG calculation bug

• Submitted: 8 Jan 2024 13:06
• Modified: 10 Jan 2024 7:14
• Reporter: Ye Shiyang
• Status: Verified
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 5.7.44, 8.0.35
• OS: Any
• CPU Architecture: Any
• Tags: AVG calculation average

[8 Jan 2024 13:06] Ye Shiyang

Description:

In theory, the result of sql should have the same result of sql2

SELECT AVG(-1.7E308) AS f1 FROM t1 HAVING f1; -- sql1

SELECT f1 FROM (
SELECT AVG(-1.7E308) AS f1, AVG(-1.7E308) IS TRUE AS flag FROM t1 HAVING flag=1
) AS tmp_t; -- sql2

However, when use subquery,the value 0 changed to -1.7976931348623157e308.

mysql> SELECT AVG(-1.7E308) AS f1 FROM t1 HAVING f1; --sql1
+------+
| f1   |
+------+
|    0 |
+------+
1 row in set (0.00 sec)

mysql> SELECT f1 FROM (SELECT AVG(-1.7E308) AS f1, AVG(-1.7E308) IS TRUE AS flag FROM t1 HAVING flag=1) AS tmp_t; --sql2
+-------------------------+
| f1                      |
+-------------------------+
| -1.7976931348623157e308 |
+-------------------------+
1 row in set (0.00 sec)

How to repeat:
DROP DATABASE IF EXISTS database1;
CREATE DATABASE database1;
USE database1;
CREATE TABLE IF NOT EXISTS t1(c0 int) ;
REPLACE INTO t1(c0) VALUES(1),(2);

SELECT AVG(-1.7E308) AS f1 FROM t1 HAVING f1; -- sql1

SELECT f1 FROM (
SELECT AVG(-1.7E308) AS f1, AVG(-1.7E308) IS TRUE AS flag FROM t1 HAVING flag=1
) AS tmp_t; -- sql2

Suggested fix:
I think sql1 should return the same result of sql2.

[8 Jan 2024 13:42] MySQL Verification Team

Hi Mr. eric,

Thank you for your bug report.

However, this is not a bug.

The reason for that is that HAVING clause can be used ONLY when GROUP BY clause is used.

When GROUP BY is not used, you should use the reserved word WHERE .......

Hence, this is not a bug.

[10 Jan 2024 7:14] MySQL Verification Team

Hello Ye Shiyang,

Thank you for the report and feedback

regards,
Umesh

[21 May 2024 9:07] Jack Dong

mysql>  select f1 from (SELECT AVG(-1.7E308) AS f1 FROM t1) as tmp_1;
+-------------------------+
| f1                      |
+-------------------------+
| -1.7976931348623157e308 |
+-------------------------+
1 row in set (0.00 sec)

mysql>  select f1 from (SELECT AVG(1.7E308) AS f1 FROM t1) as tmp_1;
+------------------------+
| f1                     |
+------------------------+
| 1.7976931348623157e308 |
+------------------------+
1 row in set (0.01 sec)

mysql>  SELECT AVG(-1.7E308) AS f1 from t1;
+------+
| f1   |
+------+
|    0 |
+------+
1 row in set (0.01 sec)

mysql>  SELECT AVG(1.7E308) AS f1 from t1;
+------+
| f1   |
+------+
|    0 |
+------+
1 row in set (0.00 sec)

mysql> 

I think this problem may be related to the precision of floating point.