after changing SUM(cte.col_2) to -SUM(-cte.col_2),the column value unexpectedly change.

[chen8908917]

description ：after changing SUM(cte.col_2) in having clause to -SUM(-cte.col_2),the column value unexpectedly change.
JSON_OBJECT both order clause both can't be removed

mysql> WITH cte_329 AS (SELECT exq36.c5 AS col_1, CEIL(exq36.c4) AS col_2 FROM t1 AS exq36) SELECT cte.col_1 AS col_1, AVG(cte.col_2) AS col_1_2, JSON_OBJECTAGG('sample_98', cte.col_1) AS col_1_3 FROM cte_329 AS cte GROUP BY cte.col_1 HAVING (SUM(cte.col_2) >= 4.94) ORDER BY cte.col_1 DESC;
+------------+---------+-----------------------------+
| col_1      | col_1_2 | col_1_3                     |
+------------+---------+-----------------------------+
| 2025-12-02 | 61.0000 | {"sample_98": "2025-02-01"} |
+------------+---------+-----------------------------+
1 row in set (0.02 sec)

mysql> WITH cte_329 AS (SELECT exq36.c5 AS col_1, CEIL(exq36.c4) AS col_2 FROM t1 AS exq36) SELECT cte.col_1 AS col_1, AVG(cte.col_2) AS col_1_2, JSON_OBJECTAGG('sample_98', cte.col_1) AS col_1_3 FROM cte_329 AS cte GROUP BY cte.col_1 HAVING (-SUM(-cte.col_2) >= 4.94) ORDER BY cte.col_1 DESC;
+------------+---------+-----------------------------+
| col_1      | col_1_2 | col_1_3                     |
+------------+---------+-----------------------------+
| 2025-11-25 | 32.0000 | {"sample_98": "2025-12-02"} |
+------------+---------+-----------------------------+
1 row in set (0.02 sec)

Additionally,in mysql:9.4.0,this problem has been solved

mysql> WITH cte_329 AS (SELECT exq36.c5 AS col_1, CEIL(exq36.c4) AS col_2 FROM t1 AS exq36) SELECT cte.col_1 AS col_1, AVG(cte.col_2) AS col_1_2, JSON_OBJECTAGG('sample_98', cte.col_1) AS col_1_3 FROM cte_329 AS cte GROUP BY cte.col_1 HAVING (SUM(cte.col_2) >= 4.94) ORDER BY cte.col_1 DESC;
+------------+---------+-----------------------------+
| col_1      | col_1_2 | col_1_3                     |
+------------+---------+-----------------------------+
| 2025-12-02 | 61.0000 | {"sample_98": "2025-12-02"} |
| 2025-11-25 | 32.0000 | {"sample_98": "2025-11-25"} |
| 2025-02-01 | 49.0000 | {"sample_98": "2025-02-01"} |
+------------+---------+-----------------------------+
3 rows in set (0.01 sec)

mysql> WITH cte_329 AS (SELECT exq36.c5 AS col_1, CEIL(exq36.c4) AS col_2 FROM t1 AS exq36) SELECT cte.col_1 AS col_1, AVG(cte.col_2) AS col_1_2, JSON_OBJECTAGG('sample_98', cte.col_1) AS col_1_3 FROM cte_329 AS cte GROUP BY cte.col_1 HAVING (-SUM(-cte.col_2) >= 4.94) ORDER BY cte.col_1 DESC;
+------------+---------+-----------------------------+
| col_1      | col_1_2 | col_1_3                     |
+------------+---------+-----------------------------+
| 2025-12-02 | 61.0000 | {"sample_98": "2025-12-02"} |
| 2025-11-25 | 32.0000 | {"sample_98": "2025-11-25"} |
| 2025-02-01 | 49.0000 | {"sample_98": "2025-02-01"} |
+------------+---------+-----------------------------+
3 rows in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 9.4.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');
INSERT INTO t1 (c1, c2, c3, c4, c5, c6) VALUES (3143, 'sample_COuw3sqV16xthjVaunYhBidChBZTOAx5R4eLFM7CnQ1e2H7aFOHZVXa0KP29TYGwdRPQ4pqubPHmMEMhhnGXtaEygfeG4DitS0XA', 'sample_5PVBQigDUIpT1E8e8ZgDdHbvI6fJkc55dpG2kpnjBcm8pCMfJrJun0ucpHhgUZBO97o4QjRSLGzlp2TdcTBTS39hoNuYdBWUpFlTgaGa949RkAKpd9dDpJ5iEAOBJZPk2Y6We4E6yd7WHGzraFvzwjSnLKHFCU1NnhHrbR2dByeScJWOopDr0daPmsZa4YUlw4UJLCO', 32, '2025-11-25', 'sample_QW8');


WITH cte_329 AS (SELECT exq36.c5 AS col_1, CEIL(exq36.c4) AS col_2 FROM t1 AS exq36) SELECT cte.col_1 AS col_1, AVG(cte.col_2) AS col_1_2, JSON_OBJECTAGG('sample_98', cte.col_1) AS col_1_3 FROM cte_329 AS cte GROUP BY cte.col_1 HAVING (SUM(cte.col_2) >= 4.94) ORDER BY cte.col_1 DESC;
WITH cte_329 AS (SELECT exq36.c5 AS col_1, CEIL(exq36.c4) AS col_2 FROM t1 AS exq36) SELECT cte.col_1 AS col_1, AVG(cte.col_2) AS col_1_2, JSON_OBJECTAGG('sample_98', cte.col_1) AS col_1_3 FROM cte_329 AS cte GROUP BY cte.col_1 HAVING (-SUM(-cte.col_2) >= 4.94) ORDER BY cte.col_1 DESC;

Version

mysql> select @@version;
+-------------------------+
| @@version               |
+-------------------------+
| 8.0.32-X-Cluster-8.4.19 |
+-------------------------+
1 row in set (0.02 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.03 sec)