|
test case is a part of the set_operation.test:
create table t1 (a int, b int) engine=MyISAM;
|
create table t2 (c int, d int) engine=MyISAM;
|
create table t3 (e int, f int) engine=MyISAM;
|
create table t4 (g int, h int) engine=MyISAM;
|
insert into t1 values (1,1),(2,2),(3,3),(2,2),(3,3);
|
insert into t2 values (2,2),(3,3),(5,5),(2,2),(2,2),(3,3);
|
insert into t3 values (4,4),(2,2),(2,2),(1,1),(3,3);
|
insert into t4 values (3,3),(3,3);
|
|
let $q=
|
select * from t1
|
INTERSECT ALL
|
select * from t2
|
UNION ALL
|
select * from t3
|
EXCEPT ALL
|
select * from t1
|
UNION
|
select * from t4
|
EXCEPT
|
select * from t3
|
UNION ALL
|
select * from t1;
|
|
eval $q;
|
|
set big_tables=1;
|
|
eval $q;
|
MariaDB [test1]> select * from t1
|
-> INTERSECT ALL
|
-> select * from t2
|
-> UNION ALL
|
-> select * from t3
|
-> EXCEPT ALL
|
-> select * from t1
|
-> UNION
|
-> select * from t4
|
-> EXCEPT
|
-> select * from t3
|
-> UNION ALL
|
-> select * from t1;
|
+------+------+
|
| a | b |
|
+------+------+
|
| 2 | 2 |
|
| 2 | 2 |
|
| 1 | 1 |
|
| 3 | 3 |
|
| 3 | 3 |
|
+------+------+
|
5 rows in set (0.009 sec)
|
|
MariaDB [test1]> set big_tables=1;
|
Query OK, 0 rows affected, 1 warning (0.001 sec)
|
|
MariaDB [test1]> select * from t1
|
-> INTERSECT ALL
|
-> select * from t2
|
-> UNION ALL
|
-> select * from t3
|
-> EXCEPT ALL
|
-> select * from t1
|
-> UNION
|
-> select * from t4
|
-> EXCEPT
|
-> select * from t3
|
-> UNION ALL
|
-> select * from t1;
|
Empty set (0.013 sec)
|
|
MariaDB [test1]> select * from t1 INTERSECT ALL select * from t2 UNION ALL select * from t3 EXCEPT ALL select * from t1 UNION select * from t4 EXCEPT select * from t3 UNION (select * from t1);
|
+------+------+
|
| a | b |
|
+------+------+
|
| 2 | 2 |
|
| 1 | 1 |
|
| 3 | 3 |
|
+------+------+
|
3 rows in set (0.013 sec)
|
|
MariaDB [test1]> select * from t1 INTERSECT ALL select * from t2 UNION ALL select * from t3 EXCEPT ALL select * from t1 UNION select * from t4 EXCEPT select * from t3 UNION all (select * from t1);
|
Empty set (0.007 sec)
|
|
|