diff options
Diffstat (limited to 'mysql-test/main/union.result')
| -rw-r--r-- | mysql-test/main/union.result | 39 |
1 files changed, 27 insertions, 12 deletions
diff --git a/mysql-test/main/union.result b/mysql-test/main/union.result index 3c8ef81537e..e3a69f54949 100644 --- a/mysql-test/main/union.result +++ b/mysql-test/main/union.result @@ -81,7 +81,7 @@ a b 2 b 1 a (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 +ERROR 42000: Table 't1' from one of the SELECTs cannot be used in 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 filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 4 100.00 @@ -494,7 +494,7 @@ drop temporary table t1; create table t1 select a from t1 union select a from t2; ERROR 42S01: Table 't1' already exists select a from t1 union select a from t2 order by t2.a; -ERROR 42000: Table 't2' from one of the SELECTs cannot be used in field list +ERROR 42000: Table 't2' from one of the SELECTs cannot be used in ORDER clause drop table t1,t2; select length(version()) > 1 as `*` UNION select 2; * @@ -561,12 +561,12 @@ a b 3 3 4 4 set @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity,@save_optimizer_switch=@@optimizer_switch; -SET optimizer_switch='outer_join_with_cache=off',@@optimizer_use_condition_selectivity=1; +SET optimizer_switch='outer_join_with_cache=off',@@optimizer_use_condition_selectivity=4; explain (select * from t1 where a=1 and b=10) union (select straight_join t1.a,t2.a from t1,t2 where t1.a=t2.a); 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 4 Using where; Using index; Using join buffer (flat, BNL join) +2 UNION t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using index NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity,@@optimizer_switch=@save_optimizer_switch; explain (select * from t1 where a=1) union (select * from t1 where b=1); @@ -1533,12 +1533,15 @@ SELECT a FROM (SELECT a FROM t1 UNION SELECT a FROM t1 ORDER BY c) AS test; ERROR 42S22: Unknown column 'c' in 'order clause' DROP TABLE t1; (select 1 into @var) union (select 1); -ERROR HY000: Incorrect usage of UNION and INTO +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'into @var) union (select 1)' at line 1 (select 1) union (select 1 into @var); -select @var; -@var -1 +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'into @var)' at line 1 (select 2) union (select 1 into @var); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'into @var)' at line 1 +(select 1) union (select 1) into @var; +Warnings: +Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead +(select 2) union (select 1) into @var; ERROR 42000: Result consisted of more than one row CREATE TABLE t1 (a int); INSERT INTO t1 VALUES (10), (20); @@ -1664,8 +1667,20 @@ UNION SELECT a FROM t1 WHERE 0 ) alias; SELECT a FROM t1 UNION SELECT a INTO @v FROM t1; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'FROM t1' at line 1 SELECT a FROM t1 UNION SELECT a INTO OUTFILE 'union.out.file5' FROM t1; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'FROM t1' at line 1 SELECT a FROM t1 UNION SELECT a INTO OUTFILE 'union.out.file6' FROM t1; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'FROM t1' at line 1 +SELECT a FROM t1 UNION SELECT a FROM t1 INTO @v ; +Warnings: +Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead +SELECT a FROM t1 UNION SELECT a FROM t1 INTO OUTFILE 'union.out.file5'; +Warnings: +Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead +SELECT a FROM t1 UNION SELECT a FROM t1 INTO OUTFILE 'union.out.file6'; +Warnings: +Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead SELECT a INTO @v FROM t1 UNION SELECT a FROM t1; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION SELECT a FROM t1' at line 1 SELECT a INTO OUTFILE 'union.out.file7' FROM t1 UNION SELECT a FROM t1; @@ -2035,7 +2050,7 @@ insert t1 values (1),(2),(3),(1); explain select 1 from dual where exists (select max(a) from t1 group by a union select a+2 from t1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used -2 SUBQUERY t1 ALL NULL NULL NULL NULL 4 Using temporary; Using filesort +2 SUBQUERY t1 ALL NULL NULL NULL NULL 4 Using temporary 3 UNION t1 ALL NULL NULL NULL NULL 4 NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL drop table t1; @@ -2173,7 +2188,7 @@ select id from t5 where name = (select name from t3 where id = t1.product_id)) l id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system PRIMARY NULL NULL NULL 1 2 SUBQUERY t4 eq_ref PRIMARY PRIMARY 4 func 1 Using where -3 SUBQUERY t2 ref PRIMARY PRIMARY 4 const 3 Using index +3 SUBQUERY t2 ref PRIMARY PRIMARY 4 const 4 Using index 4 UNION t2 ref PRIMARY PRIMARY 4 func 1 Using where; Using index 5 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 6 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables @@ -2316,9 +2331,9 @@ GROUP BY i HAVING i = 10; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used -2 UNION NULL NULL NULL NULL NULL NULL NULL NULL Impossible HAVING noticed after reading const tables +2 UNION NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables Warnings: -Note 1003 /* select#1 */ select 1 AS `1`,2 AS `2` union all /* select#2 */ select 1 AS `i`,count(0) AS `COUNT(*)` from `test`.`t2` where 1 group by 1 having 0 +Note 1003 /* select#1 */ select 1 AS `1`,2 AS `2` union all /* select#2 */ select 1 AS `i`,count(0) AS `COUNT(*)` from `test`.`t2` where 0 group by 1 having 1 DROP TABLE t1,t2; # # Start of 10.3 tests |