MySQL Bugs: #69515: group_concat_max_len >= 4Gb does not work

Bug #69515	group_concat_max_len >= 4Gb does not work
Submitted:	19 Jun 2013 14:55	Modified:	19 Jun 2013 15:09
Reporter:	Elena Stepanova	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Options	Severity:	S3 (Non-critical)
Version:	5.1, 5.5, 5.6, 5.7	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Initially reported at https://mariadb.atlassian.net/browse/MDEV-4677

Please note that however strange, it's a real-life story.

With group_concat_max_len = 4Gb or more, even tiny GROUP_CONCAT() values get truncated. 

set group_concat_max_len=1024*1024*1024*4;
create table t1 (i int, j int) engine=MyISAM;
insert into t1 values (1,1),(1,2);
select i, group_concat(j) from t1 group by i;
i	group_concat(j)
1	
Warnings:
Warning	1260	Row 1 was cut by GROUP_CONCAT()
drop table t1;

I suppose if the value has a limit, it shouldn't be allowed to be set above it (should be truncated to the closest possible value, as it usually happens). 

How to repeat:
set group_concat_max_len=1024*1024*1024*4;

create table t1 (i int, j int) engine=MyISAM;
insert into t1 values (1,1),(1,2);
select i, group_concat(j) from t1 group by i;

drop table t1;

I do not see this problem with 5.6.11 on 64-bit Windows 7 for some reason - I get proper warning and value truncated:

mysql> set group_concat_max_len=1024*1024*1024*4;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1292
Message: Truncated incorrect group_concat_max_len value: '4294967296'
1 row in set (0.00 sec)

mysql> show variables like 'group%';
+----------------------+------------+
| Variable_name        | Value      |
+----------------------+------------+
| group_concat_max_len | 4294967295 |
+----------------------+------------+
1 row in set (0.02 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.6.11    |
+-----------+
1 row in set (0.01 sec)

mysql> show variables like 'ver%';
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| version                 | 5.6.11                       |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | Win64                        |
+-------------------------+------------------------------+
4 rows in set (0.00 sec)

Probably on Windows the limit is the same as for 32-bit OSes, and it works as expected.

Verified as described!