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).

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.053 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.