Bug #119114 Column Value change caused by COUNT(column) --->COUNT(DISTINCT column)
Submitted: 8 Oct 7:54 Modified: 23 Oct 12:14
Reporter: cl hl Email Updates:
Status: Open Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:9.4.0 OS:Any
Assigned to: CPU Architecture:Any

[8 Oct 7:54] cl hl 
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);
[23 Oct 12:09] cl hl 
Sorry this isn't a bug
[23 Oct 12:14] cl hl 
the difference is caused by limit clause without order by clause.So this isn't a bug.But,in the second query's result ,it defualtly order by col_3,it may be a bug 

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 ;
+-------+-------+-------+-------------+
| col_1 | col_2 | col_3 | col_4       |
+-------+-------+-------+-------------+
|    76 |     1 |     6 | sample_meWG |
|    72 |     1 |    65 | sample_EGeV |
|    92 |     1 |    38 | sample_lc6I |
|    15 |     1 |     0 | sample_NRvx |
|    49 |     1 |    19 | sample_2kjk |
|     4 |     1 |    75 | sample_gPta |
|    55 |     1 |    19 | sample_NTrY |
|    40 |     1 |    77 | sample_CEI6 |
|    38 |     1 |     9 | sample_Igun |
|    82 |     1 |    12 | sample_b0H7 |
|     5 |     1 |    87 | sample_gDJ6 |
|    81 |     1 |    58 | sample_ZYXx |
|    41 |     1 |    14 | sample_etX2 |
+-------+-------+-------+-------------+
13 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(tfy
46.c10, 1, 11) AS col_4 FROM t3 AS tfy46 GROUP BY ROUND(tfy46.c6, 1), SUBSTRING(tfy46.c10, 1, 11), tfy46.c8 ;
+-------+-------+-------+-------------+
| col_1 | col_2 | col_3 | col_4       |
+-------+-------+-------+-------------+
|    15 |     1 |     0 | sample_NRvx |
|    76 |     1 |     6 | sample_meWG |
|    38 |     1 |     9 | sample_Igun |
|    82 |     1 |    12 | sample_b0H7 |
|    41 |     1 |    14 | sample_etX2 |
|    49 |     1 |    19 | sample_2kjk |
|    55 |     1 |    19 | sample_NTrY |
|    92 |     1 |    38 | sample_lc6I |
|    81 |     1 |    58 | sample_ZYXx |
|    72 |     1 |    65 | sample_EGeV |
|     4 |     1 |    75 | sample_gPta |
|    40 |     1 |    77 | sample_CEI6 |
|     5 |     1 |    87 | sample_gDJ6 |
+-------+-------+-------+-------------+
13 rows in set (0.00 sec)