Description:
after changing AVG(cte.col_2) to -AVG(-cte.col_2) ,rows become less
mysql> WITH cte_814 AS (SELECT STR_TO_DATE(fgs19.c4, fgs19.c6) AS col_1, YEAR(fgs19.c13) AS col_2, YEAR(fgs19.c5) AS col_3, CEIL(fgs19.c2) AS col_4 FROM t2 AS fgs19) SELECT DISTINCT AVG(cte.col_2) AS col_1_3, LAST_VALUE(cte.col_2) OVER () AS col_1_4 FROM cte_814 AS cte GROUP BY cte.col_1 - INTERVAL cte.col_3 DAY, TIME(cte.col_1), cte.col_2 HAVING (GROUP_CONCAT(cte.col_2, cte.col_2 ORDER BY cte.col_2) > 10);
+-----------+---------+
| col_1_3 | col_1_4 |
+-----------+---------+
| 2024.0000 | 2025 |
| 2025.0000 | 2025 |
+-----------+---------+
2 rows in set, 4 warnings (0.00 sec)
mysql> WITH cte_814 AS (SELECT STR_TO_DATE(fgs19.c4, fgs19.c6) AS col_1, YEAR(fgs19.c13) AS col_2, YEAR(fgs19.c5) AS col_3, CEIL(fgs19.c2) AS col_4 FROM t2 AS fgs19) SELECT DISTINCT -AVG(-cte.col_2) AS col_1_3, LAST_VALUE(cte.col_2) OVER () AS col_1_4 FROM cte_814 AS cte GROUP BY cte.col_1 - INTERVAL cte.col_3 DAY, TIME(cte.col_1), cte.col_2 HAVING (GROUP_CONCAT(cte.col_2, cte.col_2 ORDER BY cte.col_2) > 10);
+-----------+---------+
| col_1_3 | col_1_4 |
+-----------+---------+
| 2024.0000 | 2025 |
+-----------+---------+
1 row in set, 4 warnings (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 (4201, 139, 983.73, 'sample_LFWAE', '2025-04-28', 'sample_HgO1xcnIDKDGPBxAFcVtHhFyAtU8o14XoSmWJMp5xRMdK64CGU7pZw3L2NVg36pWjSrRHtXijabsAe2dAqDkvJbyKOsJwXKurDsbjRj2qNzJ9LEgpJzu2qjkTkoBX0Ejr3mVLeJbpakJbP7IIWHB49OBfNV458yUdQPreQdDQcH5VeMX9QSj4p2tzmawpCG1VVUfYSN2A4y5J9HlQzvRe3cy0uB6mvPjVxQC2ZJlbJaCXTD46yfRyarRJ3Oxr2mP0qmSmmGzDVBSQSNbVg6GlMMEMKrqjDOw4m36QLRNpAeA7lmdnoA4iOYGM5FNXiDZhVhrOqzGya7kzhsAb88V6cndWPiDB5FRNCfUDvdMatEspIanFwrOL6nHxcB5RAJ2bEhLAfNPtGtbcE2zKjuZKR1TuxVv', 'sample_kpyoWe4re7fisU9DeyA9qqUBTECHdqENoJH0kG8Hwzudp2lD19RPMRlA3DLgoUT4K6B5czF9Itf1pfsdLIrCzk26hTjCnmTRFHkkDhNE86x1aWpjk3x34S6GKVByovB9mfa6yooyw0gNAgQ2KZ5ummouMoTKdyMcrbFMooWI7Ua0ccvdOzW9rSoTkHoApTW3DemS4aR7fAoS5gzGWPPwW0XaJLhfRr90znJafq8MvSTdxQAXIZwOOIfv1WuHRzptmjNidXsy1RS0sqNiehDYAjrpZjPRewnAbPDXZCrOODBlty6cLmzdJK8Hn2vDCtFdQsLeTihRGG6Rozs6vjDNqhNz14NF23hMxEfp4IZTeWO6iXxp8iZMfPo6AB3Sr0D4ikc3B0OuAoL4FKS6NUstKDFZEdQP9ybU4uFOWEbViNjwYR0zBpb8dmODObc89MW6MEgEtoZdHChdvlxspN3d2irDwJIOMYVqVPIpsUkMvt2PTZJagfWU40syfBA6rIxoc53OVGPzDISg9GNLZs1TJTShyOXn6zx6', X'110BDEA5E5818BECAAB6C5BBD7B203C98C697FEAA182EF9CAFD0BC', X'CE8717E48C84D486E39BBBD1BDD590D682C990D697EA89B72EEE98B8EDA591CBBA', 'value3', 'b', b'11101100', '2025-07-15 10:22:35', 27.45, 18.31, '{"k1": 448, "k2": "mixed_8", "k3": ["tag_73"], "k4": {"k5": "A", "k6": [81, 61, 60, 23], "k7": {"k8": "user_69", "k9": "2025-12-03T14:34:08.822739"}}, "k10": true}');
INSERT INTO t2 (c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16) VALUES (6040, 2644, 953.79, 'sample_FmLs', '2025-05-22', 'sample_voMjYlYT1PYWru1rIObmF7dvXCkj3pmrN6mPoBqyYzsa4cy52tYKesQOkWr9qzosQEjx6GT15b0FfCpHIdwczWNizWtqpLKPzKN6u1sZnbgBNdn28YevPpyUaeb3a1MyMbvPuXDCwOsQGJxxBfFKnd8lnlZ5gDdsPFSza2kWkBR5Xl7pdPCDaGcVXSAnOloAlf23vV2JQsVnODTacuIAKrDvhP2pSJ8xXbFiec38jxl483nfC82rtu6jCOhvQiB4fnHp9xE49Sp3aBvjEWDpkVlNwOb7MCdl', 'sample_a0HduYSdEF6lJiDOt3dDhTJxwE2xYUDQtspt176F26rVyPguE1OoStOGKlTz3zDpFmWXAFkvGdC6S1QuMOQUBrNxWKwp0FOxh9Oz2ISoIezizCVN8jo9ZQzMnyIA0txY9pbMZsYHhuqjFGsOrXD6PaGzR7cPNq7G6Lf2ibgalxSIFrxKz6BiVQZyA81pldb9OqMwk66UTgB8NVWous8FqUCCH7tJMtyepXI3WcZbyZjNqWq8al1DSkJFQyZCfjhuWwNU8Lhia9LL1cfDysGVqe9NNfu9IsRzlB0MSlv8zb2UyT2XiXisyCF3CfJWU7XBs5zy5OuNrJX7JQau7TYtAeWnGrgMHCDTVNO5EatuE7si3fFmYmrTUYhXnsxBaUmaMowxjJyHS1fZxd9jcsbDpv6CMjHSPaEpU9adSJ16CudwQ6RS1LiPlPcX7fTdnv8xbaNcfqRtoAfx0fySLyF3Dy2xSoFTFD34weNfwE2GUlhx2ME5jnpBu6b1lX7VfnxghYAlNnN7EEr1EtCDvJ9TrqrfkMJasFplkAEUFfEA4MzAqfBeNp93HDani6si3c6rVpDDrHRrKh3jYOeHM0Jfg9bVf30O1ToxS6l9qPBbyOAXNBl8kiY9yTku7bdiJs6IFpAuWtX3p6yY1eEPT4gfWdG0Ty0UarzN4XkMNrNY5dDh5aOziHV4JJbJd5cfIj8posZnzPOHW6d3snxDRpaifS7dlWvWP6z7ovaoPjmDekyEunkTOGiRjRku3GZ4DTdtJOJRWLe8rJyAiXoUGQAr7n6Yjvb8lVq2GcKiPAtqXIUr30fW8dkBkKhf5I3e1o9N9MeoxjrWgNejxGlNTafJxxnYFYGlapYBbJvfUWkXrZ1TL4dtDJ2STb88J25g3gcJepPcMSC1A9vQ2xJqfYTjHxXaveZtjNegH9heHB9LlPOxCRCpMYxWjXWw81jw9FPtZv1Mx6e1lDWpIdQ6HdJz6iY1NjJ5ey3hEVbQ7oTR67fYMpJYJDTNBYGrK6n17CsawpwZRdBxbFeJSfvQChJjP89Femi6PDPDDfbs72YVe0yJvMWIqEwXTlPhIXEtdL2ycViVqGlANGWPldIpCxSVmJ5Cz1fqLSO6le7klLoJPtjulZZp0GMPbV8UpVHriNYbXHGsBk4Fdhnp9qOy5oAOmw3UmbaiJCbj3tF7AIl4zHweAg1dTbCLb1vXLwyYKF7HqW6uNDTbrZhVmBn2j45CFUaE5hLxu7GKVgz0o6g71MgJhPEqobXItfSP7YHWrK33q8xQW5qkx3DlzIRusigYPytZZOU8nM59yN497VbKB0LFidzbItg4lebCsCNgsvACsFxA7LWumMFaZCB0FZ9DC8KtznQpenasJa1BfuCEEJuuBZuCrY91coKV1SzOnUgYdqNjXYYA0vyIm6rYI87ViEd0PNLvL8Y8EBXHtWKJBIejp3SQAb9nFoZl8xG2ZpsQXq8PSDPMiQYRNewQ68eVPlCrizUD', X'4DE8AFAD1F7F', X'680513D3A6CCA3C59DCFA4CB95DFA8E7BFB0CE8FECBBB965E3A78341', 'value2', 'c', b'00001110', '2024-12-11 14:46:51', 16.60, 19.66, '{"k1": {"k2": 503, "k3": "user_23", "k4": {"k5": 35, "k6": "user2@example.com", "k7": {"k8": "city_93", "k9": "Sample Country"}}}, "k10": "2025-12-03T14:34:08.823738"}');
WITH cte_814 AS (SELECT STR_TO_DATE(fgs19.c4, fgs19.c6) AS col_1, YEAR(fgs19.c13) AS col_2, YEAR(fgs19.c5) AS col_3, CEIL(fgs19.c2) AS col_4 FROM t2 AS fgs19) SELECT DISTINCT AVG(cte.col_2) AS col_1_3, LAST_VALUE(cte.col_2) OVER () AS col_1_4 FROM cte_814 AS cte GROUP BY cte.col_1 - INTERVAL cte.col_3 DAY, TIME(cte.col_1), cte.col_2 HAVING (GROUP_CONCAT(cte.col_2, cte.col_2 ORDER BY cte.col_2) > 10);
WITH cte_814 AS (SELECT STR_TO_DATE(fgs19.c4, fgs19.c6) AS col_1, YEAR(fgs19.c13) AS col_2, YEAR(fgs19.c5) AS col_3, CEIL(fgs19.c2) AS col_4 FROM t2 AS fgs19) SELECT DISTINCT -AVG(-cte.col_2) AS col_1_3, LAST_VALUE(cte.col_2) OVER () AS col_1_4 FROM cte_814 AS cte GROUP BY cte.col_1 - INTERVAL cte.col_3 DAY, TIME(cte.col_1), cte.col_2 HAVING (GROUP_CONCAT(cte.col_2, cte.col_2 ORDER BY cte.col_2) > 10);
Description: after changing AVG(cte.col_2) to -AVG(-cte.col_2) ,rows become less mysql> WITH cte_814 AS (SELECT STR_TO_DATE(fgs19.c4, fgs19.c6) AS col_1, YEAR(fgs19.c13) AS col_2, YEAR(fgs19.c5) AS col_3, CEIL(fgs19.c2) AS col_4 FROM t2 AS fgs19) SELECT DISTINCT AVG(cte.col_2) AS col_1_3, LAST_VALUE(cte.col_2) OVER () AS col_1_4 FROM cte_814 AS cte GROUP BY cte.col_1 - INTERVAL cte.col_3 DAY, TIME(cte.col_1), cte.col_2 HAVING (GROUP_CONCAT(cte.col_2, cte.col_2 ORDER BY cte.col_2) > 10); +-----------+---------+ | col_1_3 | col_1_4 | +-----------+---------+ | 2024.0000 | 2025 | | 2025.0000 | 2025 | +-----------+---------+ 2 rows in set, 4 warnings (0.00 sec) mysql> WITH cte_814 AS (SELECT STR_TO_DATE(fgs19.c4, fgs19.c6) AS col_1, YEAR(fgs19.c13) AS col_2, YEAR(fgs19.c5) AS col_3, CEIL(fgs19.c2) AS col_4 FROM t2 AS fgs19) SELECT DISTINCT -AVG(-cte.col_2) AS col_1_3, LAST_VALUE(cte.col_2) OVER () AS col_1_4 FROM cte_814 AS cte GROUP BY cte.col_1 - INTERVAL cte.col_3 DAY, TIME(cte.col_1), cte.col_2 HAVING (GROUP_CONCAT(cte.col_2, cte.col_2 ORDER BY cte.col_2) > 10); +-----------+---------+ | col_1_3 | col_1_4 | +-----------+---------+ | 2024.0000 | 2025 | +-----------+---------+ 1 row in set, 4 warnings (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 (4201, 139, 983.73, 'sample_LFWAE', '2025-04-28', 'sample_HgO1xcnIDKDGPBxAFcVtHhFyAtU8o14XoSmWJMp5xRMdK64CGU7pZw3L2NVg36pWjSrRHtXijabsAe2dAqDkvJbyKOsJwXKurDsbjRj2qNzJ9LEgpJzu2qjkTkoBX0Ejr3mVLeJbpakJbP7IIWHB49OBfNV458yUdQPreQdDQcH5VeMX9QSj4p2tzmawpCG1VVUfYSN2A4y5J9HlQzvRe3cy0uB6mvPjVxQC2ZJlbJaCXTD46yfRyarRJ3Oxr2mP0qmSmmGzDVBSQSNbVg6GlMMEMKrqjDOw4m36QLRNpAeA7lmdnoA4iOYGM5FNXiDZhVhrOqzGya7kzhsAb88V6cndWPiDB5FRNCfUDvdMatEspIanFwrOL6nHxcB5RAJ2bEhLAfNPtGtbcE2zKjuZKR1TuxVv', 'sample_kpyoWe4re7fisU9DeyA9qqUBTECHdqENoJH0kG8Hwzudp2lD19RPMRlA3DLgoUT4K6B5czF9Itf1pfsdLIrCzk26hTjCnmTRFHkkDhNE86x1aWpjk3x34S6GKVByovB9mfa6yooyw0gNAgQ2KZ5ummouMoTKdyMcrbFMooWI7Ua0ccvdOzW9rSoTkHoApTW3DemS4aR7fAoS5gzGWPPwW0XaJLhfRr90znJafq8MvSTdxQAXIZwOOIfv1WuHRzptmjNidXsy1RS0sqNiehDYAjrpZjPRewnAbPDXZCrOODBlty6cLmzdJK8Hn2vDCtFdQsLeTihRGG6Rozs6vjDNqhNz14NF23hMxEfp4IZTeWO6iXxp8iZMfPo6AB3Sr0D4ikc3B0OuAoL4FKS6NUstKDFZEdQP9ybU4uFOWEbViNjwYR0zBpb8dmODObc89MW6MEgEtoZdHChdvlxspN3d2irDwJIOMYVqVPIpsUkMvt2PTZJagfWU40syfBA6rIxoc53OVGPzDISg9GNLZs1TJTShyOXn6zx6', X'110BDEA5E5818BECAAB6C5BBD7B203C98C697FEAA182EF9CAFD0BC', X'CE8717E48C84D486E39BBBD1BDD590D682C990D697EA89B72EEE98B8EDA591CBBA', 'value3', 'b', b'11101100', '2025-07-15 10:22:35', 27.45, 18.31, '{"k1": 448, "k2": "mixed_8", "k3": ["tag_73"], "k4": {"k5": "A", "k6": [81, 61, 60, 23], "k7": {"k8": "user_69", "k9": "2025-12-03T14:34:08.822739"}}, "k10": true}'); INSERT INTO t2 (c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16) VALUES (6040, 2644, 953.79, 'sample_FmLs', '2025-05-22', 'sample_voMjYlYT1PYWru1rIObmF7dvXCkj3pmrN6mPoBqyYzsa4cy52tYKesQOkWr9qzosQEjx6GT15b0FfCpHIdwczWNizWtqpLKPzKN6u1sZnbgBNdn28YevPpyUaeb3a1MyMbvPuXDCwOsQGJxxBfFKnd8lnlZ5gDdsPFSza2kWkBR5Xl7pdPCDaGcVXSAnOloAlf23vV2JQsVnODTacuIAKrDvhP2pSJ8xXbFiec38jxl483nfC82rtu6jCOhvQiB4fnHp9xE49Sp3aBvjEWDpkVlNwOb7MCdl', 'sample_a0HduYSdEF6lJiDOt3dDhTJxwE2xYUDQtspt176F26rVyPguE1OoStOGKlTz3zDpFmWXAFkvGdC6S1QuMOQUBrNxWKwp0FOxh9Oz2ISoIezizCVN8jo9ZQzMnyIA0txY9pbMZsYHhuqjFGsOrXD6PaGzR7cPNq7G6Lf2ibgalxSIFrxKz6BiVQZyA81pldb9OqMwk66UTgB8NVWous8FqUCCH7tJMtyepXI3WcZbyZjNqWq8al1DSkJFQyZCfjhuWwNU8Lhia9LL1cfDysGVqe9NNfu9IsRzlB0MSlv8zb2UyT2XiXisyCF3CfJWU7XBs5zy5OuNrJX7JQau7TYtAeWnGrgMHCDTVNO5EatuE7si3fFmYmrTUYhXnsxBaUmaMowxjJyHS1fZxd9jcsbDpv6CMjHSPaEpU9adSJ16CudwQ6RS1LiPlPcX7fTdnv8xbaNcfqRtoAfx0fySLyF3Dy2xSoFTFD34weNfwE2GUlhx2ME5jnpBu6b1lX7VfnxghYAlNnN7EEr1EtCDvJ9TrqrfkMJasFplkAEUFfEA4MzAqfBeNp93HDani6si3c6rVpDDrHRrKh3jYOeHM0Jfg9bVf30O1ToxS6l9qPBbyOAXNBl8kiY9yTku7bdiJs6IFpAuWtX3p6yY1eEPT4gfWdG0Ty0UarzN4XkMNrNY5dDh5aOziHV4JJbJd5cfIj8posZnzPOHW6d3snxDRpaifS7dlWvWP6z7ovaoPjmDekyEunkTOGiRjRku3GZ4DTdtJOJRWLe8rJyAiXoUGQAr7n6Yjvb8lVq2GcKiPAtqXIUr30fW8dkBkKhf5I3e1o9N9MeoxjrWgNejxGlNTafJxxnYFYGlapYBbJvfUWkXrZ1TL4dtDJ2STb88J25g3gcJepPcMSC1A9vQ2xJqfYTjHxXaveZtjNegH9heHB9LlPOxCRCpMYxWjXWw81jw9FPtZv1Mx6e1lDWpIdQ6HdJz6iY1NjJ5ey3hEVbQ7oTR67fYMpJYJDTNBYGrK6n17CsawpwZRdBxbFeJSfvQChJjP89Femi6PDPDDfbs72YVe0yJvMWIqEwXTlPhIXEtdL2ycViVqGlANGWPldIpCxSVmJ5Cz1fqLSO6le7klLoJPtjulZZp0GMPbV8UpVHriNYbXHGsBk4Fdhnp9qOy5oAOmw3UmbaiJCbj3tF7AIl4zHweAg1dTbCLb1vXLwyYKF7HqW6uNDTbrZhVmBn2j45CFUaE5hLxu7GKVgz0o6g71MgJhPEqobXItfSP7YHWrK33q8xQW5qkx3DlzIRusigYPytZZOU8nM59yN497VbKB0LFidzbItg4lebCsCNgsvACsFxA7LWumMFaZCB0FZ9DC8KtznQpenasJa1BfuCEEJuuBZuCrY91coKV1SzOnUgYdqNjXYYA0vyIm6rYI87ViEd0PNLvL8Y8EBXHtWKJBIejp3SQAb9nFoZl8xG2ZpsQXq8PSDPMiQYRNewQ68eVPlCrizUD', X'4DE8AFAD1F7F', X'680513D3A6CCA3C59DCFA4CB95DFA8E7BFB0CE8FECBBB965E3A78341', 'value2', 'c', b'00001110', '2024-12-11 14:46:51', 16.60, 19.66, '{"k1": {"k2": 503, "k3": "user_23", "k4": {"k5": 35, "k6": "user2@example.com", "k7": {"k8": "city_93", "k9": "Sample Country"}}}, "k10": "2025-12-03T14:34:08.823738"}'); WITH cte_814 AS (SELECT STR_TO_DATE(fgs19.c4, fgs19.c6) AS col_1, YEAR(fgs19.c13) AS col_2, YEAR(fgs19.c5) AS col_3, CEIL(fgs19.c2) AS col_4 FROM t2 AS fgs19) SELECT DISTINCT AVG(cte.col_2) AS col_1_3, LAST_VALUE(cte.col_2) OVER () AS col_1_4 FROM cte_814 AS cte GROUP BY cte.col_1 - INTERVAL cte.col_3 DAY, TIME(cte.col_1), cte.col_2 HAVING (GROUP_CONCAT(cte.col_2, cte.col_2 ORDER BY cte.col_2) > 10); WITH cte_814 AS (SELECT STR_TO_DATE(fgs19.c4, fgs19.c6) AS col_1, YEAR(fgs19.c13) AS col_2, YEAR(fgs19.c5) AS col_3, CEIL(fgs19.c2) AS col_4 FROM t2 AS fgs19) SELECT DISTINCT -AVG(-cte.col_2) AS col_1_3, LAST_VALUE(cte.col_2) OVER () AS col_1_4 FROM cte_814 AS cte GROUP BY cte.col_1 - INTERVAL cte.col_3 DAY, TIME(cte.col_1), cte.col_2 HAVING (GROUP_CONCAT(cte.col_2, cte.col_2 ORDER BY cte.col_2) > 10);