~drizzle-trunk/drizzle/development

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`

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

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`

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

108

id select_type table type possible_keys key key_len ref rows filtered Extra

109

1 SIMPLE t1 ALL NULL NULL NULL NULL 9 100.00 Using filesort

110

Warnings:

111

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`

111

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"

112

select grp,group_concat(distinct c order by c desc separator ",") from t1 group by grp;

113

grp group_concat(distinct c order by c desc separator ",")

114

1 a

163

select grp,group_concat(c order by 2) from t1 group by grp;

164

ERROR 42S22: Unknown column '2' in 'order clause'

165

drop table t1;

166

create table t1 ( URL_ID int, URL varchar(80));

167

create table t2 ( REQ_ID int, URL_ID int);

166

create table t1 ( URL_ID int(11), URL varchar(80));

167

create table t2 ( REQ_ID int(11), URL_ID int(11));

168

insert into t1 values (4,'www.host.com'), (5,'www.google.com'),(5,'www.help.com');

169

insert into t2 values (1,4), (5,4), (5,5);

170

select REQ_ID, Group_Concat(URL) as URL from t1, t2 where

224

group_concat(a1 order by (t1.a))

225

b,a,c

226

drop table t1, t2;

227

CREATE TABLE t1 (id1 int NOT NULL, id2 int NOT NULL);

227

CREATE TABLE t1 (id1 tinyint(4) NOT NULL, id2 tinyint(4) NOT NULL);

228

INSERT INTO t1 VALUES (1, 1),(1, 2),(1, 3),(1, 4),(1, 5),(2, 1),(2, 2),(2, 3);

229

CREATE TABLE t2 (id1 int NOT NULL);

229

CREATE TABLE t2 (id1 tinyint(4) NOT NULL);

230

INSERT INTO t2 VALUES (1),(2),(3),(4),(5);

231

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;

232

id1 concat_id

446

Warnings:

447

Warning 1260 2 line(s) were cut by GROUP_CONCAT()

448

drop table t1;

449

create table t1 (a varchar(255), b varchar(255) );

449

create table t1 (a varchar(255) character set cp1250 collate cp1250_general_ci,

450

b varchar(255) character set koi8r);

450

451

insert into t1 values ('xxx','yyy');

451

452

select collation(a) from t1;

452

453

collation(a)

453

utf8_general_ci

454

cp1250_general_ci

454

455

select collation(group_concat(a)) from t1;

455

456

collation(group_concat(a))

456

utf8_general_ci

457

cp1250_general_ci

457

458

create table t2 select group_concat(a) as a from t1;

458

459

show create table t2;

459

460

Table Create Table

460

t2 CREATE TABLE `t2` (

461

`a` varchar(100)

462

) ENGINE=InnoDB

461

t2 CREATE TABLE "t2" (

462

"a" varchar(400) CHARACTER SET cp1250

463

) ENGINE=MyISAM DEFAULT CHARSET=latin1

464

select collation(group_concat(a,_koi8r'test')) from t1;

465

collation(group_concat(a,_koi8r'test'))

466

cp1250_general_ci

467

select collation(group_concat(a,_koi8r 0xC1C2)) from t1;

468

ERROR HY000: Illegal mix of collations (cp1250_general_ci,IMPLICIT) and (koi8r_general_ci,COERCIBLE) for operation 'group_concat'

463

469

select collation(group_concat(a,b)) from t1;

464

collation(group_concat(a,b))

465

utf8_general_ci

470

ERROR HY000: Illegal mix of collations (cp1250_general_ci,IMPLICIT) and (koi8r_general_ci,IMPLICIT) for operation 'group_concat'

466

471

drop table t1;

467

472

drop table t2;

473

CREATE TABLE t1 (a CHAR(10) CHARACTER SET cp850);

474

INSERT INTO t1 VALUES ('�');

475

SELECT a FROM t1;

476

477

�

478

SELECT GROUP_CONCAT(a) FROM t1;

479

GROUP_CONCAT(a)

480

�

481

DROP TABLE t1;

468

482

CREATE TABLE t1 (id int);

469

483

SELECT GROUP_CONCAT(id) AS gc FROM t1 HAVING gc IS NULL;

470

484

521

535

ABW

522

536

drop table t1;

523

537

CREATE TABLE t1 (

524

aID int NOT NULL auto_increment,

525

sometitle varchar(155) NOT NULL default '',

526

bID int NOT NULL,

538

aID smallint(5) unsigned NOT NULL auto_increment,

539

sometitle varchar(255) NOT NULL default '',

540

bID smallint(5) unsigned NOT NULL,

527

541

PRIMARY KEY (aID),

528

542

UNIQUE KEY sometitle (sometitle)

529

543

);

530

544

INSERT INTO t1 SET sometitle = 'title1', bID = 1;

531

545

INSERT INTO t1 SET sometitle = 'title2', bID = 1;

532

546

CREATE TABLE t2 (

533

bID int NOT NULL auto_increment,

534

somename varchar(155) NOT NULL default '',

547

bID smallint(5) unsigned NOT NULL auto_increment,

548

somename varchar(255) NOT NULL default '',

535

549

PRIMARY KEY (bID),

536

550

UNIQUE KEY somename (somename)

537

551

);

595

609

596

610

597

611

drop table t1;

598

create table t1 (f1 int, f2 varchar(255));

612

create table t1 (f1 int unsigned, f2 varchar(255));

599

613

insert into t1 values (1,repeat('a',255)),(2,repeat('b',255));

600

614

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

601

615

Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr

602

def test t1 t1 f2 f2 10 1020 255 Y 0 0 45

603

def group_concat(f1) 10 400 1 Y 128 0 63

616

def test t1 t1 f2 f2 253 255 255 Y 0 0 8

617

def group_concat(f1) 253 400 1 Y 128 0 63

604

618

f2 group_concat(f1)

605

619

aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 1

606

620

bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb 2

607

621

drop table t1;

622

set names latin1;

608

623

create table t1 (a char, b char);

609

624

insert into t1 values ('a', 'a'), ('a', 'b'), ('b', 'a'), ('b', 'b');

610

625

create table t2 select group_concat(b) as a from t1 where a = 'a';

611

626

create table t3 (select group_concat(a) as a from t1 where a = 'a') union

612

627

(select group_concat(b) as a from t1 where a = 'b');

628

select charset(a) from t2;

629

charset(a)

630

latin1

631

select charset(a) from t3;

632

charset(a)

633

latin1

634

latin1

613

635

drop table t1, t2, t3;

614

CREATE TABLE t1 (a INT, b LONGTEXT, PRIMARY KEY (a));

636

set names default;

637

create table t1 (c1 varchar(10), c2 int);

638

select charset(group_concat(c1 order by c2)) from t1;

639

charset(group_concat(c1 order by c2))

640

latin1

641

drop table t1;

642

CREATE TABLE t1 (a INT(10), b LONGTEXT, PRIMARY KEY (a));

615

643

SET GROUP_CONCAT_MAX_LEN = 20000000;

616

644

INSERT INTO t1 VALUES (1,REPEAT(CONCAT('A',CAST(CHAR(0) AS BINARY),'B'), 40000));

617

645

INSERT INTO t1 SELECT a + 1, b FROM t1;

633

661

2 1,2

634

662

1 2,3

635

663

DROP TABLE t1;

664

set names utf8;

636

665

create table t1

637

666

(

638

x text not null,

667

x text character set utf8 not null,

639

668

y integer not null

640

669

);

641

670

insert into t1 values (repeat('a', 1022), 0), (repeat(_utf8 0xc3b7, 4), 0);

691

720

1023 1023 1023 aaaaaaaaaaa, 61616161612C

692

721

drop table t1;

693

722

set group_concat_max_len=1024;

694

create table t1 (f1 int, f2 varchar(255));

723

set names latin1;

724

create table t1 (f1 int unsigned, f2 varchar(255));

695

725

insert into t1 values (1,repeat('a',255)),(2,repeat('b',255));

696

726

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

697

727

Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr

698

def test t1 t1 f2 f2 10 1020 255 Y 0 0 45

699

def group_concat(f1) 255 1024 1 Y 128 0 63

728

def test t1 t1 f2 f2 253 255 255 Y 0 0 8

729

def group_concat(f1) 252 1024 1 Y 128 0 63

700

730

f2 group_concat(f1)

701

731

702

732

781

811

65533

782

812

SET group_concat_max_len= DEFAULT;

783

813

DROP TABLE t1, t2, t3;

814

set names latin1;

815

create table t1 (id int, name varchar(20)) DEFAULT CHARSET=utf8;

816

insert into t1 (id, name) values (1, "�ra");

817

insert into t1 (id, name) values (2, "�ra");

818

select b.id, group_concat(b.name) from t1 a, t1 b group by b.id;

819

id group_concat(b.name)

820

1 �ra,�ra

821

2 �ra,�ra

822

drop table t1;

823

create table t1(a bit not null default 0);

824

insert into t1 values (), (), ();

825

select group_concat(distinct a) from t1;

826

group_concat(distinct a)

827

828

select group_concat(distinct a order by a) from t1;

829

group_concat(distinct a order by a)

830

831

drop table t1;

832

create table t1(a bit(2) not null);

833

insert into t1 values (1), (0), (0), (3), (1);

834

select group_concat(distinct a) from t1;

835

group_concat(distinct a)

836

1,0,3

837

select group_concat(distinct a order by a) from t1;

838

group_concat(distinct a order by a)

839

0,1,3

840

select group_concat(distinct a order by a desc) from t1;

841

group_concat(distinct a order by a desc)

842

3,1,0

843

drop table t1;

844

create table t1(a bit(2), b varchar(10), c bit);

845

insert into t1 values (1, 'a', 0), (0, 'b', 1), (0, 'c', 0), (3, 'd', 1),

846

(1, 'e', 1), (3, 'f', 1), (0, 'g', 1);

847

select group_concat(distinct a, c) from t1;

848

group_concat(distinct a, c)

849

10,01,00,31,11

850

select group_concat(distinct a, c order by a) from t1;

851

group_concat(distinct a, c order by a)

852

00,01,11,10,31

853

select group_concat(distinct a, c) from t1;

854

group_concat(distinct a, c)

855

10,01,00,31,11

856

select group_concat(distinct a, c order by a, c) from t1;

857

group_concat(distinct a, c order by a, c)

858

00,01,10,11,31

859

select group_concat(distinct a, c order by a desc, c desc) from t1;

860

group_concat(distinct a, c order by a desc, c desc)

861

31,11,10,01,00

862

drop table t1;

784

863

create table t1 (f1 char(20));

785

864

insert into t1 values (''),('');

786

865

select group_concat(distinct f1) from t1;

Older »