after changing AVG(s974.c4) to -AVG(-s974.c4),rows unexpectedly become less

[chen8908917]

after changing AVG(s974.c4) to -AVG(-s974.c4),rows unexpectedly become less

mysql> WITH cte_994 AS (SELECT BIT_OR(nbk33.c1) AS col_1, nbk33.c6 AS col_2, (SELECT COUNT(s743.c3) AS subq_col FROM t1 AS s743) AS col_3, TIME(nbk33.c5) AS col_4 FROM t1 AS nbk33 GROUP BY nbk33.c6, TIME(nbk33.c5)) SELECT JSON_OBJECTAGG('sample_21', subq.c6) AS col_1, subq.c6 AS c6, ROUND(subq.sum_c4) AS col_1_2, subq.sum_c4 AS sum_c4 FROM (SELECT BIT_OR(s974.c1) AS bit_or_c1, s974.c6 AS c6, AVG(s974.c4) AS var_samp_c4, SUM(s974.c4) AS sum_c4 FROM t1 AS s974 GROUP BY s974.c6 ORDER BY s974.c6 ASC) AS subq WHERE (subq.bit_or_c1 <> '2023-08-28') GROUP BY subq.c6, ROUND(subq.sum_c4), subq.sum_c4;
+---------------------------+----------+---------+--------+
| col_1                     | c6       | col_1_2 | sum_c4 |
+---------------------------+----------+---------+--------+
| {"sample_21": "sample_h"} | sample_h |      61 |     61 |
| {"sample_21": "sample_x"} | sample_x |      49 |     49 |
+---------------------------+----------+---------+--------+
2 rows in set (0.02 sec)

mysql> WITH cte_994 AS (SELECT BIT_OR(nbk33.c1) AS col_1, nbk33.c6 AS col_2, (SELECT COUNT(s743.c3) AS subq_col FROM t1 AS s743) AS col_3, TIME(nbk33.c5) AS col_4 FROM t1 AS nbk33 GROUP BY nbk33.c6, TIME(nbk33.c5)) SELECT JSON_OBJECTAGG('sample_21', subq.c6) AS col_1, subq.c6 AS c6, ROUND(subq.sum_c4) AS col_1_2, subq.sum_c4 AS sum_c4 FROM (SELECT BIT_OR(s974.c1) AS bit_or_c1, s974.c6 AS c6, -AVG(-s974.c4) AS var_samp_c4, SUM(s974.c4) AS sum_c4 FROM t1 AS s974 GROUP BY s974.c6 ORDER BY s974.c6 ASC) AS subq WHERE (subq.bit_or_c1 <> '2023-08-28') GROUP BY subq.c6, ROUND(subq.sum_c4), subq.sum_c4;
+---------------------------+----------+---------+--------+
| col_1                     | c6       | col_1_2 | sum_c4 |
+---------------------------+----------+---------+--------+
| {"sample_21": "sample_h"} | sample_x |      49 |     49 |
+---------------------------+----------+---------+--------+
1 row in set (0.02 sec)

Additionally,in mysql:9.5.0,it has been solved

mysql> WITH cte_994 AS (SELECT BIT_OR(nbk33.c1) AS col_1, nbk33.c6 AS col_2, (SELECT COUNT(s743.c3) AS subq_col FROM t1 AS s743) AS col_3, TIME(nbk33.c5) AS col_4 FROM t1 AS nbk33 GROUP BY nbk33.c6, TIME(nbk33.c5)) SELECT JSON_OBJECTAGG('sample_21', subq.c6) AS col_1, subq.c6 AS c6, ROUND(subq.sum_c4) AS col_1_2, subq.sum_c4 AS sum_c4 FROM (SELECT BIT_OR(s974.c1) AS bit_or_c1, s974.c6 AS c6, AVG(s974.c4) AS var_samp_c4, SUM(s974.c4) AS sum_c4 FROM t1 AS s974 GROUP BY s974.c6 ORDER BY s974.c6 ASC) AS subq WHERE (subq.bit_or_c1 <> '2023-08-28') GROUP BY subq.c6, ROUND(subq.sum_c4), subq.sum_c4;
+---------------------------+----------+---------+--------+
| col_1                     | c6       | col_1_2 | sum_c4 |
+---------------------------+----------+---------+--------+
| {"sample_21": "sample_h"} | sample_h |      61 |     61 |
| {"sample_21": "sample_x"} | sample_x |      49 |     49 |
+---------------------------+----------+---------+--------+
2 rows in set, 1 warning (0.00 sec)

mysql> WITH cte_994 AS (SELECT BIT_OR(nbk33.c1) AS col_1, nbk33.c6 AS col_2, (SELECT COUNT(s743.c3) AS subq_col FROM t1 AS s743) AS col_3, TIME(nbk33.c5) AS col_4 FROM t1 AS nbk33 GROUP BY nbk33.c6, TIME(nbk33.c5)) SELECT JSON_OBJECTAGG('sample_21', subq.c6) AS col_1, subq.c6 AS c6, ROUND(subq.sum_c4) AS col_1_2, subq.sum_c4 AS sum_c4 FROM (SELECT BIT_OR(s974.c1) AS bit_or_c1, s974.c6 AS c6, -AVG(-s974.c4) AS var_samp_c4, SUM(s974.c4) AS sum_c4 FROM t1 AS s974 GROUP BY s974.c6 ORDER BY s974.c6 ASC) AS subq WHERE (subq.bit_or_c1 <> '2023-08-28') GROUP BY subq.c6, ROUND(subq.sum_c4), subq.sum_c4;
+---------------------------+----------+---------+--------+
| col_1                     | c6       | col_1_2 | sum_c4 |
+---------------------------+----------+---------+--------+
| {"sample_21": "sample_h"} | sample_h |      61 |     61 |
| {"sample_21": "sample_x"} | sample_x |      49 |     49 |
+---------------------------+----------+---------+--------+
2 rows 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 (9505, 'sample_j6tRCMl2ntyXKOJEbYA1cM0rvcwpdKAcFn0YWKrCKnu3WgFFCrLj3UNBc3r0OF7q8uip4bOEuLRYII19C2S5qEaJjRJTU8TKZLWWoGUsHSVRxz1dRztOnTDtYBY4DRndJEl80NQgnZRcyNjwqJWoje0BloyBO6pONgdOaYlL4LVYA4YBB97fuaiNZHuqN3cbb0aI6BdVxhvNwuVXqLZEI4NTUQBbg2QSoJ9IL49VpgrAVaahv9DPz', 'sample_FslXMXkrstdX6KR63rp7SuzwgkzaVAWVG7pPEULjq3STRGzDQ82473KqhecREn4XoFg63sUdHz6awadu3vYDH2tEXOy0zP4Ybr4epzn8AniupuoryIsLYB2BvUcqxi2V3X1DxNA8bf4Co0emlCYz', 61, '2025-12-02', 'sample_h');
INSERT INTO t1 (c1, c2, c3, c4, c5, c6) VALUES (7523, 'sample_UcD5Y2tr7VUjqptMEy4SF7jspb6jB1Xk1laRrPr2fQmKRH4ceeBAJ5ZiaHxlkwPWHxeWArUcyZQ0m4VpWXsE9HBnCaBBqVsbBxedWJpoI5uj3gFJmx3nCttKqa2xjTWpqMPYsKWA33o8HJeFNMbVdWdMy3INZrcQHi', 'sample_xPqhIxkYsJC2cVR4IvZZbDymOJo95JssjjyURXaPmrQMN5zSO5SWE1Ll5Qsb72IOCg9GUFrplaCLWI5YRh1n44CPb1LnGbrVFJjuPrrMyXRiEkeXYe9Sm0ZZSiGTfKNWK8jtmIQjamVJHnfW4TuE1DA', 49, '2025-02-01', 'sample_x');

WITH cte_994 AS (SELECT BIT_OR(nbk33.c1) AS col_1, nbk33.c6 AS col_2, (SELECT COUNT(s743.c3) AS subq_col FROM t1 AS s743) AS col_3, TIME(nbk33.c5) AS col_4 FROM t1 AS nbk33 GROUP BY nbk33.c6, TIME(nbk33.c5)) SELECT JSON_OBJECTAGG('sample_21', subq.c6) AS col_1, subq.c6 AS c6, ROUND(subq.sum_c4) AS col_1_2, subq.sum_c4 AS sum_c4 FROM (SELECT BIT_OR(s974.c1) AS bit_or_c1, s974.c6 AS c6, AVG(s974.c4) AS var_samp_c4, SUM(s974.c4) AS sum_c4 FROM t1 AS s974 GROUP BY s974.c6 ORDER BY s974.c6 ASC) AS subq WHERE (subq.bit_or_c1 <> '2023-08-28') GROUP BY subq.c6, ROUND(subq.sum_c4), subq.sum_c4;
WITH cte_994 AS (SELECT BIT_OR(nbk33.c1) AS col_1, nbk33.c6 AS col_2, (SELECT COUNT(s743.c3) AS subq_col FROM t1 AS s743) AS col_3, TIME(nbk33.c5) AS col_4 FROM t1 AS nbk33 GROUP BY nbk33.c6, TIME(nbk33.c5)) SELECT JSON_OBJECTAGG('sample_21', subq.c6) AS col_1, subq.c6 AS c6, ROUND(subq.sum_c4) AS col_1_2, subq.sum_c4 AS sum_c4 FROM (SELECT BIT_OR(s974.c1) AS bit_or_c1, s974.c6 AS c6, -AVG(-s974.c4) AS var_samp_c4, SUM(s974.c4) AS sum_c4 FROM t1 AS s974 GROUP BY s974.c6 ORDER BY s974.c6 ASC) AS subq WHERE (subq.bit_or_c1 <> '2023-08-28') GROUP BY subq.c6, ROUND(subq.sum_c4), subq.sum_c4;

Version

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

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.02 sec)

[hustfxj]

This bug has been fixed in the latest internal branch, and we will release it promptly later. Thanks again @chen8908917