Description:
after adding DENSE_RANK() OVER (PARTITION BY ejc47.c5 ORDER BY ejc47.c1 DESC),the result unexpectedly become Null
mysql> WITH cte_631 AS (SELECT SUM(DISTINCT ejc47.c1) AS col_1 FROM t1 AS ejc47 GROUP BY ejc47.c5, ejc47.c1) SELECT qpd67.col_1 AS col_3 FROM cte_631 AS qpd67;
+-------+
| col_3 |
+-------+
| 8644 |
| 9502 |
| 9828 |
| 3575 |
| 4556 |
| 2015 |
| 2932 |
| 8711 |
| 3423 |
| 1986 |
| 6432 |
| 4362 |
| 3584 |
| 954 |
+-------+
14 rows in set (0.00 sec)
mysql> WITH cte_631 AS (SELECT SUM(DISTINCT ejc47.c1) AS col_1, DENSE_RANK() OVER (PARTITION BY ejc47.c5 ORDER BY ejc47.c1 DESC) AS col_2 FROM t1 AS ejc47 GROUP BY ejc47.c5, ejc47.c1) SELECT qpd67.col_1 AS col_3 FROM cte_631 AS qpd67;
+-------+
| col_3 |
+-------+
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
+-------+
14 rows 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 (3584, 'sample_AQJP3FCb7PX46hF2d3VoKsPIHtIgzieVgOK1Ue7iSbNProjjh', 'sample_v', 36, '2025-09-07', 'sample_J');
INSERT INTO t1 (c1, c2, c3, c4, c5, c6) VALUES (954, 'sample_0kfPnPKeUjkycwqHxj5OtQ8', 'sample_sQ', 90, '2025-10-10', 'sample_C');
INSERT INTO t1 (c1, c2, c3, c4, c5, c6) VALUES (1986, 'sample_tJaorTY4zIEBbb8IM4auYieeVSd80foFpJh3MIv0Rz8fEgfYthMyHLdUMnlIN6NnP0VoAFBpSEUcHovYpCFbGrC98Y52vXpgXsmsEBDYj6ecgN2JkDh393f4hhIjFLpqffJrE5Z09WtyvK6PT0IcDNjF6S16yhunjPWFxaAbK4Z6', 'sample_bIa5736UIVD92z8u5rQ0PaRpPlOEwroZ4Xoqc4jDp4SiRROqU4u2EvOZZupAHmFPuFcWjOC6V16yTz58i3824l1EdwAgMPAPy9RM2sHerAB3L3eAWFGFd', 68, '2025-04-20', 'sample_KWK');
INSERT INTO t1 (c1, c2, c3, c4, c5, c6) VALUES (6432, 'sample_apUxsuYxJ7yJ6etwr3Sd7Cg7l1JHXVRSgSUF689u4w2greqF3zbivycDbv6qA4Wusopmd80qoDFLGg1ercUJBRyEl0j0aw1JXgj35Pg4JK2lNYBG2UPKBvS3jhsYpkYbhUNtq5zfTal0MXOuOk4CydQWObucwCG1tGcBtxVHzwZf18W2M7Fl2c8AzYVIIn', 'sample_xoju7XvqD9RJhJ2NAoI2KxR5QZdZKDIjrOdN8MahX1k3dJu9UxKIjMzLtBjiMnoN5JxDvSR7bHNsAECcv5onlaOPg4TBQg4yQw6ThZm8lvYZcq9lBqZap4SoxgEZLdQaIflJvl7EcmCUqqRgQEes', 48, '2025-04-28', 'sample_N');
INSERT INTO t1 (c1, c2, c3, c4, c5, c6) VALUES (8644, 'sample_IYRIsvectgAbcVIlfnvNd9kX3FNkOo4kAKKY1BDBLFQpH7ksvMtBllsZK16dP8T7twvH59dyaLTQuKIciaq8hnHB49XU7pS4kDw8Gt9mASD1ylSfvFBVtDNT', 'sample_7K12anyKLHlFNF5tfck9rj0CREpw10By71a2xm7uMRqIH61pLHnO3mXiWV0Ql013c5vK', 37, '2024-11-16', 'sample_8');
INSERT INTO t1 (c1, c2, c3, c4, c5, c6) VALUES (9828, 'sample_mbTbyuGtnVI5eoQZozrqRGnCbFGq5YiJjIAQ6m5pyEN0KYZtC8nHtcPrUXpN8Ox9SpGW03NGGx1rgIfMQzTzUQDSJyNReXc7SzF8lxZGBkTzeRFfSuV1', 'sample_w848NDNkIDLOlkpaaODWH9A0RwNZ3y4Up1tGogrgGXva1pea9fQC785AX81aC0JjCpCfVuAhR3sedKdIDkph0vCxL9t6vWoUxCcx1Z1PhcFsDkuO86Lm30jCHqsgwEkByFdBtl9Dapi0UdGLs4O7Nt1LJOAjxDeWrmGT52FyIlsB1dVyP84albq7J7x5ZLVhHk0pCHeRKK4Tq', 6, '2024-12-11', 'sample_Yb');
INSERT INTO t1 (c1, c2, c3, c4, c5, c6) VALUES (8711, 'sample_H0bxIKEbtSsSNoE4fQ02go0uWwMukodh2M0oTtTiefhg5SJf', 'sample_VCWYGsDcS9gn6qUA04DNxDeY2gvg41cRs30Zfb6T5o5USbnA4tNwyRbM5tWXmhTTzs9nfZGvC25Jwi8P8WKlHCFIxaU17VQP', 22, '2025-03-11', 'sample_ML');
INSERT INTO t1 (c1, c2, c3, c4, c5, c6) VALUES (4362, 'sample_dfW8hx4eHsITolDicvSc3HtZMQoP5soWVj5f5DSfOUrFOLuh0pGg4XrlvWqaeE8hfyNz2QHBvkIMJTykgQh79yqtceTNYfqVh7bKkLhlziwC5kLCFQLmbhrIoPhnyKS8NkUTh8EmqOqGq6gxIqnjGxIj0r7g8gJDQpMq2b6UNCCrTCE6GGo3EQ4pMKugOzDDz0Qq1jR4QpBozUDUR8CrOjPwRLWJGJ5BQBedmiz8h8NlwxwPgRsD', 'sample_y5ydeMxFnLnKxjREIetegsfUIESumZlnvBgvQ5Bt5UmgsKkuSPS4lQYYPowYSM8TMUO2jk6bzF3uknBtscdkMIvnZiY1NyEo385kHttCw1KIBlT6eNPqLVkQXrEhOpMOAPljJUuQoeXt0QcQF2Khgii5VYJiPfgMNQvKehkOwkGbHc9qqhZomCRqs', 48, '2025-08-29', 'sample_WM');
INSERT INTO t1 (c1, c2, c3, c4, c5, c6) VALUES (4556, 'sample_RhTbFvFWS1XPaycs42gVgoMSu4Z9YICN7KgWyZexa2ya1', 'sample_qmB7afvmuDgVbvaFhFpjOnqU64Zy0eujWu6xQXVhMAd1kdLe0HePMS5fmpz0oISOmKP7Th1WS3UteHN43F6mTdVZaXl2BDwRyCCVrPxthTfZ47UiVzjAT70U', 68, '2025-02-11', 'sample_w');
INSERT INTO t1 (c1, c2, c3, c4, c5, c6) VALUES (2932, 'sample_Hzj6qpaayHPRlIe9FppGcOnyHPTxVbgehj491ZooJze9rIy8RzOgRg97Igq', 'sample_4goBZVP5bQH16F3wBnbZ', 39, '2025-03-08', 'sample_C');
INSERT INTO t1 (c1, c2, c3, c4, c5, c6) VALUES (3575, 'sample_m5Bv1aDup5Nda', 'sample_4fkIoG9frxqxulw2LelBtTmYxXsSLVhmY5AZaDfRYlYeztqRhO', 71, '2024-12-20', 'sample_a');
INSERT INTO t1 (c1, c2, c3, c4, c5, c6) VALUES (2015, 'sample_uWIDMYCoSeu4lstIaExP4t4jdhYUai8NkLulSYodVu5beS0eVKkV0mWA4ZoRjR4Dumhs', 'sample_03UW3ZkzKuw3hixEOZdwogETBWxkMIxVZ8qtdf6ahBeEREuyxjhup7bSCDOtsKUuWDPthxH6HgmUQYfb2RJKmhoFbyQ4xsgBKqDxPttGDdSJg5aAVDOuVorOAKVkx507rRYCWuGHWvCyH3Llxv5lHmwUBZ4VsTN4bQMs', 47, '2025-02-25', 'sample_e');
INSERT INTO t1 (c1, c2, c3, c4, c5, c6) VALUES (9502, 'sample_gRZJisQ8CnqOPKTu10uL5iYyDxfjBuYlxeGEhdMpSqKcVtrL6sQhgrP2OkMaJlyFM7WyiE4ItMIPoNXiwYB0IqMn53P3BXgC05V8qF1488Pu5rL1KhNGHRWQbBOT69Bh6mG3l7XNZDxQWAr8BwihxWkhEtLuycJKTswb60G6lCYcPNQ1gLBax7s4MAW0IaVebCZWUK1OED8', 'sample_ZcnMv5EnefxK', 80, '2024-12-08', 'sample_O');
INSERT INTO t1 (c1, c2, c3, c4, c5, c6) VALUES (3423, 'sample_m8c5PmbiCPYM7BgM5Ef7HeBIHaTW0mU8xgjZXlvy0jcU9XnhTsfHajuW2cka6u99VmhUdzKxa2FhQDytaPeZpKtOQpFw4GlQn4fs0UfGH4aBzUNc0afdFjfcM1eZGOvWLQNuepm0nmBUfyOALf65lvE3XuSjgHNTk9mrKoa0jSXd6Ou6qt9u', 'sample_QpivJZaxO2b86puDimzQjRdO3qfJV0uDVoDgbRAQkHSvE1Smn2u6sGCwIjCSFl3nnLmjhI3B1evfQsUSsJcSz7RUjOFbMqDA8dyzwK9QS9kM0RTQ4ivh5A6AVtlntqI1uiqZrvbkjAejieEnZKsaL4mS7bvO1mQpMBiyyGfp6J8Lz0ibO3VpIop0Apg2', 41, '2025-04-01', 'sample_JY');
CREATE INDEX idx_t1_c5 ON t1 (c5);
WITH cte_631 AS (SELECT SUM(DISTINCT ejc47.c1) AS col_1 FROM t1 AS ejc47 GROUP BY ejc47.c5, ejc47.c1) SELECT qpd67.col_1 AS col_3 FROM cte_631 AS qpd67;
WITH cte_631 AS (SELECT SUM(DISTINCT ejc47.c1) AS col_1, DENSE_RANK() OVER (PARTITION BY ejc47.c5 ORDER BY ejc47.c1 DESC) AS col_2 FROM t1 AS ejc47 GROUP BY ejc47.c5, ejc47.c1) SELECT qpd67.col_1 AS col_3 FROM cte_631 AS qpd67;
Description: after adding DENSE_RANK() OVER (PARTITION BY ejc47.c5 ORDER BY ejc47.c1 DESC),the result unexpectedly become Null mysql> WITH cte_631 AS (SELECT SUM(DISTINCT ejc47.c1) AS col_1 FROM t1 AS ejc47 GROUP BY ejc47.c5, ejc47.c1) SELECT qpd67.col_1 AS col_3 FROM cte_631 AS qpd67; +-------+ | col_3 | +-------+ | 8644 | | 9502 | | 9828 | | 3575 | | 4556 | | 2015 | | 2932 | | 8711 | | 3423 | | 1986 | | 6432 | | 4362 | | 3584 | | 954 | +-------+ 14 rows in set (0.00 sec) mysql> WITH cte_631 AS (SELECT SUM(DISTINCT ejc47.c1) AS col_1, DENSE_RANK() OVER (PARTITION BY ejc47.c5 ORDER BY ejc47.c1 DESC) AS col_2 FROM t1 AS ejc47 GROUP BY ejc47.c5, ejc47.c1) SELECT qpd67.col_1 AS col_3 FROM cte_631 AS qpd67; +-------+ | col_3 | +-------+ | NULL | | NULL | | NULL | | NULL | | NULL | | NULL | | NULL | | NULL | | NULL | | NULL | | NULL | | NULL | | NULL | | NULL | +-------+ 14 rows 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 (3584, 'sample_AQJP3FCb7PX46hF2d3VoKsPIHtIgzieVgOK1Ue7iSbNProjjh', 'sample_v', 36, '2025-09-07', 'sample_J'); INSERT INTO t1 (c1, c2, c3, c4, c5, c6) VALUES (954, 'sample_0kfPnPKeUjkycwqHxj5OtQ8', 'sample_sQ', 90, '2025-10-10', 'sample_C'); INSERT INTO t1 (c1, c2, c3, c4, c5, c6) VALUES (1986, 'sample_tJaorTY4zIEBbb8IM4auYieeVSd80foFpJh3MIv0Rz8fEgfYthMyHLdUMnlIN6NnP0VoAFBpSEUcHovYpCFbGrC98Y52vXpgXsmsEBDYj6ecgN2JkDh393f4hhIjFLpqffJrE5Z09WtyvK6PT0IcDNjF6S16yhunjPWFxaAbK4Z6', 'sample_bIa5736UIVD92z8u5rQ0PaRpPlOEwroZ4Xoqc4jDp4SiRROqU4u2EvOZZupAHmFPuFcWjOC6V16yTz58i3824l1EdwAgMPAPy9RM2sHerAB3L3eAWFGFd', 68, '2025-04-20', 'sample_KWK'); INSERT INTO t1 (c1, c2, c3, c4, c5, c6) VALUES (6432, 'sample_apUxsuYxJ7yJ6etwr3Sd7Cg7l1JHXVRSgSUF689u4w2greqF3zbivycDbv6qA4Wusopmd80qoDFLGg1ercUJBRyEl0j0aw1JXgj35Pg4JK2lNYBG2UPKBvS3jhsYpkYbhUNtq5zfTal0MXOuOk4CydQWObucwCG1tGcBtxVHzwZf18W2M7Fl2c8AzYVIIn', 'sample_xoju7XvqD9RJhJ2NAoI2KxR5QZdZKDIjrOdN8MahX1k3dJu9UxKIjMzLtBjiMnoN5JxDvSR7bHNsAECcv5onlaOPg4TBQg4yQw6ThZm8lvYZcq9lBqZap4SoxgEZLdQaIflJvl7EcmCUqqRgQEes', 48, '2025-04-28', 'sample_N'); INSERT INTO t1 (c1, c2, c3, c4, c5, c6) VALUES (8644, 'sample_IYRIsvectgAbcVIlfnvNd9kX3FNkOo4kAKKY1BDBLFQpH7ksvMtBllsZK16dP8T7twvH59dyaLTQuKIciaq8hnHB49XU7pS4kDw8Gt9mASD1ylSfvFBVtDNT', 'sample_7K12anyKLHlFNF5tfck9rj0CREpw10By71a2xm7uMRqIH61pLHnO3mXiWV0Ql013c5vK', 37, '2024-11-16', 'sample_8'); INSERT INTO t1 (c1, c2, c3, c4, c5, c6) VALUES (9828, 'sample_mbTbyuGtnVI5eoQZozrqRGnCbFGq5YiJjIAQ6m5pyEN0KYZtC8nHtcPrUXpN8Ox9SpGW03NGGx1rgIfMQzTzUQDSJyNReXc7SzF8lxZGBkTzeRFfSuV1', 'sample_w848NDNkIDLOlkpaaODWH9A0RwNZ3y4Up1tGogrgGXva1pea9fQC785AX81aC0JjCpCfVuAhR3sedKdIDkph0vCxL9t6vWoUxCcx1Z1PhcFsDkuO86Lm30jCHqsgwEkByFdBtl9Dapi0UdGLs4O7Nt1LJOAjxDeWrmGT52FyIlsB1dVyP84albq7J7x5ZLVhHk0pCHeRKK4Tq', 6, '2024-12-11', 'sample_Yb'); INSERT INTO t1 (c1, c2, c3, c4, c5, c6) VALUES (8711, 'sample_H0bxIKEbtSsSNoE4fQ02go0uWwMukodh2M0oTtTiefhg5SJf', 'sample_VCWYGsDcS9gn6qUA04DNxDeY2gvg41cRs30Zfb6T5o5USbnA4tNwyRbM5tWXmhTTzs9nfZGvC25Jwi8P8WKlHCFIxaU17VQP', 22, '2025-03-11', 'sample_ML'); INSERT INTO t1 (c1, c2, c3, c4, c5, c6) VALUES (4362, 'sample_dfW8hx4eHsITolDicvSc3HtZMQoP5soWVj5f5DSfOUrFOLuh0pGg4XrlvWqaeE8hfyNz2QHBvkIMJTykgQh79yqtceTNYfqVh7bKkLhlziwC5kLCFQLmbhrIoPhnyKS8NkUTh8EmqOqGq6gxIqnjGxIj0r7g8gJDQpMq2b6UNCCrTCE6GGo3EQ4pMKugOzDDz0Qq1jR4QpBozUDUR8CrOjPwRLWJGJ5BQBedmiz8h8NlwxwPgRsD', 'sample_y5ydeMxFnLnKxjREIetegsfUIESumZlnvBgvQ5Bt5UmgsKkuSPS4lQYYPowYSM8TMUO2jk6bzF3uknBtscdkMIvnZiY1NyEo385kHttCw1KIBlT6eNPqLVkQXrEhOpMOAPljJUuQoeXt0QcQF2Khgii5VYJiPfgMNQvKehkOwkGbHc9qqhZomCRqs', 48, '2025-08-29', 'sample_WM'); INSERT INTO t1 (c1, c2, c3, c4, c5, c6) VALUES (4556, 'sample_RhTbFvFWS1XPaycs42gVgoMSu4Z9YICN7KgWyZexa2ya1', 'sample_qmB7afvmuDgVbvaFhFpjOnqU64Zy0eujWu6xQXVhMAd1kdLe0HePMS5fmpz0oISOmKP7Th1WS3UteHN43F6mTdVZaXl2BDwRyCCVrPxthTfZ47UiVzjAT70U', 68, '2025-02-11', 'sample_w'); INSERT INTO t1 (c1, c2, c3, c4, c5, c6) VALUES (2932, 'sample_Hzj6qpaayHPRlIe9FppGcOnyHPTxVbgehj491ZooJze9rIy8RzOgRg97Igq', 'sample_4goBZVP5bQH16F3wBnbZ', 39, '2025-03-08', 'sample_C'); INSERT INTO t1 (c1, c2, c3, c4, c5, c6) VALUES (3575, 'sample_m5Bv1aDup5Nda', 'sample_4fkIoG9frxqxulw2LelBtTmYxXsSLVhmY5AZaDfRYlYeztqRhO', 71, '2024-12-20', 'sample_a'); INSERT INTO t1 (c1, c2, c3, c4, c5, c6) VALUES (2015, 'sample_uWIDMYCoSeu4lstIaExP4t4jdhYUai8NkLulSYodVu5beS0eVKkV0mWA4ZoRjR4Dumhs', 'sample_03UW3ZkzKuw3hixEOZdwogETBWxkMIxVZ8qtdf6ahBeEREuyxjhup7bSCDOtsKUuWDPthxH6HgmUQYfb2RJKmhoFbyQ4xsgBKqDxPttGDdSJg5aAVDOuVorOAKVkx507rRYCWuGHWvCyH3Llxv5lHmwUBZ4VsTN4bQMs', 47, '2025-02-25', 'sample_e'); INSERT INTO t1 (c1, c2, c3, c4, c5, c6) VALUES (9502, 'sample_gRZJisQ8CnqOPKTu10uL5iYyDxfjBuYlxeGEhdMpSqKcVtrL6sQhgrP2OkMaJlyFM7WyiE4ItMIPoNXiwYB0IqMn53P3BXgC05V8qF1488Pu5rL1KhNGHRWQbBOT69Bh6mG3l7XNZDxQWAr8BwihxWkhEtLuycJKTswb60G6lCYcPNQ1gLBax7s4MAW0IaVebCZWUK1OED8', 'sample_ZcnMv5EnefxK', 80, '2024-12-08', 'sample_O'); INSERT INTO t1 (c1, c2, c3, c4, c5, c6) VALUES (3423, 'sample_m8c5PmbiCPYM7BgM5Ef7HeBIHaTW0mU8xgjZXlvy0jcU9XnhTsfHajuW2cka6u99VmhUdzKxa2FhQDytaPeZpKtOQpFw4GlQn4fs0UfGH4aBzUNc0afdFjfcM1eZGOvWLQNuepm0nmBUfyOALf65lvE3XuSjgHNTk9mrKoa0jSXd6Ou6qt9u', 'sample_QpivJZaxO2b86puDimzQjRdO3qfJV0uDVoDgbRAQkHSvE1Smn2u6sGCwIjCSFl3nnLmjhI3B1evfQsUSsJcSz7RUjOFbMqDA8dyzwK9QS9kM0RTQ4ivh5A6AVtlntqI1uiqZrvbkjAejieEnZKsaL4mS7bvO1mQpMBiyyGfp6J8Lz0ibO3VpIop0Apg2', 41, '2025-04-01', 'sample_JY'); CREATE INDEX idx_t1_c5 ON t1 (c5); WITH cte_631 AS (SELECT SUM(DISTINCT ejc47.c1) AS col_1 FROM t1 AS ejc47 GROUP BY ejc47.c5, ejc47.c1) SELECT qpd67.col_1 AS col_3 FROM cte_631 AS qpd67; WITH cte_631 AS (SELECT SUM(DISTINCT ejc47.c1) AS col_1, DENSE_RANK() OVER (PARTITION BY ejc47.c5 ORDER BY ejc47.c1 DESC) AS col_2 FROM t1 AS ejc47 GROUP BY ejc47.c5, ejc47.c1) SELECT qpd67.col_1 AS col_3 FROM cte_631 AS qpd67;