diff options
Diffstat (limited to 'mysql-test/main/func_gconcat.result')
| -rw-r--r-- | mysql-test/main/func_gconcat.result | 1445 |
1 files changed, 1445 insertions, 0 deletions
diff --git a/mysql-test/main/func_gconcat.result b/mysql-test/main/func_gconcat.result new file mode 100644 index 00000000..fea25124 --- /dev/null +++ b/mysql-test/main/func_gconcat.result @@ -0,0 +1,1445 @@ +create table t1 (grp int, a bigint unsigned, c char(10) not null, d char(10) not null); +insert into t1 values (1,1,"a","a"); +insert into t1 values (2,2,"b","a"); +insert into t1 values (2,3,"c","b"); +insert into t1 values (3,4,"E","a"); +insert into t1 values (3,5,"C","b"); +insert into t1 values (3,6,"D","b"); +insert into t1 values (3,7,"d","d"); +insert into t1 values (3,8,"d","d"); +insert into t1 values (3,9,"D","c"); +select grp,group_concat(c) from t1 group by grp; +grp group_concat(c) +1 a +2 b,c +3 E,C,D,d,d,D +explain extended select grp,group_concat(c) from t1 group by grp; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 9 100.00 Using filesort +Warnings: +Note 1003 select `test`.`t1`.`grp` AS `grp`,group_concat(`test`.`t1`.`c` separator ',') AS `group_concat(c)` from `test`.`t1` group by `test`.`t1`.`grp` +select grp,group_concat(a,c) from t1 group by grp; +grp group_concat(a,c) +1 1a +2 2b,3c +3 4E,5C,6D,7d,8d,9D +select grp,group_concat("(",a,":",c,")") from t1 group by grp; +grp group_concat("(",a,":",c,")") +1 (1:a) +2 (2:b),(3:c) +3 (4:E),(5:C),(6:D),(7:d),(8:d),(9:D) +select grp,group_concat(c separator ",") from t1 group by grp; +grp group_concat(c separator ",") +1 a +2 b,c +3 E,C,D,d,d,D +select grp,group_concat(c separator "---->") from t1 group by grp; +grp group_concat(c separator "---->") +1 a +2 b---->c +3 E---->C---->D---->d---->d---->D +select grp,group_concat(c order by c) from t1 group by grp; +grp group_concat(c order by c) +1 a +2 b,c +3 C,D,d,d,D,E +select grp,group_concat(c order by c desc) from t1 group by grp; +grp group_concat(c order by c desc) +1 a +2 c,b +3 E,D,d,d,D,C +select grp,group_concat(d order by a) from t1 group by grp; +grp group_concat(d order by a) +1 a +2 a,b +3 a,b,b,d,d,c +select grp,group_concat(d order by a desc) from t1 group by grp; +grp group_concat(d order by a desc) +1 a +2 b,a +3 c,d,d,b,b,a +select grp,group_concat(a order by a,d+c-ascii(c)-a) from t1 group by grp; +grp group_concat(a order by a,d+c-ascii(c)-a) +1 1 +2 2,3 +3 4,5,6,7,8,9 +select grp,group_concat(a order by d+c-ascii(c),a) from t1 group by grp; +grp group_concat(a order by d+c-ascii(c),a) +1 1 +2 3,2 +3 7,8,4,6,9,5 +select grp,group_concat(c order by 1) from t1 group by grp; +grp group_concat(c order by 1) +1 a +2 b,c +3 C,D,d,d,D,E +select grp,group_concat(distinct c order by c) from t1 group by grp; +grp group_concat(distinct c order by c) +1 a +2 b,c +3 C,D,E +select grp,group_concat(distinct c order by c desc) from t1 group by grp; +grp group_concat(distinct c order by c desc) +1 a +2 c,b +3 E,D,C +explain extended select grp,group_concat(distinct c order by c desc) from t1 group by grp; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 9 100.00 Using filesort +Warnings: +Note 1003 select `test`.`t1`.`grp` AS `grp`,group_concat(distinct `test`.`t1`.`c` order by `test`.`t1`.`c` DESC separator ',') AS `group_concat(distinct c order by c desc)` from `test`.`t1` group by `test`.`t1`.`grp` +select grp,group_concat(c order by c separator ",") from t1 group by grp; +grp group_concat(c order by c separator ",") +1 a +2 b,c +3 C,D,d,d,D,E +select grp,group_concat(c order by c desc separator ",") from t1 group by grp; +grp group_concat(c order by c desc separator ",") +1 a +2 c,b +3 E,D,d,d,D,C +select grp,group_concat(distinct c order by c separator ",") from t1 group by grp; +grp group_concat(distinct c order by c separator ",") +1 a +2 b,c +3 C,D,E +explain extended select grp,group_concat(distinct c order by c separator ",") from t1 group by grp; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 9 100.00 Using filesort +Warnings: +Note 1003 select `test`.`t1`.`grp` AS `grp`,group_concat(distinct `test`.`t1`.`c` order by `test`.`t1`.`c` ASC separator ',') AS `group_concat(distinct c order by c separator ",")` from `test`.`t1` group by `test`.`t1`.`grp` +select grp,group_concat(distinct c order by c desc separator ",") from t1 group by grp; +grp group_concat(distinct c order by c desc separator ",") +1 a +2 c,b +3 E,D,C +select grp,group_concat(c order by grp desc) from t1 group by grp order by grp; +grp group_concat(c order by grp desc) +1 a +2 c,b +3 D,d,d,D,C,E +select grp, group_concat(a separator "")+0 from t1 group by grp; +grp group_concat(a separator "")+0 +1 1 +2 23 +3 456789 +select grp, group_concat(a separator "")+0.0 from t1 group by grp; +grp group_concat(a separator "")+0.0 +1 1.0 +2 23.0 +3 456789.0 +select grp, ROUND(group_concat(a separator "")) from t1 group by grp; +grp ROUND(group_concat(a separator "")) +1 1 +2 23 +3 456789 +drop table t1; +create table t1 (grp int, c char(10)); +insert into t1 values (1,NULL),(2,"b"),(2,NULL),(3,"E"),(3,NULL),(3,"D"),(3,NULL),(3,NULL),(3,"D"),(4,""),(5,NULL); +select grp,group_concat(c order by c) from t1 group by grp; +grp group_concat(c order by c) +1 NULL +2 b +3 D,D,E +4 +5 NULL +set group_concat_max_len = 4; +select grp,group_concat(c) from t1 group by grp; +grp group_concat(c) +1 NULL +2 b +3 D,D, +4 +5 NULL +Warnings: +Warning 1260 Row 4 was cut by GROUP_CONCAT() +show warnings; +Level Code Message +Warning 1260 Row 4 was cut by GROUP_CONCAT() +set group_concat_max_len = 1024; +select group_concat(sum(c)) from t1 group by grp; +ERROR HY000: Invalid use of group function +select grp,group_concat(c order by 2) from t1 group by grp; +ERROR 42S22: Unknown column '2' in 'order clause' +drop table t1; +create table t1 ( URL_ID int(11), URL varchar(80)); +create table t2 ( REQ_ID int(11), URL_ID int(11)); +insert into t1 values (4,'www.host.com'), (5,'www.google.com'),(5,'www.help.com'); +insert into t2 values (1,4), (5,4), (5,5); +select REQ_ID, Group_Concat(URL) as URL from t1, t2 where +t2.URL_ID = t1.URL_ID group by REQ_ID; +REQ_ID URL +1 X +5 X,X,X +select REQ_ID, Group_Concat(URL) as URL, Min(t1.URL_ID) urll, +Max(t1.URL_ID) urlg from t1, t2 where t2.URL_ID = t1.URL_ID group by REQ_ID; +REQ_ID URL urll urlg +1 X 4 4 +5 X,X,X 4 5 +drop table t1; +drop table t2; +create table t1 (id int, name varchar(16)); +insert into t1 values (1,'longername'),(1,'evenlongername'); +select ifnull(group_concat(concat(t1.id, ':', t1.name)), 'shortname') as 'without distinct: how it should be' from t1; +without distinct: how it should be +1:longername,1:evenlongername +select distinct ifnull(group_concat(concat(t1.id, ':', t1.name)), 'shortname') as 'with distinct: cutoff at length of shortname' from t1; +with distinct: cutoff at length of shortname +1:longername,1:evenlongername +drop table t1; +create table t1(id int); +create table t2(id int); +insert into t1 values(0),(1); +select group_concat(t1.id) FROM t1,t2; +group_concat(t1.id) +NULL +drop table t1; +drop table t2; +create table t1 (bar varchar(32)); +insert into t1 values('test1'),('test2'); +select group_concat(bar order by concat(bar,bar)) from t1; +group_concat(bar order by concat(bar,bar)) +test1,test2 +select group_concat(bar order by concat(bar,bar) desc) from t1; +group_concat(bar order by concat(bar,bar) desc) +test2,test1 +select bar from t1 having group_concat(bar)=''; +bar +select bar from t1 having instr(group_concat(bar), "test") > 0; +bar +test1 +select bar from t1 having instr(group_concat(bar order by concat(bar,bar) desc), "test2,test1") > 0; +bar +test1 +drop table t1; +create table t1 (a int, a1 varchar(10)); +create table t2 (a0 int); +insert into t1 values (0,"a"),(0,"b"),(1,"c"); +insert into t2 values (1),(2),(3); +select group_concat(a1 order by (t1.a IN (select a0 from t2))) from t1; +group_concat(a1 order by (t1.a IN (select a0 from t2))) +b,a,c +select group_concat(a1 order by (t1.a)) from t1; +group_concat(a1 order by (t1.a)) +b,a,c +drop table t1, t2; +CREATE TABLE t1 (id1 tinyint(4) NOT NULL, id2 tinyint(4) NOT NULL); +INSERT INTO t1 VALUES (1, 1),(1, 2),(1, 3),(1, 4),(1, 5),(2, 1),(2, 2),(2, 3); +CREATE TABLE t2 (id1 tinyint(4) NOT NULL); +INSERT INTO t2 VALUES (1),(2),(3),(4),(5); +SELECT t1.id1, GROUP_CONCAT(t1.id2 ORDER BY t1.id2 ASC) AS concat_id FROM t1, t2 WHERE t1.id1 = t2.id1 AND t1.id1=1 GROUP BY t1.id1; +id1 concat_id +1 1,2,3,4,5 +SELECT t1.id1, GROUP_CONCAT(t1.id2 ORDER BY t1.id2 ASC) AS concat_id FROM t1, t2 WHERE t1.id1 = t2.id1 GROUP BY t1.id1; +id1 concat_id +1 1,2,3,4,5 +2 1,2,3 +SELECT t1.id1, GROUP_CONCAT(t1.id2 ORDER BY t1.id2 DESC) AS concat_id FROM t1, t2 WHERE t1.id1 = t2.id1 GROUP BY t1.id1; +id1 concat_id +1 5,4,3,2,1 +2 3,2,1 +SELECT t1.id1, GROUP_CONCAT(t1.id2 ORDER BY 6-t1.id2 ASC) AS concat_id FROM t1, t2 WHERE t1.id1 = t2.id1 GROUP BY t1.id1; +id1 concat_id +1 5,4,3,2,1 +2 3,2,1 +SELECT t1.id1, GROUP_CONCAT(t1.id2,6-t1.id2 ORDER BY 6-t1.id2 ASC) AS concat_id FROM t1, t2 WHERE t1.id1 = t2.id1 GROUP BY t1.id1; +id1 concat_id +1 51,42,33,24,15 +2 33,24,15 +SELECT t1.id1, GROUP_CONCAT(t1.id2,6-t1.id2 ORDER BY 6-t1.id2 ASC) AS concat_id FROM t1, t2 WHERE t1.id1 = t2.id1 GROUP BY t1.id1; +id1 concat_id +1 51,42,33,24,15 +2 33,24,15 +SELECT t1.id1, GROUP_CONCAT(t1.id2,"/",6-t1.id2 ORDER BY 1+0,6-t1.id2,t1.id2 ASC) AS concat_id FROM t1, t2 WHERE t1.id1 = t2.id1 GROUP BY t1.id1; +id1 concat_id +1 5/1,4/2,3/3,2/4,1/5 +2 3/3,2/4,1/5 +drop table t1,t2; +create table t1 (s1 char(10), s2 int not null); +insert into t1 values ('a',2),('b',2),('c',1),('a',3),('b',4),('c',4); +select distinct s1 from t1 order by s2,s1; +s1 +c +a +b +select group_concat(distinct s1) from t1; +group_concat(distinct s1) +a,b,c +select group_concat(distinct s1 order by s2) from t1 where s2 < 4; +group_concat(distinct s1 order by s2) +c,b,a +select group_concat(distinct s1 order by s2) from t1; +group_concat(distinct s1 order by s2) +c,b,a +drop table t1; +create table t1 (a int, c int); +insert into t1 values (1, 2), (2, 3), (2, 4), (3, 5); +create table t2 (a int, c int); +insert into t2 values (1, 5), (2, 4), (3, 3), (3,3); +select group_concat(c) from t1; +group_concat(c) +2,3,4,5 +select group_concat(c order by (select c from t2 where t2.a=t1.a limit 1)) as grp from t1; +grp +5,4,3,2 +select group_concat(c order by (select mid(group_concat(c order by a),1,5) from t2 where t2.a=t1.a)) as grp from t1; +grp +5,4,3,2 +select group_concat(c order by (select mid(group_concat(c order by a),1,5) from t2 where t2.a=t1.a) desc) as grp from t1; +grp +2,4,3,5 +select t1.a, group_concat(c order by (select c from t2 where t2.a=t1.a limit 1)) as grp from t1 group by 1; +a grp +1 2 +2 4,3 +3 5 +select t1.a, group_concat(c order by (select mid(group_concat(c order by a),1,5) from t2 where t2.a=t1.a)) as grp from t1 group by 1; +a grp +1 2 +2 4,3 +3 5 +select t1.a, group_concat(c order by (select mid(group_concat(c order by a),1,5) from t2 where t2.a=t1.a) desc) as grp from t1 group by 1; +a grp +1 2 +2 4,3 +3 5 +select group_concat(c order by (select concat(5-t1.c,group_concat(c order by a)) from t2 where t2.a=t1.a)) as grp from t1; +grp +5,4,3,2 +select group_concat(c order by (select concat(t1.c,group_concat(c)) from t2 where a=t1.a)) as grp from t1; +grp +2,3,4,5 +select a,c,(select group_concat(c order by a) from t2 where a=t1.a) as grp from t1 order by grp; +a c grp +3 5 3,3 +2 3 4 +2 4 4 +1 2 5 +drop table t1,t2; +CREATE TABLE t1 ( a int ); +CREATE TABLE t2 ( a int ); +INSERT INTO t1 VALUES (1), (2); +INSERT INTO t2 VALUES (1), (2); +SELECT GROUP_CONCAT(t1.a*t2.a ORDER BY t2.a) FROM t1, t2 GROUP BY t1.a; +GROUP_CONCAT(t1.a*t2.a ORDER BY t2.a) +1,2 +2,4 +DROP TABLE t1, t2; +CREATE TABLE t1 (a char(4)); +INSERT INTO t1 VALUES ('John'), ('Anna'), ('Bill'); +SELECT GROUP_CONCAT(a SEPARATOR '||') AS names FROM t1 +HAVING names LIKE '%An%'; +names +John||Anna||Bill +SELECT GROUP_CONCAT(a SEPARATOR '###') AS names FROM t1 +HAVING LEFT(names, 1) ='J'; +names +John###Anna###Bill +DROP TABLE t1; +CREATE TABLE t1 ( a int, b TEXT ); +INSERT INTO t1 VALUES (1,'First Row'), (2,'Second Row'); +SELECT GROUP_CONCAT(b ORDER BY b) FROM t1 GROUP BY a; +GROUP_CONCAT(b ORDER BY b) +First Row +Second Row +DROP TABLE t1; +CREATE TABLE t1 (A_ID INT NOT NULL,A_DESC CHAR(3) NOT NULL,PRIMARY KEY (A_ID)); +INSERT INTO t1 VALUES (1,'ABC'), (2,'EFG'), (3,'HIJ'); +CREATE TABLE t2 (A_ID INT NOT NULL,B_DESC CHAR(3) NOT NULL,PRIMARY KEY (A_ID,B_DESC)); +INSERT INTO t2 VALUES (1,'A'),(1,'B'),(3,'F'); +SELECT t1.A_ID, GROUP_CONCAT(t2.B_DESC) AS B_DESC FROM t1 LEFT JOIN t2 ON t1.A_ID=t2.A_ID GROUP BY t1.A_ID ORDER BY t1.A_DESC; +A_ID B_DESC +1 A,B +2 NULL +3 F +DROP TABLE t1; +DROP TABLE t2; +create table t1 (a int, b text); +insert into t1 values (1, 'bb'), (1, 'ccc'), (1, 'a'), (1, 'bb'), (1, 'ccc'); +insert into t1 values (2, 'BB'), (2, 'CCC'), (2, 'A'), (2, 'BB'), (2, 'CCC'); +select group_concat(b) from t1 group by a; +group_concat(b) +bb,ccc,a,bb,ccc +BB,CCC,A,BB,CCC +select group_concat(distinct b) from t1 group by a; +group_concat(distinct b) +a,bb,ccc +A,BB,CCC +select group_concat(b order by b) from t1 group by a; +group_concat(b order by b) +a,bb,bb,ccc,ccc +A,BB,BB,CCC,CCC +select group_concat(distinct b order by b) from t1 group by a; +group_concat(distinct b order by b) +a,bb,ccc +A,BB,CCC +set local group_concat_max_len=4; +select group_concat(b) from t1 group by a; +group_concat(b) +bb,c +BB,C +Warnings: +Warning 1260 Row 2 was cut by GROUP_CONCAT() +Warning 1260 Row 4 was cut by GROUP_CONCAT() +select group_concat(distinct b) from t1 group by a; +group_concat(distinct b) +a,bb +A,BB +Warnings: +Warning 1260 Row 3 was cut by GROUP_CONCAT() +Warning 1260 Row 6 was cut by GROUP_CONCAT() +select group_concat(b order by b) from t1 group by a; +group_concat(b order by b) +a,bb +A,BB +Warnings: +Warning 1260 Row 3 was cut by GROUP_CONCAT() +Warning 1260 Row 6 was cut by GROUP_CONCAT() +select group_concat(distinct b order by b) from t1 group by a; +group_concat(distinct b order by b) +a,bb +A,BB +Warnings: +Warning 1260 Row 3 was cut by GROUP_CONCAT() +Warning 1260 Row 6 was cut by GROUP_CONCAT() +insert into t1 values (1, concat(repeat('1', 300), '2')), +(1, concat(repeat('1', 300), '2')), (1, concat(repeat('0', 300), '1')), +(2, concat(repeat('1', 300), '2')), (2, concat(repeat('1', 300), '2')), +(2, concat(repeat('0', 300), '1')); +set local group_concat_max_len=1024; +select group_concat(b) from t1 group by a; +group_concat(b) +bb,ccc,a,bb,ccc,1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111112,1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111112,0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001 +BB,CCC,A,BB,CCC,1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111112,1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111112,0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001 +select group_concat(distinct b) from t1 group by a; +group_concat(distinct b) +0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001,1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111112,a,bb,ccc +0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001,1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111112,A,BB,CCC +select group_concat(b order by b) from t1 group by a; +group_concat(b order by b) +0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001,1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111112,1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111112,a,bb,bb,ccc,ccc +0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001,1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111112,1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111112,A,BB,BB,CCC,CCC +select group_concat(distinct b order by b) from t1 group by a; +group_concat(distinct b order by b) +0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001,1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111112,a,bb,ccc +0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001,1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111112,A,BB,CCC +set local group_concat_max_len=400; +select group_concat(b) from t1 group by a; +group_concat(b) +bb,ccc,a,bb,ccc,1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111112,1111111111111111111111111111111111111111111111111111111111111111111111111111111111 +BB,CCC,A,BB,CCC,1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111112,1111111111111111111111111111111111111111111111111111111111111111111111111111111111 +Warnings: +Warning 1260 Row 7 was cut by GROUP_CONCAT() +Warning 1260 Row 14 was cut by GROUP_CONCAT() +select group_concat(distinct b) from t1 group by a; +group_concat(distinct b) +0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001,11111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111 +0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001,11111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111 +Warnings: +Warning 1260 Row 2 was cut by GROUP_CONCAT() +Warning 1260 Row 4 was cut by GROUP_CONCAT() +select group_concat(b order by b) from t1 group by a; +group_concat(b order by b) +0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001,11111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111 +0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001,11111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111 +Warnings: +Warning 1260 Row 2 was cut by GROUP_CONCAT() +Warning 1260 Row 4 was cut by GROUP_CONCAT() +select group_concat(distinct b order by b) from t1 group by a; +group_concat(distinct b order by b) +0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001,11111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111 +0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001,11111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111 +Warnings: +Warning 1260 Row 2 was cut by GROUP_CONCAT() +Warning 1260 Row 4 was cut by GROUP_CONCAT() +drop table t1; +create table t1 (a varchar(255) character set cp1250 collate cp1250_general_ci, +b varchar(255) character set koi8r); +insert into t1 values ('xxx','yyy'); +select collation(a) from t1; +collation(a) +cp1250_general_ci +select collation(group_concat(a)) from t1; +collation(group_concat(a)) +cp1250_general_ci +create table t2 select group_concat(a) as a from t1; +show create table t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `a` varchar(400) CHARACTER SET cp1250 COLLATE cp1250_general_ci DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +select collation(group_concat(a,_koi8r'test')) from t1; +collation(group_concat(a,_koi8r'test')) +cp1250_general_ci +select collation(group_concat(a,_koi8r 0xC1C2)) from t1; +ERROR HY000: Illegal mix of collations (cp1250_general_ci,IMPLICIT) and (koi8r_general_ci,COERCIBLE) for operation 'group_concat(' +select collation(group_concat(a,b)) from t1; +ERROR HY000: Illegal mix of collations (cp1250_general_ci,IMPLICIT) and (koi8r_general_ci,IMPLICIT) for operation 'group_concat(' +drop table t1; +drop table t2; +CREATE TABLE t1 (a CHAR(10) CHARACTER SET cp850); +INSERT INTO t1 VALUES ('ภ'); +SELECT a FROM t1; +a +ภ +SELECT GROUP_CONCAT(a) FROM t1; +GROUP_CONCAT(a) +ภ +DROP TABLE t1; +CREATE TABLE t1 (id int); +SELECT GROUP_CONCAT(id) AS gc FROM t1 HAVING gc IS NULL; +gc +NULL +DROP TABLE t1; +create table t2 (a int, b int); +insert into t2 values (1,1), (2,2); +select b x, (select group_concat(x) from t2) from t2; +x (select group_concat(x) from t2) +1 1,1 +2 2,2 +drop table t2; +create table t1 (d int not null auto_increment,primary key(d), a int, b int, c int); +insert into t1(a,b) values (1,3), (1,4), (1,2), (2,7), (1,1), (1,2), (2,3), (2,3); +select d,a,b from t1 order by a; +d a b +1 1 3 +2 1 4 +3 1 2 +5 1 1 +6 1 2 +4 2 7 +7 2 3 +8 2 3 +explain select a, group_concat(b) from t1 group by a with rollup; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 8 Using filesort +select a, group_concat(b) from t1 group by a with rollup; +a group_concat(b) +1 3,4,2,1,2 +2 7,3,3 +NULL 3,4,2,1,2,7,3,3 +select a, group_concat(distinct b) from t1 group by a with rollup; +a group_concat(distinct b) +1 1,2,3,4 +2 3,7 +NULL 1,2,3,4,7 +select a, group_concat(b order by b) from t1 group by a with rollup; +a group_concat(b order by b) +1 1,2,2,3,4 +2 3,3,7 +NULL 1,2,2,3,3,3,4,7 +select a, group_concat(distinct b order by b) from t1 group by a with rollup; +a group_concat(distinct b order by b) +1 1,2,3,4 +2 3,7 +NULL 1,2,3,4,7 +drop table t1; +create table t1 (a char(3), b char(20), primary key (a, b)); +insert into t1 values ('ABW', 'Dutch'), ('ABW', 'English'); +select group_concat(a) from t1 group by b; +group_concat(a) +ABW +ABW +drop table t1; +CREATE TABLE t1 ( +aID smallint(5) unsigned NOT NULL auto_increment, +sometitle varchar(255) NOT NULL default '', +bID smallint(5) unsigned NOT NULL, +PRIMARY KEY (aID), +UNIQUE KEY sometitle (sometitle) +); +INSERT INTO t1 SET sometitle = 'title1', bID = 1; +INSERT INTO t1 SET sometitle = 'title2', bID = 1; +CREATE TABLE t2 ( +bID smallint(5) unsigned NOT NULL auto_increment, +somename varchar(255) NOT NULL default '', +PRIMARY KEY (bID), +UNIQUE KEY somename (somename) +); +INSERT INTO t2 SET somename = 'test'; +SELECT COUNT(*), GROUP_CONCAT(DISTINCT t2.somename SEPARATOR ' |') +FROM t1 JOIN t2 ON t1.bID = t2.bID; +COUNT(*) GROUP_CONCAT(DISTINCT t2.somename SEPARATOR ' |') +2 test +INSERT INTO t2 SET somename = 'test2'; +SELECT COUNT(*), GROUP_CONCAT(DISTINCT t2.somename SEPARATOR ' |') +FROM t1 JOIN t2 ON t1.bID = t2.bID; +COUNT(*) GROUP_CONCAT(DISTINCT t2.somename SEPARATOR ' |') +2 test +DELETE FROM t2 WHERE somename = 'test2'; +SELECT COUNT(*), GROUP_CONCAT(DISTINCT t2.somename SEPARATOR ' |') +FROM t1 JOIN t2 ON t1.bID = t2.bID; +COUNT(*) GROUP_CONCAT(DISTINCT t2.somename SEPARATOR ' |') +2 test +DROP TABLE t1,t2; +select * from (select group_concat('c') from DUAL) t; +group_concat('c') +c +create table t1 ( a int not null default 0); +select * from (select group_concat(a) from t1) t2; +group_concat(a) +NULL +select group_concat('x') UNION ALL select 1; +group_concat('x') +x +1 +drop table t1; +CREATE TABLE t1 (id int, a varchar(9)); +INSERT INTO t1 VALUES +(2, ''), (1, ''), (2, 'x'), (1, 'y'), (3, 'z'), (3, ''); +SELECT GROUP_CONCAT(a) FROM t1; +GROUP_CONCAT(a) +,,x,y,z, +SELECT GROUP_CONCAT(a ORDER BY a) FROM t1; +GROUP_CONCAT(a ORDER BY a) +,,,x,y,z +SELECT GROUP_CONCAT(a) FROM t1 GROUP BY id; +GROUP_CONCAT(a) +,y +,x +z, +SELECT GROUP_CONCAT(a ORDER BY a) FROM t1 GROUP BY id; +GROUP_CONCAT(a ORDER BY a) +,y +,x +,z +DROP TABLE t1; +create table t1(f1 int); +insert into t1 values(1),(2),(3); +select f1, group_concat(f1+1) from t1 group by f1 with rollup; +f1 group_concat(f1+1) +1 2 +2 3 +3 4 +NULL 2,3,4 +select count(distinct (f1+1)) from t1 group by f1 with rollup; +count(distinct (f1+1)) +1 +1 +1 +3 +drop table t1; +create table t1 (f1 int unsigned, f2 varchar(255)); +insert into t1 values (1,repeat('a',255)),(2,repeat('b',255)); +select f2,group_concat(f1) from t1 group by f2; +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def test t1 t1 f2 f2 253 255 255 Y 0 0 8 +def group_concat(f1) 253 400 1 Y 0 0 8 +f2 group_concat(f1) +aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 1 +bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb 2 +drop table t1; +set names latin1; +create table t1 (a char, b char); +insert into t1 values ('a', 'a'), ('a', 'b'), ('b', 'a'), ('b', 'b'); +create table t2 select group_concat(b) as a from t1 where a = 'a'; +create table t3 (select group_concat(a) as a from t1 where a = 'a') union +(select group_concat(b) as a from t1 where a = 'b'); +select charset(a) from t2; +charset(a) +latin1 +select charset(a) from t3; +charset(a) +latin1 +latin1 +drop table t1, t2, t3; +set names default; +create table t1 (c1 varchar(10), c2 int); +select charset(group_concat(c1 order by c2)) from t1; +charset(group_concat(c1 order by c2)) +latin1 +drop table t1; +CREATE TABLE t1 (a INT(10), b LONGTEXT, PRIMARY KEY (a)); +SET GROUP_CONCAT_MAX_LEN = 20000000; +INSERT INTO t1 VALUES (1,REPEAT(CONCAT('A',CAST(CHAR(0) AS BINARY),'B'), 40000)); +INSERT INTO t1 SELECT a + 1, b FROM t1; +SELECT a, CHAR_LENGTH(b) FROM t1; +a CHAR_LENGTH(b) +1 120000 +2 120000 +SELECT CHAR_LENGTH( GROUP_CONCAT(b) ) FROM t1; +CHAR_LENGTH( GROUP_CONCAT(b) ) +240001 +SET GROUP_CONCAT_MAX_LEN = 1024; +DROP TABLE t1; +CREATE TABLE t1 (a int, b int); +INSERT INTO t1 VALUES (2,1), (1,2), (2,2), (1,3); +SELECT GROUP_CONCAT(a), x +FROM (SELECT a, GROUP_CONCAT(b) x FROM t1 GROUP BY a) AS s +GROUP BY x; +GROUP_CONCAT(a) x +2 1,2 +1 2,3 +DROP TABLE t1; +set names utf8; +create table t1 +( +x text character set utf8 not null, +y integer not null +); +insert into t1 values (repeat('a', 1022), 0), (repeat(_utf8 0xc3b7, 4), 0); +set group_concat_max_len= 1022 + 10; +select @x:=group_concat(x) from t1 group by y; +select @@group_concat_max_len, length(@x), char_length(@x), right(@x,12), right(HEX(@x),12); +@@group_concat_max_len length(@x) char_length(@x) right(@x,12) right(HEX(@x),12) +1032 1031 1027 aaaaaaa,รทรทรทรท C3B7C3B7C3B7 +set group_concat_max_len= 1022 + 9; +select @x:=group_concat(x) from t1 group by y; +select @@group_concat_max_len, length(@x), char_length(@x), right(@x,12), right(HEX(@x),12); +@@group_concat_max_len length(@x) char_length(@x) right(@x,12) right(HEX(@x),12) +1031 1031 1027 aaaaaaa,รทรทรทรท C3B7C3B7C3B7 +set group_concat_max_len= 1022 + 8; +select @x:=group_concat(x) from t1 group by y; +select @@group_concat_max_len, length(@x), char_length(@x), right(@x,12), right(HEX(@x),12); +@@group_concat_max_len length(@x) char_length(@x) right(@x,12) right(HEX(@x),12) +1030 1029 1026 aaaaaaaa,รทรทรท C3B7C3B7C3B7 +set group_concat_max_len= 1022 + 7; +select @x:=group_concat(x) from t1 group by y; +select @@group_concat_max_len, length(@x), char_length(@x), right(@x,12), right(HEX(@x),12); +@@group_concat_max_len length(@x) char_length(@x) right(@x,12) right(HEX(@x),12) +1029 1029 1026 aaaaaaaa,รทรทรท C3B7C3B7C3B7 +set group_concat_max_len= 1022 + 6; +select @x:=group_concat(x) from t1 group by y; +select @@group_concat_max_len, length(@x), char_length(@x), right(@x,12), right(HEX(@x),12); +@@group_concat_max_len length(@x) char_length(@x) right(@x,12) right(HEX(@x),12) +1028 1027 1025 aaaaaaaaa,รทรท 612CC3B7C3B7 +set group_concat_max_len= 1022 + 5; +select @x:=group_concat(x) from t1 group by y; +select @@group_concat_max_len, length(@x), char_length(@x), right(@x,12), right(HEX(@x),12); +@@group_concat_max_len length(@x) char_length(@x) right(@x,12) right(HEX(@x),12) +1027 1027 1025 aaaaaaaaa,รทรท 612CC3B7C3B7 +set group_concat_max_len= 1022 + 4; +select @x:=group_concat(x) from t1 group by y; +select @@group_concat_max_len, length(@x), char_length(@x), right(@x,12), right(HEX(@x),12); +@@group_concat_max_len length(@x) char_length(@x) right(@x,12) right(HEX(@x),12) +1026 1025 1024 aaaaaaaaaa,รท 6161612CC3B7 +set group_concat_max_len= 1022 + 3; +select @x:=group_concat(x) from t1 group by y; +select @@group_concat_max_len, length(@x), char_length(@x), right(@x,12), right(HEX(@x),12); +@@group_concat_max_len length(@x) char_length(@x) right(@x,12) right(HEX(@x),12) +1025 1025 1024 aaaaaaaaaa,รท 6161612CC3B7 +set group_concat_max_len= 1022 + 2; +select @x:=group_concat(x) from t1 group by y; +select @@group_concat_max_len, length(@x), char_length(@x), right(@x,12), right(HEX(@x),12); +@@group_concat_max_len length(@x) char_length(@x) right(@x,12) right(HEX(@x),12) +1024 1023 1023 aaaaaaaaaaa, 61616161612C +set group_concat_max_len= 1022 + 1; +select @x:=group_concat(x) from t1 group by y; +select @@group_concat_max_len, length(@x), char_length(@x), right(@x,12), right(HEX(@x),12); +@@group_concat_max_len length(@x) char_length(@x) right(@x,12) right(HEX(@x),12) +1023 1023 1023 aaaaaaaaaaa, 61616161612C +drop table t1; +set group_concat_max_len=1024; +set names latin1; +create table t1 (f1 int unsigned, f2 varchar(255)); +insert into t1 values (1,repeat('a',255)),(2,repeat('b',255)); +select f2,group_concat(f1) from t1 group by f2; +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def test t1 t1 f2 f2 253 255 255 Y 0 0 8 +def group_concat(f1) 252 1024 1 Y 0 0 8 +f2 group_concat(f1) +aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 1 +bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb 2 +drop table t1; +CREATE TABLE t1(a TEXT, b CHAR(20)); +INSERT INTO t1 VALUES ("one.1","one.1"),("two.2","two.2"),("one.3","one.3"); +SELECT GROUP_CONCAT(DISTINCT UCASE(a)) FROM t1; +GROUP_CONCAT(DISTINCT UCASE(a)) +ONE.1,ONE.3,TWO.2 +SELECT GROUP_CONCAT(DISTINCT UCASE(b)) FROM t1; +GROUP_CONCAT(DISTINCT UCASE(b)) +ONE.1,ONE.3,TWO.2 +DROP TABLE t1; +CREATE TABLE t1( a VARCHAR( 10 ), b INT ); +INSERT INTO t1 VALUES ( repeat( 'a', 10 ), 1), +( repeat( 'b', 10 ), 2); +SET group_concat_max_len = 20; +SELECT GROUP_CONCAT( a ) FROM t1; +GROUP_CONCAT( a ) +aaaaaaaaaa,bbbbbbbbb +Warnings: +Warning 1260 Row 2 was cut by GROUP_CONCAT() +SELECT GROUP_CONCAT( DISTINCT a ) FROM t1; +GROUP_CONCAT( DISTINCT a ) +aaaaaaaaaa,bbbbbbbbb +Warnings: +Warning 1260 Row 2 was cut by GROUP_CONCAT() +SELECT GROUP_CONCAT( a ORDER BY b ) FROM t1; +GROUP_CONCAT( a ORDER BY b ) +aaaaaaaaaa,bbbbbbbbb +Warnings: +Warning 1260 Row 2 was cut by GROUP_CONCAT() +SELECT GROUP_CONCAT( DISTINCT a ORDER BY b ) FROM t1; +GROUP_CONCAT( DISTINCT a ORDER BY b ) +aaaaaaaaaa,bbbbbbbbb +Warnings: +Warning 1260 Row 2 was cut by GROUP_CONCAT() +SET group_concat_max_len = DEFAULT; +DROP TABLE t1; +SET group_concat_max_len= 65535; +CREATE TABLE t1( a TEXT, b INTEGER ); +INSERT INTO t1 VALUES ( 'a', 0 ), ( 'b', 1 ); +SELECT GROUP_CONCAT( a ORDER BY b ) FROM t1; +GROUP_CONCAT( a ORDER BY b ) +a,b +SELECT GROUP_CONCAT(DISTINCT a ORDER BY b) FROM t1; +GROUP_CONCAT(DISTINCT a ORDER BY b) +a,b +SELECT GROUP_CONCAT(DISTINCT a) FROM t1; +GROUP_CONCAT(DISTINCT a) +a,b +SET group_concat_max_len= 10; +SELECT GROUP_CONCAT(a ORDER BY b) FROM t1; +GROUP_CONCAT(a ORDER BY b) +a,b +SELECT GROUP_CONCAT(DISTINCT a ORDER BY b) FROM t1; +GROUP_CONCAT(DISTINCT a ORDER BY b) +a,b +SELECT GROUP_CONCAT(DISTINCT a) FROM t1; +GROUP_CONCAT(DISTINCT a) +a,b +SET group_concat_max_len= 65535; +CREATE TABLE t2( a TEXT ); +INSERT INTO t2 VALUES( REPEAT( 'a', 5000 ) ); +INSERT INTO t2 VALUES( REPEAT( 'b', 5000 ) ); +INSERT INTO t2 VALUES( REPEAT( 'a', 5000 ) ); +SELECT LENGTH( GROUP_CONCAT( DISTINCT a ) ) FROM t2; +LENGTH( GROUP_CONCAT( DISTINCT a ) ) +10001 +CREATE TABLE t3( a TEXT, b INT ); +INSERT INTO t3 VALUES( REPEAT( 'a', 65534 ), 1 ); +INSERT INTO t3 VALUES( REPEAT( 'a', 65535 ), 2 ); +INSERT IGNORE INTO t3 VALUES( REPEAT( 'a', 65536 ), 3 ); +Warnings: +Warning 1265 Data truncated for column 'a' at row 1 +SELECT LENGTH( GROUP_CONCAT( a ) ) FROM t3 WHERE b = 1; +LENGTH( GROUP_CONCAT( a ) ) +65534 +SELECT LENGTH( GROUP_CONCAT( a ) ) FROM t3 WHERE b = 2; +LENGTH( GROUP_CONCAT( a ) ) +65535 +SELECT LENGTH( GROUP_CONCAT( a ) ) FROM t3 WHERE b = 3; +LENGTH( GROUP_CONCAT( a ) ) +65535 +SET group_concat_max_len= DEFAULT; +DROP TABLE t1, t2, t3; +set names latin1; +create table t1 (id int, name varchar(20)) DEFAULT CHARSET=utf8; +insert into t1 (id, name) values (1, "๓ra"); +insert into t1 (id, name) values (2, "๓ra"); +select b.id, group_concat(b.name) from t1 a, t1 b group by b.id; +id group_concat(b.name) +1 ๓ra,๓ra +2 ๓ra,๓ra +drop table t1; +create table t1(a bit not null); +insert ignore into t1 values (), (), (); +Warnings: +Warning 1364 Field 'a' doesn't have a default value +select group_concat(distinct a) from t1; +group_concat(distinct a) +0 +select group_concat(distinct a order by a) from t1; +group_concat(distinct a order by a) +0 +drop table t1; +create table t1(a bit(2) not null); +insert into t1 values (1), (0), (0), (3), (1); +select group_concat(distinct a) from t1; +group_concat(distinct a) +0,1,3 +select group_concat(distinct a order by a) from t1; +group_concat(distinct a order by a) +0,1,3 +select group_concat(distinct a order by a desc) from t1; +group_concat(distinct a order by a desc) +3,1,0 +drop table t1; +create table t1(a bit(2), b varchar(10), c bit); +insert into t1 values (1, 'a', 0), (0, 'b', 1), (0, 'c', 0), (3, 'd', 1), +(1, 'e', 1), (3, 'f', 1), (0, 'g', 1); +select group_concat(distinct a, c) from t1; +group_concat(distinct a, c) +00,01,10,11,31 +select group_concat(distinct a, c order by a) from t1; +group_concat(distinct a, c order by a) +00,01,11,10,31 +select group_concat(distinct a, c) from t1; +group_concat(distinct a, c) +00,01,10,11,31 +select group_concat(distinct a, c order by a, c) from t1; +group_concat(distinct a, c order by a, c) +00,01,10,11,31 +select group_concat(distinct a, c order by a desc, c desc) from t1; +group_concat(distinct a, c order by a desc, c desc) +31,11,10,01,00 +drop table t1; +create table t1 (f1 char(20)); +insert into t1 values (''),(''); +select group_concat(distinct f1) from t1; +group_concat(distinct f1) + +select group_concat(f1) from t1; +group_concat(f1) +, +drop table t1; +CREATE TABLE t1 (a INT, b INT); +INSERT INTO t1 VALUES (1, 1), (2, 2), (2, 3); +SELECT GROUP_CONCAT(DISTINCT a ORDER BY b) FROM t1; +GROUP_CONCAT(DISTINCT a ORDER BY b) +1,2 +SELECT GROUP_CONCAT(DISTINCT a ORDER BY b DESC) FROM t1; +GROUP_CONCAT(DISTINCT a ORDER BY b DESC) +2,1 +SELECT GROUP_CONCAT(DISTINCT a) FROM t1; +GROUP_CONCAT(DISTINCT a) +1,2 +SELECT GROUP_CONCAT(DISTINCT a + 1 ORDER BY 3 - b) FROM t1; +GROUP_CONCAT(DISTINCT a + 1 ORDER BY 3 - b) +3,2 +SELECT GROUP_CONCAT(DISTINCT a + 1 ORDER BY b) FROM t1; +GROUP_CONCAT(DISTINCT a + 1 ORDER BY b) +2,3 +SELECT GROUP_CONCAT(a ORDER BY 3 - b) FROM t1; +GROUP_CONCAT(a ORDER BY 3 - b) +2,2,1 +CREATE TABLE t2 (a INT, b INT, c INT, d INT); +INSERT INTO t2 VALUES (1,1, 1,1), (1,1, 2,2), (1,2, 2,1), (2,1, 1,2); +SELECT GROUP_CONCAT(DISTINCT a, b ORDER BY c, d) FROM t2; +GROUP_CONCAT(DISTINCT a, b ORDER BY c, d) +11,21,12 +SELECT GROUP_CONCAT(DISTINCT a, b ORDER BY d, c) FROM t2; +GROUP_CONCAT(DISTINCT a, b ORDER BY d, c) +11,12,21 +CREATE TABLE t3 (a INT, b INT, c INT); +INSERT INTO t3 VALUES (1, 1, 1), (2, 1, 2), (3, 2, 1); +SELECT GROUP_CONCAT(DISTINCT a, b ORDER BY b, c) FROM t3; +GROUP_CONCAT(DISTINCT a, b ORDER BY b, c) +11,21,32 +SELECT GROUP_CONCAT(DISTINCT a, b ORDER BY c, b) FROM t3; +GROUP_CONCAT(DISTINCT a, b ORDER BY c, b) +11,32,21 +SELECT GROUP_CONCAT(DISTINCT a, b ORDER BY a, b) FROM t1; +GROUP_CONCAT(DISTINCT a, b ORDER BY a, b) +11,22,23 +SELECT GROUP_CONCAT(DISTINCT b, a ORDER BY a, b) FROM t1; +GROUP_CONCAT(DISTINCT b, a ORDER BY a, b) +11,22,32 +SELECT GROUP_CONCAT(DISTINCT a, b ORDER BY b, a) FROM t1; +GROUP_CONCAT(DISTINCT a, b ORDER BY b, a) +11,22,23 +SELECT GROUP_CONCAT(DISTINCT b, a ORDER BY a, b) FROM t1; +GROUP_CONCAT(DISTINCT b, a ORDER BY a, b) +11,22,32 +SELECT GROUP_CONCAT(DISTINCT a ORDER BY a, b) FROM t1; +GROUP_CONCAT(DISTINCT a ORDER BY a, b) +1,2 +SELECT GROUP_CONCAT(DISTINCT b ORDER BY b, a) FROM t1; +GROUP_CONCAT(DISTINCT b ORDER BY b, a) +1,2,3 +SELECT GROUP_CONCAT(DISTINCT a, b ORDER BY a) FROM t1; +GROUP_CONCAT(DISTINCT a, b ORDER BY a) +11,23,22 +SELECT GROUP_CONCAT(DISTINCT b, a ORDER BY b) FROM t1; +GROUP_CONCAT(DISTINCT b, a ORDER BY b) +11,22,32 +DROP TABLE t1, t2, t3; +CREATE TABLE t1(a INT); +INSERT INTO t1 VALUES (),(); +SELECT s1.d1 FROM +( +SELECT +t1.a as d1, +GROUP_CONCAT(DISTINCT t1.a) AS d2 +FROM +t1 AS t1, +t1 AS t2 +GROUP BY 1 +) AS s1; +d1 +NULL +DROP TABLE t1; +CREATE TABLE t1 (a INT); +CREATE TABLE t2 (a INT); +INSERT INTO t1 VALUES(1); +SELECT GROUP_CONCAT(DISTINCT t2.a) FROM t1 LEFT JOIN t2 ON t2.a = t1.a GROUP BY t1.a; +GROUP_CONCAT(DISTINCT t2.a) +NULL +DROP TABLE t1, t2; +CREATE TABLE t1 (a INT, KEY(a)); +CREATE TABLE t2 (b INT); +INSERT INTO t1 VALUES (NULL), (8), (2); +INSERT INTO t2 VALUES (4), (10); +SELECT 1 FROM t1 WHERE t1.a NOT IN +( +SELECT GROUP_CONCAT(DISTINCT t1.a) +FROM t1 WHERE t1.a IN +( +SELECT b FROM t2 +) +AND NOT t1.a >= (SELECT t1.a FROM t1 LIMIT 1) +GROUP BY t1.a +); +1 +1 +1 +1 +DROP TABLE t1, t2; +CREATE TABLE t1 (f1 INT); +INSERT INTO t1 VALUES (),(); +EXPLAIN EXTENDED SELECT 1 FROM +(SELECT DISTINCT GROUP_CONCAT(td.f1) FROM t1,t1 AS td GROUP BY td.f1) AS d,t1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 4 100.00 Using join buffer (flat, BNL join) +2 DERIVED t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort +2 DERIVED td ALL NULL NULL NULL NULL 2 100.00 Using join buffer (flat, BNL join) +Warnings: +Note 1003 /* select#1 */ select 1 AS `1` from (/* select#2 */ select distinct group_concat(`test`.`td`.`f1` separator ',') AS `GROUP_CONCAT(td.f1)` from `test`.`t1` join `test`.`t1` `td` group by `test`.`td`.`f1`) `d` join `test`.`t1` +SELECT 1 FROM +(SELECT DISTINCT GROUP_CONCAT(td.f1) FROM t1,t1 AS td GROUP BY td.f1) AS d,t1; +1 +1 +1 +DROP TABLE t1; +End of 5.0 tests +# +# Bug #52397: another crash with explain extended and group_concat +# +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (0), (0); +EXPLAIN EXTENDED SELECT 1 FROM +(SELECT GROUP_CONCAT(t1.a ORDER BY t1.a ASC) FROM +t1 t2, t1 GROUP BY t1.a) AS d; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 4 100.00 +2 DERIVED t2 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort +2 DERIVED t1 ALL NULL NULL NULL NULL 2 100.00 Using join buffer (flat, BNL join) +Warnings: +Note 1003 /* select#1 */ select 1 AS `1` from (/* select#2 */ select group_concat(`test`.`t1`.`a` order by `test`.`t1`.`a` ASC separator ',') AS `GROUP_CONCAT(t1.a ORDER BY t1.a ASC)` from `test`.`t1` `t2` join `test`.`t1` group by `test`.`t1`.`a`) `d` +DROP TABLE t1; +End of 5.0 tests +# +# Bug #54476: crash when group_concat and 'with rollup' in prepared statements +# +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1), (2); +PREPARE stmt FROM "SELECT GROUP_CONCAT(t1.a ORDER BY t1.a) FROM t1 JOIN t1 t2 GROUP BY t1.a WITH ROLLUP"; +EXECUTE stmt; +GROUP_CONCAT(t1.a ORDER BY t1.a) +1,1 +2,2 +1,1,2,2 +EXECUTE stmt; +GROUP_CONCAT(t1.a ORDER BY t1.a) +1,1 +2,2 +1,1,2,2 +DEALLOCATE PREPARE stmt; +DROP TABLE t1; +# +# Bug#57194 group_concat cause crash and/or invalid memory reads with type errors +# +CREATE TABLE t1(f1 int); +INSERT INTO t1 values (0),(0); +SELECT POLYGON((SELECT 1 FROM (SELECT 1 IN (GROUP_CONCAT(t1.f1)) FROM t1, t1 t GROUP BY t.f1 ) d)); +ERROR HY000: Illegal parameter data type int for operation 'polygon' +DROP TABLE t1; +# +# Bug#58396 group_concat and explain extended are still crashy +# +CREATE TABLE t1(a INT); +EXPLAIN EXTENDED SELECT UPDATEXML('1', a, '1') +FROM t1 ORDER BY (SELECT GROUP_CONCAT(1) FROM t1); +ERROR HY000: Only constant XPATH queries are supported +SHOW WARNINGS; +Level Code Message +Error 1105 Only constant XPATH queries are supported +DROP TABLE t1; +End of 5.1 tests +DROP TABLE IF EXISTS t1, t2; +CREATE TABLE t1 (a VARCHAR(6), b INT); +CREATE TABLE t2 (a VARCHAR(6), b INT); +INSERT INTO t1 VALUES ('111111', 1); +INSERT INTO t1 VALUES ('222222', 2); +INSERT INTO t1 VALUES ('333333', 3); +INSERT INTO t1 VALUES ('444444', 4); +INSERT INTO t1 VALUES ('555555', 5); +SET group_concat_max_len = 5; +SET @old_sql_mode = @@sql_mode, @@sql_mode = 'traditional'; +SELECT GROUP_CONCAT(a), b FROM t1 GROUP BY b LIMIT 3; +GROUP_CONCAT(a) b +11111 1 +22222 2 +33333 3 +Warnings: +Warning 1260 Row 1 was cut by GROUP_CONCAT() +Warning 1260 Row 2 was cut by GROUP_CONCAT() +Warning 1260 Row 3 was cut by GROUP_CONCAT() +INSERT INTO t2 SELECT GROUP_CONCAT(a), b FROM t1 GROUP BY b; +ERROR HY000: Row 1 was cut by GROUP_CONCAT() +UPDATE t1 SET a = '11111' WHERE b = 1; +UPDATE t1 SET a = '22222' WHERE b = 2; +INSERT INTO t2 SELECT GROUP_CONCAT(a), b FROM t1 GROUP BY b; +ERROR HY000: Row 3 was cut by GROUP_CONCAT() +SET group_concat_max_len = DEFAULT; +SET @@sql_mode = @old_sql_mode; +DROP TABLE t1, t2; +create table t1 (a char(1) character set utf8); +insert into t1 values ('a'),('b'); +select 1 from t1 where a in (select group_concat(a) from t1); +1 +drop table t1; +CREATE TABLE t1 (f1 VARCHAR(10)) ENGINE=MyISAM; +INSERT INTO t1 VALUES ('a'),('b'); +CREATE TABLE t2 (f2 VARCHAR(10)) ENGINE=MyISAM; +INSERT INTO t2 VALUES ('c'); +CREATE TABLE t3 (f3 VARCHAR(10)) ENGINE=MyISAM; +INSERT INTO t3 VALUES ('d'),('e'); +SELECT GROUP_CONCAT( f2 ORDER BY ( f2 IN ( SELECT f1 FROM t1 WHERE f1 <= f2 ) ) ) AS field +FROM ( SELECT * FROM t2 ) AS sq2, t3 +ORDER BY field; +field +c,c +drop table t3, t2, t1; +# +# MDEV-7821 - Server crashes in Item_func_group_concat::fix_fields on 2nd +# execution of PS +# +CREATE TABLE t1(a INT); +INSERT INTO t1 VALUES(1),(2); +PREPARE stmt FROM "SELECT GROUP_CONCAT(t1a.a ORDER BY 1, t1a.a=0) FROM t1 AS t1a, t1 AS t1b GROUP BY t1a.a"; +EXECUTE stmt; +GROUP_CONCAT(t1a.a ORDER BY 1, t1a.a=0) +1,1 +2,2 +EXECUTE stmt; +GROUP_CONCAT(t1a.a ORDER BY 1, t1a.a=0) +1,1 +2,2 +DROP TABLE t1; +# +# WL#6098 Eliminate GROUP_CONCAT intermediate result limitation. +# Bug#13387020 GROUP_CONCAT WITH ORDER BY RESULTS ARE TRUNCATED. +# +SET group_concat_max_len= 9999999; +CREATE TABLE t1 (f1 LONGTEXT , f2 INTEGER); +INSERT INTO t1 VALUES (REPEAT('a', 500000), 0), (REPEAT('b', 500000), 1), (REPEAT('c', 500000), 2); +SELECT LENGTH(GROUP_CONCAT(f1 ORDER BY f2)) FROM t1; +LENGTH(GROUP_CONCAT(f1 ORDER BY f2)) +1500002 +SELECT LENGTH(GROUP_CONCAT(DISTINCT f1 ORDER BY f1 DESC)) FROM t1; +LENGTH(GROUP_CONCAT(DISTINCT f1 ORDER BY f1 DESC)) +1500002 +SELECT SUBSTRING(GROUP_CONCAT(DISTINCT f1 ORDER BY f1 DESC), 1, 5) FROM t1; +SUBSTRING(GROUP_CONCAT(DISTINCT f1 ORDER BY f1 DESC), 1, 5) +ccccc +SELECT LENGTH(GROUP_CONCAT(DISTINCT f1)) FROM t1; +LENGTH(GROUP_CONCAT(DISTINCT f1)) +1500002 +SELECT LENGTH(GROUP_CONCAT(UPPER(f1) ORDER BY f2)) FROM t1; +LENGTH(GROUP_CONCAT(UPPER(f1) ORDER BY f2)) +1500002 +SELECT LENGTH(GROUP_CONCAT(DISTINCT UPPER(f1) ORDER BY f1)) FROM t1; +LENGTH(GROUP_CONCAT(DISTINCT UPPER(f1) ORDER BY f1)) +1500002 +SELECT SUBSTRING(GROUP_CONCAT(DISTINCT UPPER(f1) ORDER BY f1), 1, 5) FROM t1; +SUBSTRING(GROUP_CONCAT(DISTINCT UPPER(f1) ORDER BY f1), 1, 5) +AAAAA +SELECT LENGTH(GROUP_CONCAT(DISTINCT UPPER(f1))) FROM t1; +LENGTH(GROUP_CONCAT(DISTINCT UPPER(f1))) +1500002 +CREATE TABLE t2 SELECT GROUP_CONCAT(f1 order by f2) FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `GROUP_CONCAT(f1 order by f2)` mediumtext DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t2; +CREATE TABLE t2 SELECT GROUP_CONCAT(UPPER(f1) ORDER BY f2) FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `GROUP_CONCAT(UPPER(f1) ORDER BY f2)` mediumtext DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t2; +SET group_concat_max_len= 1024; +SELECT LENGTH(GROUP_CONCAT(f1 ORDER BY f2)) FROM t1; +LENGTH(GROUP_CONCAT(f1 ORDER BY f2)) +1024 +Warnings: +Warning 1260 Row 2 was cut by GROUP_CONCAT() +SET group_concat_max_len= 499999; +SELECT LENGTH(GROUP_CONCAT(f1 ORDER BY f2)) FROM t1 WHERE f2 = 0; +LENGTH(GROUP_CONCAT(f1 ORDER BY f2)) +499999 +Warnings: +Warning 1260 Row 1 was cut by GROUP_CONCAT() +SELECT LENGTH(GROUP_CONCAT(f1 ORDER BY f2)) FROM t1 GROUP BY f2; +LENGTH(GROUP_CONCAT(f1 ORDER BY f2)) +499999 +499999 +499999 +Warnings: +Warning 1260 Row 1 was cut by GROUP_CONCAT() +Warning 1260 Row 2 was cut by GROUP_CONCAT() +Warning 1260 Row 3 was cut by GROUP_CONCAT() +INSERT INTO t1 VALUES (REPEAT('a', 499999), 3), (REPEAT('b', 500000), 4); +SELECT LENGTH(GROUP_CONCAT(f1 ORDER BY f2)) FROM t1 GROUP BY f2; +LENGTH(GROUP_CONCAT(f1 ORDER BY f2)) +499999 +499999 +499999 +499999 +499999 +Warnings: +Warning 1260 Row 1 was cut by GROUP_CONCAT() +Warning 1260 Row 2 was cut by GROUP_CONCAT() +Warning 1260 Row 3 was cut by GROUP_CONCAT() +Warning 1260 Row 5 was cut by GROUP_CONCAT() +DROP TABLE t1; +SET group_concat_max_len= DEFAULT; +set session group_concat_max_len=1024; +set max_session_mem_used=16*1024*1024; +SELECT GROUP_CONCAT(concat(seq/1.1), concat(seq/1.1), concat(seq/1.1), concat(seq/1.1), concat(seq/1.1), concat(seq/1.1), concat(seq/1.1), concat(seq/1.1) ORDER BY 2,1,3,4,6,5,8,7) AS c +FROM seq_1_to_200000; +c +0.90910.90910.90910.90910.90910.90910.90910.9091,1.81821.81821.81821.81821.81821.81821.81821.8182,10.000010.000010.000010.000010.000010.000010.000010.0000,10.909110.909110.909110.909110.909110.909110.909110.9091,100.0000100.0000100.0000100.0000100.0000100.0000100.0000100.0000,100.9091100.9091100.9091100.9091100.9091100.9091100.9091100.9091,1000.00001000.00001000.00001000.00001000.00001000.00001000.00001000.0000,1000.90911000.90911000.90911000.90911000.90911000.90911000.90911000.9091,10000.000010000.000010000.000010000.000010000.000010000.000010000.000010000.0000,10000.909110000.909110000.909110000.909110000.909110000.909110000.909110000.9091,100000.0000100000.0000100000.0000100000.0000100000.0000100000.0000100000.0000100000.0000,100000.9091100000.9091100000.9091100000.9091100000.9091100000.9091100000.9091100000.9091,100001.8182100001.8182100001.8182100001.8182100001.8182100001.8182100001.8182100001.8182,100002.7273100002.7273100002.7273100002.7273100002.7273100002.7273100002.7273100002.7273,100003.6364100003. +Warnings: +Warning 1260 Row 15 was cut by GROUP_CONCAT() +set max_session_mem_used=default; +set session group_concat_max_len=default; +SET group_concat_max_len= 8; +CREATE TABLE t1 (a INT); +INSERT t1 VALUES (1),(2); +CREATE TABLE t2 (b DATE, c INT); +INSERT t2 VALUES ('2019-12-04',1),('2020-03-28',2); +CREATE TABLE t3 (d INT); +INSERT t3 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14); +CREATE TABLE t4 (e INT); +INSERT t4 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15); +SELECT (SELECT MAX(a) FROM t1 WHERE t2_sq.c > 0) AS f, +GROUP_CONCAT(t2_sq.b ORDER BY 1) AS gc +FROM (SELECT t2_a.* FROM t2 AS t2_a, t2 AS t2_b) AS t2_sq, t3, t4 +GROUP BY f; +f gc +2 2019-12- +Warnings: +Warning 1260 Row 1 was cut by GROUP_CONCAT() +DROP TABLE t1, t2, t3, t4; +SET group_concat_max_len= default; +# +# Start of 10.2 tests +# +# +# MDEV-10124 Incorrect usage of CUBE/ROLLUP and ORDER BY with GROUP_CONCAT(a ORDER BY a) +# +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (10),(20),(30); +SELECT a,GROUP_CONCAT(a ORDER BY a) FROM t1 GROUP BY a WITH ROLLUP; +a GROUP_CONCAT(a ORDER BY a) +10 10 +20 20 +30 30 +NULL 10,20,30 +CREATE VIEW v1 AS +SELECT a,GROUP_CONCAT(a ORDER BY a) FROM t1 GROUP BY a WITH ROLLUP; +SELECT * FROM v1; +a GROUP_CONCAT(a ORDER BY a) +10 10 +20 20 +30 30 +NULL 10,20,30 +DROP VIEW v1; +SELECT a,GROUP_CONCAT(a ORDER BY a) FROM t1 GROUP BY a WITH ROLLUP HAVING GROUP_CONCAT(a ORDER BY a)='10,20,30'; +a GROUP_CONCAT(a ORDER BY a) +NULL 10,20,30 +CREATE VIEW v1 AS +SELECT a,GROUP_CONCAT(a ORDER BY a) FROM t1 GROUP BY a WITH ROLLUP HAVING GROUP_CONCAT(a ORDER BY a)='10,20,30'; +SELECT * FROM v1; +a GROUP_CONCAT(a ORDER BY a) +NULL 10,20,30 +DROP VIEW v1; +SELECT * FROM (SELECT a,GROUP_CONCAT(a ORDER BY a) FROM t1 GROUP BY a WITH ROLLUP HAVING GROUP_CONCAT(a ORDER BY a)='10,20,30') t1; +a GROUP_CONCAT(a ORDER BY a) +NULL 10,20,30 +CREATE VIEW v1 AS +SELECT * FROM (SELECT a,GROUP_CONCAT(a ORDER BY a) FROM t1 GROUP BY a WITH ROLLUP HAVING GROUP_CONCAT(a ORDER BY a)='10,20,30') t1; +SELECT * FROM v1; +a GROUP_CONCAT(a ORDER BY a) +NULL 10,20,30 +DROP VIEW v1; +SELECT (SELECT GROUP_CONCAT(a ORDER BY a) FROM t1 GROUP BY a WITH ROLLUP HAVING GROUP_CONCAT(a ORDER BY a)='10,20,30'); +(SELECT GROUP_CONCAT(a ORDER BY a) FROM t1 GROUP BY a WITH ROLLUP HAVING GROUP_CONCAT(a ORDER BY a)='10,20,30') +10,20,30 +CREATE VIEW v1 AS +SELECT (SELECT GROUP_CONCAT(a ORDER BY a) FROM t1 GROUP BY a WITH ROLLUP HAVING GROUP_CONCAT(a ORDER BY a)='10,20,30'); +SELECT * FROM v1; +Name_exp_1 +10,20,30 +DROP VIEW v1; +DROP TABLE t1; +# +# MDEV-4677 GROUP_CONCAT not showing any output with group_concat_max_len >= 4Gb +# +set group_concat_max_len=1024*1024*1024*4; +Warnings: +Warning 1292 Truncated incorrect group_concat_max_len value: '4294967296' +create table t1 (i int, j int); +insert into t1 values (1,1),(1,2); +select i, group_concat(j) from t1 group by i; +i group_concat(j) +1 1,2 +drop table t1; +# +# End of 10.2 tests +# +# +# Start of 10.3 tests +# +drop table if exists t1, t2; +create table t1 (grp int, a bigint unsigned, c char(10) , d char(10) not null); +insert into t1 values (1,1,NULL,"a"); +insert into t1 values (1,10,"b","a"); +insert into t1 values (1,11,"c","a"); +insert into t1 values (2,2,"c","a"); +insert into t1 values (2,3,"b","b"); +insert into t1 values (3,4,"E","a"); +insert into t1 values (3,5,"C","b"); +insert into t1 values (3,6,"D","c"); +insert into t1 values (3,7,"E","c"); +select grp,group_concat(c) from t1 group by grp; +grp group_concat(c) +1 b,c +2 c,b +3 E,C,D,E +select grp,group_concat(c limit 1 ) from t1 group by grp; +grp group_concat(c limit 1 ) +1 b +2 c +3 E +select grp,group_concat(c limit 1,1 ) from t1 group by grp; +grp group_concat(c limit 1,1 ) +1 c +2 b +3 C +select grp,group_concat(c limit 1,10 ) from t1 group by grp; +grp group_concat(c limit 1,10 ) +1 c +2 b +3 C,D,E +select grp,group_concat(c limit 1000) from t1 group by grp; +grp group_concat(c limit 1000) +1 b,c +2 c,b +3 E,C,D,E +select group_concat(grp limit 0) from t1; +group_concat(grp limit 0) + +select group_concat(grp limit "sdjadjs") from t1 +--error ER_PARSE_ERROR +select grp,group_concat(c limit 5.5) from t1 group by grp ; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '"sdjadjs") from t1 +--error ER_PARSE_ERROR +select grp,group_concat(c limit 5.5...' at line 1 +select grp,group_concat(distinct c limit 1,10 ) from t1 group by grp; +grp group_concat(distinct c limit 1,10 ) +1 c +2 c +3 D,E +select grp,group_concat(c order by a) from t1 group by grp; +grp group_concat(c order by a) +1 b,c +2 c,b +3 E,C,D,E +select grp,group_concat(c order by a limit 2 ) from t1 group by grp; +grp group_concat(c order by a limit 2 ) +1 b,c +2 c,b +3 E,C +select grp,group_concat(c order by a limit 1,1 ) from t1 group by grp; +grp group_concat(c order by a limit 1,1 ) +1 c +2 b +3 C +select grp,group_concat(c order by c) from t1 group by grp; +grp group_concat(c order by c) +1 b,c +2 b,c +3 C,D,E,E +select grp,group_concat(c order by c limit 2) from t1 group by grp; +grp group_concat(c order by c limit 2) +1 b,c +2 b,c +3 C,D +select grp,group_concat(c order by c desc) from t1 group by grp; +grp group_concat(c order by c desc) +1 c,b +2 c,b +3 E,E,D,C +select grp,group_concat(c order by c desc limit 2) from t1 group by grp; +grp group_concat(c order by c desc limit 2) +1 c,b +2 c,b +3 E,E +# +# Empty results for group concat as offset is greater than the rows +# for a group +# +select grp,group_concat(distinct c limit 10,1 ) from t1 group by grp; +grp group_concat(distinct c limit 10,1 ) +1 +2 +3 +drop table t1; +create table t2 (a int, b varchar(10)); +insert into t2 values(1,'a'),(1,'b'),(NULL,'c'),(2,'x'),(2,'y'); +select group_concat(a,b limit 2) from t2; +group_concat(a,b limit 2) +1a,1b +set @x=4; +prepare STMT from 'select group_concat(b limit ?) from t2'; +execute STMT using @x; +group_concat(b limit ?) +a,b,c,x +set @x=2; +execute STMT using @x; +group_concat(b limit ?) +a,b +set @x=1000; +execute STMT using @x; +group_concat(b limit ?) +a,b,c,x,y +set @x=0; +execute STMT using @x; +group_concat(b limit ?) + +set @x="adasfa"; +execute STMT using @x; +ERROR HY000: Limit only accepts integer values +set @x=-1; +execute STMT using @x; +ERROR HY000: Incorrect arguments to EXECUTE +set @x=4; +prepare STMT from 'select group_concat(a,b limit ?) from t2'; +execute STMT using @x; +group_concat(a,b limit ?) +1a,1b,2x,2y +drop table t2; +# +# MDEV-18943: Group Concat with limit not working with views +# +create table t1 (a int, b varchar(10)); +insert into t1 values(1,'a'),(1,'b'),(NULL,'c'),(2,'x'),(2,'y'); +select group_concat(a,b limit 2) from t1; +group_concat(a,b limit 2) +1a,1b +create view v1 as select group_concat(a,b limit 2) from t1; +select * from v1; +group_concat(a,b limit 2) +1a,1b +drop view v1; +drop table t1; +# +# End of 10.3 tests +# |