Bug #119771 after changing SUM(DISTINCT s911.col_3) to -SUM(DISTINCT -s911.col_3),the value should be the same totally instead of -0

• Submitted: 25 Jan 7:11
• Modified: 25 Jan 20:46
• Reporter: cl hl
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 9.5.0
• OS: Any
• CPU Architecture: Any

[25 Jan 7:11] cl hl

Description:
after changing SUM(DISTINCT s911.col_3) to -SUM(DISTINCT -s911.col_3),the value should be the same totally instead of -0

mysql> WITH cte_462 AS (SELECT (SELECT STDDEV_SAMP(63) AS subq_col FROM t1 AS s227) AS col_3 FROM t1 AS dmr10 WHERE (dmr10.c3 > '26')) SELECT SUM(DISTINCT s911.col_3) AS sum_distinct_180 FROM cte_462 AS s911;
+------------------+
| sum_distinct_180 |
+------------------+
|                0 |
+------------------+
1 row in set (0.00 sec)

mysql> WITH cte_462 AS (SELECT (SELECT STDDEV_SAMP(63) AS subq_col FROM t1 AS s227) AS col_3 FROM t1 AS dmr10 WHERE (dmr10.c3 > '26')) SELECT -SUM(DISTINCT -s911.col_3) AS sum_distinct_180 FROM cte_462 AS s911;
+------------------+
| sum_distinct_180 |
+------------------+
|               -0 |
+------------------+
1 row in set (0.00 sec)

How to repeat:
DROP DATABASE IF EXISTS test;
CREATE DATABASE IF NOT EXISTS test;
SET GLOBAL sort_buffer_size = 64 * 1024 * 1024;
SET GLOBAL read_rnd_buffer_size = 8 * 1024 * 1024;
USE test;
CREATE TABLE t1 (
    c1 INT NOT NULL AUTO_INCREMENT,
    c2 VARCHAR(255) NOT NULL,
    c3 VARCHAR(255) NULL,
    c4 INT NULL,
    c5 DATE NOT NULL,
    c6 VARCHAR(10) NOT NULL,
    PRIMARY KEY (c1)
);

CREATE TABLE t2 (
    c1 INT NOT NULL AUTO_INCREMENT,
    c2 INT NOT NULL,
    c3 DECIMAL(10,2) NOT NULL,
    c4 VARCHAR(50) NOT NULL,
    c5 DATE NOT NULL,
    c6 MEDIUMTEXT NULL,
    c7 LONGTEXT NULL,
    c8 MEDIUMBLOB NULL,
    c9 LONGBLOB NULL,
    c10 ENUM('value1','value2','value3') NULL,
    c11 SET('a','b','c','d') NULL,
    c12 BIT(8) NULL,
    c13 DATETIME NULL,
    c14 FLOAT(8,2) NULL,
    c15 DOUBLE(12,4) NULL,
    c16 JSON NULL,
    PRIMARY KEY (c1)
);

CREATE TABLE t3 (
    c1 INT NOT NULL AUTO_INCREMENT,
    c2 INT NOT NULL,
    c3 INT NOT NULL,
    c4 YEAR NOT NULL,
    c5 DATETIME NULL,
    c6 TINYINT NULL,
    c7 SMALLINT NULL,
    c8 MEDIUMINT NULL,
    c9 BIGINT NULL,
    c10 LONGTEXT NULL,
    c11 GEOMETRY NULL,
    c12 TINYTEXT NULL,
    c13 TINYBLOB NULL,
    c14 SET('x','y','z') NULL,
    c15 TINYINT(1) NULL,
    PRIMARY KEY (c1)
);

INSERT INTO t1 (c1, c2, c3, c4, c5, c6) VALUES (1536, 'sample_GnY9n2QDV85qyHNJTDLrOJTPlIQ8oGxJimWfYOsEh6behbazn3qFWLWwZZsbxWQEyMnsnutN5yfm3twOXszfJ8wc7cGulFFLkCJnGAyz2q3KFBkHgi', 'sample_mpQ8QD4GfiYD4nC1iUqox16013d5wRliF5oBHe8JA54FsT1uxuT8tGMkjdPdO4flcJdxSaERUZGMsKQooraoLUeKjvdU0kz8lCcYWgWwFuN2UOilVKapxKf5srFx9uDJYs55NxNomluPlyynCP7oeNXWS6HXUACuKgkAL3ww5OFzuTuY76XVGFDHViVUAhRCKRTGheCwHfwkD90Gsa6ZPcxJK4vZJkqCqtoTLmF4OoY0glRObF64Bakz', 74, '2025-10-31', 'sample_7b');
INSERT INTO t1 (c1, c2, c3, c4, c5, c6) VALUES (6081, 'sample_TdybGh5DCFprNB52PEDpDEAPD6aBeyp3Y1FJZHORnb5hkWwEcKKmZM7wG9sJ0EubNDVXqsrWx8Vc2jFhHloyMvPpfJw7oCgUUwDbAJ6bMiP2wmWKe7dBK8eovE06I', 'sample_gMvM8uwODzXr4sdOHzzqRQZBKSK1dZJdo9u5SDRYmhSbVwqygV1WrtCIEln3Zk0OYUzJrWrrMS2E4Kv4YgHGjSnMl8NTFG9JTyK1VgcH0yWnX4cwNTqCXbkMvL', 61, '2025-05-18', 'sample_C');

WITH cte_462 AS (SELECT (SELECT STDDEV_SAMP(63) AS subq_col FROM t1 AS s227) AS col_3 FROM t1 AS dmr10 WHERE (dmr10.c3 > '26')) SELECT SUM(DISTINCT s911.col_3) AS sum_distinct_180 FROM cte_462 AS s911;
WITH cte_462 AS (SELECT (SELECT STDDEV_SAMP(63) AS subq_col FROM t1 AS s227) AS col_3 FROM t1 AS dmr10 WHERE (dmr10.c3 > '26')) SELECT -SUM(DISTINCT -s911.col_3) AS sum_distinct_180 FROM cte_462 AS s911;

[25 Jan 7:13] cl hl

the sum function acts like it

mysql> select sum(distinct 0);
+-----------------+
| sum(distinct 0) |
+-----------------+
|               0 |
+-----------------+
1 row in set (0.00 sec)

mysql> select -sum(distinct -0);
+-------------------+
| -sum(distinct -0) |
+-------------------+
|                 0 |
+-------------------+
1 row in set (0.01 sec)

[25 Jan 20:46] Roy Lyseng

Thank you for the test case.
However, -0 and 0 are identical values.
Not a bug.