floating-precision loss happen after changing AVG(x) to -AVG(-x)

[chen8908917]

after changing AVG(48) to -AVG(-48),floating-precision loss happen

mysql> WITH cte_619 AS (SELECT (SELECT AVG(48) AS subq_col FROM t1 AS s890) AS col_1, (SELECT SUM(1) AS subq_col FROM t1 AS s180) AS col_2, AVG(anl57.c1) AS col_3 FROM t1 AS anl57) SELECT riq85.col_3 AS col_1, MINUTE('2023-01-01') AS col_2 FROM cte_619 AS riq85 WHERE NOT riq85.col_2 IS NULL EXCEPT SELECT DISTINCT wao91.col_1 AS col_1, wao91.col_3 AS col_2 FROM cte_619 AS wao91 WHERE (wao91.col_2 <> 91);
+---------------------------+----------------+
| col_1                     | col_2          |
+---------------------------+----------------+
| 4574.00000000000000000000 | 20.00000000000 |
+---------------------------+----------------+
1 row in set (0.03 sec)

mysql> WITH cte_619 AS (SELECT (SELECT -AVG(-48) AS subq_col FROM t1 AS s890) AS col_1, (SELECT SUM(1) AS subq_col FROM t1 AS s180) AS col_2, AVG(anl57.c1) AS col_3 FROM t1 AS anl57) SELECT riq85.col_3 AS col_1, MINUTE('2023-01-01') AS col_2 FROM cte_619 AS riq85 WHERE NOT riq85.col_2 IS NULL EXCEPT SELECT DISTINCT wao91.col_1 AS col_1, wao91.col_3 AS col_2 FROM cte_619 AS wao91 WHERE (wao91.col_2 <> 91);
+-------+----------------+
| col_1 | col_2          |
+-------+----------------+
|  4574 | 20.00000000000 |
+-------+----------------+
1 row in set (0.04 sec)

Additionally in mysql:9.5.0,it has been solved

mysql> WITH cte_619 AS (SELECT (SELECT AVG(48) AS subq_col FROM t1 AS s890) AS col_1, (SELECT SUM(1) AS subq_col FROM t1 AS s180) AS col_2, AVG(anl57.c1) AS col_3 FROM t1 AS anl57) SELECT riq85.col_3 AS col_1, MINUTE('2023-01-01') AS col_2 FROM cte_619 AS riq85 WHERE NOT riq85.col_2 IS NULL EXCEPT SELECT DISTINCT wao91.col_1 AS col_1, wao91.col_3 AS col_2 FROM cte_619 AS wao91 WHERE (wao91.col_2 <> 91);
+-----------+---------+
| col_1     | col_2   |
+-----------+---------+
| 4856.2353 | 20.0000 |
+-----------+---------+
1 row in set, 1 warning (0.00 sec)

mysql> WITH cte_619 AS (SELECT (SELECT -AVG(-48) AS subq_col FROM t1 AS s890) AS col_1, (SELECT SUM(1) AS subq_col FROM t1 AS s180) AS col_2, AVG(anl57.c1) AS col_3 FROM t1 AS anl57) SELECT riq85.col_3 AS col_1, MINUTE('2023-01-01') AS col_2 FROM cte_619 AS riq85 WHERE NOT riq85.col_2 IS NULL EXCEPT SELECT DISTINCT wao91.col_1 AS col_1, wao91.col_3 AS col_2 FROM cte_619 AS wao91 WHERE (wao91.col_2 <> 91);
+-----------+---------+
| col_1     | col_2   |
+-----------+---------+
| 4856.2353 | 20.0000 |
+-----------+---------+
1 row in set, 1 warning (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 9.5.0     |
+-----------+
1 row in set (0.00 sec)

How to repeat

DROP DATABASE IF EXISTS test;
CREATE DATABASE IF NOT EXISTS test;
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)
);

INSERT INTO t1 (c1, c2, c3, c4, c5, c6) VALUES (4574, 'sample_MLnRpnfpWc7B3vTK1UgPC238vpi0s1RBbTi3w1PPfyiCY1Qe3HkuK1BirM4sFOCpLFIVVZUQXcx9E7nJ8DcoteaIYoCmMAG7ak253nzfIWaLMff8QqQKFiqQNUboBQGfaRwYCYnqkwLYJgdCydQI8rNRPWTPFFUt5xCqVEXgLW1N7qUEH0AoSM4VD1c2wRQCsbvEjBympYHgczmQWCjQtC0HRccDiIPzzJRje', 'sample_3onsNJOHa5682kdaEqRRg3lQcCXjurNd7iSfXX3h', 37, '2025-04-02', 'sample_jMS');


WITH cte_619 AS (SELECT (SELECT AVG(48) AS subq_col FROM t1 AS s890) AS col_1, (SELECT SUM(1) AS subq_col FROM t1 AS s180) AS col_2, AVG(anl57.c1) AS col_3 FROM t1 AS anl57) SELECT riq85.col_3 AS col_1, MINUTE('2023-01-01') AS col_2 FROM cte_619 AS riq85 WHERE NOT riq85.col_2 IS NULL EXCEPT SELECT DISTINCT wao91.col_1 AS col_1, wao91.col_3 AS col_2 FROM cte_619 AS wao91 WHERE (wao91.col_2 <> 91);
WITH cte_619 AS (SELECT (SELECT -AVG(-48) AS subq_col FROM t1 AS s890) AS col_1, (SELECT SUM(1) AS subq_col FROM t1 AS s180) AS col_2, AVG(anl57.c1) AS col_3 FROM t1 AS anl57) SELECT riq85.col_3 AS col_1, MINUTE('2023-01-01') AS col_2 FROM cte_619 AS riq85 WHERE NOT riq85.col_2 IS NULL EXCEPT SELECT DISTINCT wao91.col_1 AS col_1, wao91.col_3 AS col_2 FROM cte_619 AS wao91 WHERE (wao91.col_2 <> 91);

version

mysql> select polardb_version();
+---------+--------------+---------------------+
| TYPE    | VERSION      | RELEASE_DATE        |
+---------+--------------+---------------------+
| Product | PolarDB V2.0 | Distributed Edition |
| CN      | 2.4.0.5.4.19 | SNAPSHOT            |
| DN      | 2.4.0.8.4.19 | 20240430            |
| GMS     | 2.4.0.8.4.19 | 20240430            |
+---------+--------------+---------------------+
4 rows in set (0.01 sec)

mysql> select @@version;
+-------------------------+
| @@version               |
+-------------------------+
| 8.0.32-X-Cluster-8.4.19 |
+-------------------------+
1 row in set (0.00 sec)