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.