diff options
Diffstat (limited to 'mysql-test/r/union.result')
| -rw-r--r-- | mysql-test/r/union.result | 234 |
1 files changed, 145 insertions, 89 deletions
diff --git a/mysql-test/r/union.result b/mysql-test/r/union.result index 6007fdd403a..2a10f34d081 100644 --- a/mysql-test/r/union.result +++ b/mysql-test/r/union.result @@ -83,10 +83,10 @@ a b (select a,b from t1 limit 2) union all (select a,b from t2 order by a limit 1) order by t1.b; ERROR 42000: Table 't1' from one of the SELECTs cannot be used in global ORDER clause explain extended (select a,b from t1 limit 2) union all (select a,b from t2 order by a limit 1) order by b desc; -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 4 -2 UNION t2 ALL NULL NULL NULL NULL 4 Using filesort -NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL Using filesort +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 100.00 +2 UNION t2 ALL NULL NULL NULL NULL 4 100.00 Using filesort +NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL NULL Using filesort Warnings: Note 1003 (select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` limit 2) union all (select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` order by `test`.`t2`.`a` limit 1) order by `b` desc (select sql_calc_found_rows a,b from t1 limit 2) union all (select a,b from t2 order by a) limit 2; @@ -153,6 +153,48 @@ ERROR 42S22: Unknown column 'a' in 'field list' 1 3 1 3 2 1 +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1); +SELECT a INTO @v FROM ( +SELECT a FROM t1 +UNION +SELECT a FROM t1 +) alias; +SELECT a INTO OUTFILE 'union.out.file' FROM ( +SELECT a FROM t1 +UNION +SELECT a FROM t1 WHERE 0 +) alias; +SELECT a INTO DUMPFILE 'union.out.file2' FROM ( +SELECT a FROM t1 +UNION +SELECT a FROM t1 WHERE 0 +) alias; +SELECT a FROM ( +SELECT a FROM t1 +UNION +SELECT a INTO @v FROM t1 +) alias; +SELECT a FROM ( +SELECT a FROM t1 +UNION +SELECT a INTO OUTFILE 'union.out.file3' FROM t1 +) alias; +SELECT a FROM ( +SELECT a FROM t1 +UNION +SELECT a INTO DUMPFILE 'union.out.file4' FROM t1 +) alias; +SELECT a FROM t1 UNION SELECT a INTO @v FROM t1; +SELECT a FROM t1 UNION SELECT a INTO OUTFILE 'union.out.file5' FROM t1; +SELECT a FROM t1 UNION SELECT a INTO OUTFILE 'union.out.file6' FROM t1; +SELECT a INTO @v FROM t1 UNION SELECT a FROM t1; +ERROR HY000: Incorrect usage of UNION and INTO +SELECT a INTO OUTFILE 'union.out.file7' FROM t1 UNION SELECT a FROM t1; +ERROR HY000: Incorrect usage of UNION and INTO +SELECT a INTO DUMPFILE 'union.out.file8' FROM t1 UNION SELECT a FROM t1; +ERROR HY000: Incorrect usage of UNION and INTO +DROP TABLE t1; CREATE TABLE t1 ( `pseudo` char(35) NOT NULL default '', `pseudo1` char(35) NOT NULL default '', @@ -475,10 +517,10 @@ create table t2 (a int not null primary key auto_increment, b int); insert into t1 (b) values (1),(2),(2),(3); insert into t2 (b) values (10),(11),(12),(13); explain extended (select * from t1 where a=1) union (select * from t2 where a=1); -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 const PRIMARY PRIMARY 4 const 1 -2 UNION t2 const PRIMARY PRIMARY 4 const 1 -NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 const PRIMARY PRIMARY 4 const 1 100.00 +2 UNION t2 const PRIMARY PRIMARY 4 const 1 100.00 +NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL NULL Warnings: Note 1003 (select '1' AS `a`,'1' AS `b` from `test`.`t1` where ('1' = 1)) union (select '1' AS `a`,'10' AS `b` from `test`.`t2` where ('1' = 1)) (select * from t1 where a=5) union (select * from t2 where a=1); @@ -500,7 +542,7 @@ explain (select * from t1 where a=1 and b=10) union (select straight_join t1.a,t id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 2 UNION t1 index PRIMARY PRIMARY 4 NULL 4 Using index -2 UNION t2 index PRIMARY PRIMARY 4 NULL 3 Using where; Using index +2 UNION t2 index PRIMARY PRIMARY 4 NULL 4 Using where; Using index; Using join buffer NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL explain (select * from t1 where a=1) union (select * from t1 where b=1); id select_type table type possible_keys key key_len ref rows Extra @@ -543,7 +585,7 @@ aa show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `a` varchar(2) NOT NULL default '' + `a` varchar(2) NOT NULL DEFAULT '' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; create table t1 SELECT 12 as a UNION select "aa" as a; @@ -554,7 +596,7 @@ aa show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `a` varbinary(2) NOT NULL default '' + `a` varbinary(2) NOT NULL DEFAULT '' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; create table t1 SELECT 12 as a UNION select 12.2 as a; @@ -565,7 +607,7 @@ a show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `a` decimal(3,1) NOT NULL default '0.0' + `a` decimal(3,1) NOT NULL DEFAULT '0.0' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; create table t2 (it1 tinyint, it2 tinyint not null, i int not null, ib bigint, f float, d double, y year, da date, dt datetime, sc char(10), sv varchar(10), b blob, tx text); @@ -578,7 +620,7 @@ NULL show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `it2` tinyint(4) default NULL + `it2` tinyint(4) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; create table t1 SELECT it2 from t2 UNION select i from t2; @@ -589,7 +631,7 @@ it2 show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `it2` int(11) NOT NULL default '0' + `it2` int(11) NOT NULL DEFAULT '0' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; create table t1 SELECT i from t2 UNION select f from t2; @@ -600,7 +642,7 @@ i show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `i` double default NULL + `i` double DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; create table t1 SELECT f from t2 UNION select d from t2; @@ -611,7 +653,7 @@ f show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `f` double default NULL + `f` double DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; create table t1 SELECT ib from t2 UNION select f from t2; @@ -622,7 +664,7 @@ ib show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `ib` double default NULL + `ib` double DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; create table t1 SELECT ib from t2 UNION select d from t2; @@ -633,7 +675,7 @@ ib show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `ib` double default NULL + `ib` double DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; create table t1 SELECT f from t2 UNION select y from t2; @@ -644,7 +686,7 @@ f show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `f` float default NULL + `f` float DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; create table t1 SELECT f from t2 UNION select da from t2; @@ -655,7 +697,7 @@ f show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `f` varbinary(12) default NULL + `f` varbinary(12) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; create table t1 SELECT y from t2 UNION select da from t2; @@ -666,7 +708,7 @@ y show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `y` varbinary(10) default NULL + `y` varbinary(10) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; create table t1 SELECT y from t2 UNION select dt from t2; @@ -677,7 +719,7 @@ y show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `y` varbinary(19) default NULL + `y` varbinary(19) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; create table t1 SELECT da from t2 UNION select dt from t2; @@ -688,7 +730,7 @@ da show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `da` datetime default NULL + `da` datetime DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; create table t1 SELECT dt from t2 UNION select trim(sc) from t2; @@ -699,7 +741,7 @@ testc show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `dt` varbinary(19) default NULL + `dt` varbinary(19) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; create table t1 SELECT dt from t2 UNION select sv from t2; @@ -710,7 +752,7 @@ testv show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `dt` varbinary(19) default NULL + `dt` varbinary(19) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; create table t1 SELECT sc from t2 UNION select sv from t2; @@ -721,7 +763,7 @@ testv show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `sc` varchar(10) default NULL + `sc` varchar(10) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; create table t1 SELECT dt from t2 UNION select b from t2; @@ -788,7 +830,7 @@ select * from t1; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `1` bigint(20) NOT NULL default '0' + `1` bigint(20) NOT NULL DEFAULT '0' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; create table t1 select _latin1"test" union select _latin2"testt" ; @@ -797,7 +839,7 @@ create table t1 select _latin2"test" union select _latin2"testt" ; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `test` varchar(5) character set latin2 NOT NULL default '' + `test` varchar(5) CHARACTER SET latin2 NOT NULL DEFAULT '' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; create table t1 (s char(200)); @@ -1041,7 +1083,7 @@ create table t1 as show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `test` varchar(4) NOT NULL default '' + `test` varchar(4) NOT NULL DEFAULT '' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 select count(*) from t1; count(*) @@ -1054,7 +1096,7 @@ create table t1 as show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `_latin1'test' collate latin1_bin` varchar(4) character set latin1 collate latin1_bin NOT NULL default '' + `_latin1'test' collate latin1_bin` varchar(4) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 select count(*) from t1; count(*) @@ -1067,7 +1109,7 @@ create table t1 as show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `test` varchar(4) character set latin1 collate latin1_bin NOT NULL default '' + `test` varchar(4) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 select count(*) from t1; count(*) @@ -1080,7 +1122,7 @@ create table t1 as show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `test` varchar(4) character set latin1 collate latin1_bin NOT NULL default '' + `test` varchar(4) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 select count(*) from t1; count(*) @@ -1099,7 +1141,7 @@ create table t1 as show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `a collate latin1_german1_ci` varchar(1) character set latin1 collate latin1_german1_ci default NULL + `a collate latin1_german1_ci` varchar(1) CHARACTER SET latin1 COLLATE latin1_german1_ci DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; create table t1 as @@ -1108,7 +1150,7 @@ create table t1 as show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `a` varchar(1) character set latin1 collate latin1_german1_ci default NULL + `a` varchar(1) CHARACTER SET latin1 COLLATE latin1_german1_ci DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; create table t1 as @@ -1118,7 +1160,7 @@ create table t1 as show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `a` varchar(1) character set latin1 collate latin1_german1_ci default NULL + `a` varchar(1) CHARACTER SET latin1 COLLATE latin1_german1_ci DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; drop table t2; @@ -1203,9 +1245,9 @@ c ENUM("one", "two") show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `a` enum('ä','ö','ü') character set utf8 NOT NULL default 'ü', - `b` enum('one','two') character set utf8 default NULL, - `c` enum('one','two') default NULL + `a` enum('ä','ö','ü') CHARACTER SET utf8 NOT NULL DEFAULT 'ü', + `b` enum('one','two') CHARACTER SET utf8 DEFAULT NULL, + `c` enum('one','two') DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 insert into t1 values ('ä', 'one', 'one'), ('ö', 'two', 'one'), ('ü', NULL, NULL); create table t2 select NULL union select a from t1; @@ -1261,7 +1303,7 @@ create table t2 select * from t1 union select 'abcdefghijkl'; show create table t2; Table Create Table t2 CREATE TABLE `t2` ( - `a` varchar(12) default NULL + `a` varchar(12) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 select row_format from information_schema.TABLES where table_schema="test" and table_name="t2"; row_format @@ -1270,7 +1312,7 @@ alter table t2 ROW_FORMAT=fixed; show create table t2; Table Create Table t2 CREATE TABLE `t2` ( - `a` varchar(12) default NULL + `a` varchar(12) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED drop table t1,t2; CREATE TABLE t1 (a mediumtext); @@ -1284,9 +1326,65 @@ create table t3 SELECT left(a,100000000) FROM t1 UNION SELECT b FROM t2; show create table t3; Table Create Table t3 CREATE TABLE `t3` ( + `left(a,100000000)` mediumtext +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop tables t1,t2,t3; +CREATE TABLE t1 (a longtext); +CREATE TABLE t2 (b varchar(20)); +INSERT INTO t1 VALUES ('a'),('b'); +SELECT left(a,100000000) FROM t1 UNION SELECT b FROM t2; +left(a,100000000) +a +b +create table t3 SELECT left(a,100000000) FROM t1 UNION SELECT b FROM t2; +show create table t3; +Table Create Table +t3 CREATE TABLE `t3` ( `left(a,100000000)` longtext ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop tables t1,t2,t3; +SELECT @tmp_max:= @@global.max_allowed_packet; +@tmp_max:= @@global.max_allowed_packet +1048576 +SET @@global.max_allowed_packet=25000000; +CREATE TABLE t1 (a mediumtext); +CREATE TABLE t2 (b varchar(20)); +INSERT INTO t1 VALUES ('a'); +CREATE TABLE t3 SELECT REPEAT(a,20000000) AS a FROM t1 UNION SELECT b FROM t2; +SHOW CREATE TABLE t3; +Table Create Table +t3 CREATE TABLE `t3` ( + `a` longtext +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLES t1,t3; +CREATE TABLE t1 (a tinytext); +INSERT INTO t1 VALUES ('a'); +CREATE TABLE t3 SELECT REPEAT(a,2) AS a FROM t1 UNION SELECT b FROM t2; +SHOW CREATE TABLE t3; +Table Create Table +t3 CREATE TABLE `t3` ( + `a` varchar(510) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLES t1,t3; +CREATE TABLE t1 (a mediumtext); +INSERT INTO t1 VALUES ('a'); +CREATE TABLE t3 SELECT REPEAT(a,2) AS a FROM t1 UNION SELECT b FROM t2; +SHOW CREATE TABLE t3; +Table Create Table +t3 CREATE TABLE `t3` ( + `a` longtext +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLES t1,t3; +CREATE TABLE t1 (a tinyblob); +INSERT INTO t1 VALUES ('a'); +CREATE TABLE t3 SELECT REPEAT(a,2) AS a FROM t1 UNION SELECT b FROM t2; +SHOW CREATE TABLE t3; +Table Create Table +t3 CREATE TABLE `t3` ( + `a` varbinary(510) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLES t1,t2,t3; +SET @@global.max_allowed_packet:= @tmp_max; create table t1 ( id int not null auto_increment, primary key (id), col1 int); insert into t1 (col1) values (2),(3),(4),(5),(6); select 99 union all select id from t1 order by 1; @@ -1311,14 +1409,14 @@ create table t2 as select *, f6 as f8 from t1 union select *, f7 from t1; show create table t2; Table Create Table t2 CREATE TABLE `t2` ( - `f1` char(1) default NULL, - `f2` char(5) default NULL, - `f3` binary(1) default NULL, - `f4` binary(5) default NULL, - `f5` timestamp NOT NULL default '0000-00-00 00:00:00', - `f6` varchar(1) character set utf8 default NULL, + `f1` char(1) DEFAULT NULL, + `f2` char(5) DEFAULT NULL, + `f3` binary(1) DEFAULT NULL, + `f4` binary(5) DEFAULT NULL, + `f5` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', + `f6` varchar(1) CHARACTER SET utf8 DEFAULT NULL, `f7` text, - `f8` mediumtext character set utf8 + `f8` mediumtext CHARACTER SET utf8 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1, t2; (select avg(1)) union (select avg(1)) union (select avg(1)) union @@ -1441,48 +1539,6 @@ SELECT 1,1; ERROR HY000: Incorrect usage of UNION and ORDER BY DROP TABLE t1,t2; CREATE TABLE t1 (a INT); -INSERT INTO t1 VALUES (1); -SELECT a INTO @v FROM ( -SELECT a FROM t1 -UNION -SELECT a FROM t1 -) alias; -SELECT a INTO OUTFILE 'union.out.file' FROM ( -SELECT a FROM t1 -UNION -SELECT a FROM t1 WHERE 0 -) alias; -SELECT a INTO DUMPFILE 'union.out.file2' FROM ( -SELECT a FROM t1 -UNION -SELECT a FROM t1 WHERE 0 -) alias; -SELECT a FROM ( -SELECT a FROM t1 -UNION -SELECT a INTO @v FROM t1 -) alias; -SELECT a FROM ( -SELECT a FROM t1 -UNION -SELECT a INTO OUTFILE 'union.out.file3' FROM t1 -) alias; -SELECT a FROM ( -SELECT a FROM t1 -UNION -SELECT a INTO DUMPFILE 'union.out.file4' FROM t1 -) alias; -SELECT a FROM t1 UNION SELECT a INTO @v FROM t1; -SELECT a FROM t1 UNION SELECT a INTO OUTFILE 'union.out.file5' FROM t1; -SELECT a FROM t1 UNION SELECT a INTO OUTFILE 'union.out.file6' FROM t1; -SELECT a INTO @v FROM t1 UNION SELECT a FROM t1; -ERROR HY000: Incorrect usage of UNION and INTO -SELECT a INTO OUTFILE 'union.out.file7' FROM t1 UNION SELECT a FROM t1; -ERROR HY000: Incorrect usage of UNION and INTO -SELECT a INTO DUMPFILE 'union.out.file8' FROM t1 UNION SELECT a FROM t1; -ERROR HY000: Incorrect usage of UNION and INTO -DROP TABLE t1; -CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (1), (2), (3); CREATE TABLE t2 SELECT * FROM (SELECT NULL) a UNION SELECT a FROM t1; DESC t2; |