Bug #84320 DISTINCT clause does not work in GROUP_CONCAT
Submitted: 22 Dec 2016 19:35 Modified: 3 Aug 2017 5:27
Reporter: varun gupta Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.7 OS:Any
Assigned to: MySQL Verification Team CPU Architecture:Any
Tags: group_concat

[22 Dec 2016 19:35] varun gupta 
Description:
Distinct clause in GROUP_CONCAT fails to work when multiple unique trees are required. I  see that multiple values are repeated for an attribute that is marked as distinct. 

How to repeat:
create table t1 (
a int,
b int
);
insert into t1 values
( 0 , 1 ),
( 1 , 2 ),
( 2 , 3 ),
( 3 , 4 ),
( 4 , 5 ),
( 5 , 6 ),
( 6 , 7 ),
( 7 , 8 ),
( 0 , 11 ),
( 1 , 12 ),
( 2 , 13 ),
( 3 , 14 ),
( 4 , 15 ),
( 5 , 16 ),
( 6 , 17 ),
( 7 , 18 ),
( 0 , 31 ),
( 1 , 32 ),
( 2 , 33 ),
( 3 , 34 ),
( 4 , 35 ),
( 5 , 36 ),
( 6 , 37 ),
( 7 , 38 );
gdb) b Unique::Unique
  Breakpoint 3 at 0x170180e: ~/mysql-5.7/sql/uniques.cc, line 67.

  Breakpoint 3, Unique::Unique (this=0x7fff4c01f318, comp_func=0x149fa07 <group_concat_key_cmp_with_distinct(void const*, void const*, void const*)>, comp_func_fixed_arg=0x7fff4c006300, size_arg=8, max_in_memory_size_arg=16777216) at ~/mysql-5.7/sql/uniques.cc:67

(gdb) p size_arg + sizeof(TREE_ELEMENT)
  $14 = 32

(gdb) set max_in_memory_size=6*32
(gdb) set max_in_memory_size_arg=6*32
(gdb) c

MySQL [j7]> select group_concat(distinct a order by b) from t1;
+-------------------------------------------------+
| group_concat(distinct a order by b)             |
+-------------------------------------------------+
| 0,1,2,3,4,5,6,7,0,1,2,3,4,5,6,7,0,1,2,3,4,5,6,7 |
+-------------------------------------------------+
[22 Dec 2016 20:06] MySQL Verification Team 
Thank you for the bug report. Below is the result you expect?. Thanks.

Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.18-log Source distribution PULL: 2016-DEC-18

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.7 > use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql 5.7 > create table t1 (
    -> a int,
    -> b int
    -> );
Query OK, 0 rows affected (0,91 sec)

mysql 5.7 > insert into t1 values
    -> ( 0 , 1 ),
    -> ( 1 , 2 ),
    -> ( 2 , 3 ),
    -> ( 3 , 4 ),
    -> ( 4 , 5 ),
    -> ( 5 , 6 ),
    -> ( 6 , 7 ),
    -> ( 7 , 8 ),
    -> ( 0 , 11 ),
    -> ( 1 , 12 ),
    -> ( 2 , 13 ),
    -> ( 3 , 14 ),
    -> ( 4 , 15 ),
    -> ( 5 , 16 ),
    -> ( 6 , 17 ),
    -> ( 7 , 18 ),
    -> ( 0 , 31 ),
    -> ( 1 , 32 ),
    -> ( 2 , 33 ),
    -> ( 3 , 34 ),
    -> ( 4 , 35 ),
    -> ( 5 , 36 ),
    -> ( 6 , 37 ),
    -> ( 7 , 38 );
Query OK, 24 rows affected (0,02 sec)
Records: 24  Duplicates: 0  Warnings: 0

mysql 5.7 > select group_concat(distinct a order by b) from t1;
+-------------------------------------+
| group_concat(distinct a order by b) |
+-------------------------------------+
| 0,1,2,3,4,5,6,7                     |
+-------------------------------------+
1 row in set (0,02 sec)

mysql 5.7 >
[22 Dec 2016 20:11] varun gupta 
Yes. Thanks
[22 Dec 2016 21:17] MySQL Verification Team 
Thank you for the feedback.
[23 Dec 2016 6:41] varun gupta 
Yes the result posted by you is what is expect, but when i change the elements in the unique tree, then I end up getting the result that I posted.
[27 Dec 2016 12:57] MySQL Verification Team 
Hi,

> but when i change the elements in the unique tree

You do it trough debugger or trough sql? 'cause as Miguel shown if you insert these values and execute a select the results is what's expected. Can you reproduce the problem without invoking a debugger?

kind regards
Bogdan
[29 Dec 2016 11:59] varun gupta 
The test which gives the wrong output

set @tmp_table_size=1024;
create table t1 (
		a VARCHAR(10000),
		b int
	    );

insert into t1 values
( 0 , 1 ),
( 1 , 2 ),
( 2 , 3 ),
( 3 , 4 ),
( 4 , 5 ),
( 5 , 6 ),
( 6 , 7 ),
( 7 , 8 ),
( 8 , 9 ),
( 9 , 10 ),
( 10 , 11 ),
( 11 , 12 ),
( 12 , 13 ),
( 13 , 14 ),
( 14 , 15 ),
( 15 , 16 ),
( 16 , 17 ),
( 17 , 18 ),
( 18 , 19 ),
( 19 , 20 ),
( 20 , 21 ),
( 21 , 22 ),
( 22 , 23 ),
( 23 , 24 ),
( 24 , 25 ),
( 25 , 26 ),
( 26 , 27 ),
( 27 , 28 ),
( 28 , 29 ),
( 29 , 30 ),
( 30 , 31 ),
( 31 , 32 ),
( 32 , 33 ),
( 33 , 34 ),
( 34 , 35 ),
( 35 , 36 ),
( 36 , 37 ),
( 37 , 38 ),
( 38 , 39 ),
( 39 , 40 ),
( 40 , 41 ),
( 41 , 42 ),
( 42 , 43 ),
( 43 , 44 ),
( 44 , 45 ),
( 45 , 46 ),
( 46 , 47 ),
( 47 , 48 ),
( 48 , 49 ),
( 49 , 50 ),
( 50 , 51 ),
( 51 , 52 ),
( 52 , 53 ),
( 53 , 54 ),
( 54 , 55 ),
( 55 , 56 ),
( 56 , 57 ),
( 57 , 58 ),
( 58 , 59 ),
( 59 , 60 ),
( 60 , 61 ),
( 61 , 62 ),
( 62 , 63 ),
( 63 , 64 ),
( 64 , 65 ),
( 65 , 66 ),
( 66 , 67 ),
( 67 , 68 ),
( 68 , 69 ),
( 69 , 70 ),
( 70 , 71 ),
( 71 , 72 ),
( 72 , 73 ),
( 73 , 74 ),
( 74 , 75 ),
( 75 , 76 ),
( 76 , 77 ),
( 77 , 78 ),
( 78 , 79 ),
( 79 , 80 ),
( 80 , 81 ),
( 81 , 82 ),
( 82 , 83 ),
( 83 , 84 ),
( 84 , 85 ),
( 85 , 86 ),
( 86 , 87 ),
( 87 , 88 ),
( 88 , 89 ),
( 89 , 90 ),
( 90 , 91 ),
( 91 , 92 ),
( 92 , 93 ),
( 93 , 94 ),
( 94 , 95 ),
( 95 , 96 ),
( 96 , 97 ),
( 97 , 98 ),
( 98 , 99 ),
( 99 , 100 ),
( 100 , 101 ),
( 101 , 102 ),
( 102 , 103 ),
( 103 , 104 ),
( 104 , 105 ),
( 105 , 106 ),
( 106 , 107 ),
( 107 , 108 ),
( 108 , 109 ),
( 109 , 110 ),
( 0 , 111 ),
( 1 , 112 ),
( 2 , 113 ),
( 3 , 114 ),
( 4 , 115 ),
( 5 , 116 ),
( 6 , 117 ),
( 7 , 118 ),
( 8 , 119 ),
( 9 , 120 ),
( 10 , 121 ),
( 11 , 122 ),
( 12 , 123 ),
( 13 , 124 ),
( 14 , 125 ),
( 15 , 126 ),
( 16 , 127 ),
( 17 , 128 ),
( 18 , 129 ),
( 19 , 130 ),
( 20 , 131 ),
( 21 , 132 ),
( 22 , 133 ),
( 23 , 134 ),
( 24 , 135 ),
( 25 , 136 ),
( 26 , 137 ),
( 27 , 138 ),
( 28 , 139 ),
( 29 , 140 ),
( 30 , 141 ),
( 31 , 142 ),
( 32 , 143 ),
( 33 , 144 ),
( 34 , 145 ),
( 35 , 146 ),
( 36 , 147 ),
( 37 , 148 ),
( 38 , 149 ),
( 39 , 150 ),
( 40 , 151 ),
( 41 , 152 ),
( 42 , 153 ),
( 43 , 154 ),
( 44 , 155 ),
( 45 , 156 ),
( 46 , 157 ),
( 47 , 158 ),
( 48 , 159 ),
( 49 , 160 ),
( 50 , 161 ),
( 51 , 162 ),
( 52 , 163 ),
( 53 , 164 ),
( 54 , 165 ),
( 55 , 166 ),
( 56 , 167 ),
( 57 , 168 ),
( 58 , 169 ),
( 59 , 170 ),
( 60 , 171 ),
( 61 , 172 ),
( 62 , 173 ),
( 63 , 174 ),
( 64 , 175 ),
( 65 , 176 ),
( 66 , 177 ),
( 67 , 178 ),
( 68 , 179 ),
( 69 , 180 ),
( 70 , 181 ),
( 71 , 182 ),
( 72 , 183 ),
( 73 , 184 ),
( 74 , 185 ),
( 75 , 186 ),
( 76 , 187 ),
( 77 , 188 ),
( 78 , 189 ),
( 79 , 190 ),
( 80 , 191 ),
( 81 , 192 ),
( 82 , 193 ),
( 83 , 194 ),
( 84 , 195 ),
( 85 , 196 ),
( 86 , 197 ),
( 87 , 198 ),
( 88 , 199 ),
( 89 , 200 ),
( 90 , 201 ),
( 91 , 202 ),
( 92 , 203 ),
( 93 , 204 ),
( 94 , 205 ),
( 95 , 206 ),
( 96 , 207 ),
( 97 , 208 ),
( 98 , 209 ),
( 99 , 210 ),
( 100 , 211 ),
( 101 , 212 ),
( 102 , 213 ),
( 103 , 214 ),
( 104 , 215 ),
( 105 , 216 ),
( 106 , 217 ),
( 107 , 218 ),
( 108 , 219 ),
( 109 , 220 );
select group_concat(distinct a) from t1;
drop table t1;

The result I am getting is
0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109
[29 Dec 2016 16:17] MySQL Verification Team 
Hi, I'm not reproducing this with your script:

mysql> set @tmp_table_size=1024;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t1 (
    ->
    -> a VARCHAR(10000),
    ->
    -> b int
    ->     );
Query OK, 0 rows affected (0.40 sec)

mysql> show tables;
+--------------------+
| Tables_in_bogitest |
+--------------------+
| t1                 |
+--------------------+
1 row in set (0.00 sec)

mysql> insert into t1 values
    -> ( 0 , 1 ),
    -> ( 1 , 2 ),
    -> ( 2 , 3 ),
    -> ( 3 , 4 ),
    -> ( 4 , 5 ),
    -> ( 5 , 6 ),
    -> ( 6 , 7 ),
    -> ( 7 , 8 ),
    -> ( 8 , 9 ),
    -> ( 9 , 10 ),
    -> ( 10 , 11 ),
    -> ( 11 , 12 ),
    -> ( 12 , 13 ),
    -> ( 13 , 14 ),
    -> ( 14 , 15 ),
    -> ( 15 , 16 ),
    -> ( 16 , 17 ),
    -> ( 17 , 18 ),
    -> ( 18 , 19 ),
    -> ( 19 , 20 ),
    -> ( 20 , 21 ),
    -> ( 21 , 22 ),
    -> ( 22 , 23 ),
    -> ( 23 , 24 ),
    -> ( 24 , 25 ),
    -> ( 25 , 26 ),
    -> ( 26 , 27 ),
    -> ( 27 , 28 ),
    -> ( 28 , 29 ),
    -> ( 29 , 30 ),
    -> ( 30 , 31 ),
    -> ( 31 , 32 ),
    -> ( 32 , 33 ),
    -> ( 33 , 34 ),
    -> ( 34 , 35 ),
    -> ( 35 , 36 ),
    -> ( 36 , 37 ),
    -> ( 37 , 38 ),
    -> ( 38 , 39 ),
    -> ( 39 , 40 ),
    -> ( 40 , 41 ),
    -> ( 41 , 42 ),
    -> ( 42 , 43 ),
    -> ( 43 , 44 ),
    -> ( 44 , 45 ),
    -> ( 45 , 46 ),
    -> ( 46 , 47 ),
    -> ( 47 , 48 ),
    -> ( 48 , 49 ),
    -> ( 49 , 50 ),
    -> ( 50 , 51 ),
    -> ( 51 , 52 ),
    -> ( 52 , 53 ),
    -> ( 53 , 54 ),
    -> ( 54 , 55 ),
    -> ( 55 , 56 ),
    -> ( 56 , 57 ),
    -> ( 57 , 58 ),
    -> ( 58 , 59 ),
    -> ( 59 , 60 ),
    -> ( 60 , 61 ),
    -> ( 61 , 62 ),
    -> ( 62 , 63 ),
    -> ( 63 , 64 ),
    -> ( 64 , 65 ),
    -> ( 65 , 66 ),
    -> ( 66 , 67 ),
    -> ( 67 , 68 ),
    -> ( 68 , 69 ),
    -> ( 69 , 70 ),
    -> ( 70 , 71 ),
    -> ( 71 , 72 ),
    -> ( 72 , 73 ),
    -> ( 73 , 74 ),
    -> ( 74 , 75 ),
    -> ( 75 , 76 ),
    -> ( 76 , 77 ),
    -> ( 77 , 78 ),
    -> ( 78 , 79 ),
    -> ( 79 , 80 ),
    -> ( 80 , 81 ),
    -> ( 81 , 82 ),
    -> ( 82 , 83 ),
    -> ( 83 , 84 ),
    -> ( 84 , 85 ),
    -> ( 85 , 86 ),
    -> ( 86 , 87 ),
    -> ( 87 , 88 ),
    -> ( 88 , 89 ),
    -> ( 89 , 90 ),
    -> ( 90 , 91 ),
    -> ( 91 , 92 ),
    -> ( 92 , 93 ),
    -> ( 93 , 94 ),
    -> ( 94 , 95 ),
    -> ( 95 , 96 ),
    -> ( 96 , 97 ),
    -> ( 97 , 98 ),
    -> ( 98 , 99 ),
    -> ( 99 , 100 ),
    -> ( 100 , 101 ),
    -> ( 101 , 102 ),
    -> ( 102 , 103 ),
    -> ( 103 , 104 ),
    -> ( 104 , 105 ),
    -> ( 105 , 106 ),
    -> ( 106 , 107 ),
    -> ( 107 , 108 ),
    -> ( 108 , 109 ),
    -> ( 109 , 110 ),
    -> ( 0 , 111 ),
    -> ( 1 , 112 ),
    -> ( 2 , 113 ),
    -> ( 3 , 114 ),
    -> ( 4 , 115 ),
    -> ( 5 , 116 ),
    -> ( 6 , 117 ),
    -> ( 7 , 118 ),
    -> ( 8 , 119 ),
    -> ( 9 , 120 ),
    -> ( 10 , 121 ),
    -> ( 11 , 122 ),
    -> ( 12 , 123 ),
    -> ( 13 , 124 ),
    -> ( 14 , 125 ),
    -> ( 15 , 126 ),
    -> ( 16 , 127 ),
    -> ( 17 , 128 ),
    -> ( 18 , 129 ),
    -> ( 19 , 130 ),
    -> ( 20 , 131 ),
    -> ( 21 , 132 ),
    -> ( 22 , 133 ),
    -> ( 23 , 134 ),
    -> ( 24 , 135 ),
    -> ( 25 , 136 ),
    -> ( 26 , 137 ),
    -> ( 27 , 138 ),
    -> ( 28 , 139 ),
    -> ( 29 , 140 ),
    -> ( 30 , 141 ),
    -> ( 31 , 142 ),
    -> ( 32 , 143 ),
    -> ( 33 , 144 ),
    -> ( 34 , 145 ),
    -> ( 35 , 146 ),
    -> ( 36 , 147 ),
    -> ( 37 , 148 ),
    -> ( 38 , 149 ),
    -> ( 39 , 150 ),
    -> ( 40 , 151 ),
    -> ( 41 , 152 ),
    -> ( 42 , 153 ),
    -> ( 43 , 154 ),
    -> ( 44 , 155 ),
    -> ( 45 , 156 ),
    -> ( 46 , 157 ),
    -> ( 47 , 158 ),
    -> ( 48 , 159 ),
    -> ( 49 , 160 ),
    -> ( 50 , 161 ),
    -> ( 51 , 162 ),
    -> ( 52 , 163 ),
    -> ( 53 , 164 ),
    -> ( 54 , 165 ),
    -> ( 55 , 166 ),
    -> ( 56 , 167 ),
    -> ( 57 , 168 ),
    -> ( 58 , 169 ),
    -> ( 59 , 170 ),
    -> ( 60 , 171 ),
    -> ( 61 , 172 ),
    -> ( 62 , 173 ),
    -> ( 63 , 174 ),
    -> ( 64 , 175 ),
    -> ( 65 , 176 ),
    -> ( 66 , 177 ),
    -> ( 67 , 178 ),
    -> ( 68 , 179 ),
    -> ( 69 , 180 ),
    -> ( 70 , 181 ),
    -> ( 71 , 182 ),
    -> ( 72 , 183 ),
    -> ( 73 , 184 ),
    -> ( 74 , 185 ),
    -> ( 75 , 186 ),
    -> ( 76 , 187 ),
    -> ( 77 , 188 ),
    -> ( 78 , 189 ),
    -> ( 79 , 190 ),
    -> ( 80 , 191 ),
    -> ( 81 , 192 ),
    -> ( 82 , 193 ),
    -> ( 83 , 194 ),
    -> ( 84 , 195 ),
    -> ( 85 , 196 ),
    -> ( 86 , 197 ),
    -> ( 87 , 198 ),
    -> ( 88 , 199 ),
    -> ( 89 , 200 ),
    -> ( 90 , 201 ),
    -> ( 91 , 202 ),
    -> ( 92 , 203 ),
    -> ( 93 , 204 ),
    -> ( 94 , 205 ),
    -> ( 95 , 206 ),
    -> ( 96 , 207 ),
    -> ( 97 , 208 ),
    -> ( 98 , 209 ),
    -> ( 99 , 210 ),
    -> ( 100 , 211 ),
    -> ( 101 , 212 ),
    -> ( 102 , 213 ),
    -> ( 103 , 214 ),
    -> ( 104 , 215 ),
    -> ( 105 , 216 ),
    -> ( 106 , 217 ),
    -> ( 107 , 218 ),
    -> ( 108 , 219 ),
    -> ( 109 , 220 );
Query OK, 220 rows affected (0.01 sec)
Records: 220  Duplicates: 0  Warnings: 0

mysql> select group_concat(distinct a) from t1;
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| group_concat(distinct a)                                                                                                                                                                                                                                                                                                                  |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109 |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select @@version
    -> ;
+-----------+
| @@version |
+-----------+
| 5.7.17    |
+-----------+
1 row in set (0.00 sec)

no duplicates as I see them ?!

what binaries you get this with?
[29 Dec 2016 17:52] varun gupta 
select @@version;
@@version
5.7.17-log
[29 Dec 2016 18:04] MySQL Verification Team 
Hi,

and this binary is built by ?

as you can see from my sample, the 7.7.17 built by oracle does not reproduce your test case

kind regards
Bogdan
[29 Dec 2016 18:22] varun gupta 
With the current binary I dont see the error, but when I run the same test on the code i cloned from https://github.com/mysql/mysql-server, the 5.7 branch gives me the wrong answer
[29 Dec 2016 18:44] MySQL Verification Team 
Hi,
I can't reproduce it with the latest sources neither, I assume something about, either sources you got, or the build process is wrong.

With regards to Sergei's comment in the other place this bug is mentioned, I have Sinisa here that was around when that feature was designed and decision on how it should work is made and while this is a very "non standard" syntax the decision was to "Hidden columns should be prohibited in group_concat(). It should be documented in our manual and the error should be returned. DISTINCT is supposed to be fully supported in this 100 % non-standard function. Also, ORDER BY should be fully supported, but, only by a column that is in the list within the function GROUP_CONCAT() itself. That is how it was designed back then.". 

I was not around back then so I can only look at the code and I don't see the bug + I can't reproduce your test case with the code I see and the binaries I get from Oracle so.. maybe we can reopen if for some reason 5.7.18 comes out with the problem but I don't see it in 5.7.17!

All best
Bogdan
[29 Dec 2016 20:45] varun gupta 
Here the updated test,

mysql> create table t1(a varchar(100), b int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values
    -> ( 0 , 1 ),
    -> ( 1 , 2 ),
    -> ( 2 , 3 ),
    -> ( 3 , 4 ),
    -> ( 4 , 5 ),
    -> ( 5 , 6 ),
    -> ( 6 , 7 ),
    -> ( 7 , 8 ),
    -> ( 8 , 9 ),
    -> ( 9 , 10 ),
    -> ( 10 , 11 ),
    -> ( 11 , 12 ),
    -> ( 12 , 13 ),
    -> ( 13 , 14 ),
    -> ( 14 , 15 ),
    -> ( 15 , 16 ),
    -> ( 16 , 17 ),
    -> ( 17 , 18 ),
    -> ( 18 , 19 ),
    -> ( 19 , 20 ),
    -> ( 20 , 21 ),
    -> ( 21 , 22 ),
    -> ( 22 , 23 ),
    -> ( 23 , 24 ),
    -> ( 24 , 25 ),
    -> ( 25 , 26 ),
    -> ( 26 , 27 ),
    -> ( 27 , 28 ),
    -> ( 28 , 29 ),
    -> ( 29 , 30 ),
    -> ( 30 , 31 ),
    -> ( 31 , 32 ),
    -> ( 32 , 33 ),
    -> ( 33 , 34 ),
    -> ( 34 , 35 ),
    -> ( 35 , 36 ),
    -> ( 36 , 37 ),
    -> ( 37 , 38 ),
    -> ( 38 , 39 ),
    -> ( 39 , 40 ),
    -> ( 40 , 41 ),
    -> ( 41 , 42 ),
    -> ( 42 , 43 ),
    -> ( 43 , 44 ),
    -> ( 44 , 45 ),
    -> ( 45 , 46 ),
    -> ( 46 , 47 ),
    -> ( 47 , 48 ),
    -> ( 48 , 49 ),
    -> ( 49 , 50 ),
    -> ( 0 , 51 ),
    -> ( 1 , 52 ),
    -> ( 2 , 53 ),
    -> ( 3 , 54 ),
    -> ( 4 , 55 ),
    -> ( 5 , 56 ),
    -> ( 6 , 57 ),
    -> ( 7 , 58 ),
    -> ( 8 , 59 ),
    -> ( 9 , 60 ),
    -> ( 10 , 61 ),
    -> ( 11 , 62 ),
    -> ( 12 , 63 ),
    -> ( 13 , 64 ),
    -> ( 14 , 65 ),
    -> ( 15 , 66 ),
    -> ( 16 , 67 ),
    -> ( 17 , 68 ),
    -> ( 18 , 69 ),
    -> ( 19 , 70 ),
    -> ( 20 , 71 ),
    -> ( 21 , 72 ),
    -> ( 22 , 73 ),
    -> ( 23 , 74 ),
    -> ( 24 , 75 ),
    -> ( 25 , 76 ),
    -> ( 26 , 77 ),
    -> ( 27 , 78 ),
    -> ( 28 , 79 ),
    -> ( 29 , 80 ),
    -> ( 30 , 81 ),
    -> ( 31 , 82 ),
    -> ( 32 , 83 ),
    -> ( 33 , 84 ),
    -> ( 34 , 85 ),
    -> ( 35 , 86 ),
    -> ( 36 , 87 ),
    -> ( 37 , 88 ),
    -> ( 38 , 89 ),
    -> ( 39 , 90 ),
    -> ( 40 , 91 ),
    -> ( 41 , 92 ),
    -> ( 42 , 93 ),
    -> ( 43 , 94 ),
    -> ( 44 , 95 ),
    -> ( 45 , 96 ),
    -> ( 46 , 97 ),
    -> ( 47 , 98 ),
    -> ( 48 , 99 ),
    -> ( 49 , 100 ),
    -> ( 0 , 101 ),
    -> ( 1 , 102 ),
    -> ( 2 , 103 ),
    -> ( 3 , 104 ),
    -> ( 4 , 105 ),
    -> ( 5 , 106 ),
    -> ( 6 , 107 ),
    -> ( 7 , 108 ),
    -> ( 8 , 109 ),
    -> ( 9 , 110 ),
    -> ( 10 , 111 ),
    -> ( 11 , 112 ),
    -> ( 12 , 113 ),
    -> ( 13 , 114 ),
    -> ( 14 , 115 ),
    -> ( 15 , 116 ),
    -> ( 16 , 117 ),
    -> ( 17 , 118 ),
    -> ( 18 , 119 ),
    -> ( 19 , 120 ),
    -> ( 20 , 121 ),
    -> ( 21 , 122 ),
    -> ( 22 , 123 ),
    -> ( 23 , 124 ),
    -> ( 24 , 125 ),
    -> ( 25 , 126 ),
    -> ( 26 , 127 ),
    -> ( 27 , 128 ),
    -> ( 28 , 129 ),
    -> ( 29 , 130 ),
    -> ( 30 , 131 ),
    -> ( 31 , 132 ),
    -> ( 32 , 133 ),
    -> ( 33 , 134 ),
    -> ( 34 , 135 ),
    -> ( 35 , 136 ),
    -> ( 36 , 137 ),
    -> ( 37 , 138 ),
    -> ( 38 , 139 ),
    -> ( 39 , 140 ),
    -> ( 40 , 141 ),
    -> ( 41 , 142 ),
    -> ( 42 , 143 ),
    -> ( 43 , 144 ),
    -> ( 44 , 145 ),
    -> ( 45 , 146 ),
    -> ( 46 , 147 ),
    -> ( 47 , 148 ),
    -> ( 48 , 149 ),
    -> ( 49 , 150 ),
    -> ( 0 , 151 ),
    -> ( 1 , 152 ),
    -> ( 2 , 153 ),
    -> ( 3 , 154 ),
    -> ( 4 , 155 ),
    -> ( 5 , 156 ),
    -> ( 6 , 157 ),
    -> ( 7 , 158 ),
    -> ( 8 , 159 ),
    -> ( 9 , 160 ),
    -> ( 10 , 161 ),
    -> ( 11 , 162 ),
    -> ( 12 , 163 ),
    -> ( 13 , 164 ),
    -> ( 14 , 165 ),
    -> ( 15 , 166 ),
    -> ( 16 , 167 ),
    -> ( 17 , 168 ),
    -> ( 18 , 169 ),
    -> ( 19 , 170 ),
    -> ( 20 , 171 ),
    -> ( 21 , 172 ),
    -> ( 22 , 173 ),
    -> ( 23 , 174 ),
    -> ( 24 , 175 ),
    -> ( 25 , 176 ),
    -> ( 26 , 177 ),
    -> ( 27 , 178 ),
    -> ( 28 , 179 ),
    -> ( 29 , 180 ),
    -> ( 30 , 181 ),
    -> ( 31 , 182 ),
    -> ( 32 , 183 ),
    -> ( 33 , 184 ),
    -> ( 34 , 185 ),
    -> ( 35 , 186 ),
    -> ( 36 , 187 ),
    -> ( 37 , 188 ),
    -> ( 38 , 189 ),
    -> ( 39 , 190 ),
    -> ( 40 , 191 ),
    -> ( 41 , 192 ),
    -> ( 42 , 193 ),
    -> ( 43 , 194 ),
    -> ( 44 , 195 ),
    -> ( 45 , 196 ),
    -> ( 46 , 197 ),
    -> ( 47 , 198 ),
    -> ( 48 , 199 ),
    -> ( 49 , 200 );
Query OK, 200 rows affected (0.00 sec)
Records: 200  Duplicates: 0  Warnings: 0

mysql> set local tmp_table_size=1024;
Query OK, 0 rows affected (0.00 sec)

mysql> select group_concat(distinct a) from t1;
| group_concat(distinct a)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
| 0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49 |

1 row in set (0.00 sec)

The first result is without setting the tmp_table_size variable and the other one is with setting tmp_table_size = 1024
[29 Dec 2016 20:54] MySQL Verification Team 
Hi Varun,

there's definitively bug in our latest source code, thanks for the report.

Our devs are on it :)

all best
Bogdan
[3 Jan 2017 15:07] Sergey Petrunya 
See also: BUG#68145. Looks like the same issue as this one.
[24 Apr 2017 17:49] Paul DuBois 
Posted by developer:
 
Noted in 5.7.19, 8.0.2 changelogs.

GROUP_CONCAT(DISTINCT) returned nonunique values if the data size was
greater than the value of the tmp_table_size system variable.
[2 Aug 2017 16:31] varun gupta 
The same test with the temp_table_size set to
with order by in group_concat we get non unique results

set local tmp_table_size=1024;

select group_concat(distinct a order by b) from t1;

group_concat(distinct a order by b)
2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49
[3 Aug 2017 2:34] Sreeharsha Ramanavarapu 
Posted by developer:
 
Hi Varun,

This bug only deals with issues related DISTINCT clause (with no ORDER BY).

The ORDER BY related issues in GROUP_CONCAT can be dealt with in Bug#25858782 (Bug#85851).