summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
-rw-r--r--mysql-test/main/union.result1
-rw-r--r--mysql-test/main/union.test44
2 files changed, 20 insertions, 25 deletions
diff --git a/mysql-test/main/union.result b/mysql-test/main/union.result
index 717f7e1c4ec..bf65bbd44d1 100644
--- a/mysql-test/main/union.result
+++ b/mysql-test/main/union.result
@@ -1,4 +1,3 @@
-drop table if exists t1,t2,t3,t4,t5,t6;
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);
diff --git a/mysql-test/main/union.test b/mysql-test/main/union.test
index a3a672e2937..eff3cfd260b 100644
--- a/mysql-test/main/union.test
+++ b/mysql-test/main/union.test
@@ -2,10 +2,6 @@
# Test of unions
#
---disable_warnings
-drop table if exists t1,t2,t3,t4,t5,t6;
---enable_warnings
-
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);
@@ -23,7 +19,7 @@ select 't1',b,count(*) from t1 group by b UNION select 't2',b,count(*) from t2 g
(select a,b from t1 limit 2) union all (select a,b from t2 order by a) limit 4;
(select a,b from t1 limit 2) union all (select a,b from t2 order by a limit 1);
(select a,b from t1 limit 2) union all (select a,b from t2 order by a limit 1) order by b desc;
---error 1250
+--error ER_TABLENAME_NOT_ALLOWED_HERE
(select a,b from t1 limit 2) union all (select a,b from t2 order by a limit 1) order by t1.b;
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;
--disable_view_protocol
@@ -42,13 +38,13 @@ select found_rows();
explain select a,b from t1 union all select a,b from t2;
---error 1054
+--error ER_BAD_FIELD_ERROR
explain select xx from t1 union select 1;
---error 1222
+--error ER_WRONG_NUMBER_OF_COLUMNS_IN_SELECT
explain select a,b from t1 union select 1;
---error 1222
+--error ER_WRONG_NUMBER_OF_COLUMNS_IN_SELECT
explain select 1 union select a,b from t1 union select 1;
---error 1222
+--error ER_WRONG_NUMBER_OF_COLUMNS_IN_SELECT
explain select a,b from t1 union select 1 limit 0;
--error ER_PARSE_ERROR
@@ -60,19 +56,19 @@ select a,b from t1 order by a union select a,b from t2;
--error ER_PARSE_ERROR
insert into t3 select a from t1 order by a union select a from t2;
---error 1222
+--error ER_WRONG_NUMBER_OF_COLUMNS_IN_SELECT
create table t3 select a,b from t1 union select a from t2;
---error 1222
+--error ER_WRONG_NUMBER_OF_COLUMNS_IN_SELECT
select a,b from t1 union select a from t2;
---error 1222
+--error ER_WRONG_NUMBER_OF_COLUMNS_IN_SELECT
select * from t1 union select a from t2;
---error 1222
+--error ER_WRONG_NUMBER_OF_COLUMNS_IN_SELECT
select a from t1 union select * from t2;
---error 1234
+--error ER_CANT_USE_OPTION_HERE
select * from t1 union select SQL_BUFFER_RESULT * from t2;
# Test CREATE, INSERT and REPLACE
@@ -86,13 +82,13 @@ drop table t1,t2,t3;
#
# Test some unions without tables
#
---error 1096
+--error ER_NO_TABLES_USED
select * union select 1;
select 1 as a,(select a union select a);
---error 1054
+--error ER_BAD_FIELD_ERROR
(select 1) union (select 2) order by 0;
SELECT @a:=1 UNION SELECT @a:=@a+1;
---error 1054
+--error ER_BAD_FIELD_ERROR
(SELECT 1) UNION (SELECT 2) ORDER BY (SELECT a);
(SELECT 1,3) UNION (SELECT 2,1) ORDER BY (SELECT 2);
@@ -294,7 +290,7 @@ SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY a desc LIMIT 1;
(SELECT * FROM t1 ORDER by a) UNION ALL (SELECT * FROM t2 ORDER BY a) ORDER BY A desc LIMIT 4;
# Wrong usage
---error 1234
+--error ER_CANT_USE_OPTION_HERE
(SELECT * FROM t1) UNION all (SELECT SQL_CALC_FOUND_ROWS * FROM t2) LIMIT 1;
create temporary table t1 select a from t1 union select a from t2;
@@ -477,7 +473,7 @@ create table t1 select 1 union select -1;
select * from t1;
show create table t1;
drop table t1;
--- error 1267
+-- error ER_CANT_AGGREGATE_2COLLATIONS
create table t1 select _latin1"test" union select _latin2"testt" ;
create table t1 select _latin2"test" union select _latin2"testt" ;
show create table t1;
@@ -585,7 +581,7 @@ set sql_select_limit=default;
#
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
+--error ER_BAD_FIELD_ERROR
explain (select * from t1) union (select * from t2) order by not_existing_column;
drop table t1, t2;
@@ -687,7 +683,7 @@ 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
+--error ER_CANT_AGGREGATE_NCOLLATIONS
create table t1 as
(select a from t2) union
(select b from t2);
@@ -984,7 +980,7 @@ CREATE TABLE t1 (a int);
INSERT INTO t1 VALUES (3),(1),(2),(4),(1);
SELECT a FROM (SELECT a FROM t1 UNION SELECT a FROM t1 ORDER BY a) AS test;
---error 1054
+--error ER_BAD_FIELD_ERROR
SELECT a FROM (SELECT a FROM t1 UNION SELECT a FROM t1 ORDER BY c) AS test;
DROP TABLE t1;
@@ -992,11 +988,11 @@ DROP TABLE t1;
#
# Bug#23345: Wrongly allowed INTO in a non-last select of a UNION.
#
---error 1221
+--error ER_WRONG_USAGE
(select 1 into @var) union (select 1);
(select 1) union (select 1 into @var);
select @var;
---error 1172
+--error ER_TOO_MANY_ROWS
(select 2) union (select 1 into @var);
#