Bug #83667 Limitation @@group_concat_max_len is not respected
Submitted: 3 Nov 2016 10:34 Modified: 5 Oct 2017 15:36
Reporter: Roberto Caiola Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.6.32-78.0 OS:Any
Assigned to: CPU Architecture:Any
Tags: group_concat_max_len, limitation

[3 Nov 2016 10:34] Roberto Caiola 
Description:
Limitation @@group_concat_max_len of the SESSION and GLOBAL is 1024 chars.

When I ran GROUP_CONCAT with UNION the limitation is shorter than the value defined in @@group_concat_max_len

How to repeat:
Example query that I am running with shorter limitation (340 chars):

SELECT
(
	SELECT
	   GROUP_CONCAT(DISTINCT ' ', sub_ec.numerocartao) as card_ids
	FROM
	   equipamento sub_ec
	   INNER JOIN contaservico sub_cs ON sub_cs.idcontaservico = sub_ec.idcontaservico
	   INNER JOIN conta sub_c ON sub_c.idconta = sub_cs.idconta
	   INNER JOIN cliente sub_cli ON sub_cli.idcliente = sub_c.idcliente
	WHERE
	   sub_cli.idcliente = 43531               
) UNION ( 
  SELECT 
  	'' as cards
);

The following query, runs properly and trims the string at 1024 chars:

        SELECT
	   GROUP_CONCAT(DISTINCT ' ', sub_ec.numerocartao) as card_ids
	FROM
	   equipamento sub_ec
	   INNER JOIN contaservico sub_cs ON sub_cs.idcontaservico = sub_ec.idcontaservico
	   INNER JOIN conta sub_c ON sub_c.idconta = sub_cs.idconta
	   INNER JOIN cliente sub_cli ON sub_cli.idcliente = sub_c.idcliente
	WHERE
	   sub_cli.idcliente = 43531     

Suggested fix:
Limitation of @@group_concat_max_len should run properly on a query based on UNION
[3 Nov 2016 10:58] Roberto Caiola 
Example to reproduce:

CREATE TABLE `equipment` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`card` VARCHAR(40) NULL DEFAULT NULL,
	PRIMARY KEY (`id`),
	INDEX `card` (`card`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=1
;

-- Repeat the following query a few dozen times
INSERT INTO equipment (
	card
)
SELECT ( SUBSTRING(MD5(RAND()) FROM 1 FOR 12)) as card
;

-- Limited SELECT @@group_concat_max_len;
SELECT
	   GROUP_CONCAT(DISTINCT ' ', e.card) as cards
	FROM
	   equipment e     
;

-- BUG: Limited at 340 chars
SELECT
(
	SELECT
	   GROUP_CONCAT(DISTINCT ' ', e.card) as cards
	FROM
	   equipment e     
) UNION ( 
  SELECT 
  	'' as cards
);
[4 Nov 2016 17:11] MySQL Verification Team 
Indeed I managed to repeat the exact behavior:

mysql> CREATE TABLE `equipment` (`id` INT(11) NOT NULL AUTO_INCREMENT, `card` VARCHAR(40) NULL DEFAULT NULL, PRIMARY KEY (`id`),  INDEX `card` (`card`) ) COLLATE='utf8_general_ci' ENGINE=InnoDB;
Query OK, 0 rows affected (0.23 sec)

[sinisa@local mysql-5.7.3-m13]$ for ((i=0;i<36;i++)) ; do ./client/Debug/mysql test -e "INSERT INTO equipment (card) SELECT ( SUBSTRING(MD5(RAND()) FROM 1 FOR 12)) as card" ; done
[sinisa@local mysql-5.7.3-m13]$ ./client/Debug/mysql test -e "SELECT GROUP_CONCAT(DISTINCT ' ', e.card) as cards FROM equipment e"
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| cards                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|  06e56d704b72, 1182e0d4163f, 1391deadd038, 18903fcdddb7, 38c484610647, 3de68142ca08, 40b7afac4e8e, 460c10acccae, 46ddb703d786, 4a1969e44340, 4a3d8db7e8eb, 4b24d7aa6c6a, 55b4043cbbae, 5c7531e503c8, 6398c594bec6, 6666fd2842db, 75ac74b0982a, 785061ec41c0, 7c2d49c3f815, 9b6e5f9f7e3e, a06c2c66629b, a427ee35d75f, a4872df4f0e3, a53e15b1eaee, abf56d6c4969, b570ac7ac720, b9b6647e05a0, b9d37db475b3, befb4ea27c74, c1faed885e0c, d111003aae0f, e3ef78508912, e603de7acac7, f99f6934a1e7, fa94ebb9dd88, fd4d8be6d10c |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
[sinisa@local mysql-5.7.3-m13]$ ./client/Debug/mysql test -e "(SELECT GROUP_CONCAT(DISTINCT ' ', e.card) as cards FROM equipment e) UNION ALL (SELECT '' as cards)"
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| cards                                                                                                                                                                                                                                                                                                                                                 |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|  06e56d704b72, 1182e0d4163f, 1391deadd038, 18903fcdddb7, 38c484610647, 3de68142ca08, 40b7afac4e8e, 460c10acccae, 46ddb703d786, 4a1969e44340, 4a3d8db7e8eb, 4b24d7aa6c6a, 55b4043cbbae, 5c7531e503c8, 6398c594bec6, 6666fd2842db, 75ac74b0982a, 785061ec41c0, 7c2d49c3f815, 9b6e5f9f7e3e, a06c2c66629b, a427ee35d75f, a4872df4f0e3, a53e15b1eaee, abf5 |
|                                                                                                                                                                                                                                                                                                                                                       |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Fully verified.
[5 Oct 2017 15:36] Paul DuBois 
Posted by developer:
 
Fixed in 8.0.4.

For some queries, such as those involving UNION, column width for
GROUP_CONCAT() could be calculated incorrectly, leading to incorrect
application of group_concat_max_len.