[MDEV-28490] Strange result truncation with group_concat_max_len=1GB - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
Affects Version/s: 10.5.15, 10.6.7, 10.7.3, 10.8.2
Fix Version/s: 10.5.16, 10.6.8, 10.7.4, 10.8.3
Component/s: Configuration
Labels:
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

Variable_name	Value

group_concat_max_len	1073741826

simple

bar,foo

nested

ba

group_concat_max_len	2147483649

simple

bar,foo

nested

Attachments

Activity

Ascending order - Click to sort in descending order

Hartmut Holzgraefe created issue - 2022-05-06 13:44

Hartmut Holzgraefe added a comment - 2022-05-06 13:45

10.4 and earlier are not affected, this only started with 10.5

Hartmut Holzgraefe added a comment - 2022-05-06 13:45 10.4 and earlier are not affected, this only started with 10.5

Max Mether made changes - 2022-05-06 14:36

Field	Original Value	New Value
Priority	Major [ 3 ]	Critical [ 2 ]

Julien Fritsch made changes - 2022-05-06 15:16

Fix Version/s		10.5 [ 23123 ]
Fix Version/s		10.6 [ 24028 ]
Fix Version/s		10.7 [ 24805 ]
Fix Version/s		10.8 [ 26121 ]

Hartmut Holzgraefe added a comment - 2022-05-06 18:25

Actually started between 10.5.3 and 10.5.4

Hartmut Holzgraefe added a comment - 2022-05-06 18:25 Actually started between 10.5.3 and 10.5.4

Max Mether made changes - 2022-05-06 20:05

Assignee

Alexey Botchkov [ holyfoot ]

Hartmut Holzgraefe added a comment - 2022-05-06 20:33

from looking at 10.5.4 changelog the most likely candidates for having introduced this seem to be:

Hartmut Holzgraefe added a comment - 2022-05-06 20:33 from looking at 10.5.4 changelog the most likely candidates for having introduced this seem to be: https://github.com/MariaDB/server/commit/6c573a9146 https://github.com/MariaDB/server/commit/81a08c5462 https://github.com/MariaDB/server/commit/a006e88cac

Julien Fritsch made changes - 2022-05-09 07:57

Status

Open [ 1 ]

Confirmed [ 10101 ]

Alexey Botchkov added a comment - 2022-05-16 05:07

fixing patch
https://github.com/MariaDB/server/commit/b03ab1270d24c1fe011aa50f9e6b495c6d508706

Alexey Botchkov added a comment - 2022-05-16 05:07 fixing patch https://github.com/MariaDB/server/commit/b03ab1270d24c1fe011aa50f9e6b495c6d508706

Alexey Botchkov made changes - 2022-05-16 05:07

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 ]

Nayuta Yanagisawa (Inactive) made changes - 2022-06-09 10:51

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}

Jira Automation (IT) made changes - 2024-07-04 01:40

Zendesk Related Tickets

181471

People

Assignee:: Alexey Botchkov

Reporter:: Hartmut Holzgraefe

Votes:: 1 Vote for this issue

Watchers:: 6 Start watching this issue

Dates

Created:: 2022-05-06 13:44

Updated:: 2024-07-07 20:18

Resolved:: 2022-05-16 05:07

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Activity

People

Dates

Git Integration