Description:
after changing COUNT to COUNT(DISTINCT),the col_1 and col_3 shouldn`t change,but
it happens
mysql> (SELECT DISTINCT tfy46.c8 AS col_1, COUNT(tfy46.c14) AS col_2, ROUND(tfy46.c6, 1) AS col_3, SUBSTRING(tfy46.c10,
1, 11) AS col_4 FROM t3 AS tfy46 GROUP BY ROUND(tfy46.c6, 1), SUBSTRING(tfy46.c10, 1, 11), tfy46.c8 LIMIT 2);
+-------+-------+-------+-------+
| col_1 | col_2 | col_3 | col_4 |
+-------+-------+-------+-------+
| 93 | 1 | 98 | NULL |
| 23 | 1 | 45 | NULL |
+-------+-------+-------+-------+
2 rows in set (0.00 sec)
mysql> (SELECT DISTINCT tfy46.c8 AS col_1, COUNT(DISTINCT tfy46.c14) AS col_2, ROUND(tfy46.c6, 1) AS col_3, SUBSTRING(tfy46.c10, 1, 11) AS col_4 FROM t3 AS tfy46 GROUP BY ROUND(tfy46.c6, 1), SUBSTRING(tfy46.c10, 1, 11), tfy46.c8 LIMIT 2);
+-------+-------+-------+-------+
| col_1 | col_2 | col_3 | col_4 |
+-------+-------+-------+-------+
| 24 | 1 | 2 | NULL |
| 55 | 1 | 5 | NULL |
+-------+-------+-------+-------+
2 rows in set (0.00 sec)
How to repeat:
DROP DATABASE IF EXISTS test;
CREATE DATABASE IF NOT EXISTS test;
USE test;
CREATE TABLE t3 (
c1 INT NOT NULL AUTO_INCREMENT,
c2 INT NOT NULL,
c3 INT NOT NULL,
c4 YEAR NOT NULL,
c5 TIME NULL,
c6 TINYINT NULL,
c7 SMALLINT NULL,
c8 MEDIUMINT NULL,
c9 BIGINT NULL,
c10 JSON NULL,
c11 GEOMETRY NULL,
c12 TINYTEXT NULL,
c13 TINYBLOB NULL,
c14 SET('x','y','z') NULL,
c15 BOOLEAN NULL,
PRIMARY KEY (c1)
);
INSERT INTO t3 (c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15) VALUES (4262, 3270, 9756, 2023, '2023-01-01 15:57:20', 91, 31, 9, 87, NULL, ST_GeomFromText('POINT(57.166741 102.135334)'), 'sample_btFngF0vxQRYXgBYdTOAiZHmbAcwW12DT2sNk7CW4DVAarTa2Udc87M17LN2yIRPuq410v3zJkb3vcHFhhpHdTkJMoGOWlLL448jDs4LftxMhUF4sIPhL4iXHaXZ80k0wJJzHGRkV', X'DAA3E9A7A9C082', 'x,y', false);
INSERT INTO t3 (c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15) VALUES (2119, 2652, 8812, 2013, '2023-01-01 03:02:12', 65, 85, 99, 75, NULL, ST_GeomFromText('POINT(-39.972157 95.403051)'), 'sample_Xj0g', X'D6BD', 'x', false);
INSERT INTO t3 (c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15) VALUES (3208, 1578, 2695, 2015, '2023-01-01 00:27:22', 25, 85, 73, 45, NULL, ST_GeomFromText('POINT(41.649071 -31.484065)'), 'sample_8GGllnNwwkzglo26HxPRccXPGBu06JixEN0dQDdYBnQZhPZMNlaLq2770i3e2CveMBeNCC7FVcc1aG7FbZ02DhjJRt83c3CGNDwvhadnpP12v0uYn', X'0EC486E0A498EF8DB6', 'z', false);
INSERT INTO t3 (c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15) VALUES (3741, 7036, 6222, 2015, '2023-01-01 12:52:11', 72, 89, 94, 31, NULL, ST_GeomFromText('POINT(-4.666206 -70.424344)'), 'sample_0w', X'D3A6C4BAE190A2', 'y', true);
INSERT INTO t3 (c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15) VALUES (8240, 5173, 9756, 2012, '2023-01-01 22:23:19', 14, 46, 10, 74, NULL, ST_GeomFromText('POINT(75.916041 -152.370402)'), 'sample_aSS2ciU1K1LasSB7fdH9NSf3716VD5FkY1eeyUZhGy5bdlPdULGbSsh', X'7353', 'y,x,z', true);
INSERT INTO t3 (c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15) VALUES (9489, 1386, 2503, 2011, '2023-01-01 19:21:59', 57, 36, 26, 1, NULL, ST_GeomFromText('POINT(15.305966 -146.72827)'), 'sample_46us8bGP9CBUJ395jozpY2w', X'DFA2E1A8AD32C3BC1B', 'y', true);
INSERT INTO t3 (c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15) VALUES (498, 1386, 8709, 2007, '2023-01-01 14:46:55', 42, 85, 18, 77, NULL, ST_GeomFromText('POINT(14.460314 80.300226)'), 'sample_cpRcxn6Stync6RgDLQBzMEcgLxJMvwhJ9evirxBXRqCx7parTrl547m', X'01', 'z,y,x', true);
INSERT INTO t3 (c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15) VALUES (2771, 3063, 6847, 2022, '2023-01-01 02:22:34', 96, 14, 0, 82, NULL, ST_GeomFromText('POINT(38.58788 -61.149121)'), 'sample_zfRc9BEZgioAypyKbTh4njEcwcyxozdaHBDLJz9Wp2FzGeSgT6vkQUVtTvmAVVHFEpK2RdkuCYkuZaS5W9J0IJAOrPExidqH49WE4CINpotuos585JRb1t4FUC3zBv8caLt41HLMHQ6xjHgFhdmwhHWkCgVHaden4aYmJS10n97VOF', X'EAA692DF9D2B', 'y,x,z', true);
INSERT INTO t3 (c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15) VALUES (4884, 1386, 2503, 2007, '2023-01-01 11:04:41', 35, 90, 93, 4, NULL, ST_GeomFromText('POINT(32.293305 -38.540507)'), 'sample_N75zQBk65gTYLHw85UPkvB9QvHGglynr2PEhlT', X'E098BFC7B1', 'y,x', false);
INSERT INTO t3 (c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15) VALUES (1973, 1578, 6847, 2019, '2023-01-01 23:41:17', 43, 88, 61, 69, NULL, ST_GeomFromText('POINT(53.474653 -33.260698)'), 'sample_t', X'C7A2EA8CB1C58B6B', 'y', false);
INSERT INTO t3 (c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15) VALUES (9334, 2652, 8709, 2003, '2023-01-01 19:56:35', 78, 15, 10, 79, NULL, ST_GeomFromText('POINT(56.930625 43.549206)'), 'sample_ZbpOWJkQFZjkCRnbVwBuxDEsGfmf9dJCQOgUNuTgG2fzw6gsBL52GCHVHVVu', X'E5BC9E', 'z,x,y', true);
INSERT INTO t3 (c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15) VALUES (4892, 1063, 8709, 2012, '2023-01-01 18:05:50', 57, 41, 80, 9, NULL, ST_GeomFromText('POINT(30.448402 113.796755)'), 'sample_6hSfR6xHkA5zF10mYbzQqF5wjWPKkzkdP7DAXPNRLjpnh0WhFP9CUXudrOiyGvnzPcIL05GShzUf3zMCapLvoKELRq65snWQGFdrvBqMzE2AGhOmOqrfCpRrnEV3JSfg7t8SN85SBoBziijpfXU42Gkqs6nAdRYGyKqhl8J0vYSV0l5RZ75Y9rvbm', X'CCB6', 'x', true);
INSERT INTO t3 (c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15) VALUES (4701, 3270, 8723, 2013, '2023-01-01 05:17:16', 10, 62, 47, 96, NULL, ST_GeomFromText('POINT(-72.590604 27.198543)'), 'sample_36JkalORodX0VoF7dAV3E4lxxTUSEsnbxoR20Lx', X'4820ECB99524C1BE', 'x', true);
(SELECT DISTINCT tfy46.c8 AS col_1, COUNT(tfy46.c14) AS col_2, ROUND(tfy46.c6, 1) AS col_3, SUBSTRING(tfy46.c10,
1, 11) AS col_4 FROM t3 AS tfy46 GROUP BY ROUND(tfy46.c6, 1), SUBSTRING(tfy46.c10, 1, 11), tfy46.c8 LIMIT 2);
(SELECT DISTINCT tfy46.c8 AS col_1, COUNT(DISTINCT tfy46.c14) AS col_2, ROUND(tfy46.c6, 1) AS col_3, SUBSTRING(tfy46.c10, 1, 11) AS col_4 FROM t3 AS tfy46 GROUP BY ROUND(tfy46.c6, 1), SUBSTRING(tfy46.c10, 1, 11), tfy46.c8 LIMIT 2);
© 2025, Oracle Corporation and/or its affiliates
Description: after changing COUNT to COUNT(DISTINCT),the col_1 and col_3 shouldn`t change,but it happens mysql> (SELECT DISTINCT tfy46.c8 AS col_1, COUNT(tfy46.c14) AS col_2, ROUND(tfy46.c6, 1) AS col_3, SUBSTRING(tfy46.c10, 1, 11) AS col_4 FROM t3 AS tfy46 GROUP BY ROUND(tfy46.c6, 1), SUBSTRING(tfy46.c10, 1, 11), tfy46.c8 LIMIT 2); +-------+-------+-------+-------+ | col_1 | col_2 | col_3 | col_4 | +-------+-------+-------+-------+ | 93 | 1 | 98 | NULL | | 23 | 1 | 45 | NULL | +-------+-------+-------+-------+ 2 rows in set (0.00 sec) mysql> (SELECT DISTINCT tfy46.c8 AS col_1, COUNT(DISTINCT tfy46.c14) AS col_2, ROUND(tfy46.c6, 1) AS col_3, SUBSTRING(tfy46.c10, 1, 11) AS col_4 FROM t3 AS tfy46 GROUP BY ROUND(tfy46.c6, 1), SUBSTRING(tfy46.c10, 1, 11), tfy46.c8 LIMIT 2); +-------+-------+-------+-------+ | col_1 | col_2 | col_3 | col_4 | +-------+-------+-------+-------+ | 24 | 1 | 2 | NULL | | 55 | 1 | 5 | NULL | +-------+-------+-------+-------+ 2 rows in set (0.00 sec) How to repeat: DROP DATABASE IF EXISTS test; CREATE DATABASE IF NOT EXISTS test; USE test; CREATE TABLE t3 ( c1 INT NOT NULL AUTO_INCREMENT, c2 INT NOT NULL, c3 INT NOT NULL, c4 YEAR NOT NULL, c5 TIME NULL, c6 TINYINT NULL, c7 SMALLINT NULL, c8 MEDIUMINT NULL, c9 BIGINT NULL, c10 JSON NULL, c11 GEOMETRY NULL, c12 TINYTEXT NULL, c13 TINYBLOB NULL, c14 SET('x','y','z') NULL, c15 BOOLEAN NULL, PRIMARY KEY (c1) ); INSERT INTO t3 (c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15) VALUES (4262, 3270, 9756, 2023, '2023-01-01 15:57:20', 91, 31, 9, 87, NULL, ST_GeomFromText('POINT(57.166741 102.135334)'), 'sample_btFngF0vxQRYXgBYdTOAiZHmbAcwW12DT2sNk7CW4DVAarTa2Udc87M17LN2yIRPuq410v3zJkb3vcHFhhpHdTkJMoGOWlLL448jDs4LftxMhUF4sIPhL4iXHaXZ80k0wJJzHGRkV', X'DAA3E9A7A9C082', 'x,y', false); INSERT INTO t3 (c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15) VALUES (2119, 2652, 8812, 2013, '2023-01-01 03:02:12', 65, 85, 99, 75, NULL, ST_GeomFromText('POINT(-39.972157 95.403051)'), 'sample_Xj0g', X'D6BD', 'x', false); INSERT INTO t3 (c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15) VALUES (3208, 1578, 2695, 2015, '2023-01-01 00:27:22', 25, 85, 73, 45, NULL, ST_GeomFromText('POINT(41.649071 -31.484065)'), 'sample_8GGllnNwwkzglo26HxPRccXPGBu06JixEN0dQDdYBnQZhPZMNlaLq2770i3e2CveMBeNCC7FVcc1aG7FbZ02DhjJRt83c3CGNDwvhadnpP12v0uYn', X'0EC486E0A498EF8DB6', 'z', false); INSERT INTO t3 (c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15) VALUES (3741, 7036, 6222, 2015, '2023-01-01 12:52:11', 72, 89, 94, 31, NULL, ST_GeomFromText('POINT(-4.666206 -70.424344)'), 'sample_0w', X'D3A6C4BAE190A2', 'y', true); INSERT INTO t3 (c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15) VALUES (8240, 5173, 9756, 2012, '2023-01-01 22:23:19', 14, 46, 10, 74, NULL, ST_GeomFromText('POINT(75.916041 -152.370402)'), 'sample_aSS2ciU1K1LasSB7fdH9NSf3716VD5FkY1eeyUZhGy5bdlPdULGbSsh', X'7353', 'y,x,z', true); INSERT INTO t3 (c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15) VALUES (9489, 1386, 2503, 2011, '2023-01-01 19:21:59', 57, 36, 26, 1, NULL, ST_GeomFromText('POINT(15.305966 -146.72827)'), 'sample_46us8bGP9CBUJ395jozpY2w', X'DFA2E1A8AD32C3BC1B', 'y', true); INSERT INTO t3 (c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15) VALUES (498, 1386, 8709, 2007, '2023-01-01 14:46:55', 42, 85, 18, 77, NULL, ST_GeomFromText('POINT(14.460314 80.300226)'), 'sample_cpRcxn6Stync6RgDLQBzMEcgLxJMvwhJ9evirxBXRqCx7parTrl547m', X'01', 'z,y,x', true); INSERT INTO t3 (c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15) VALUES (2771, 3063, 6847, 2022, '2023-01-01 02:22:34', 96, 14, 0, 82, NULL, ST_GeomFromText('POINT(38.58788 -61.149121)'), 'sample_zfRc9BEZgioAypyKbTh4njEcwcyxozdaHBDLJz9Wp2FzGeSgT6vkQUVtTvmAVVHFEpK2RdkuCYkuZaS5W9J0IJAOrPExidqH49WE4CINpotuos585JRb1t4FUC3zBv8caLt41HLMHQ6xjHgFhdmwhHWkCgVHaden4aYmJS10n97VOF', X'EAA692DF9D2B', 'y,x,z', true); INSERT INTO t3 (c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15) VALUES (4884, 1386, 2503, 2007, '2023-01-01 11:04:41', 35, 90, 93, 4, NULL, ST_GeomFromText('POINT(32.293305 -38.540507)'), 'sample_N75zQBk65gTYLHw85UPkvB9QvHGglynr2PEhlT', X'E098BFC7B1', 'y,x', false); INSERT INTO t3 (c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15) VALUES (1973, 1578, 6847, 2019, '2023-01-01 23:41:17', 43, 88, 61, 69, NULL, ST_GeomFromText('POINT(53.474653 -33.260698)'), 'sample_t', X'C7A2EA8CB1C58B6B', 'y', false); INSERT INTO t3 (c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15) VALUES (9334, 2652, 8709, 2003, '2023-01-01 19:56:35', 78, 15, 10, 79, NULL, ST_GeomFromText('POINT(56.930625 43.549206)'), 'sample_ZbpOWJkQFZjkCRnbVwBuxDEsGfmf9dJCQOgUNuTgG2fzw6gsBL52GCHVHVVu', X'E5BC9E', 'z,x,y', true); INSERT INTO t3 (c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15) VALUES (4892, 1063, 8709, 2012, '2023-01-01 18:05:50', 57, 41, 80, 9, NULL, ST_GeomFromText('POINT(30.448402 113.796755)'), 'sample_6hSfR6xHkA5zF10mYbzQqF5wjWPKkzkdP7DAXPNRLjpnh0WhFP9CUXudrOiyGvnzPcIL05GShzUf3zMCapLvoKELRq65snWQGFdrvBqMzE2AGhOmOqrfCpRrnEV3JSfg7t8SN85SBoBziijpfXU42Gkqs6nAdRYGyKqhl8J0vYSV0l5RZ75Y9rvbm', X'CCB6', 'x', true); INSERT INTO t3 (c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15) VALUES (4701, 3270, 8723, 2013, '2023-01-01 05:17:16', 10, 62, 47, 96, NULL, ST_GeomFromText('POINT(-72.590604 27.198543)'), 'sample_36JkalORodX0VoF7dAV3E4lxxTUSEsnbxoR20Lx', X'4820ECB99524C1BE', 'x', true); (SELECT DISTINCT tfy46.c8 AS col_1, COUNT(tfy46.c14) AS col_2, ROUND(tfy46.c6, 1) AS col_3, SUBSTRING(tfy46.c10, 1, 11) AS col_4 FROM t3 AS tfy46 GROUP BY ROUND(tfy46.c6, 1), SUBSTRING(tfy46.c10, 1, 11), tfy46.c8 LIMIT 2); (SELECT DISTINCT tfy46.c8 AS col_1, COUNT(DISTINCT tfy46.c14) AS col_2, ROUND(tfy46.c6, 1) AS col_3, SUBSTRING(tfy46.c10, 1, 11) AS col_4 FROM t3 AS tfy46 GROUP BY ROUND(tfy46.c6, 1), SUBSTRING(tfy46.c10, 1, 11), tfy46.c8 LIMIT 2);