~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(11), URL varchar(80));

167

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

166

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

167

create table t2 ( REQ_ID int, URL_ID int);

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 tinyint(4) NOT NULL, id2 tinyint(4) NOT NULL);

227

CREATE TABLE t1 (id1 int NOT NULL, id2 int 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 tinyint(4) NOT NULL);

229

CREATE TABLE t2 (id1 int 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) character set cp1250 collate cp1250_general_ci,

450

b varchar(255) character set koi8r);

449

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

451

450

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

452

451

select collation(a) from t1;

453

452

collation(a)

454

cp1250_general_ci

453

utf8_general_ci

455

454

select collation(group_concat(a)) from t1;

456

455

collation(group_concat(a))

457

cp1250_general_ci

456

utf8_general_ci

458

457

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

459

458

show create table t2;

460

459

Table Create Table

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'

460

t2 CREATE TABLE `t2` (

461

`a` varchar(100)

462

) ENGINE=InnoDB

469

463

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

470

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

464

collation(group_concat(a,b))

465

utf8_general_ci

471

466

drop table t1;

472

467

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;

482

468

CREATE TABLE t1 (id int);

483

469

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

484

470

535

521

ABW

536

522

drop table t1;

537

523

CREATE TABLE t1 (

538

aID smallint(5) unsigned NOT NULL auto_increment,

539

sometitle varchar(255) NOT NULL default '',

540

bID smallint(5) unsigned NOT NULL,

524

aID int NOT NULL auto_increment,

525

sometitle varchar(155) NOT NULL default '',

526

bID int NOT NULL,

541

527

PRIMARY KEY (aID),

542

528

UNIQUE KEY sometitle (sometitle)

543

529

);

544

530

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

545

531

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

546

532

CREATE TABLE t2 (

547

bID smallint(5) unsigned NOT NULL auto_increment,

548

somename varchar(255) NOT NULL default '',

533

bID int NOT NULL auto_increment,

534

somename varchar(155) NOT NULL default '',

549

535

PRIMARY KEY (bID),

550

536

UNIQUE KEY somename (somename)

551

537

);

609

595

610

596

611

597

drop table t1;

612

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

598

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

613

599

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

614

600

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

615

601

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

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

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

618

604

f2 group_concat(f1)

619

605

aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 1

620

606

bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb 2

621

607

drop table t1;

622

set names latin1;

623

608

create table t1 (a char, b char);

624

609

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

625

610

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

626

611

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

627

612

(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

635

613

drop table t1, t2, t3;

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

614

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

643

615

SET GROUP_CONCAT_MAX_LEN = 20000000;

644

616

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

645

617

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

661

633

2 1,2

662

634

1 2,3

663

635

DROP TABLE t1;

664

set names utf8;

665

636

create table t1

666

637

(

667

x text character set utf8 not null,

638

x text not null,

668

639

y integer not null

669

640

);

670

641

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

720

691

1023 1023 1023 aaaaaaaaaaa, 61616161612C

721

692

drop table t1;

722

693

set group_concat_max_len=1024;

723

set names latin1;

724

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

694

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

725

695

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

726

696

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

727

697

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

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

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

730

700

f2 group_concat(f1)

731

701

732

702

811

781

65533

812

782

SET group_concat_max_len= DEFAULT;

813

783

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;

863

784

create table t1 (f1 char(20));

864

785

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

865

786

select group_concat(distinct f1) from t1;

Older »