Bug #58427 group_concat_max_len incorrected hit with outer SELECT and create table
Submitted: 23 Nov 2010 19:39 Modified: 24 Dec 2012 9:07
Reporter: Leandro Morgado Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Options Severity:S2 (Serious)
Version:5.1.30,5.1.52, 5.5.7-rc OS:Any
Assigned to: CPU Architecture:Any
Tags: group_concat, group_concat_max_len

[23 Nov 2010 19:39] Leandro Morgado
Description:
The group_concat_max_len variable controls the maximum length of a resulting GROUP_CONCAT. In certain situations (described below), the group_concat_max_len is incorrectly hit and varies without apparent reason. 

It is also alarming that many of the truncated results do not return a warning.

The test below was run on the following versions which the same result:

5.1.30, 5.5.7-rc (64bit Linux)

5.1.52 (apple-darwin10.3.0 i386)

How to repeat:
--------
set sql_mode='';
set names utf8;
set session group_concat_max_len=11;
drop table if exists t,f,g;
create table t(a text)default charset=utf8 engine=myisam;
insert into t values ('aaaaa'),('bbbbb');
select group_concat(a) as r from t;
select * from (select group_concat(a) as r from t) d;

create table f select group_concat(a) as r from t;
select * from f;
create table g select * from (select group_concat(a) as r from t) d;
select * from g;
--------

mysql> set sql_mode='';
Query OK, 0 rows affected (0.00 sec)

mysql> set names utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> set session group_concat_max_len=11;
Query OK, 0 rows affected (0.00 sec)

mysql> drop table if exists t,f,g;
Query OK, 0 rows affected, 3 warnings (0.00 sec)

mysql> create table t(a text)default charset=utf8 engine=myisam;
Query OK, 0 rows affected (0.05 sec)

mysql> insert into t values ('aaaaa'),('bbbbb');
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select group_concat(a) as r from t;
+-------------+
| r           |
+-------------+
| aaaaa,bbbbb |
+-------------+
1 row in set (0.00 sec)

mysql> select * from (select group_concat(a) as r from t) d;
+------+
| r    |
+------+
| aaa  |
+------+
1 row in set (0.00 sec)

mysql> 
mysql> create table f select group_concat(a) as r from t;
Query OK, 1 row affected, 1 warning (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 1

mysql> select * from f;
+------+
| r    |
+------+
| aaaa |
+------+
1 row in set (0.00 sec)

mysql> create table g select * from (select group_concat(a) as r from t) d;
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from g;
+------+
| r    |
+------+
| aaa  |
+------+
1 row in set (0.00 sec)

Suggested fix:
group_concat_max_len should return consistent results.
[23 Nov 2010 19:42] Leandro Morgado
BTW, the workaround is to raise the group_concat_max_len.
[25 Nov 2011 17:22] Leandro Morgado
Duplicate of Bug 41090