Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.5
-
None
Description
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)
|
|
Attachments
Issue Links
- relates to
-
MDEV-18844 Implement EXCEPT ALL and INTERSECT ALL operations
-
- Closed
-
A simpler test case:
For this test case we have:
MariaDB [test]> set big_tables=0;Query OK, 0 rows affected, 1 warning (0.000 sec)MariaDB [test]> select * from t1 except all select * from t1 except select * from t1 union all select * from t1;+------+------+| a | b |+------+------+| 2 | 2 || 1 | 1 |+------+------+2 rows in set (0.001 sec)MariaDB [test]> set big_tables=1;Query OK, 0 rows affected, 1 warning (0.000 sec)MariaDB [test]> select * from t1 except all select * from t1 except select * from t1 union all select * from t1;Empty set (0.002 sec)