~drizzle-trunk/drizzle/development

select 1 'is_in_group', a.user_name, c.group_name, b.id from t1 a, t3 b, t2 c where a.id = b.user_id and b.group_id = c.id UNION select 0 'is_in_group', a.user_name, c.group_name, null from t1 a CROSS JOIN t2 c;

319

320

drop table t3, t1, t2;

321

322

436

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

437

show create table t1;

438

drop table t1;

439

--error ER_BAD_FIELD_ERROR

439

-- error 1054

440

create table t1 select _latin1"test" union select _latin1"testt" ;

441

--error ER_BAD_FIELD_ERROR

441

-- error 1054

442

create table t1 select _utf8"test" union select _utf8"testt" ;

443

create table t1 select "test" union select "testt" ;

444

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

457

insert into t1 select * from t2;

458

insert into t2 select * from t1;

459

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;

460

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

461

select count(*) from t1;

462

select count(*) from t2;

463

drop table t1,t2;

474

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

475

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

476

FLUSH STATUS;

477

--replace_column 2 #

478

477

show status like 'Slow_queries';

479

478

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

480

--replace_column 2 #

481

479

show status like 'Slow_queries';

482

480

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

483

--replace_column 2 #

484

481

show status like 'Slow_queries';

485

482

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

486

--replace_column 2 #

487

483

show status like 'Slow_queries';

488

484

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

489

--replace_column 2 #

490

485

show status like 'Slow_queries';

491

486

# additional test for examined rows

492

487

flush status;

493

488

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

489

show status like 'Slow_queries';

496

490

drop table t1;

497

491

548

542

549

543

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

550

544

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

551

--error ER_BAD_FIELD_ERROR

545

--error 1054

552

546

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

553

547

drop table t1, t2;

554

548

602

596

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

603

597

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

604

598

insert into t3 values (1);

605

--error ER_INVALID_ENUM_VALUE # Bad enum

599

--error 1691 # Bad enum

606

600

insert into t4 values (3);

607

601

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

608

602

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

653

647

drop table t1;

654

648

655

649

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

656

--error ER_PARSE_ERROR

650

--error 1064

657

651

create table t2 (

658

652

a char character set utf8 collate utf8_swedish_ci,

659

653

b char character set utf8 collate utf8_spanish_ci);

661

655

create table t2 (

662

656

a char collate utf8_swedish_ci,

663

657

b char collate utf8_spanish_ci);

664

--error ER_CANT_AGGREGATE_NCOLLATIONS

658

--error 1271

665

659

create table t1 as

666

660

(select a from t2) union

667

661

(select b from t2);

668

662

669

--error ER_WRONG_COLUMN_NAME

663

--error 1166

670

664

create table t1 as

671

665

(select a collate utf8_swedish_ci from t2) union

672

666

(select b from t2);

746

740

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

747

741

748

742

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

749

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

743

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

750

744

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

751

--error ER_TABLE_UNKNOWN

745

--error 1146

752

746

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

753

747

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

754

748

drop table t1;

807

801

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

808

802

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

809

803

show create table t2;

810

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

804

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

805

alter table t2 ROW_FORMAT=fixed;

811

806

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

812

807

show create table t2;

813

808

drop table t1,t2;

956

951

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

957

952

958

953

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

959

--error ER_BAD_FIELD_ERROR

954

--error 1054

960

955

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

961

956

962

957

DROP TABLE t1;

964

959

965

960

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

966

961

967

--error ER_WRONG_USAGE

962

--error 1221

968

963

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

969

964

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

970

965

select @var;

971

--error ER_TOO_MANY_ROWS

966

--error 1172

972

967

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

973

968

974

969

Older »