union.test

这是linux下运行的mysql软件包,可用于linux 下安装 php + mysql + apach 的网络配置

show create table t1;
drop table t1;
create table t1 SELECT ib from t2 UNION select f from t2;
select * from t1;
show create table t1;
drop table t1;
create table t1 SELECT ib from t2 UNION select d from t2;
select * from t1;
show create table t1;
drop table t1;
create table t1 SELECT f from t2 UNION select y from t2;
select * from t1;
show create table t1;
drop table t1;
create table t1 SELECT f from t2 UNION select da from t2;
select * from t1;
show create table t1;
drop table t1;
create table t1 SELECT y from t2 UNION select da from t2;
select * from t1;
show create table t1;
drop table t1;
create table t1 SELECT y from t2 UNION select dt from t2;
select * from t1;
show create table t1;
drop table t1;
create table t1 SELECT da from t2 UNION select dt from t2;
select * from t1;
show create table t1;
drop table t1;
create table t1 SELECT dt from t2 UNION select sc from t2;
select * from t1;
show create table t1;
drop table t1;
create table t1 SELECT dt from t2 UNION select sv from t2;
select * from t1;
show create table t1;
drop table t1;
create table t1 SELECT sc from t2 UNION select sv from t2;
select * from t1;
show create table t1;
drop table t1;
create table t1 SELECT dt from t2 UNION select b from t2;
select * from t1;
show create table t1;
drop table t1;
create table t1 SELECT sv from t2 UNION select b from t2;
select * from t1;
show create table t1;
drop table t1;
create table t1 SELECT i from t2 UNION select d from t2 UNION select b from t2;
select * from t1;
show create table t1;
drop table t1;
create table t1 SELECT sv from t2 UNION select tx from t2;
select * from t1;
show create table t1;
drop table t1;
create table t1 SELECT b from t2 UNION select tx from t2;
select * from t1;
show create table t1;
drop table t1,t2;
create table t1 select 1 union select -1;
select * from t1;
show create table t1;
drop table t1;
-- error 1267
create table t1 select _latin1"test" union select _latin2"testt" ;
create table t1 select _latin2"test" union select _latin2"testt" ;
show create table t1;
drop table t1;

#
# conversion memory->disk table
#
create table t1 (s char(200));
insert into t1 values (repeat("1",200));
create table t2 select * from t1;
insert into t2 select * from t1;
insert into t1 select * from t2;
insert into t2 select * from t1;
insert into t1 select * from t2;
insert into t2 select * from t1;
set local tmp_table_size=1024;
select count(*) from (select * from t1 union all select * from t2 order by 1) b;
select count(*) from t1;
select count(*) from t2;
drop table t1,t2;
set local tmp_table_size=default;

#
# slow logging
#
create table t1 (a int, index (a), b int);
insert t1 values (1,1),(2,2),(3,3),(4,4),(5,5);
insert t1 select a+1, a+b from t1;
insert t1 select a+1, a+b from t1;
insert t1 select a+1, a+b from t1;
insert t1 select a+1, a+b from t1;
insert t1 select a+1, a+b from t1;
FLUSH STATUS;
show status like 'Slow_queries';
select count(*) from t1 where a=7;
show status like 'Slow_queries';
select count(*) from t1 where b=13;
show status like 'Slow_queries';
select count(*) from t1 where b=13 union select count(*) from t1 where a=7;
show status like 'Slow_queries';
select count(*) from t1 where a=7 union select count(*) from t1 where b=13;
show status like 'Slow_queries';
# additional test for examined rows
flush status;
select a from t1 where b not in (1,2,3) union select a from t1 where b not in (4,5,6); 
show status like 'Slow_queries';
drop table t1;

#
# Column 'name' cannot be null (error with union and left join) (bug #2508)
#
create table t1 (   RID int(11) not null default '0',   IID int(11) not null default '0',    nada varchar(50)  not null,NAME varchar(50) not null,PHONE varchar(50) not null) engine=MyISAM;
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');
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);
drop  table t1;

#
# Bug #2809 (UNION fails on MyIsam tables when index on second column from
# same table)
#
create table t1 (col1 tinyint unsigned, col2 tinyint unsigned);
insert into t1 values (1,2),(3,4),(5,6),(7,8),(9,10);
select col1 n from t1 union select col2 n from t1 order by n;
alter table t1 add index myindex (col2);
select col1 n from t1 union select col2 n from t1 order by n;
drop  table t1;

#
# Incorrect handling of UNION ALL (Bug #1428)
#
create table t1 (i int);
insert into t1 values (1);
select * from t1 UNION select * from t1;
select * from t1 UNION ALL select * from t1;
select * from t1 UNION select * from t1 UNION ALL select * from t1;
drop table t1;
select 1 as a union all select 1 union all select 2 union select 1 union all select 2;
set sql_select_limit=1;
select 1 union select 2;
(select 1) union (select 2);
(select 1) union (select 2) union (select 3) limit 2;
set sql_select_limit=default;

#
# ORDER with LIMIT
#
create table t1 (a int);
insert into t1 values (100), (1);
create table t2 (a int);
insert into t2 values (100);
select a from t1 union select a from t2 order by a;
SET SQL_SELECT_LIMIT=1;
select a from t1 union select a from t2 order by a;
drop table t1, t2;
set sql_select_limit=default;

#
# nonexisting column in global ORDER BY
#
CREATE TABLE t1 (i int(11) default NULL,c char(1) default NULL,KEY i (i));
CREATE TABLE t2 (i int(11) default NULL,c char(1) default NULL,KEY i (i));
--error 1054
explain (select * from t1) union (select * from t2) order by not_existing_column;
drop table t1, t2;

#
# length detecting
#
CREATE TABLE t1 (uid int(1));
INSERT INTO t1 SELECT 150;
SELECT 'a' UNION SELECT uid FROM t1;
drop table t1;

#
# parser stack overflow
#
CREATE TABLE t1 ( ID1 int(10) unsigned NOT NULL DEFAULT '0' , ID2 datetime NOT NULL DEFAULT '0000-00-00 00:00:00' , DATA1 varchar(10) , DATA2 double(5,4) , DATA3 datetime , PRIMARY KEY (ID1,ID2));

CREATE TABLE t2 ( ID int(3) unsigned NOT NULL DEFAULT '0' , DATA1 timestamp DEFAULT '0000-00-00 00:00:00' , PRIMARY KEY (ID));
(SELECT * FROM t1 AS PARTITIONED, t2 AS
PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1) UNION
(SELECT * FROM t1 AS PARTITIONED, t2 AS
PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1) UNION
(SELECT * FROM t1 AS PARTITIONED, t2 AS
PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1) UNION
(SELECT * FROM t1 AS PARTITIONED, t2 AS
PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1) UNION
(SELECT * FROM t1 AS PARTITIONED, t2 AS
PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1) UNION
(SELECT * FROM t1 AS PARTITIONED, t2 AS
PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1) UNION
(SELECT * FROM t1 AS PARTITIONED, t2 AS
PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1) UNION
(SELECT * FROM t1 AS PARTITIONED, t2 AS
PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1) UNION
(SELECT * FROM t1 AS PARTITIONED, t2 AS
PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1) UNION
(SELECT * FROM t1 AS PARTITIONED, t2 AS
PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1) UNION
(SELECT * FROM t1 AS PARTITIONED, t2 AS
PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1) UNION
(SELECT * FROM t1 AS PARTITIONED, t2 AS
PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1);
drop table t1,t2;

#
# merging ENUM and SET fields in one UNION
#
create table t1 (a ENUM('Yes', 'No') NOT NULL);
create table t2 (a ENUM('aaa', 'bbb') NOT NULL);
insert into t1 values ('No');
insert into t2 values ('bbb');
create table t3 (a SET('Yes', 'No') NOT NULL);
create table t4 (a SET('aaa', 'bbb') NOT NULL);
insert into t3 values (1);
insert into t4 values (3);
select "1" as a union select a from t1;
select a as a from t1 union select "1";
select a as a from t2 union select a from t1;
select "1" as a union select a from t3;
select a as a from t3 union select "1";
select a as a from t4 union select a from t3;
select a as a from t1 union select a from t4;
drop table t1,t2,t3,t4;

#
# Bug #6139 UNION doesn't understand collate in the column of second select
#
create table t1 as
(select _latin1'test') union
(select _latin1'TEST') union
(select _latin1'TeST');
show create table t1;
select count(*) from t1;
drop table t1;

create table t1 as
(select _latin1'test' collate latin1_bin) union
(select _latin1'TEST') union
(select _latin1'TeST');
show create table t1;
select count(*) from t1;
drop table t1;

create table t1 as
(select _latin1'test') union
(select _latin1'TEST' collate latin1_bin) union
(select _latin1'TeST');
show create table t1;
select count(*) from t1;
drop table t1;

create table t1 as
(select _latin1'test') union
(select _latin1'TEST') union
(select _latin1'TeST' collate latin1_bin);
show create table t1;
select count(*) from t1;
drop table t1;

create table t2 (
a char character set latin1 collate latin1_swedish_ci,
b char character set latin1 collate latin1_german1_ci);
--error 1271
create table t1 as
(select a from t2) union
(select b from t2);
create table t1 as
(select a collate latin1_german1_ci from t2) union
(select b from t2);
show create table t1;
drop table t1;
create table t1 as
(select a from t2) union
(select b collate latin1_german1_ci from t2);
show create table t1;
drop table t1;
create table t1 as
(select a from t2) union
(select b from t2) union
(select 'c' collate latin1_german1_ci from t2);
show create table t1;
drop table t1;
drop table t2;

#
# Bug 6931: Date Type column problem when using UNION-Table.
#
create table t1(a1 int, f1 char(10));
create table t2
select f2,a1 from (select a1, CAST('2004-12-31' AS DATE) f2 from t1) a
union
select f2,a1 from (select a1, CAST('2004-12-31' AS DATE) f2 from t1) a
order by f2, a1;
show columns from t2;
drop table t1, t2;

create table t1 (f1 int);
create table t2 (f1 int, f2 int ,f3 date);
create table t3 (f1 int, f2 char(10));
create table t4
(
  select t2.f3 as sdate
  from t1
  left outer join t2 on (t1.f1 = t2.f1)
  inner join t3 on (t2.f2 = t3.f1)
  order by t1.f1, t3.f1, t2.f3
)
union
(
  select cast('2004-12-31' as date) as sdate
  from t1
  left outer join t2 on (t1.f1 = t2.f1)
  inner join t3 on (t2.f2 = t3.f1)
  group by t1.f1
  order by t1.f1, t3.f1, t2.f3
)
order by sdate;
show columns from t4;
drop table t1, t2, t3, t4;

#
# Bug #2435 UNION with parentheses not supported
#
create table t1 (a int not null, b char (10) not null); 
insert into t1 values(1,'a'),(2,'b'),(3,'c'),(3,'c'); 
select * from ((select * from t1 limit 1)) a;
select * from ((select * from t1 limit 1) union (select * from t1 limit 1)) a;
select * from ((select * from t1 limit 1) union (select * from t1 limit 1) union (select * from t1 limit 1)) a;
select * from ((((select * from t1))) union (select * from t1) union (select * from t1)) a;
select * from ((select * from t1) union (((select * from t1))) union (select * from t1)) a;
drop table t1;

#
# Bugs#6519 UNION with collation binary and latin1_swedish_ci fails
#
set @val:=6;
select concat('value is: ', @val) union select 'some text';

#
# Bug#15949 union + illegal mix of collations (IMPLICIT + COERCIBLE)
#
select concat(_latin1'a', _ascii'b' collate ascii_bin);
create table t1 (foo varchar(100)) collate ascii_bin;
insert into t1 (foo) values ("foo");
select foo from t1 union select 'bar' as foo from dual;
drop table t1;

#
# Enum merging test
#
CREATE TABLE t1 (
  a ENUM('