Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.5.15, 10.6.7, 10.7.3, 10.8.2
-
None
Description
When setting group_concat_max_len to exactly one GB (1073741824) or more and using GROUP_CONCAT() in a subquery the result gets truncated as if the actual group concat limit had been set to the given value modulo 1073741824.
How to reproduce:
(this originally occurred in combination with JSON functions, but this is the most simple case I could strip it down to while still getting the same truncated results)
USE test; |
|
|
DROP TABLE IF EXISTS t1; |
CREATE TABLE t1(val VARCHAR(100) PRIMARY KEY); |
INSERT INTO t1 VALUES('bar'); |
INSERT INTO t1 VALUES('foo'); |
|
|
SET group_concat_max_len = 1073741823; -- 1GB minus one |
SHOW VARIABLES LIKE 'group_concat_max_len'; |
SELECT GROUP_CONCAT(val) AS simple FROM t1; |
SELECT * FROM ( SELECT GROUP_CONCAT(val) AS nested FROM t1) As tmp; |
|
|
SET group_concat_max_len = 1073741824; -- exactly 1GB |
SHOW VARIABLES LIKE 'group_concat_max_len'; |
SELECT GROUP_CONCAT(val) AS simple FROM t1; |
SELECT * FROM ( SELECT GROUP_CONCAT(val) AS nested FROM t1) As tmp; |
|
|
SET group_concat_max_len = 1073741825; -- 1GB plus one |
SHOW VARIABLES LIKE 'group_concat_max_len'; |
SELECT GROUP_CONCAT(val) AS simple FROM t1; |
SELECT * FROM ( SELECT GROUP_CONCAT(val) AS nested FROM t1) As tmp; |
|
|
SET group_concat_max_len = 1073741826; -- 1GB plus two |
SHOW VARIABLES LIKE 'group_concat_max_len'; |
SELECT GROUP_CONCAT(val) AS simple FROM t1; |
SELECT * FROM ( SELECT GROUP_CONCAT(val) AS nested FROM t1) As tmp; |
|
|
SET group_concat_max_len = 2147483649; -- 2GB plus one |
SHOW VARIABLES LIKE 'group_concat_max_len'; |
SELECT GROUP_CONCAT(val) AS simple FROM t1; |
SELECT * FROM ( SELECT GROUP_CONCAT(val) AS nested FROM t1) As tmp; |
Expected result:
Variable_name Value
|
group_concat_max_len 1073741823
|
simple
|
bar,foo
|
nested
|
bar,foo
|
Variable_name Value
|
group_concat_max_len 1073741824
|
simple
|
bar,foo
|
nested
|
bar,foo
|
Variable_name Value
|
group_concat_max_len 1073741825
|
simple
|
bar,foo
|
nested
|
bar,foo
|
Variable_name Value
|
group_concat_max_len 1073741826
|
simple
|
bar,foo
|
nested
|
bar,foo
|
group_concat_max_len 2147483649
|
simple
|
bar,foo
|
nested
|
bar,foo
|
Actual result:
Variable_name Value
|
group_concat_max_len 1073741823
|
simple
|
bar,foo
|
nested
|
bar,foo
|
Variable_name Value
|
group_concat_max_len 1073741824
|
simple
|
bar,foo
|
nested
|
|
|
Variable_name Value
|
group_concat_max_len 1073741825
|
simple
|
bar,foo
|
nested
|
b
|
Variable_name Value
|
group_concat_max_len 1073741826
|
simple
|
bar,foo
|
nested
|
ba
|
group_concat_max_len 2147483649
|
simple
|
bar,foo
|
nested
|
b
|
Attachments
Activity
| Field | Original Value | New Value |
|---|---|---|
| Priority | Major [ 3 ] | Critical [ 2 ] |
| Fix Version/s | 10.5 [ 23123 ] | |
| Fix Version/s | 10.6 [ 24028 ] | |
| Fix Version/s | 10.7 [ 24805 ] | |
| Fix Version/s | 10.8 [ 26121 ] |
| Assignee | Alexey Botchkov [ holyfoot ] |
from looking at 10.5.4 changelog the most likely candidates for having introduced this seem to be:
| Status | Open [ 1 ] | Confirmed [ 10101 ] |
| Fix Version/s | 10.5.16 [ 27508 ] | |
| Fix Version/s | 10.6.8 [ 27506 ] | |
| Fix Version/s | 10.7.4 [ 27504 ] | |
| Fix Version/s | 10.8.3 [ 27502 ] | |
| Fix Version/s | 10.5 [ 23123 ] | |
| Fix Version/s | 10.6 [ 24028 ] | |
| Fix Version/s | 10.7 [ 24805 ] | |
| Fix Version/s | 10.8 [ 26121 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | Confirmed [ 10101 ] | Closed [ 6 ] |
| Description |
When setting {{group_concat_max_len}} to exactly one GB (1073741824) or more and using {{GROUP_CONCAT()}} in a subquery the result gets truncated as if the actual group concat limit had been set to the given value modulo 1073741824.
How to reproduce: (this originally occurred in combination with JSON functions, but this is the most simple case I could strip it down to while still getting the same truncated results) {code:sql} USE test; DROP TABLE IF EXISTS t1; CREATE TABLE t1(val VARCHAR(100) PRIMARY KEY); INSERT INTO t1 VALUES('bar'); INSERT INTO t1 VALUES('foo'); SET group_concat_max_len = 1073741823; -- 1GB minus one SHOW VARIABLES LIKE 'group_concat_max_len'; SELECT GROUP_CONCAT(val) AS simple FROM t1; SELECT * FROM ( SELECT GROUP_CONCAT(val) AS nested FROM t1) As tmp; SET group_concat_max_len = 1073741824; -- exactly 1GB SHOW VARIABLES LIKE 'group_concat_max_len'; SELECT GROUP_CONCAT(val) AS simple FROM t1; SELECT * FROM ( SELECT GROUP_CONCAT(val) AS nested FROM t1) As tmp; SET group_concat_max_len = 1073741825; -- 1GB plus one SHOW VARIABLES LIKE 'group_concat_max_len'; SELECT GROUP_CONCAT(val) AS simple FROM t1; SELECT * FROM ( SELECT GROUP_CONCAT(val) AS nested FROM t1) As tmp; SET group_concat_max_len = 1073741826; -- 1GB plus two SHOW VARIABLES LIKE 'group_concat_max_len'; SELECT GROUP_CONCAT(val) AS simple FROM t1; SELECT * FROM ( SELECT GROUP_CONCAT(val) AS nested FROM t1) As tmp; SET group_concat_max_len = 2147483649; -- 2GB plus one SHOW VARIABLES LIKE 'group_concat_max_len'; SELECT GROUP_CONCAT(val) AS simple FROM t1; SELECT * FROM ( SELECT GROUP_CONCAT(val) AS nested FROM t1) As tmp; {code:sql} Expected result: {noformat} Variable_name Value group_concat_max_len 1073741823 simple bar,foo nested bar,foo Variable_name Value group_concat_max_len 1073741824 simple bar,foo nested bar,foo Variable_name Value group_concat_max_len 1073741825 simple bar,foo nested bar,foo Variable_name Value group_concat_max_len 1073741826 simple bar,foo nested bar,foo group_concat_max_len 2147483649 simple bar,foo nested bar,foo {noformat} Actual result: {noformat} Variable_name Value group_concat_max_len 1073741823 simple bar,foo nested bar,foo Variable_name Value group_concat_max_len 1073741824 simple bar,foo nested Variable_name Value group_concat_max_len 1073741825 simple bar,foo nested b Variable_name Value group_concat_max_len 1073741826 simple bar,foo nested ba group_concat_max_len 2147483649 simple bar,foo nested b {noformat} |
When setting {{group_concat_max_len}} to exactly one GB (1073741824) or more and using {{GROUP_CONCAT()}} in a subquery the result gets truncated as if the actual group concat limit had been set to the given value modulo 1073741824.
How to reproduce: (this originally occurred in combination with JSON functions, but this is the most simple case I could strip it down to while still getting the same truncated results) {code:sql} USE test; DROP TABLE IF EXISTS t1; CREATE TABLE t1(val VARCHAR(100) PRIMARY KEY); INSERT INTO t1 VALUES('bar'); INSERT INTO t1 VALUES('foo'); SET group_concat_max_len = 1073741823; -- 1GB minus one SHOW VARIABLES LIKE 'group_concat_max_len'; SELECT GROUP_CONCAT(val) AS simple FROM t1; SELECT * FROM ( SELECT GROUP_CONCAT(val) AS nested FROM t1) As tmp; SET group_concat_max_len = 1073741824; -- exactly 1GB SHOW VARIABLES LIKE 'group_concat_max_len'; SELECT GROUP_CONCAT(val) AS simple FROM t1; SELECT * FROM ( SELECT GROUP_CONCAT(val) AS nested FROM t1) As tmp; SET group_concat_max_len = 1073741825; -- 1GB plus one SHOW VARIABLES LIKE 'group_concat_max_len'; SELECT GROUP_CONCAT(val) AS simple FROM t1; SELECT * FROM ( SELECT GROUP_CONCAT(val) AS nested FROM t1) As tmp; SET group_concat_max_len = 1073741826; -- 1GB plus two SHOW VARIABLES LIKE 'group_concat_max_len'; SELECT GROUP_CONCAT(val) AS simple FROM t1; SELECT * FROM ( SELECT GROUP_CONCAT(val) AS nested FROM t1) As tmp; SET group_concat_max_len = 2147483649; -- 2GB plus one SHOW VARIABLES LIKE 'group_concat_max_len'; SELECT GROUP_CONCAT(val) AS simple FROM t1; SELECT * FROM ( SELECT GROUP_CONCAT(val) AS nested FROM t1) As tmp; {code} Expected result: {noformat} Variable_name Value group_concat_max_len 1073741823 simple bar,foo nested bar,foo Variable_name Value group_concat_max_len 1073741824 simple bar,foo nested bar,foo Variable_name Value group_concat_max_len 1073741825 simple bar,foo nested bar,foo Variable_name Value group_concat_max_len 1073741826 simple bar,foo nested bar,foo group_concat_max_len 2147483649 simple bar,foo nested bar,foo {noformat} Actual result: {noformat} Variable_name Value group_concat_max_len 1073741823 simple bar,foo nested bar,foo Variable_name Value group_concat_max_len 1073741824 simple bar,foo nested Variable_name Value group_concat_max_len 1073741825 simple bar,foo nested b Variable_name Value group_concat_max_len 1073741826 simple bar,foo nested ba group_concat_max_len 2147483649 simple bar,foo nested b {noformat} |
| Zendesk Related Tickets | 181471 |
10.4 and earlier are not affected, this only started with 10.5