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

440

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

441

--error ER_BAD_FIELD_ERROR

440

442

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

441

443

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

442

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 count(*) from (select * from t1 union all select * from t2 order by 1) b;

460

select SQL_BIG_RESULT count(*) from (select SQL_BIG_RESULT * 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;

548

549

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

550

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

551

--error 1054

551

--error ER_BAD_FIELD_ERROR

552

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

553

drop table t1, t2;

554

602

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

603

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

604

insert into t3 values (1);

605

--error 1691 # Bad enum

605

--error ER_INVALID_ENUM_VALUE # Bad enum

606

insert into t4 values (3);

607

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

608

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

653

drop table t1;

654

655

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

656

--error 1064

656

--error ER_PARSE_ERROR

657

create table t2 (

658

a char character set utf8 collate utf8_swedish_ci,

659

b char character set utf8 collate utf8_spanish_ci);

661

create table t2 (

662

a char collate utf8_swedish_ci,

663

b char collate utf8_spanish_ci);

664

--error 1271

664

--error ER_CANT_AGGREGATE_NCOLLATIONS

665

create table t1 as

666

(select a from t2) union

667

(select b from t2);

668

669

--error 1166

669

--error ER_WRONG_COLUMN_NAME

670

create table t1 as

671

(select a collate utf8_swedish_ci from t2) union

672

(select b from t2);

746

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

747

748

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

749

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

749

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

750

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

751

--error 1146

751

--error ER_TABLE_UNKNOWN

752

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

753

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

754

drop table t1;

956

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

957

958

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

959

--error 1054

959

--error ER_BAD_FIELD_ERROR

960

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

961

962

DROP TABLE t1;

964

965

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

966

967

--error 1221

967

--error ER_WRONG_USAGE

968

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

969

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

970

select @var;

971

--error 1172

971

--error ER_TOO_MANY_ROWS

972

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

973

974

Older »