~drizzle-trunk/drizzle/development

CREATE TEMPORARY TABLE t2 ( id int default '0', id_master int default '0', text1 varchar(5) default NULL, text2 varchar(5) default NULL) ENGINE=MyISAM;

281

CREATE TABLE t2 ( id int default '0', id_master int default '0', text1 varchar(5) default NULL, text2 varchar(5) default NULL) ENGINE=MyISAM;

284

282

INSERT INTO t2 (id, id_master, text1, text2) VALUES("1", "1",

285

283

"foo1", "bar1");

286

284

INSERT INTO t2 (id, id_master, text1, text2) VALUES("2", "1",

457

455

insert into t1 select * from t2;

458

456

insert into t2 select * from t1;

459

457

set local tmp_table_size=1024;

460

select SQL_BIG_RESULT count(*) from (select SQL_BIG_RESULT * from t1 union all select * from t2 order by 1) b;

458

select count(*) from (select * from t1 union all select * from t2 order by 1) b;

461

459

select count(*) from t1;

462

460

select count(*) from t2;

463

461

drop table t1,t2;

474

472

insert t1 select a+1, a+b from t1;

475

473

insert t1 select a+1, a+b from t1;

476

474

FLUSH STATUS;

477

--replace_column 2 #

478

475

show status like 'Slow_queries';

479

476

select count(*) from t1 where a=7;

480

--replace_column 2 #

481

477

show status like 'Slow_queries';

482

478

select count(*) from t1 where b=13;

483

--replace_column 2 #

484

479

show status like 'Slow_queries';

485

480

select count(*) from t1 where b=13 union select count(*) from t1 where a=7;

486

--replace_column 2 #

487

481

show status like 'Slow_queries';

488

482

select count(*) from t1 where a=7 union select count(*) from t1 where b=13;

489

--replace_column 2 #

490

483

show status like 'Slow_queries';

491

484

# additional test for examined rows

492

485

flush status;

493

486

select a from t1 where b not in (1,2,3) union select a from t1 where b not in (4,5,6);

494

--replace_column 2 #

495

487

show status like 'Slow_queries';

496

488

drop table t1;

497

489

498

490

499

491

# Column 'name' cannot be null (error with union and left join) (bug #2508)

500

492

501

create table t1 ( RID int not null default '0', IID int not null default '0', nada varchar(50) not null,NAME varchar(50) not null,PHONE varchar(50) not null);

493

create table t1 ( RID int not null default '0', IID int not null default '0', nada varchar(50) not null,NAME varchar(50) not null,PHONE varchar(50) not null) engine=MyISAM;

502

494

insert into t1 ( RID,IID,nada,NAME,PHONE) values (1, 1, 'main', 'a', '111'), (2, 1, 'main', 'b', '222'), (3, 1, 'main', 'c', '333'), (4, 1, 'main', 'd', '444'), (5, 1, 'main', 'e', '555'), (6, 2, 'main', 'c', '333'), (7, 2, 'main', 'd', '454'), (8, 2, 'main', 'e', '555'), (9, 2, 'main', 'f', '666'), (10, 2, 'main', 'g', '777');

503

495

select A.NAME, A.PHONE, B.NAME, B.PHONE from t1 A left join t1 B on A.NAME = B.NAME and B.IID = 2 where A.IID = 1 and (A.PHONE <> B.PHONE or B.NAME is null) union select A.NAME, A.PHONE, B.NAME, B.PHONE from t1 B left join t1 A on B.NAME = A.NAME and A.IID = 1 where B.IID = 2 and (A.PHONE <> B.PHONE or A.NAME is null);

504

496

drop table t1;

548

540

549

541

CREATE TABLE t1 (i int default NULL,c char(1) default NULL,KEY i (i));

550

542

CREATE TABLE t2 (i int default NULL,c char(1) default NULL,KEY i (i));

551

--error ER_BAD_FIELD_ERROR

543

--error 1054

552

544

explain (select * from t1) union (select * from t2) order by not_existing_column;

553

545

drop table t1, t2;

554

546

602

594

create table t3 (a ENUM('Yes', 'No') NOT NULL);

603

595

create table t4 (a ENUM('aaa', 'bbb') NOT NULL);

604

596

insert into t3 values (1);

605

--error ER_INVALID_ENUM_VALUE # Bad enum

597

--error 1691 # Bad enum

606

598

insert into t4 values (3);

607

599

select "1" as a union select a from t1;

608

600

select a as a from t1 union select "1";

653

645

drop table t1;

654

646

655

647

# Drizzle doesn't support specifying character set, it is all UTF8

656

--error ER_PARSE_ERROR

648

--error 1064

657

649

create table t2 (

658

650

a char character set utf8 collate utf8_swedish_ci,

659

651

b char character set utf8 collate utf8_spanish_ci);

661

653

create table t2 (

662

654

a char collate utf8_swedish_ci,

663

655

b char collate utf8_spanish_ci);

664

--error ER_CANT_AGGREGATE_NCOLLATIONS

656

--error 1271

665

657

create table t1 as

666

658

(select a from t2) union

667

659

(select b from t2);

668

660

669

--error ER_WRONG_COLUMN_NAME

661

--error 1166

670

662

create table t1 as

671

663

(select a collate utf8_swedish_ci from t2) union

672

664

(select b from t2);

746

738

# Bug#15949 union + illegal mix of collations (IMPLICIT + COERCIBLE)

747

739

748

740

select concat('a', 'b' collate utf8_bin);

749

create table t1 (foo varchar(100)) collate=utf8_bin;

741

create table t1 (foo varchar(100)) collate utf8_bin;

750

742

insert into t1 (foo) values ("foo");

751

--error ER_NO_SUCH_TABLE

743

--error 1146

752

744

select foo from t1 union select 'bar' as foo from dual;

753

745

select foo from t1 union select 'bar' as foo;

754

746

drop table t1;

807

799

create table t2 select * from t1 union select 'abcdefghijkl';

808

800

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

809

801

show create table t2;

810

select row_format from data_dictionary.TABLES where table_schema="test" and table_name="t2";

802

select row_format from information_schema.TABLES where table_schema="test" and table_name="t2";

803

alter table t2 ROW_FORMAT=fixed;

811

804

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

812

805

show create table t2;

813

806

drop table t1,t2;

956

949

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

957

950

958

951

SELECT a FROM (SELECT a FROM t1 UNION SELECT a FROM t1 ORDER BY a) AS test;

959

--error ER_BAD_FIELD_ERROR

952

--error 1054

960

953

SELECT a FROM (SELECT a FROM t1 UNION SELECT a FROM t1 ORDER BY c) AS test;

961

954

962

955

DROP TABLE t1;

964

957

965

958

# Bug#23345: Wrongly allowed INTO in a non-last select of a UNION.

966

959

967

--error ER_WRONG_USAGE

960

--error 1221

968

961

(select 1 into @var) union (select 1);

969

962

(select 1) union (select 1 into @var);

970

963

select @var;

971

--error ER_TOO_MANY_ROWS

964

--error 1172

972

965

(select 2) union (select 1 into @var);

973

966

974

967

Older »