diff options
Diffstat (limited to 'mysql-test/r/union.result')
| -rw-r--r-- | mysql-test/r/union.result | 94 |
1 files changed, 93 insertions, 1 deletions
diff --git a/mysql-test/r/union.result b/mysql-test/r/union.result index ef1749eda52..9b7a1c57aa1 100644 --- a/mysql-test/r/union.result +++ b/mysql-test/r/union.result @@ -89,6 +89,10 @@ id select_type table type possible_keys key key_len ref rows filtered Extra NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL NULL Using filesort Warnings: Note 1003 (select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` limit 2) union all (select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` order by `test`.`t2`.`a` limit 1) order by `b` desc +select count(*) from ( +(select a,b from t1 limit 2) union all (select a,b from t2 order by a)) q; +count(*) +6 (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 @@ -96,6 +100,10 @@ a b select found_rows(); found_rows() 6 +select count(*) from ( +select a,b from t1 union all select a,b from t2) q; +count(*) +8 select sql_calc_found_rows a,b from t1 union all select a,b from t2 limit 2; a b 1 a @@ -308,12 +316,20 @@ create table t1 (a int); insert into t1 values (1),(2),(3); create table t2 (a int); insert into t2 values (3),(4),(5); +SELECT COUNT(*) FROM ( +(SELECT * FROM t1) UNION all (SELECT * FROM t2)) q; +COUNT(*) +6 (SELECT SQL_CALC_FOUND_ROWS * FROM t1) UNION all (SELECT * FROM t2) LIMIT 1; a 1 select found_rows(); found_rows() 6 +SELECT COUNT(*) FROM ( +(SELECT * FROM t1 LIMIT 1) UNION all (SELECT * FROM t2)) q; +COUNT(*) +4 (SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 1) UNION all (SELECT * FROM t2) LIMIT 2; a 1 @@ -321,6 +337,10 @@ a select found_rows(); found_rows() 4 +SELECT COUNT(*) FROM ( +(SELECT * FROM t1 LIMIT 1) UNION all (SELECT * FROM t2)) q; +COUNT(*) +4 (SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 1) UNION all (SELECT * FROM t2); a 1 @@ -330,6 +350,10 @@ a select found_rows(); found_rows() 4 +SELECT COUNT(*) FROM ( +(SELECT * FROM t1) UNION all (SELECT * FROM t2 LIMIT 1)) q; +COUNT(*) +4 (SELECT SQL_CALC_FOUND_ROWS * FROM t1) UNION all (SELECT * FROM t2 LIMIT 1); a 1 @@ -345,6 +369,16 @@ a select found_rows(); found_rows() 4 +SELECT COUNT(*) FROM ( +(SELECT * FROM t1 LIMIT 1) UNION SELECT * FROM t2) q; +COUNT(*) +4 +(SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 1) UNION SELECT * FROM t2 LIMIT 1; +a +1 +select found_rows(); +found_rows() +4 SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 1 UNION all SELECT * FROM t2 LIMIT 2; ERROR HY000: Incorrect usage of UNION and LIMIT SELECT SQL_CALC_FOUND_ROWS * FROM t1 UNION all SELECT * FROM t2 LIMIT 2; @@ -354,13 +388,29 @@ a select found_rows(); found_rows() 6 +SELECT COUNT(*) FROM ( +SELECT * FROM t1 UNION SELECT * FROM t2) q; +COUNT(*) +5 SELECT SQL_CALC_FOUND_ROWS * FROM t1 UNION SELECT * FROM t2 LIMIT 2; a 1 2 +SELECT COUNT(*) FROM ( +(SELECT * FROM t1 LIMIT 1) UNION all SELECT * FROM t2) q; +COUNT(*) +4 +(SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 1) UNION all SELECT * FROM t2 LIMIT 2; +a +1 +3 select found_rows(); found_rows() -5 +4 +SELECT COUNT(*) FROM ( +SELECT * FROM t1 UNION all SELECT * FROM t2) q; +COUNT(*) +6 SELECT SQL_CALC_FOUND_ROWS * FROM t1 UNION SELECT * FROM t2 LIMIT 100; a 1 @@ -373,10 +423,39 @@ found_rows() 5 SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 100 UNION SELECT * FROM t2; ERROR HY000: Incorrect usage of UNION and LIMIT +SELECT COUNT(*) FROM ( +(SELECT * FROM t1 LIMIT 100) UNION SELECT * FROM t2) q; +COUNT(*) +5 +(SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 100) UNION SELECT * FROM t2; +a +1 +2 +3 +4 +5 SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 1 UNION SELECT * FROM t2; ERROR HY000: Incorrect usage of UNION and LIMIT +SELECT COUNT(*) FROM ( +(SELECT * FROM t1 LIMIT 1) UNION SELECT * FROM t2) q; +COUNT(*) +4 +(SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 1) UNION SELECT * FROM t2; +a +1 +3 +4 +5 SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 1 UNION SELECT * FROM t2 LIMIT 2; ERROR HY000: Incorrect usage of UNION and LIMIT +SELECT COUNT(*) FROM ( +(SELECT * FROM t1 LIMIT 1) UNION SELECT * FROM t2) q; +COUNT(*) +4 +(SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 1) UNION SELECT * FROM t2 LIMIT 2; +a +1 +3 SELECT SQL_CALC_FOUND_ROWS * FROM t1 UNION SELECT * FROM t2 LIMIT 2,2; a 3 @@ -384,8 +463,21 @@ a select found_rows(); found_rows() 5 +SELECT COUNT(*) FROM ( +SELECT * FROM t1 UNION SELECT * FROM t2) q; +COUNT(*) +5 SELECT SQL_CALC_FOUND_ROWS * FROM t1 limit 2,2 UNION SELECT * FROM t2; ERROR HY000: Incorrect usage of UNION and LIMIT +SELECT COUNT(*) FROM ( +(SELECT * FROM t1 limit 2,2) UNION SELECT * FROM t2) q; +COUNT(*) +3 +(SELECT SQL_CALC_FOUND_ROWS * FROM t1 limit 2,2) UNION SELECT * FROM t2; +a +3 +4 +5 SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY a desc LIMIT 1; a 5 |