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