[MDEV-5542] GROUP_CONCAT truncate output to 65.536 chars when using DISTINCT or ORDER BY - JIRA

Details

Type: Bug
Status: Confirmed
Priority: Major
Resolution: Unresolved
Affects Version/s: 10.1, 10.0, 5.5
Fix Version/s: 10.1, 10.0
Component/s: OTHER
Labels:
- upstream-fixed

Description

As in the summary, I try to GROUP_CONCAT a LONGTEXT field; result is fine if I don'use DISTINCT or ORDER BY as options for the GROUP BY function, otherwise the result is truncated to 65.536 chars (maybe the result is VARCHAR?).
I've already set the variable group_concat_max_len to 100.000.000.
However the query return a warning of the result will be truncated.

Gliffy Diagrams

Attachments

Activity

Ascending order - Click to sort in descending order

Hide

Permalink

Elena Stepanova added a comment - 2015-06-02 15:17

I don't see it happen, if it's still reproducible for you, can you paste or attach the output that demonstrates it?
Mine is below.

MariaDB [test]> select @@version;
+----------------+
| @@version      |
+----------------+
| 5.5.34-MariaDB |
+----------------+
1 row in set (0.00 sec)

MariaDB [test]> set group_concat_max_len = 100000000;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> create table t1 (f longtext) engine=MyISAM;Query OK, 0 rows affected (0.04 sec)

insert into t1 values (repeat('1abcdefgh',8192)), (repeat('2abcdefgh',8192)), (repeat('3abcdefgh',8192)), (repeat('4abcdefgh',8192));
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

MariaDB [test]> select group_concat(distinct f order by f) from t1;
....
1 row in set (0.00 sec)

MariaDB [test]> select length(group_concat(distinct f order by f)) from t1;
+---------------------------------------------+
| length(group_concat(distinct f order by f)) |
+---------------------------------------------+
|                                      262143 |
+---------------------------------------------+
1 row in set (0.00 sec)

Show

Elena Stepanova added a comment - 2015-06-02 15:17 I don't see it happen, if it's still reproducible for you, can you paste or attach the output that demonstrates it? Mine is below. MariaDB [test]> select @@version; +----------------+ | @@version | +----------------+ | 5.5.34-MariaDB | +----------------+ 1 row in set (0.00 sec) MariaDB [test]> set group_concat_max_len = 100000000; Query OK, 0 rows affected (0.00 sec) MariaDB [test]> create table t1 (f longtext) engine=MyISAM;Query OK, 0 rows affected (0.04 sec) insert into t1 values (repeat('1abcdefgh',8192)), (repeat('2abcdefgh',8192)), (repeat('3abcdefgh',8192)), (repeat('4abcdefgh',8192)); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 MariaDB [test]> select group_concat(distinct f order by f) from t1; .... 1 row in set (0.00 sec) MariaDB [test]> select length(group_concat(distinct f order by f)) from t1; +---------------------------------------------+ | length(group_concat(distinct f order by f)) | +---------------------------------------------+ | 262143 | +---------------------------------------------+ 1 row in set (0.00 sec)

Hide

Permalink

Risato Stefano added a comment - 2015-06-08 15:28

The problem is still present. Here my steps, with MariaDB 10.0.19:

Only 1 record:

drop table if exists t1;create table t1 (f longtext) engine=Aria;
insert into t1 values (repeat('abcdefgh',9000));
select length(group_concat(distinct f order by f)) from t1;
result: 72000 -OK

With 2 records:

drop table if exists t1;create table t1 (f longtext) engine=Aria;
insert into t1 values (repeat('abcdefgh',9000)),(repeat('abcdefgh',9000));
select length(group_concat(distinct f order by f)) from t1;
result: 65535 -KO

drop table if exists t1;create table t1 (f longtext) engine=Aria;
insert into t1 values (repeat('abcdefgh',9000)),(repeat('abcdefgh',9000));
select length(group_concat(f)) from t1;
result: 144001 -OK

drop table if exists t1;create table t1 (f longtext) engine=Aria;
insert into t1 values (repeat('abcdefgh',9000)),(repeat('abcdefgh',9000));
select length(group_concat(f order by f)) from t1;
result: 131071 -KO

The problem occur when using DISTINCT and/or ORDER BY.

Thank you.

----~~Messaggio originale~~----
Da: Elena Stepanova (JIRA) jira@mariadb.atlassian.net
Inviato: martedì 2 giugno 2015 14:18
A: Risato Stefano (IR SCR)
Oggetto: [JIRA] (MDEV-5542) GROUP_CONCAT truncate output to 65.536 chars when using DISTINCT or ORDER BY

[ https://mariadb.atlassian.net/browse/MDEV-5542?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Elena Stepanova updated MDEV-5542:
----------------------------------
Labels: need_feedback (was: )
Fix Version/s: (was: 5.5)

I don't see it happen, if it's still reproducible for you, can you paste or attach the output that demonstrates it?
Mine is below.

MariaDB [test]> select @@version;
+----------------+
+----------------+
+----------------+
1 row in set (0.00 sec)

MariaDB [test]> set group_concat_max_len = 100000000; Query OK, 0 rows affected (0.00 sec)

 MariaDB [test]> create table t1 (f longtext) engine=MyISAM;Query OK, 0 rows affected (0.04 sec)

insert into t1 values (repeat('1abcdefgh',8192)), (repeat('2abcdefgh',8192)), (repeat('3abcdefgh',8192)), (repeat('4abcdefgh',8192)); Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

MariaDB [test]> select group_concat(distinct f order by f) from t1; ....
1 row in set (0.00 sec)

MariaDB [test]> select length(group_concat(distinct f order by f)) from t1;
+---------------------------------------------+
+---------------------------------------------+
+---------------------------------------------+
1 row in set (0.00 sec)

–
This message was sent by Atlassian JIRA
(v6.5-OD-05-041#65001)

Show

Risato Stefano added a comment - 2015-06-08 15:28 The problem is still present. Here my steps, with MariaDB 10.0.19: Only 1 record: drop table if exists t1;create table t1 (f longtext) engine=Aria; insert into t1 values (repeat('abcdefgh',9000)); select length(group_concat(distinct f order by f)) from t1; result: 72000 -OK With 2 records: drop table if exists t1;create table t1 (f longtext) engine=Aria; insert into t1 values (repeat('abcdefgh',9000)),(repeat('abcdefgh',9000)); select length(group_concat(distinct f order by f)) from t1; result: 65535 -KO drop table if exists t1;create table t1 (f longtext) engine=Aria; insert into t1 values (repeat('abcdefgh',9000)),(repeat('abcdefgh',9000)); select length(group_concat(f)) from t1; result: 144001 -OK drop table if exists t1;create table t1 (f longtext) engine=Aria; insert into t1 values (repeat('abcdefgh',9000)),(repeat('abcdefgh',9000)); select length(group_concat(f order by f)) from t1; result: 131071 -KO The problem occur when using DISTINCT and/or ORDER BY. Thank you. ---- Messaggio originale ---- Da: Elena Stepanova (JIRA) jira@mariadb.atlassian.net Inviato: martedì 2 giugno 2015 14:18 A: Risato Stefano (IR SCR) Oggetto: [JIRA] ( MDEV-5542 ) GROUP_CONCAT truncate output to 65.536 chars when using DISTINCT or ORDER BY [ https://mariadb.atlassian.net/browse/MDEV-5542?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Elena Stepanova updated MDEV-5542 : ---------------------------------- Labels: need_feedback (was: ) Fix Version/s: (was: 5.5) I don't see it happen, if it's still reproducible for you, can you paste or attach the output that demonstrates it? Mine is below. MariaDB [test]> select @@version; +----------------+ +----------------+ +----------------+ 1 row in set (0.00 sec) MariaDB [test]> set group_concat_max_len = 100000000; Query OK, 0 rows affected (0.00 sec) MariaDB [test]> create table t1 (f longtext) engine=MyISAM;Query OK, 0 rows affected (0.04 sec) insert into t1 values (repeat('1abcdefgh',8192)), (repeat('2abcdefgh',8192)), (repeat('3abcdefgh',8192)), (repeat('4abcdefgh',8192)); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 MariaDB [test]> select group_concat(distinct f order by f) from t1; .... 1 row in set (0.00 sec) MariaDB [test]> select length(group_concat(distinct f order by f)) from t1; +---------------------------------------------+ +---------------------------------------------+ +---------------------------------------------+ 1 row in set (0.00 sec) – This message was sent by Atlassian JIRA (v6.5-OD-05-041#65001)

Hide

Permalink

Elena Stepanova added a comment - 2015-06-08 16:46

Thanks for clarification. Sorry I misunderstood your description before.
So, it's not the resulting string that is truncated to 65535, it's each concatenated value.
That's why with ORDER BY without DISTINCT it becomes 131071 (65535*2 + 1), and with DISTINCT it's 65535.

Test case

set group_concat_max_len = 100000000;

drop table if exists t1;
create table t1 (f longtext) engine=MyISAM;
insert into t1 values (repeat('abcdefgh',9000)),(repeat('abcdefgh',9000)); 
select length(group_concat(f)) from t1;
select length(group_concat(f order by f)) from t1;
select length(group_concat(distinct f order by f)) from t1;
drop table t1;

create table t1 (f longtext) engine=MyISAM;
insert into t1 values (repeat('1abcdefg',9000)),(repeat('2abcdefg',9000)); 
select length(group_concat(f)) from t1;
select length(group_concat(f order by f)) from t1;
select length(group_concat(distinct f order by f)) from t1;
drop table t1;

Expected result

create table t1 (f longtext) engine=MyISAM;
insert into t1 values (repeat('abcdefgh',9000)),(repeat('abcdefgh',9000));
select length(group_concat(f)) from t1;
length(group_concat(f))
144001
select length(group_concat(f order by f)) from t1;
length(group_concat(f order by f))
144001
select length(group_concat(distinct f order by f)) from t1;
length(group_concat(distinct f order by f))
72000
drop table t1;
create table t1 (f longtext) engine=MyISAM;
insert into t1 values (repeat('1abcdefg',9000)),(repeat('2abcdefg',9000));
select length(group_concat(f)) from t1;
length(group_concat(f))
144001
select length(group_concat(f order by f)) from t1;
length(group_concat(f order by f))
144001
select length(group_concat(distinct f order by f)) from t1;
length(group_concat(distinct f order by f))
144001
drop table t1;

Actual result

create table t1 (f longtext) engine=MyISAM;
insert into t1 values (repeat('abcdefgh',9000)),(repeat('abcdefgh',9000));
select length(group_concat(f)) from t1;
length(group_concat(f))
144001
select length(group_concat(f order by f)) from t1;
length(group_concat(f order by f))
131071
select length(group_concat(distinct f order by f)) from t1;
length(group_concat(distinct f order by f))
65535
drop table t1;
create table t1 (f longtext) engine=MyISAM;
insert into t1 values (repeat('1abcdefg',9000)),(repeat('2abcdefg',9000));
select length(group_concat(f)) from t1;
length(group_concat(f))
144001
select length(group_concat(f order by f)) from t1;
length(group_concat(f order by f))
131071
select length(group_concat(distinct f order by f)) from t1;
length(group_concat(distinct f order by f))
131071
drop table t1;

The problem exists in MySQL 5.5, but not in 5.6/5.7.

Show

Elena Stepanova added a comment - 2015-06-08 16:46 Thanks for clarification. Sorry I misunderstood your description before. So, it's not the resulting string that is truncated to 65535, it's each concatenated value. That's why with ORDER BY without DISTINCT it becomes 131071 (65535*2 + 1), and with DISTINCT it's 65535. Test case set group_concat_max_len = 100000000; drop table if exists t1; create table t1 (f longtext) engine=MyISAM; insert into t1 values (repeat('abcdefgh',9000)),(repeat('abcdefgh',9000)); select length(group_concat(f)) from t1; select length(group_concat(f order by f)) from t1; select length(group_concat(distinct f order by f)) from t1; drop table t1; create table t1 (f longtext) engine=MyISAM; insert into t1 values (repeat('1abcdefg',9000)),(repeat('2abcdefg',9000)); select length(group_concat(f)) from t1; select length(group_concat(f order by f)) from t1; select length(group_concat(distinct f order by f)) from t1; drop table t1; Expected result create table t1 (f longtext) engine=MyISAM; insert into t1 values (repeat('abcdefgh',9000)),(repeat('abcdefgh',9000)); select length(group_concat(f)) from t1; length(group_concat(f)) 144001 select length(group_concat(f order by f)) from t1; length(group_concat(f order by f)) 144001 select length(group_concat(distinct f order by f)) from t1; length(group_concat(distinct f order by f)) 72000 drop table t1; create table t1 (f longtext) engine=MyISAM; insert into t1 values (repeat('1abcdefg',9000)),(repeat('2abcdefg',9000)); select length(group_concat(f)) from t1; length(group_concat(f)) 144001 select length(group_concat(f order by f)) from t1; length(group_concat(f order by f)) 144001 select length(group_concat(distinct f order by f)) from t1; length(group_concat(distinct f order by f)) 144001 drop table t1; Actual result create table t1 (f longtext) engine=MyISAM; insert into t1 values (repeat('abcdefgh',9000)),(repeat('abcdefgh',9000)); select length(group_concat(f)) from t1; length(group_concat(f)) 144001 select length(group_concat(f order by f)) from t1; length(group_concat(f order by f)) 131071 select length(group_concat(distinct f order by f)) from t1; length(group_concat(distinct f order by f)) 65535 drop table t1; create table t1 (f longtext) engine=MyISAM; insert into t1 values (repeat('1abcdefg',9000)),(repeat('2abcdefg',9000)); select length(group_concat(f)) from t1; length(group_concat(f)) 144001 select length(group_concat(f order by f)) from t1; length(group_concat(f order by f)) 131071 select length(group_concat(distinct f order by f)) from t1; length(group_concat(distinct f order by f)) 131071 drop table t1; The problem exists in MySQL 5.5, but not in 5.6/5.7.

GROUP_CONCAT truncate output to 65.536 chars when using DISTINCT or ORDER BY

Details

Description

Gliffy Diagrams

Attachments

Activity

People

Dates

Agile