diff options
Diffstat (limited to 'mysql-test/main/union.test')
| -rw-r--r-- | mysql-test/main/union.test | 50 |
1 files changed, 28 insertions, 22 deletions
diff --git a/mysql-test/main/union.test b/mysql-test/main/union.test index e2d7cc09947..4cebdcff9bb 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); --sorted_result (SELECT 1,3) UNION (SELECT 2,1) ORDER BY (SELECT 2); @@ -295,7 +291,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; @@ -478,7 +474,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; @@ -586,7 +582,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; @@ -688,7 +684,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); @@ -985,7 +981,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; @@ -998,6 +994,7 @@ DROP TABLE t1; (select 1 into @var) union (select 1); --error ER_PARSE_ERROR (select 1) union (select 1 into @var); +select @var; --error ER_PARSE_ERROR (select 2) union (select 1 into @var); --enable_prepare_warnings @@ -2002,5 +1999,14 @@ union (select 0 as a, 99 as b, drop table t1,t2; --echo # +--echo # MDEV-30066 (limit + offset) union all (...) limit = incorrect result +--echo # +create table t1(id int primary key auto_increment, c1 int); +insert into t1(c1) values(1),(2),(3); +(select * from t1 where c1>=1 order by c1 desc limit 2,1) union all (select * from t1 where c1>1 order by c1 desc); +(select * from t1 where c1>=1 order by c1 desc limit 2,1) union all (select * from t1 where c1>1 order by c1 desc) limit 2; +drop table t1; + +--echo # --echo # End of 10.3 tests --echo # |