diff options
Diffstat (limited to 'mysql-test/r/union.result')
| -rw-r--r-- | mysql-test/r/union.result | 204 |
1 files changed, 204 insertions, 0 deletions
diff --git a/mysql-test/r/union.result b/mysql-test/r/union.result new file mode 100644 index 00000000000..2af9d5a3584 --- /dev/null +++ b/mysql-test/r/union.result @@ -0,0 +1,204 @@ +drop table if exists t1,t2,t3; +CREATE TABLE t1 (a int not null, b char (10) not null); +insert into t1 values(1,'a'),(2,'b'),(3,'c'),(3,'c'); +CREATE TABLE t2 (a int not null, b char (10) not null); +insert into t2 values (3,'c'),(4,'d'),(5,'f'),(6,'e'); +select a,b from t1 union select a,b from t2; +a b +1 a +2 b +3 c +4 d +5 f +6 e +select a,b from t1 union all select a,b from t2; +a b +1 a +2 b +3 c +3 c +3 c +4 d +5 f +6 e +select a,b from t1 union all select a,b from t2 order by b; +a b +1 a +2 b +3 c +3 c +3 c +4 d +6 e +5 f +select a,b from t1 union all select a,b from t2 union select 7,'g'; +a b +1 a +2 b +3 c +3 c +3 c +4 d +5 f +6 e +7 g +select 0,'#' union select a,b from t1 union all select a,b from t2 union select 7,'gg'; +0 # +0 # +1 a +2 b +3 c +3 c +3 c +4 d +5 f +6 e +7 g +select a,b from t1 union select a,b from t1; +a b +1 a +2 b +3 c +select 't1',b,count(*) from t1 group by b UNION select 't2',b,count(*) from t2 group by b; +t1 b count(*) +t1 a 1 +t1 b 1 +t1 c 2 +t2 c 1 +t2 d 1 +t2 e 1 +t2 f 1 +(select a,b from t1 limit 2) union all (select a,b from t2 order by a) limit 4; +a b +1 a +2 b +3 c +4 d +(select a,b from t1 limit 2) union all (select a,b from t2 order by a limit 1); +a b +1 a +2 b +3 c +(select a,b from t1 limit 2) union all (select a,b from t2 order by a limit 1) order by b desc; +a b +3 c +2 b +1 a +explain (select a,b from t1 limit 2) union all (select a,b from t2 order by a limit 1) order by b desc; +table type possible_keys key key_len ref rows Extra +t1 ALL NULL NULL NULL NULL 4 +t2 ALL NULL NULL NULL NULL 4 Using filesort +t1 ALL NULL NULL NULL NULL 4 +(select sql_calc_found_rows a,b from t1 limit 2) union all (select a,b from t2 order by a) limit 2; +a b +1 a +2 b +select found_rows(); +found_rows() +6 +explain select a,b from t1 union all select a,b from t2; +table type possible_keys key key_len ref rows Extra +t1 ALL NULL NULL NULL NULL 4 +t2 ALL NULL NULL NULL NULL 4 +explain select xx from t1 union select 1; +Unknown column 'xx' in 'field list' +explain select a,b from t1 union select 1; +table type possible_keys key key_len ref rows Extra +t1 ALL NULL NULL NULL NULL 4 + 0 0 No tables used +explain select 1 union select a,b from t1 union select 1; +table type possible_keys key key_len ref rows Extra + 0 0 No tables used +t1 ALL NULL NULL NULL NULL 4 + 0 0 No tables used +explain select a,b from t1 union select 1 limit 0; +table type possible_keys key key_len ref rows Extra +t1 ALL NULL NULL NULL NULL 4 + 0 0 Impossible WHERE +select a,b from t1 into outfile 'skr' union select a,b from t2; +Wrong usage of UNION and INTO +select a,b from t1 order by a union select a,b from t2; +Wrong usage of UNION and ORDER BY +insert into t3 select a from t1 order by a union select a from t2; +Wrong usage of UNION and ORDER BY +create table t3 select a,b from t1 union select a from t2; +The used SELECT statements have a different number of columns +select a,b from t1 union select a from t2; +The used SELECT statements have a different number of columns +select * from t1 union select a from t2; +The used SELECT statements have a different number of columns +select a from t1 union select * from t2; +The used SELECT statements have a different number of columns +select * from t1 union select SQL_BUFFER_RESULT * from t2; +Wrong usage/placement of 'SQL_BUFFER_RESULT' +create table t3 select a,b from t1 union all select a,b from t2; +insert into t3 select a,b from t1 union all select a,b from t2; +replace into t3 select a,b as c from t1 union all select a,b from t2; +drop table t1,t2,t3; +CREATE TABLE t1 ( +`pseudo` char(35) NOT NULL default '', +`pseudo1` char(35) NOT NULL default '', +`same` tinyint(1) unsigned NOT NULL default '1', +PRIMARY KEY (`pseudo1`), +KEY `pseudo` (`pseudo`) +) TYPE=MyISAM; +INSERT INTO t1 (pseudo,pseudo1,same) VALUES ('joce', 'testtt', 1),('joce', 'tsestset', 1),('dekad', 'joce', 1); +SELECT pseudo FROM t1 WHERE pseudo1='joce' UNION SELECT pseudo FROM t1 WHERE pseudo='joce'; +pseudo +dekad +joce +SELECT pseudo1 FROM t1 WHERE pseudo1='joce' UNION SELECT pseudo1 FROM t1 WHERE pseudo='joce'; +pseudo1 +joce +testtt +tsestset +SELECT * FROM t1 WHERE pseudo1='joce' UNION SELECT * FROM t1 WHERE pseudo='joce' order by pseudo desc,pseudo1 desc; +pseudo pseudo1 same +joce tsestset 1 +joce testtt 1 +dekad joce 1 +SELECT pseudo1 FROM t1 WHERE pseudo='joce' UNION SELECT pseudo FROM t1 WHERE pseudo1='joce'; +pseudo1 +testtt +tsestset +dekad +SELECT pseudo1 FROM t1 WHERE pseudo='joce' UNION ALL SELECT pseudo FROM t1 WHERE pseudo1='joce'; +pseudo1 +testtt +tsestset +dekad +SELECT pseudo1 FROM t1 WHERE pseudo='joce' UNION SELECT 1; +pseudo1 +testtt +tsestset +1 +drop table t1; +drop table if exists t1,t2; +create table t1 (a int); +create table t2 (a int); +insert into t1 values (1),(2),(3),(4),(5); +insert into t2 values (11),(12),(13),(14),(15); +(select * from t1 limit 2) union (select * from t2 limit 3) limit 4; +a +1 +2 +11 +12 +(select * from t1 limit 2) union (select * from t2 limit 3); +a +1 +2 +11 +12 +13 +(select * from t1 limit 2) union (select * from t2 limit 20,3); +a +1 +2 +set SQL_SELECT_LIMIT=2; +(select * from t1 limit 1) union (select * from t2 limit 3); +a +1 +11 +set SQL_SELECT_LIMIT=DEFAULT; +drop table t1,t2; |