Description:
after changing SUM(s732.col_2) to -SUM(-s732.col_2),subq_col's value unexpectedly change
mysql> WITH cte_498 AS (SELECT (SELECT AVG(s255.c2) AS subq_col FROM t2 AS s255) AS col_2 FROM t2 AS eqw89) SELECT SUM(s732.col_2) AS subq_col FROM cte_498 AS s732;
+------------+
| subq_col |
+------------+
| 23905.0000 |
+------------+
1 row in set (0.00 sec)
mysql> WITH cte_498 AS (SELECT (SELECT AVG(s255.c2) AS subq_col FROM t2 AS s255) AS col_2 FROM t2 AS eqw89) SELECT -SUM(-s732.col_2) AS subq_col FROM cte_498 AS s732;
+------------+
| subq_col |
+------------+
| 23904.9999 |
+------------+
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 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)
);
INSERT INTO t2 (c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16) VALUES (6313, 9505, 133.75, 'sample_ZyDTZ8ESP9DDoOyVEgikHelQFe6dU0LQzq2', '2025-11-04', 'sample_RrD7RAubAR0Pjs9AZzJQvGLQGMyvbAdQPgkZp8xQYYeqgWfyO49jDgoeuhubdNVq6N62RAc1f7lIbLIU125n8oAYeLGxX0MVKGQPE7BmjtJXfz9VsZoaUsDkgnVR1RvFNE1kvvyngyflVBZLgO', 'sample_Du5XE3hhgU8gqKw1vBZmA4R2E7o67hASeSDgjM2wvIbv8EG3iIJHB4BeIZsLzxWSNoC4QLr5jpD1dajMSpsuA8OHNy7hu1FG805rvyt8ZLhYbzyIbp5EzJgJoAilMd0GRaTI90rZeEN9LuvTlb3XzgLRBApayxSy5K0N97KbylZUbxxJl3A7Es17cyDTQrktS7qX75hN0Ru2zoPQXsOtjjjG0bKDIQLebElgfazyO9ks3KOBiuOChogsmE8FixhQZ7hBCm6cl0uftBfDEfekyElRosUNhL8RSBBjSd4VR9RihCY7sk5ZDZo5V3vzZKrpSlDVK1L3tg1OaTBVooqS5PTghRklHwuvQYIWYNdGv1IyEsVcgy3ZvwlfeC0QrG1XiLuIBE1P0eIysQeCbmYw', X'E58088CB98DFBED8B208EBBE88E9AAB8E2A58C6C', X'CEB2EAAE8B67E9B5A667', 'value2', 'b,c,d,a', b'11010011', '2025-06-16 14:46:26', 28.27, 55.30, '{"k1": 242, "k2": "sample_90", "k3": 999.0640950094917, "k4": true}');
INSERT INTO t2 (c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16) VALUES (8075, 8871, 233.69, 'sample_b', '2025-08-17', 'sample_eVmg6h5eFETW0ji5WVAtAizX4Ds5DTpxchZYKuoSYIMp6gCB3KUwkbsl7MWqYg4DA3wPMqv0dQIbCFAjPZTaa5aiUj6DCvzMou7Xh0A4X4gQetbKECTE5fKXo9zV182eEAl7szL7QGp9wo9VyJyTIWZIteUTpYSeiCWw04mvB6JHfaxlmkXtbBaaj6MctzJjkoWADA9xBj9BBe0IK5qzNufqAEOhUf9S7FyNXQqzfI0jWvz30bhtKdS3OOVr2ul0qpcWMKfGWUZBw6etqPnPkxJF0yYRwePv5juxHu1rNBXNvFNEkJAlgxtQ0CWrEfcWqIqJ4NjzAMKN1ZiOvHLZaf5yYQ4WKemqfHQXnhTpTNDJXgzDJlcs8wmxb0vsX8ZrVlS5CYNahB47gB5L6gJMkSD6LPUpbMn97E56V3vquBP4efeuyLMD9TBHtADVeltHceJgvCKY3FE2ZsHHtZ98oiiprClRJhto9YNv4N7zgjSZ00o17MVQKPICGg2ROJ2br7lNzyhuBQJziDtucuc3DNTyYvMe3YToZKxobZeMwNxyizv4sP5yFlw6RmRcMQTOrTCR1rkm6PvaV0kO4JIHHlJ5cotfs4CujoSSXHtkZjaYMKEhymmmV2ij6UXpPwnAHN179qecWbzYNxkxMcOdYgpfhpwY5kNzz2DddugBeCeGfvLe017qCcXJd3i9toZBjPlJxgWT5RPGhi7VXj1Hif9xq7NkmuCmtuD6lMJxsUNmiijeun8fjnFiQrKALzJYMqENTms4n1SdA1OaiCdS97dtMOuSUJip9aTSomB7GO3uM5m3dsTEkqXekZP1azeZUNH89kQQOMknLnvweSpU5y8kl5q6a9GY14SUwIqsp5jH1ZmDKaXmYjooK2KUIpkrYKCUSzTBhfBkWQDb', 'sample_0TCMJFboCpnG3Qb6fJo6I58epvxFSXZxHHZthdoeWUaFLoG9irPhQ2KTqH92YtLvtrVa0wowLc0V0WOXo1Cqqaj9offwXtMWp7Qkc2iWHZuJWqHopXpHTRjpHRUUT9W442Qm19mFloQqXy6FNzpW00mhnZamXg18nuoJJ0KKLuODn0pbgsAHHM9LCMyepZPk8hf5FLT1jRsOKjmeY5X9sxDO0cLw3D671qT6iXYGd4mWOQKGBp8xWt9XakRzvxfPiUtewDhk1BlDuxUqiKB2SUjiTtNzr6JHQqfq6ioPHd9mZefKj3J8tM65vyaQEb2eOILdIBs1XGcnsbJh6mLrsMaYI5po6RXpRm', X'C381E2AAB1C18E457517DA81E59F9CDC86EEB6952123DA8FCFA2E294AEE5859EE784A6EF8EB8C0853D', X'28190B61E69EA6D5B6', 'value3', 'd,a,b', b'01011001', '2025-09-13 03:59:17', 73.32, 65.05, '{"k1": 576, "k2": "mixed_80", "k3": ["tag_16", "tag_91", "tag_35", "tag_8"], "k4": {"k5": "C", "k6": [7, 24, 98], "k7": {"k8": "user_15", "k9": "2025-12-03T14:34:08.822739"}}, "k10": false}');
INSERT INTO t2 (c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16) VALUES (9871, 5529, 516.71, 'sample_d', '2025-08-09', 'sample_eB3Ce8nncgTWYIlD9LVgQrlW46TvwPRhXQvF0zIK6nGPyxpsCft4ufgOPpjWG98QfSnfyGV8I8YyB03RB2iLlRmWasFbfT18YRqnBZJXBhs7JuqzGG3w3oPtXgDV58UBW656W36hU10ItcTf5JfWt3712vZozBlDI2UJl6qjfyGZEu4yTpw3OYsweeOyyeyJY8j7HJW3tnosFQBpKpu0DfuyXkscPG22KKNyv39A6eS9oZ9YzAvWS10GWoUzsvEhmIVDAiG4yNRJZ0hVYUiXdIliDrfO4I5kGUH1lHaFT9ghMjW4177a62kxMJkVDowDQSYWehiwElPIHjR92dnYjLCxh0RtQ9H5Hznzbb7H6tO3FYI7ehVcHubyxDAlUYaY6lY0lXkrbvZxdfZ3HWXUQYZoFoX1PNSD9suLFbU8NtZYBMf93FpOcZl1jdah0KdiGXS4omDfUh8pN51MStiizZKI5CWZ4qXUDBt9J6sa6pj3U5OMGQtVyyifo5a8wx58jfZqxdGHqnVsVuoLo2EG9MvANVlJm4R6mo9yg2BOwF47jdyhLQ5zLMsmhYQsQt9x3LHLFJgVZMvcHLc8fl37g9EeoflA2Ogir79QtKl9NINVpepOkZRQKxBqcQTTYRx27', 'sample_B4ExlE824y40fJydcfY4M3E9AwmjLFOJZKxkCzsffimVwEXcoe9WT34YGyf0JnE6v9iHoszujZIkiL1AoJuDBj6WJ0tTMMmOMCCfz8lSK1xYlyTi0bzOLLICL8yDSZrlRYHqXshyU2LZkexv4SvlGvveKgMQia0tzTShhmHV10BzSsBpsliPzBZBLIYpWNUiSwN7wQcFEcsz0vE1vzgAiWSWuich796dM4MCWNCc4qP6nYPRmDpwOBJaLbHLf8ZUmOfKUVnHX5WkkkkIKscRi1kGpRYBpPggOfAmffkaydkF8ZLpS8', X'CEA9D983E8B685', X'E29292E299A5CD85E6ACB0E1BBA8481E', 'value1', 'c,d,a', b'01101011', '2025-08-19 08:39:40', 9.86, 70.97, '{"k1": 838, "k2": "mixed_100", "k3": ["tag_31"], "k4": {"k5": "B", "k6": [84, 69, 24, 59, 83], "k7": {"k8": "user_29", "k9": "2025-12-03T14:34:08.822739"}}, "k10": false}');
WITH cte_498 AS (SELECT (SELECT AVG(s255.c2) AS subq_col FROM t2 AS s255) AS col_2 FROM t2 AS eqw89) SELECT SUM(s732.col_2) AS subq_col FROM cte_498 AS s732;
WITH cte_498 AS (SELECT (SELECT AVG(s255.c2) AS subq_col FROM t2 AS s255) AS col_2 FROM t2 AS eqw89) SELECT -SUM(-s732.col_2) AS subq_col FROM cte_498 AS s732;
Description: after changing SUM(s732.col_2) to -SUM(-s732.col_2),subq_col's value unexpectedly change mysql> WITH cte_498 AS (SELECT (SELECT AVG(s255.c2) AS subq_col FROM t2 AS s255) AS col_2 FROM t2 AS eqw89) SELECT SUM(s732.col_2) AS subq_col FROM cte_498 AS s732; +------------+ | subq_col | +------------+ | 23905.0000 | +------------+ 1 row in set (0.00 sec) mysql> WITH cte_498 AS (SELECT (SELECT AVG(s255.c2) AS subq_col FROM t2 AS s255) AS col_2 FROM t2 AS eqw89) SELECT -SUM(-s732.col_2) AS subq_col FROM cte_498 AS s732; +------------+ | subq_col | +------------+ | 23904.9999 | +------------+ 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 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) ); INSERT INTO t2 (c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16) VALUES (6313, 9505, 133.75, 'sample_ZyDTZ8ESP9DDoOyVEgikHelQFe6dU0LQzq2', '2025-11-04', 'sample_RrD7RAubAR0Pjs9AZzJQvGLQGMyvbAdQPgkZp8xQYYeqgWfyO49jDgoeuhubdNVq6N62RAc1f7lIbLIU125n8oAYeLGxX0MVKGQPE7BmjtJXfz9VsZoaUsDkgnVR1RvFNE1kvvyngyflVBZLgO', 'sample_Du5XE3hhgU8gqKw1vBZmA4R2E7o67hASeSDgjM2wvIbv8EG3iIJHB4BeIZsLzxWSNoC4QLr5jpD1dajMSpsuA8OHNy7hu1FG805rvyt8ZLhYbzyIbp5EzJgJoAilMd0GRaTI90rZeEN9LuvTlb3XzgLRBApayxSy5K0N97KbylZUbxxJl3A7Es17cyDTQrktS7qX75hN0Ru2zoPQXsOtjjjG0bKDIQLebElgfazyO9ks3KOBiuOChogsmE8FixhQZ7hBCm6cl0uftBfDEfekyElRosUNhL8RSBBjSd4VR9RihCY7sk5ZDZo5V3vzZKrpSlDVK1L3tg1OaTBVooqS5PTghRklHwuvQYIWYNdGv1IyEsVcgy3ZvwlfeC0QrG1XiLuIBE1P0eIysQeCbmYw', X'E58088CB98DFBED8B208EBBE88E9AAB8E2A58C6C', X'CEB2EAAE8B67E9B5A667', 'value2', 'b,c,d,a', b'11010011', '2025-06-16 14:46:26', 28.27, 55.30, '{"k1": 242, "k2": "sample_90", "k3": 999.0640950094917, "k4": true}'); INSERT INTO t2 (c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16) VALUES (8075, 8871, 233.69, 'sample_b', '2025-08-17', 'sample_eVmg6h5eFETW0ji5WVAtAizX4Ds5DTpxchZYKuoSYIMp6gCB3KUwkbsl7MWqYg4DA3wPMqv0dQIbCFAjPZTaa5aiUj6DCvzMou7Xh0A4X4gQetbKECTE5fKXo9zV182eEAl7szL7QGp9wo9VyJyTIWZIteUTpYSeiCWw04mvB6JHfaxlmkXtbBaaj6MctzJjkoWADA9xBj9BBe0IK5qzNufqAEOhUf9S7FyNXQqzfI0jWvz30bhtKdS3OOVr2ul0qpcWMKfGWUZBw6etqPnPkxJF0yYRwePv5juxHu1rNBXNvFNEkJAlgxtQ0CWrEfcWqIqJ4NjzAMKN1ZiOvHLZaf5yYQ4WKemqfHQXnhTpTNDJXgzDJlcs8wmxb0vsX8ZrVlS5CYNahB47gB5L6gJMkSD6LPUpbMn97E56V3vquBP4efeuyLMD9TBHtADVeltHceJgvCKY3FE2ZsHHtZ98oiiprClRJhto9YNv4N7zgjSZ00o17MVQKPICGg2ROJ2br7lNzyhuBQJziDtucuc3DNTyYvMe3YToZKxobZeMwNxyizv4sP5yFlw6RmRcMQTOrTCR1rkm6PvaV0kO4JIHHlJ5cotfs4CujoSSXHtkZjaYMKEhymmmV2ij6UXpPwnAHN179qecWbzYNxkxMcOdYgpfhpwY5kNzz2DddugBeCeGfvLe017qCcXJd3i9toZBjPlJxgWT5RPGhi7VXj1Hif9xq7NkmuCmtuD6lMJxsUNmiijeun8fjnFiQrKALzJYMqENTms4n1SdA1OaiCdS97dtMOuSUJip9aTSomB7GO3uM5m3dsTEkqXekZP1azeZUNH89kQQOMknLnvweSpU5y8kl5q6a9GY14SUwIqsp5jH1ZmDKaXmYjooK2KUIpkrYKCUSzTBhfBkWQDb', 'sample_0TCMJFboCpnG3Qb6fJo6I58epvxFSXZxHHZthdoeWUaFLoG9irPhQ2KTqH92YtLvtrVa0wowLc0V0WOXo1Cqqaj9offwXtMWp7Qkc2iWHZuJWqHopXpHTRjpHRUUT9W442Qm19mFloQqXy6FNzpW00mhnZamXg18nuoJJ0KKLuODn0pbgsAHHM9LCMyepZPk8hf5FLT1jRsOKjmeY5X9sxDO0cLw3D671qT6iXYGd4mWOQKGBp8xWt9XakRzvxfPiUtewDhk1BlDuxUqiKB2SUjiTtNzr6JHQqfq6ioPHd9mZefKj3J8tM65vyaQEb2eOILdIBs1XGcnsbJh6mLrsMaYI5po6RXpRm', X'C381E2AAB1C18E457517DA81E59F9CDC86EEB6952123DA8FCFA2E294AEE5859EE784A6EF8EB8C0853D', X'28190B61E69EA6D5B6', 'value3', 'd,a,b', b'01011001', '2025-09-13 03:59:17', 73.32, 65.05, '{"k1": 576, "k2": "mixed_80", "k3": ["tag_16", "tag_91", "tag_35", "tag_8"], "k4": {"k5": "C", "k6": [7, 24, 98], "k7": {"k8": "user_15", "k9": "2025-12-03T14:34:08.822739"}}, "k10": false}'); INSERT INTO t2 (c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16) VALUES (9871, 5529, 516.71, 'sample_d', '2025-08-09', 'sample_eB3Ce8nncgTWYIlD9LVgQrlW46TvwPRhXQvF0zIK6nGPyxpsCft4ufgOPpjWG98QfSnfyGV8I8YyB03RB2iLlRmWasFbfT18YRqnBZJXBhs7JuqzGG3w3oPtXgDV58UBW656W36hU10ItcTf5JfWt3712vZozBlDI2UJl6qjfyGZEu4yTpw3OYsweeOyyeyJY8j7HJW3tnosFQBpKpu0DfuyXkscPG22KKNyv39A6eS9oZ9YzAvWS10GWoUzsvEhmIVDAiG4yNRJZ0hVYUiXdIliDrfO4I5kGUH1lHaFT9ghMjW4177a62kxMJkVDowDQSYWehiwElPIHjR92dnYjLCxh0RtQ9H5Hznzbb7H6tO3FYI7ehVcHubyxDAlUYaY6lY0lXkrbvZxdfZ3HWXUQYZoFoX1PNSD9suLFbU8NtZYBMf93FpOcZl1jdah0KdiGXS4omDfUh8pN51MStiizZKI5CWZ4qXUDBt9J6sa6pj3U5OMGQtVyyifo5a8wx58jfZqxdGHqnVsVuoLo2EG9MvANVlJm4R6mo9yg2BOwF47jdyhLQ5zLMsmhYQsQt9x3LHLFJgVZMvcHLc8fl37g9EeoflA2Ogir79QtKl9NINVpepOkZRQKxBqcQTTYRx27', 'sample_B4ExlE824y40fJydcfY4M3E9AwmjLFOJZKxkCzsffimVwEXcoe9WT34YGyf0JnE6v9iHoszujZIkiL1AoJuDBj6WJ0tTMMmOMCCfz8lSK1xYlyTi0bzOLLICL8yDSZrlRYHqXshyU2LZkexv4SvlGvveKgMQia0tzTShhmHV10BzSsBpsliPzBZBLIYpWNUiSwN7wQcFEcsz0vE1vzgAiWSWuich796dM4MCWNCc4qP6nYPRmDpwOBJaLbHLf8ZUmOfKUVnHX5WkkkkIKscRi1kGpRYBpPggOfAmffkaydkF8ZLpS8', X'CEA9D983E8B685', X'E29292E299A5CD85E6ACB0E1BBA8481E', 'value1', 'c,d,a', b'01101011', '2025-08-19 08:39:40', 9.86, 70.97, '{"k1": 838, "k2": "mixed_100", "k3": ["tag_31"], "k4": {"k5": "B", "k6": [84, 69, 24, 59, 83], "k7": {"k8": "user_29", "k9": "2025-12-03T14:34:08.822739"}}, "k10": false}'); WITH cte_498 AS (SELECT (SELECT AVG(s255.c2) AS subq_col FROM t2 AS s255) AS col_2 FROM t2 AS eqw89) SELECT SUM(s732.col_2) AS subq_col FROM cte_498 AS s732; WITH cte_498 AS (SELECT (SELECT AVG(s255.c2) AS subq_col FROM t2 AS s255) AS col_2 FROM t2 AS eqw89) SELECT -SUM(-s732.col_2) AS subq_col FROM cte_498 AS s732;