diff options
Diffstat (limited to 'mysql-test/main/func_gconcat.test')
| -rw-r--r-- | mysql-test/main/func_gconcat.test | 1068 |
1 files changed, 1068 insertions, 0 deletions
diff --git a/mysql-test/main/func_gconcat.test b/mysql-test/main/func_gconcat.test new file mode 100644 index 00000000..cc5236a1 --- /dev/null +++ b/mysql-test/main/func_gconcat.test @@ -0,0 +1,1068 @@ +# +# simple test of group_concat function +# + +source include/have_sequence.inc; + +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; +explain extended 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; +--disable_warnings +select grp,group_concat(a order by a,d+c-ascii(c)-a) from t1 group by grp; +select grp,group_concat(a order by d+c-ascii(c),a) from t1 group by grp; +--enable_warnings +select grp,group_concat(c order by 1) 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; +explain extended 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; +explain extended 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; +drop table t1; + +# Test NULL values + +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; + +# Test warnings + +--disable_ps2_protocol +set group_concat_max_len = 4; +select grp,group_concat(c) from t1 group by grp; +show warnings; +set group_concat_max_len = 1024; + +# Test errors + +--error 1111 +select group_concat(sum(c)) from t1 group by grp; +--error 1054 +select grp,group_concat(c order by 2) from t1 group by grp; +--enable_ps2_protocol + +drop table t1; + +# 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; +# check min/max function +--replace_result www.help.com X www.host.com X www.google.com 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; + +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; +select distinct ifnull(group_concat(concat(t1.id, ':', t1.name)), 'shortname') as 'with distinct: cutoff at length of shortname' from t1; +drop table t1; + +# check zero rows (bug#836) +create table t1(id int); +create table t2(id int); +insert into t1 values(0),(1); +select group_concat(t1.id) FROM t1,t2; +drop table t1; +drop table t2; + +# check having +create table t1 (bar varchar(32)); +insert into t1 values('test1'),('test2'); +select group_concat(bar order by concat(bar,bar)) from t1; +select group_concat(bar order by concat(bar,bar) desc) from t1; +select bar from t1 having group_concat(bar)=''; +select bar from t1 having instr(group_concat(bar), "test") > 0; +select bar from t1 having instr(group_concat(bar order by concat(bar,bar) desc), "test2,test1") > 0; +drop table t1; + +# ORDER BY fix_fields() +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; +select group_concat(a1 order by (t1.a)) from t1; +drop table t1, t2; + +# +# Problem with GROUP BY (Bug #2695) +# + +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; +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; +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; +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; + +# The following failed when it was run twice: +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; +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; + +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; +drop table t1,t2; + +# +# Problem with distinct (Bug #3381) +# + +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; +select group_concat(distinct s1) from t1; +select group_concat(distinct s1 order by s2) from t1 where s2 < 4; +# The following is wrong and needs to be fixed ASAP +select group_concat(distinct s1 order by s2) from t1; +drop table t1; + +# +# Test with subqueries (Bug #3319) +# + +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; +select group_concat(c order by (select c from t2 where t2.a=t1.a limit 1)) as grp from t1; +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; +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; +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; +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; +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; + +# The following returns random results as we are sorting on blob addresses +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; +select group_concat(c order by (select concat(t1.c,group_concat(c)) from t2 where a=t1.a)) as grp from t1; + +select a,c,(select group_concat(c order by a) from t2 where a=t1.a) as grp from t1 order by grp; +drop table t1,t2; + +# +# group_concat of expression with GROUP BY and external GROUP BY +# +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; +DROP TABLE t1, t2; + +# +# Bug #4035: group_concat() and HAVING +# + +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%'; +SELECT GROUP_CONCAT(a SEPARATOR '###') AS names FROM t1 + HAVING LEFT(names, 1) ='J'; +DROP TABLE t1; + +# +# check blobs +# + +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; +DROP TABLE t1; + +# +# check null values #2 +# + +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; +DROP TABLE t1; +DROP TABLE t2; + +# +# blobs +# + +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; +select group_concat(distinct b) from t1 group by a; +select group_concat(b order by b) from t1 group by a; +select group_concat(distinct b order by b) from t1 group by a; +set local group_concat_max_len=4; +--disable_ps2_protocol +select group_concat(b) from t1 group by a; +select group_concat(distinct b) from t1 group by a; +select group_concat(b order by b) from t1 group by a; +select group_concat(distinct b order by b) from t1 group by a; +--enable_ps2_protocol + +# +# long blobs +# + +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; +select group_concat(distinct b) from t1 group by a; +select group_concat(b order by b) from t1 group by a; +select group_concat(distinct b order by b) from t1 group by a; +set local group_concat_max_len=400; +--disable_ps2_protocol +select group_concat(b) from t1 group by a; +select group_concat(distinct b) from t1 group by a; +select group_concat(b order by b) from t1 group by a; +select group_concat(distinct b order by b) from t1 group by a; +--enable_ps2_protocol + +drop table t1; + +# +# Bug#10201 +# +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; +select collation(group_concat(a)) from t1; +create table t2 select group_concat(a) as a from t1; +show create table t2; +select collation(group_concat(a,_koi8r'test')) from t1; +--error 1267 +select collation(group_concat(a,_koi8r 0xC1C2)) from t1; +--error 1267 +select collation(group_concat(a,b)) from t1; +drop table t1; +drop table t2; + +# +# Bug #12829 +# Cannot convert the charset of a GROUP_CONCAT result +# +CREATE TABLE t1 (a CHAR(10) CHARACTER SET cp850); +INSERT INTO t1 VALUES ('Ŕ'); +SELECT a FROM t1; +SELECT GROUP_CONCAT(a) FROM t1; +DROP TABLE t1; + +# +# bug #7769: group_concat returning null is checked in having +# +CREATE TABLE t1 (id int); +SELECT GROUP_CONCAT(id) AS gc FROM t1 HAVING gc IS NULL; +DROP TABLE t1; + +# +# Bug #8656: Crash with group_concat on alias in outer table +# +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; +drop table t2; + +# +# Bug #7405: problems with rollup +# + +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; +explain select a, group_concat(b) from t1 group by a with rollup; +select a, group_concat(b) from t1 group by a with rollup; +select a, group_concat(distinct b) from t1 group by a with rollup; +select a, group_concat(b order by b) from t1 group by a with rollup; +select a, group_concat(distinct b order by b) from t1 group by a with rollup; +drop table t1; + +# +# Bug #6475 +# +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; +drop table t1; +# +# Bug #12095: GROUP_CONCAT for one row table +# + +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; +INSERT INTO t2 SET somename = 'test2'; +SELECT COUNT(*), GROUP_CONCAT(DISTINCT t2.somename SEPARATOR ' |') + FROM t1 JOIN t2 ON t1.bID = t2.bID; +DELETE FROM t2 WHERE somename = 'test2'; +SELECT COUNT(*), GROUP_CONCAT(DISTINCT t2.somename SEPARATOR ' |') + FROM t1 JOIN t2 ON t1.bID = t2.bID; + +DROP TABLE t1,t2; + +# +# Bug #12861 hang with group_concat insubquery FROM DUAL +# +select * from (select group_concat('c') from DUAL) t; + +# +# Bug #12859 group_concat in subquery cause incorrect not null +# +create table t1 ( a int not null default 0); +select * from (select group_concat(a) from t1) t2; +select group_concat('x') UNION ALL select 1; +drop table t1; + +# +# Bug #12863 : missing separators after first empty concatenated elements +# + +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; +SELECT GROUP_CONCAT(a ORDER BY a) FROM t1; + +SELECT GROUP_CONCAT(a) FROM t1 GROUP BY id; +SELECT GROUP_CONCAT(a ORDER BY a) FROM t1 GROUP BY id; + +DROP TABLE t1; + +# +# Bug #15560: GROUP_CONCAT wasn't ready for WITH ROLLUP queries +# +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; +select count(distinct (f1+1)) from t1 group by f1 with rollup; +drop table t1; + +# +# Bug#14169 type of group_concat() result changed to blob if tmp_table was used +# +create table t1 (f1 int unsigned, f2 varchar(255)); +insert into t1 values (1,repeat('a',255)),(2,repeat('b',255)); +--enable_metadata +--disable_view_protocol +select f2,group_concat(f1) from t1 group by f2; +--enable_view_protocol +--disable_metadata +drop table t1; + +# End of 4.1 tests + +# +# Bug#8568 "GROUP_CONCAT returns string, unless in a UNION in which case +# returns BLOB": add a test case, the bug can not be repeated any more. +# + +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; +select charset(a) from t3; +drop table t1, t2, t3; +set names default; + +# +# Bug#18281 group_concat changes charset to binary +# +create table t1 (c1 varchar(10), c2 int); +select charset(group_concat(c1 order by c2)) from t1; +drop table t1; + +# +# Bug #16712: group_concat returns odd string instead of intended result +# +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; +SELECT CHAR_LENGTH( GROUP_CONCAT(b) ) FROM t1; +SET GROUP_CONCAT_MAX_LEN = 1024; +DROP TABLE t1; + +# +# Bug #22015: crash with GROUP_CONCAT over a derived table that +# returns the results of aggregation by GROUP_CONCAT +# + +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; + +DROP TABLE t1; +# +# Bug#23451 GROUP_CONCAT truncates a multibyte utf8 character +# +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); +let $1= 10; +while ($1) +{ + eval set group_concat_max_len= 1022 + $1; + --disable_result_log + select @x:=group_concat(x) from t1 group by y; + --enable_result_log + select @@group_concat_max_len, length(@x), char_length(@x), right(@x,12), right(HEX(@x),12); + dec $1; +} +drop table t1; +set group_concat_max_len=1024; +set names latin1; + +# +# Bug#14169 type of group_concat() result changed to blob if tmp_table was used +# +create table t1 (f1 int unsigned, f2 varchar(255)); +insert into t1 values (1,repeat('a',255)),(2,repeat('b',255)); +--enable_metadata +--disable_view_protocol +select f2,group_concat(f1) from t1 group by f2; +--enable_view_protocol +--disable_metadata +drop table t1; + +# +# Bug #26815: Unexpected built-in function behavior: group_concat(distinct +# substring_index()) +# +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; +SELECT GROUP_CONCAT(DISTINCT UCASE(b)) FROM t1; +DROP TABLE t1; + +# +# Bug #28273: GROUP_CONCAT and ORDER BY: No warning when result gets truncated. +# +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; +--disable_ps2_protocol +SELECT GROUP_CONCAT( a ) FROM t1; +SELECT GROUP_CONCAT( DISTINCT a ) FROM t1; +SELECT GROUP_CONCAT( a ORDER BY b ) FROM t1; +SELECT GROUP_CONCAT( DISTINCT a ORDER BY b ) FROM t1; +--enable_ps2_protocol +SET group_concat_max_len = DEFAULT; +DROP TABLE t1; +# Bug #23856:GROUP_CONCAT and ORDER BY: junk from previous rows for query on I_S +# +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; +SELECT GROUP_CONCAT(DISTINCT a ORDER BY b) FROM t1; +SELECT GROUP_CONCAT(DISTINCT a) FROM t1; +SET group_concat_max_len= 10; +SELECT GROUP_CONCAT(a ORDER BY b) FROM t1; +SELECT GROUP_CONCAT(DISTINCT a ORDER BY b) FROM t1; +SELECT GROUP_CONCAT(DISTINCT a) FROM t1; + +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; + +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 ); +SELECT LENGTH( GROUP_CONCAT( a ) ) FROM t3 WHERE b = 1; +SELECT LENGTH( GROUP_CONCAT( a ) ) FROM t3 WHERE b = 2; +SELECT LENGTH( GROUP_CONCAT( a ) ) FROM t3 WHERE b = 3; + +SET group_concat_max_len= DEFAULT; +DROP TABLE t1, t2, t3; + +# +# Bug#29850: Wrong charset of the GROUP_CONCAT result when the select employs +# a temporary table. +# +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; +drop table t1; + +# +# Bug #31154: group_concat() and bit fields; +# +create table t1(a bit not null); +insert ignore into t1 values (), (), (); +select group_concat(distinct a) from t1; +select group_concat(distinct a order by a) from t1; +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; +select group_concat(distinct a order by a) from t1; +select group_concat(distinct a order by a desc) from t1; +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; +select group_concat(distinct a, c order by a) from t1; +select group_concat(distinct a, c) from t1; +select group_concat(distinct a, c order by a, c) from t1; +select group_concat(distinct a, c order by a desc, c desc) from t1; + +drop table t1; + + +# +# Bug#30897 GROUP_CONCAT returns extra comma on empty fields +# +create table t1 (f1 char(20)); +insert into t1 values (''),(''); +select group_concat(distinct f1) from t1; +select group_concat(f1) from t1; +drop table t1; +# Bug#32798: DISTINCT in GROUP_CONCAT clause fails when ordering by a column +# with null values +#' +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; +SELECT GROUP_CONCAT(DISTINCT a ORDER BY b DESC) FROM t1; +SELECT GROUP_CONCAT(DISTINCT a) FROM t1; + +SELECT GROUP_CONCAT(DISTINCT a + 1 ORDER BY 3 - b) FROM t1; +SELECT GROUP_CONCAT(DISTINCT a + 1 ORDER BY b) FROM t1; +SELECT GROUP_CONCAT(a ORDER BY 3 - b) FROM t1; + +CREATE TABLE t2 (a INT, b INT, c INT, d INT); + +# There is one duplicate in the expression list: 1,10 +# There is one duplicate in ORDER BY list, but that shouldnt matter: 1,10 +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; +SELECT GROUP_CONCAT(DISTINCT a, b ORDER BY d, c) FROM t2; + +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; +SELECT GROUP_CONCAT(DISTINCT a, b ORDER BY c, b) FROM t3; + +SELECT GROUP_CONCAT(DISTINCT a, b ORDER BY a, b) FROM t1; +SELECT GROUP_CONCAT(DISTINCT b, a ORDER BY a, b) FROM t1; +SELECT GROUP_CONCAT(DISTINCT a, b ORDER BY b, a) FROM t1; +SELECT GROUP_CONCAT(DISTINCT b, a ORDER BY a, b) FROM t1; +SELECT GROUP_CONCAT(DISTINCT a ORDER BY a, b) FROM t1; +SELECT GROUP_CONCAT(DISTINCT b ORDER BY b, a) FROM t1; +SELECT GROUP_CONCAT(DISTINCT a, b ORDER BY a) FROM t1; +SELECT GROUP_CONCAT(DISTINCT b, a ORDER BY b) FROM t1; + +DROP TABLE t1, t2, t3; + +# +# Bug #34747: crash in debug assertion check after derived table +# +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; +DROP TABLE t1; + +# +# Bug #35298: GROUP_CONCAT with DISTINCT can crash the server +# + +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; + +DROP TABLE t1, t2; + +# +# Bug #36024: group_concat distinct in subquery crash +# + +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 +); + +DROP TABLE t1, t2; + +# +# Bug #49487: crash with explain extended and group_concat in a derived table +# + +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; + +SELECT 1 FROM + (SELECT DISTINCT GROUP_CONCAT(td.f1) FROM t1,t1 AS td GROUP BY td.f1) AS d,t1; + +DROP TABLE t1; + +--echo End of 5.0 tests + +--echo # +--echo # Bug #52397: another crash with explain extended and group_concat +--echo # +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; +DROP TABLE t1; + + +--echo End of 5.0 tests + + +--echo # +--echo # Bug #54476: crash when group_concat and 'with rollup' in prepared statements +--echo # + +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; +EXECUTE stmt; + +DEALLOCATE PREPARE stmt; +DROP TABLE t1; + +--echo # +--echo # Bug#57194 group_concat cause crash and/or invalid memory reads with type errors +--echo # + +CREATE TABLE t1(f1 int); +INSERT INTO t1 values (0),(0); +--disable_ps_protocol +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT POLYGON((SELECT 1 FROM (SELECT 1 IN (GROUP_CONCAT(t1.f1)) FROM t1, t1 t GROUP BY t.f1 ) d)); +--enable_ps_protocol +DROP TABLE t1; + +--echo # +--echo # Bug#58396 group_concat and explain extended are still crashy +--echo # + +CREATE TABLE t1(a INT); +--error ER_UNKNOWN_ERROR +EXPLAIN EXTENDED SELECT UPDATEXML('1', a, '1') +FROM t1 ORDER BY (SELECT GROUP_CONCAT(1) FROM t1); +SHOW WARNINGS; +DROP TABLE t1; + +--echo End of 5.1 tests + + +# +# Bug#36785: Wrong error message when group_concat() exceeds max length +# + +--disable_warnings +DROP TABLE IF EXISTS t1, t2; +--enable_warnings + +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'; + +--disable_ps2_protocol +SELECT GROUP_CONCAT(a), b FROM t1 GROUP BY b LIMIT 3; +--enable_ps2_protocol +--error ER_CUT_VALUE_GROUP_CONCAT +INSERT INTO t2 SELECT GROUP_CONCAT(a), b FROM t1 GROUP BY b; +UPDATE t1 SET a = '11111' WHERE b = 1; +UPDATE t1 SET a = '22222' WHERE b = 2; +--error ER_CUT_VALUE_GROUP_CONCAT +INSERT INTO t2 SELECT GROUP_CONCAT(a), b FROM t1 GROUP BY b; + +SET group_concat_max_len = DEFAULT; +SET @@sql_mode = @old_sql_mode; +DROP TABLE t1, t2; + +# +# MDEV-3987 uninitialized read in Item_cond::fix_fields leads to crash: select .. where .. in ( select ... ) +# +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); +drop table t1; + +# +# MDEV-7820 Server crashes in in my_strcasecmp_utf8 on subquery in ORDER BY clause of GROUP_CONCAT +# +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; + +drop table t3, t2, t1; + +--echo # +--echo # MDEV-7821 - Server crashes in Item_func_group_concat::fix_fields on 2nd +--echo # execution of PS +--echo # +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; +EXECUTE stmt; +DROP TABLE t1; + +--echo # +--echo # WL#6098 Eliminate GROUP_CONCAT intermediate result limitation. +--echo # Bug#13387020 GROUP_CONCAT WITH ORDER BY RESULTS ARE TRUNCATED. +--echo # + + +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; +SELECT LENGTH(GROUP_CONCAT(DISTINCT f1 ORDER BY f1 DESC)) FROM t1; +SELECT SUBSTRING(GROUP_CONCAT(DISTINCT f1 ORDER BY f1 DESC), 1, 5) FROM t1; +SELECT LENGTH(GROUP_CONCAT(DISTINCT f1)) FROM t1; + +SELECT LENGTH(GROUP_CONCAT(UPPER(f1) ORDER BY f2)) FROM t1; +SELECT LENGTH(GROUP_CONCAT(DISTINCT UPPER(f1) ORDER BY f1)) FROM t1; +SELECT SUBSTRING(GROUP_CONCAT(DISTINCT UPPER(f1) ORDER BY f1), 1, 5) FROM t1; +SELECT LENGTH(GROUP_CONCAT(DISTINCT UPPER(f1))) FROM t1; + +CREATE TABLE t2 SELECT GROUP_CONCAT(f1 order by f2) FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; + +CREATE TABLE t2 SELECT GROUP_CONCAT(UPPER(f1) ORDER BY f2) FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; + +--disable_ps2_protocol +SET group_concat_max_len= 1024; +SELECT LENGTH(GROUP_CONCAT(f1 ORDER BY f2)) FROM t1; + +SET group_concat_max_len= 499999; +SELECT LENGTH(GROUP_CONCAT(f1 ORDER BY f2)) FROM t1 WHERE f2 = 0; +SELECT LENGTH(GROUP_CONCAT(f1 ORDER BY f2)) FROM t1 GROUP BY f2; + +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; +--enable_ps2_protocol + +DROP TABLE t1; +SET group_concat_max_len= DEFAULT; + +# +# MDEV-9531 GROUP_CONCAT with ORDER BY inside takes a lot of memory while it's executed +# +set session group_concat_max_len=1024; +set max_session_mem_used=16*1024*1024; # 8M..32M +--disable_ps2_protocol +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; +--enable_ps2_protocol +set max_session_mem_used=default; +set session group_concat_max_len=default; + +# +# MDEV-19350 Server crashes in delete_tree_element / ... / Item_func_group_concat::repack_tree +# +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; +DROP TABLE t1, t2, t3, t4; +SET group_concat_max_len= default; + + +--echo # +--echo # Start of 10.2 tests +--echo # + +--echo # +--echo # MDEV-10124 Incorrect usage of CUBE/ROLLUP and ORDER BY with GROUP_CONCAT(a ORDER BY a) +--echo # + +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; +CREATE VIEW v1 AS +SELECT a,GROUP_CONCAT(a ORDER BY a) FROM t1 GROUP BY a WITH ROLLUP; +SELECT * FROM v1; +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'; +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; +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; +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; +DROP VIEW v1; + +#enable after fix MDEV-27871 +--disable_view_protocol +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'); +--enable_view_protocol +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; +DROP VIEW v1; + +DROP TABLE t1; + + +--echo # +--echo # MDEV-4677 GROUP_CONCAT not showing any output with group_concat_max_len >= 4Gb +--echo # +set group_concat_max_len=1024*1024*1024*4; +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; +drop table t1; + +--echo # +--echo # End of 10.2 tests +--echo # + + +--echo # +--echo # Start of 10.3 tests +--echo # + +# +# MDEV-11297: Add support for LIMIT clause in GROUP_CONCAT() +# +--disable_warnings +drop table if exists t1, t2; +--enable_warnings + +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; +select grp,group_concat(c limit 1 ) from t1 group by grp; +select grp,group_concat(c limit 1,1 ) from t1 group by grp; +select grp,group_concat(c limit 1,10 ) from t1 group by grp; +select grp,group_concat(c limit 1000) from t1 group by grp; +select group_concat(grp limit 0) from t1; +--error ER_PARSE_ERROR +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 ; +select grp,group_concat(distinct c limit 1,10 ) from t1 group by grp; +select grp,group_concat(c order by a) from t1 group by grp; +select grp,group_concat(c order by a limit 2 ) from t1 group by grp; +select grp,group_concat(c order by a limit 1,1 ) 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 limit 2) from t1 group by grp; +select grp,group_concat(c order by c desc) from t1 group by grp; +select grp,group_concat(c order by c desc limit 2) from t1 group by grp; + +--echo # +--echo # Empty results for group concat as offset is greater than the rows +--echo # for a group +--echo # + +select grp,group_concat(distinct c limit 10,1 ) from t1 group by grp; + +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; + +set @x=4; +prepare STMT from 'select group_concat(b limit ?) from t2'; +execute STMT using @x; +set @x=2; +execute STMT using @x; +set @x=1000; +execute STMT using @x; +set @x=0; +execute STMT using @x; +set @x="adasfa"; +--error ER_INVALID_VALUE_TO_LIMIT +execute STMT using @x; +set @x=-1; +--error ER_WRONG_ARGUMENTS +execute STMT using @x; +set @x=4; +prepare STMT from 'select group_concat(a,b limit ?) from t2'; +execute STMT using @x; +drop table t2; + +--echo # +--echo # MDEV-18943: Group Concat with limit not working with views +--echo # + +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; +create view v1 as select group_concat(a,b limit 2) from t1; +select * from v1; +drop view v1; +drop table t1; + +--echo # +--echo # End of 10.3 tests +--echo # |