func_gconcat.test 3.7 KB
Newer Older
1 2 3
#
# simple test of group_concat function
#
4 5 6
--disable_warnings
drop table if exists t1, t2;
--enable_warnings
7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52

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");

# Test of MySQL simple request
select grp,group_concat(c) from t1 group by grp;
select grp,group_concat(a,c) from t1 group by grp;
select grp,group_concat("(",a,":",c,")") from t1 group by grp;

# Test of MySQL with options
select grp,group_concat(c separator ",") from t1 group by grp;
select grp,group_concat(c separator "---->") from t1 group by grp;
select grp,group_concat(c order by c) from t1 group by grp;
select grp,group_concat(c order by c desc) from t1 group by grp;
select grp,group_concat(d order by a) from t1 group by grp;
select grp,group_concat(d order by a desc) from t1 group by grp;
select grp,group_concat(a order by a,d+c) from t1 group by grp;
select grp,group_concat(c order by 1) from t1 group by grp;
select grp,group_concat(c order by "c") from t1 group by grp;
select grp,group_concat(distinct c order by c) from t1 group by grp;
select grp,group_concat(distinct c order by c desc) from t1 group by grp;
select grp,group_concat(c order by c separator ",") from t1 group by grp;
select grp,group_concat(c order by c desc separator ",") from t1 group by grp;
select grp,group_concat(distinct c order by c separator ",") from t1 group by grp;
select grp,group_concat(distinct c order by c desc separator ",") from t1 group by grp;

# Test of SQL_LIST objects
select grp,group_concat(c order by grp desc) from t1 group by grp order by grp;


# Test transfer to real values

select grp, group_concat(a separator "")+0 from t1 group by grp;
select grp, group_concat(a separator "")+0.0 from t1 group by grp;
select grp, ROUND(group_concat(a separator "")) from t1 group by grp;

# Test NULL values

unknown's avatar
SCRUM  
unknown committed
53
drop table t1;
54 55 56 57 58 59 60 61 62 63
create table t1 (grp int, c char(10));
insert into t1 values (1,NULL);
insert into t1 values (2,"b");
insert into t1 values (2,NULL);
insert into t1 values (3,"E");
insert into t1 values (3,NULL);
insert into t1 values (3,"D");
insert into t1 values (3,NULL);
insert into t1 values (3,NULL);
insert into t1 values (3,"D");
unknown's avatar
SCRUM  
unknown committed
64 65
insert into t1 values (4,"");
insert into t1 values (5,NULL);
66 67 68 69 70 71 72
select grp,group_concat(c order by c) from t1 group by grp;

# Test warnings

set group_concat_max_len = 5;
select grp,group_concat(c) from t1 group by grp;
show warnings;
unknown's avatar
unknown committed
73 74
set group_concat_max_len = 1024;

75 76 77 78 79 80 81
# Test errors

--error 1111
select group_concat(sum(a)) from t1 group by grp;
--error 1054
select grp,group_concat(c order by 2) from t1 group by grp;

unknown's avatar
unknown committed
82
drop table t1;
83

84 85 86 87 88 89 90 91 92 93 94 95 96
# Test variable length

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);
# Make this order independent
--replace_result www.help.com X www.host.com X www.google.com X
select REQ_ID, Group_Concat(URL) as URL from t1, t2 where 
t2.URL_ID = t1.URL_ID group by REQ_ID;
drop table t1;
drop table t2;

97 98 99 100 101
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;
select distinct ifnull(group_concat(concat(t1.id, ':', t1.name)), 'shortname') as 'with distinct: cutoff at length of shortname' from t1;
drop table t1;