Bug #113448 The sum function returns a value problem if the argument is -0 or '-0.0'

• Submitted: 18 Dec 2023 8:15
• Modified: 18 Dec 2023 11:47
• Reporter: xu zhangxu
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 8.0.22
• OS: Any
• CPU Architecture: Any

[18 Dec 2023 8:15] xu zhangxu

Description:
When using the sum function to evaluate a column, if the values of this column are of type varchar and the value is' -0.0 ', sometimes 0 and sometimes -0 are returned under different group by and where conditions. How mysql determines when to return 0 and when to return -0. As a user, I want to know when to return 0 and when to return -0, if the upper layer application is sensitive to 0 and -0.

How to repeat:
case1：
CREATE TABLE t2(a INT, b VARCHAR(64), c decimal(10, 4));
INSERT INTO t2 (a, b, c) VALUES (1, '-0', 0);
INSERT INTO t2 (a, b, c) VALUES (2, '-0.0', -0);
INSERT INTO t2 (a, b, c) VALUES (3, '-00.00', -0.0);
select sum(b) from t2 group by a;
+--------+
| sum(b) |
+--------+
|     -0 |
|     -0 |
|     -0 |
+--------+

case2:
CREATE TABLE t3(a INT, b VARCHAR(64), c decimal(10, 4), primary key(a));
INSERT INTO t3 (a, b, c) VALUES (1, '-0', 0);
INSERT INTO t3 (a, b, c) VALUES (2, '-0.0', -0);
INSERT INTO t3 (a, b, c) VALUES (3, '-00.00', -0.0);
select sum(b) from t3 group by a;
+--------+
| sum(b) |
+--------+
|      0 |
|      0 |
|      0 |
+--------+
select sum(b) from t3 group by c;
+--------+
| sum(b) |
+--------+
|     -0 |
+--------+

[18 Dec 2023 11:47] MySQL Verification Team

Hi M.r zhangxu,

Thank you very much for your bug report.

However, we do not understand what is a bug that you are reporting.

According to the standard for DECIMAL domain, 0 and -0 are identical values.

Not a bug.