MySQL Bugs: #113588: When group_concat_max_len is multiple of 1GB, the query result is wrong

Bug #113588	When group_concat_max_len is multiple of 1GB, the query result is wrong
Submitted:	9 Jan 2024 8:18	Modified:	9 Jan 2024 9:41
Reporter:	Ke Yu (OCA)	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.7.44	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When group_concat_max_len is multiple of 1GB, the query result is wrong. The result seems to be truncated to 255 and there was no error/warning messages.

When group_concat_max_len is set to other value, the result is correct.

5.7.44 has this problem, 8.0.34 does not have this problem.

How to repeat:
-- prepare a table
CREATE TABLE `t` (
  `id` int,
  `k_id` int,
  `name` varchar(255)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ;

-- insert some data
insert into t values(1, 2,repeat('a',100));
insert into t values(1, 2,repeat('a',100));
insert into t values(1, 3,repeat('a',100));

mysql> -- set group_concat_max_len = 1073741824 (1GB) to do a select
mysql> set group_concat_max_len = 1073741824;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT LENGTH(GROUP_CONCAT( `name` ORDER BY `k_id` )) AS names FROM t GROUP BY id ORDER BY LENGTH(GROUP_CONCAT( `name` ORDER BY `k_id`));
+-------+
| names |
+-------+
|   255 |
+-------+
1 row in set (0.01 sec)

mysql> -- set group_concat_max_len = 1073741824 * 2 (2GB) to do the same select
mysql> set group_concat_max_len = 1073741824 * 2;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT LENGTH(GROUP_CONCAT( `name` ORDER BY `k_id` )) AS names FROM t GROUP BY id ORDER BY LENGTH(GROUP_CONCAT( `name` ORDER BY `k_id`));
+-------+
| names |
+-------+
|   255 |
+-------+
1 row in set (0.01 sec)

mysql> -- set group_concat_max_len = 1610612832 (1.5GB) to do the same select
mysql> set group_concat_max_len = 1610612832 ;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT LENGTH(GROUP_CONCAT( `name` ORDER BY `k_id` )) AS names FROM t GROUP BY id ORDER BY LENGTH(GROUP_CONCAT( `name` ORDER BY `k_id`));
+-------+
| names |
+-------+
|   302 |
+-------+
1 row in set (0.00 sec)

Hello Ke Yu,

Thank you for the report and feedback.
IMHO - Per Oracle's Lifetime Support policy, as of October 25, 2023, MySQL 5.7 is covered under Oracle Sustaining Support. Thus, Users are encouraged to upgrade to MySQL 8.0. - More details at https://www.mysql.com/support/eol-notice.html

As you rightly pointed, this issue not seen with 8.0.35.

--
bin/mysql -uroot -S /tmp/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.35 Source distribution

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database test;
Query OK, 1 row affected (0.01 sec)

mysql> use test
Database changed
mysql> CREATE TABLE `t` (
    ->   `id` int,
    ->   `k_id` int,
    ->   `name` varchar(255)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ;
Query OK, 0 rows affected (0.02 sec)

mysql> -- insert some data
mysql> insert into t values(1, 2,repeat('a',100));
Query OK, 1 row affected (0.01 sec)

mysql> insert into t values(1, 2,repeat('a',100));
Query OK, 1 row affected (0.00 sec)

mysql> insert into t values(1, 3,repeat('a',100));
Query OK, 1 row affected (0.00 sec)

mysql> -- set group_concat_max_len = 1073741824 (1GB) to do a select
mysql> set group_concat_max_len = 1073741824;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT LENGTH(GROUP_CONCAT( `name` ORDER BY `k_id` )) AS names FROM t GROUP BY id ORDER BY LENGTH(GROUP_CONCAT( `name` ORDER BY `k_id`));
+-------+
| names |
+-------+
|   302 |
+-------+
1 row in set (0.00 sec)

mysql> -- set group_concat_max_len = 1073741824 * 2 (2GB) to do the same select
mysql> set group_concat_max_len = 1073741824 * 2;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT LENGTH(GROUP_CONCAT( `name` ORDER BY `k_id` )) AS names FROM t GROUP BY id ORDER BY LENGTH(GROUP_CONCAT( `name` ORDER BY `k_id`));
+-------+
| names |
+-------+
|   302 |
+-------+
1 row in set (0.00 sec)

mysql> -- set group_concat_max_len = 1610612832 (1.5GB) to do the same select
mysql> set group_concat_max_len = 1610612832 ;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT LENGTH(GROUP_CONCAT( `name` ORDER BY `k_id` )) AS names FROM t GROUP BY id ORDER BY LENGTH(GROUP_CONCAT( `name` ORDER BY `k_id`));
+-------+
| names |
+-------+
|   302 |
+-------+
1 row in set (0.00 sec)

mysql> set group_concat_max_len = 1610612832*2 ;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT LENGTH(GROUP_CONCAT( `name` ORDER BY `k_id` )) AS names FROM t GROUP BY id ORDER BY LENGTH(GROUP_CONCAT( `name` ORDER BY `k_id`));
+-------+
| names |
+-------+
|   302 |
+-------+
1 row in set (0.00 sec)

regards,
Umesh