diff options
Diffstat (limited to 'mysql-test/main/union.test')
| -rw-r--r-- | mysql-test/main/union.test | 44 |
1 files changed, 20 insertions, 24 deletions
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); # |