~drizzle-trunk/drizzle/development

#

# simple test of group_concat function

#

--disable_warnings

drop table if exists t1, t2;

--enable_warnings

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

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;

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;

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;

#

# 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;

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;

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 #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 cancatanated 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

select f2,group_concat(f1) from t1 group by f2;

--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

select f2,group_concat(f1) from t1 group by f2;

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

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; 

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 INTO t3 VALUES( REPEAT( 'a', 65533 ), 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 default 0);

insert 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