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 (2,2),(4,4),(1,1); create view v0(g, h) as select a,c from t1,t2; # test optimization select * from t1 INTERSECT ALL select * from t2 INTERSECT ALL select * from t3; a b 2 2 2 2 3 3 EXPLAIN EXTENDED select * from t1 INTERSECT ALL select * from t2 INTERSECT ALL select * from t3; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 5 100.00 2 INTERSECT t2 ALL NULL NULL NULL NULL 6 100.00 3 INTERSECT t3 ALL NULL NULL NULL NULL 5 100.00 NULL INTERSECT RESULT ALL NULL NULL NULL NULL NULL NULL Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` intersect all /* select#2 */ select `test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` intersect all /* select#3 */ select `test`.`t3`.`e` AS `e`,`test`.`t3`.`f` AS `f` from `test`.`t3` select * from t1 INTERSECT ALL select * from t2 INTERSECT ALL select * from t3 INTERSECT select * from t1; a b 2 2 3 3 EXPLAIN EXTENDED select * from t1 INTERSECT ALL select * from t2 INTERSECT ALL select * from t3 INTERSECT select * from t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 5 100.00 2 INTERSECT t2 ALL NULL NULL NULL NULL 6 100.00 3 INTERSECT t3 ALL NULL NULL NULL NULL 5 100.00 4 INTERSECT t1 ALL NULL NULL NULL NULL 5 100.00 NULL INTERSECT RESULT ALL NULL NULL NULL NULL NULL NULL Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` intersect /* select#2 */ select `test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` intersect /* select#3 */ select `test`.`t3`.`e` AS `e`,`test`.`t3`.`f` AS `f` from `test`.`t3` intersect /* select#4 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` select * from t1 INTERSECT ALL select * from t2 INTERSECT ALL select * from t3 EXCEPT ALL select * from t4; a b 2 2 3 3 EXPLAIN EXTENDED select * from t1 INTERSECT ALL select * from t2 INTERSECT ALL select * from t3 EXCEPT ALL select * from t4; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 5 100.00 2 INTERSECT t2 ALL NULL NULL NULL NULL 6 100.00 3 INTERSECT t3 ALL NULL NULL NULL NULL 5 100.00 4 EXCEPT t4 ALL NULL NULL NULL NULL 3 100.00 NULL UNIT RESULT ALL NULL NULL NULL NULL NULL NULL Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` intersect all /* select#2 */ select `test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` intersect all /* select#3 */ select `test`.`t3`.`e` AS `e`,`test`.`t3`.`f` AS `f` from `test`.`t3` except all /* select#4 */ select `test`.`t4`.`g` AS `g`,`test`.`t4`.`h` AS `h` from `test`.`t4` select * from t1 INTERSECT select * from t2 EXCEPT ALL select * from t4; a b 3 3 EXPLAIN EXTENDED select * from t1 INTERSECT select * from t2 EXCEPT ALL select * from t4; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 5 100.00 2 INTERSECT t2 ALL NULL NULL NULL NULL 6 100.00 3 EXCEPT t4 ALL NULL NULL NULL NULL 3 100.00 NULL UNIT RESULT ALL NULL NULL NULL NULL NULL NULL Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` intersect /* select#2 */ select `test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` except /* select#3 */ select `test`.`t4`.`g` AS `g`,`test`.`t4`.`h` AS `h` from `test`.`t4` insert into t4 values (1,1),(9,9); select * from t1 UNION ALL select * from t2 UNION ALL select * from t3 EXCEPT select * from t4; a b 3 3 5 5 EXPLAIN EXTENDED select * from t1 UNION ALL select * from t2 UNION ALL select * from t3 EXCEPT select * from t4; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 5 100.00 2 UNION t2 ALL NULL NULL NULL NULL 6 100.00 3 UNION t3 ALL NULL NULL NULL NULL 5 100.00 4 EXCEPT t4 ALL NULL NULL NULL NULL 5 100.00 NULL UNIT RESULT ALL NULL NULL NULL NULL NULL NULL Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` union /* select#2 */ select `test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` union /* select#3 */ select `test`.`t3`.`e` AS `e`,`test`.`t3`.`f` AS `f` from `test`.`t3` except /* select#4 */ select `test`.`t4`.`g` AS `g`,`test`.`t4`.`h` AS `h` from `test`.`t4` delete from t4; insert into t4 values (3,3),(3,3); 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 EXPLAIN EXTENDED 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; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 5 100.00 2 INTERSECT t2 ALL NULL NULL NULL NULL 6 100.00 3 UNION t3 ALL NULL NULL NULL NULL 5 100.00 4 EXCEPT t1 ALL NULL NULL NULL NULL 5 100.00 5 UNION t4 ALL NULL NULL NULL NULL 2 100.00 6 EXCEPT t3 ALL NULL NULL NULL NULL 5 100.00 7 UNION t1 ALL NULL NULL NULL NULL 5 100.00 NULL UNIT RESULT ALL NULL NULL NULL NULL NULL NULL Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` intersect all /* select#2 */ select `test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` union all /* select#3 */ select `test`.`t3`.`e` AS `e`,`test`.`t3`.`f` AS `f` from `test`.`t3` except all /* select#4 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` union /* select#5 */ select `test`.`t4`.`g` AS `g`,`test`.`t4`.`h` AS `h` from `test`.`t4` except /* select#6 */ select `test`.`t3`.`e` AS `e`,`test`.`t3`.`f` AS `f` from `test`.`t3` union all /* select#7 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` drop table t4; # test optimization with brackets ( (select 1 except select 5 union all select 6) union (select 2 intersect all select 3 intersect all select 4) except (select 7 intersect all select 8) ) union all (select 9 union all select 10) except all select 11; 1 1 6 9 10 EXPLAIN EXTENDED ( (select 1 except select 5 union all select 6) union (select 2 intersect all select 3 intersect all select 4) except (select 7 intersect all select 8) ) union all (select 9 union all select 10) except all select 11; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY ALL NULL NULL NULL NULL 4 100.00 8 DERIVED ALL NULL NULL NULL NULL 2 100.00 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 3 EXCEPT NULL NULL NULL NULL NULL NULL NULL NULL No tables used 4 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used NULL UNIT RESULT ALL NULL NULL NULL NULL NULL NULL 9 UNION ALL NULL NULL NULL NULL 2 100.00 5 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 6 INTERSECT NULL NULL NULL NULL NULL NULL NULL NULL No tables used 7 INTERSECT NULL NULL NULL NULL NULL NULL NULL NULL No tables used NULL INTERSECT RESULT ALL NULL NULL NULL NULL NULL NULL 12 EXCEPT ALL NULL NULL NULL NULL 2 100.00 10 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 11 INTERSECT NULL NULL NULL NULL NULL NULL NULL NULL No tables used NULL INTERSECT RESULT ALL NULL NULL NULL NULL NULL NULL NULL UNIT RESULT ALL NULL NULL NULL NULL NULL NULL 15 UNION ALL NULL NULL NULL NULL 2 100.00 13 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 14 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used 16 EXCEPT NULL NULL NULL NULL NULL NULL NULL NULL No tables used NULL UNIT RESULT ALL NULL NULL NULL NULL NULL NULL Warnings: Note 1003 /* select#1 */ select `__14`.`1` AS `1` from (/* select#8 */ select `__7`.`1` AS `1` from (/* select#2 */ select 1 AS `1` except /* select#3 */ select 5 AS `5` union /* select#4 */ select 6 AS `6`) `__7` union /* select#9 */ select `__8`.`2` AS `2` from (/* select#5 */ select 2 AS `2` intersect /* select#6 */ select 3 AS `3` intersect /* select#7 */ select 4 AS `4`) `__8` except /* select#12 */ select `__11`.`7` AS `7` from (/* select#10 */ select 7 AS `7` intersect /* select#11 */ select 8 AS `8`) `__11`) `__14` union all /* select#15 */ select `__15`.`9` AS `9` from (/* select#13 */ select 9 AS `9` union all /* select#14 */ select 10 AS `10`) `__15` except all /* select#16 */ select 11 AS `11` (select 1 union all select 2) union (select 3 union all select 4); 1 1 2 3 4 EXPLAIN EXTENDED (select 1 union all select 2) union (select 3 union all select 4); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY ALL NULL NULL NULL NULL 2 100.00 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 3 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used 6 UNION ALL NULL NULL NULL NULL 2 100.00 4 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 5 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used NULL UNION RESULT ALL NULL NULL NULL NULL NULL NULL Warnings: Note 1003 /* select#1 */ select `__5`.`1` AS `1` from (/* select#2 */ select 1 AS `1` union /* select#3 */ select 2 AS `2`) `__5` union /* select#6 */ select `__6`.`3` AS `3` from (/* select#4 */ select 3 AS `3` union /* select#5 */ select 4 AS `4`) `__6` (select 1 intersect all select 2) except select 3; 1 EXPLAIN EXTENDED (select 1 intersect all select 2) except select 3; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY ALL NULL NULL NULL NULL 2 100.00 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 3 INTERSECT NULL NULL NULL NULL NULL NULL NULL NULL No tables used NULL INTERSECT RESULT ALL NULL NULL NULL NULL NULL NULL 4 EXCEPT NULL NULL NULL NULL NULL NULL NULL NULL No tables used NULL EXCEPT RESULT ALL NULL NULL NULL NULL NULL NULL Warnings: Note 1003 /* select#1 */ select `__4`.`1` AS `1` from (/* select#2 */ select 1 AS `1` intersect /* select#3 */ select 2 AS `2`) `__4` except /* select#4 */ select 3 AS `3` (select 1 intersect all select 2 intersect all select 3) intersect (select 4 intersect all select 5); 1 EXPLAIN EXTENDED (select 1 intersect all select 2 intersect all select 3) intersect (select 4 intersect all select 5); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY ALL NULL NULL NULL NULL 2 100.00 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 3 INTERSECT NULL NULL NULL NULL NULL NULL NULL NULL No tables used 4 INTERSECT NULL NULL NULL NULL NULL NULL NULL NULL No tables used NULL INTERSECT RESULT ALL NULL NULL NULL NULL NULL NULL 7 INTERSECT ALL NULL NULL NULL NULL 2 100.00 5 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 6 INTERSECT NULL NULL NULL NULL NULL NULL NULL NULL No tables used NULL INTERSECT RESULT ALL NULL NULL NULL NULL NULL NULL NULL INTERSECT RESULT ALL NULL NULL NULL NULL NULL NULL Warnings: Note 1003 /* select#1 */ select `__6`.`1` AS `1` from (/* select#2 */ select 1 AS `1` intersect /* select#3 */ select 2 AS `2` intersect /* select#4 */ select 3 AS `3`) `__6` intersect /* select#7 */ select `__7`.`4` AS `4` from (/* select#5 */ select 4 AS `4` intersect /* select#6 */ select 5 AS `5`) `__7` # test set operations with table value constructor (values (1,1),(1,1),(1,1),(2,2),(2,2),(3,3),(9,9)) INTERSECT ALL (values (1,1),(2,2),(2,2),(3,3),(3,3),(3,3),(8,8)) EXCEPT ALL (values (7,7),(1,1)); 1 1 2 2 2 2 3 3 delete from t1; insert into t1 values(1,1),(1,1),(2,2),(4,4),(9,9); select * from t1 UNION ALL (values (11,12),(3,3),(2,2),(3,3),(4,4),(8,8)) INTERSECT (values (13,14),(7,7),(2,2),(3,3),(1,1)) INTERSECT ALL (values (15,16),(2,2),(1,1)) EXCEPT (values (17,18),(1,1)); a b 2 2 4 4 9 9 # test set operations with derived table select * from ( select * from t1 UNION ALL select * from t2 )dt1 INTERSECT ALL select * from ( select * from t2 EXCEPT ALL select * from t3 )dt2; a b 2 2 3 3 5 5 select * from ( select * from t1 UNION ALL select * from t3 )dt1 EXCEPT ALL select * from ( select * from t2 INTERSECT ALL select * from t2 )dt2; a b 1 1 1 1 4 4 9 9 1 1 4 4 SELECT * from( select * from ( select * from t1 UNION ALL select * from t2 )dt1 INTERSECT ALL select * from ( select * from t2 EXCEPT ALL select * from t3 )dt2 )dt3; a b 2 2 3 3 5 5 # integration test select * from t1 UNION ALL select * from t2 INTERSECT ALL (values (1,1), (2,2), (2,2), (5,5), (2,2)) INTERSECT ALL select * from (select * from t1 union all select * from t1) sq EXCEPT ALL select * from t3 UNION ALL select * from t2 UNION select * from t3 EXCEPT select a,c from t1,t2 UNION ALL select * from v0 where g < 4 UNION ALL select * from t3; a b 1 1 1 2 3 3 9 9 5 5 4 4 1 2 2 2 1 3 1 3 2 3 1 5 1 5 2 5 1 2 1 2 2 2 1 2 1 2 2 2 1 3 1 3 2 3 4 4 2 2 2 2 1 1 3 3 select * from t1 UNION ALL select * from t2 INTERSECT ALL (values (1,1), (2,2), (2,2), (5,5), (2,2)) INTERSECT ALL select * from (select * from t1 union all select * from t1) sq EXCEPT ALL select * from t3 UNION ALL select * from t2 UNION select * from t3 EXCEPT select a,c from t1,t2 UNION ALL select * from v0 where g < 4 UNION ALL select * from t3 ORDER BY a; a b 1 1 1 1 1 2 1 2 1 2 1 2 1 2 1 2 1 3 1 3 1 3 1 3 1 5 1 5 2 2 2 2 2 2 2 2 2 2 2 3 2 3 2 5 3 3 3 3 4 4 4 4 5 5 9 9 select * from ( select * from t1 UNION ALL select * from t2 INTERSECT ALL (values (1,1), (2,2), (2,2), (5,5), (2,2) ) INTERSECT ALL select * from (select * from t1 union all select * from t1) sq EXCEPT ALL select * from t3 UNION ALL select * from t2 UNION select * from t3 EXCEPT select a,c from t1,t2 UNION ALL select * from v0 where g < 4 UNION ALL select * from t3 ) dt; a b 1 1 1 2 3 3 9 9 5 5 4 4 1 2 2 2 1 3 1 3 2 3 1 5 1 5 2 5 1 2 1 2 2 2 1 2 1 2 2 2 1 3 1 3 2 3 4 4 2 2 2 2 1 1 3 3 EXPLAIN select * from t1 UNION ALL select * from t2 INTERSECT ALL (values (1,1), (2,2), (2,2), (5,5), (2,2) ) INTERSECT ALL select * from (select * from t1 union all select * from t1) sq EXCEPT ALL select * from t3 UNION ALL select * from t2 UNION select * from t3 EXCEPT select a,c from t1,t2 UNION ALL select * from v0 where g < 4 UNION ALL select * from t3; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 5 8 UNION ALL NULL NULL NULL NULL 5 2 DERIVED t2 ALL NULL NULL NULL NULL 6 3 INTERSECT NULL NULL NULL NULL NULL NULL NULL No tables used 4 INTERSECT ALL NULL NULL NULL NULL 10 5 DERIVED t1 ALL NULL NULL NULL NULL 5 6 UNION t1 ALL NULL NULL NULL NULL 5 NULL INTERSECT RESULT ALL NULL NULL NULL NULL NULL 7 EXCEPT t3 ALL NULL NULL NULL NULL 5 9 UNION t2 ALL NULL NULL NULL NULL 6 10 UNION t3 ALL NULL NULL NULL NULL 5 11 EXCEPT t1 ALL NULL NULL NULL NULL 5 11 EXCEPT t2 ALL NULL NULL NULL NULL 6 Using join buffer (flat, BNL join) 12 UNION t1 ALL NULL NULL NULL NULL 5 Using where 12 UNION t2 ALL NULL NULL NULL NULL 6 Using join buffer (flat, BNL join) 13 UNION t3 ALL NULL NULL NULL NULL 5 NULL UNIT RESULT ALL NULL NULL NULL NULL NULL EXPLAIN format=json select * from t1 UNION ALL select * from t2 INTERSECT ALL (values (1,1), (2,2), (2,2), (5,5), (2,2) ) INTERSECT ALL select * from (select * from t1 union all select * from t1) sq EXCEPT ALL select * from t3 UNION ALL select * from t2 UNION select * from t3 EXCEPT select a,c from t1,t2 UNION ALL select * from v0 where g < 4 UNION ALL select * from t3; EXPLAIN { "query_block": { "union_result": { "table_name": "", "access_type": "ALL", "query_specifications": [ { "query_block": { "select_id": 1, "nested_loop": [ { "table": { "table_name": "t1", "access_type": "ALL", "rows": 5, "filtered": 100 } } ] } }, { "query_block": { "select_id": 8, "operation": "UNION", "nested_loop": [ { "table": { "table_name": "", "access_type": "ALL", "rows": 5, "filtered": 100, "materialized": { "query_block": { "union_result": { "table_name": "", "access_type": "ALL", "query_specifications": [ { "query_block": { "select_id": 2, "nested_loop": [ { "table": { "table_name": "t2", "access_type": "ALL", "rows": 6, "filtered": 100 } } ] } }, { "query_block": { "select_id": 3, "operation": "INTERSECT", "table": { "message": "No tables used" } } }, { "query_block": { "select_id": 4, "operation": "INTERSECT", "nested_loop": [ { "table": { "table_name": "", "access_type": "ALL", "rows": 10, "filtered": 100, "materialized": { "query_block": { "union_result": { "query_specifications": [ { "query_block": { "select_id": 5, "nested_loop": [ { "table": { "table_name": "t1", "access_type": "ALL", "rows": 5, "filtered": 100 } } ] } }, { "query_block": { "select_id": 6, "operation": "UNION", "nested_loop": [ { "table": { "table_name": "t1", "access_type": "ALL", "rows": 5, "filtered": 100 } } ] } } ] } } } } } ] } } ] } } } } } ] } }, { "query_block": { "select_id": 7, "operation": "EXCEPT", "nested_loop": [ { "table": { "table_name": "t3", "access_type": "ALL", "rows": 5, "filtered": 100 } } ] } }, { "query_block": { "select_id": 9, "operation": "UNION", "nested_loop": [ { "table": { "table_name": "t2", "access_type": "ALL", "rows": 6, "filtered": 100 } } ] } }, { "query_block": { "select_id": 10, "operation": "UNION", "nested_loop": [ { "table": { "table_name": "t3", "access_type": "ALL", "rows": 5, "filtered": 100 } } ] } }, { "query_block": { "select_id": 11, "operation": "EXCEPT", "nested_loop": [ { "table": { "table_name": "t1", "access_type": "ALL", "rows": 5, "filtered": 100 } }, { "block-nl-join": { "table": { "table_name": "t2", "access_type": "ALL", "rows": 6, "filtered": 100 }, "buffer_type": "flat", "buffer_size": "65", "join_type": "BNL" } } ] } }, { "query_block": { "select_id": 12, "operation": "UNION", "nested_loop": [ { "table": { "table_name": "t1", "access_type": "ALL", "rows": 5, "filtered": 100, "attached_condition": "t1.a < 4" } }, { "block-nl-join": { "table": { "table_name": "t2", "access_type": "ALL", "rows": 6, "filtered": 100 }, "buffer_type": "flat", "buffer_size": "65", "join_type": "BNL" } } ] } }, { "query_block": { "select_id": 13, "operation": "UNION", "nested_loop": [ { "table": { "table_name": "t3", "access_type": "ALL", "rows": 5, "filtered": 100 } } ] } } ] } } } EXPLAIN EXTENDED select * from t1 UNION ALL select * from t2 INTERSECT ALL (values (1,1), (2,2), (2,2), (5,5), (2,2) ) INTERSECT ALL select * from (select * from t1 union all select * from t1) sq EXCEPT ALL select * from t3 UNION ALL select * from t2 UNION select * from t3 EXCEPT select a,c from t1,t2 UNION ALL select * from v0 where g < 4 UNION ALL select * from t3; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 5 100.00 8 UNION ALL NULL NULL NULL NULL 5 100.00 2 DERIVED t2 ALL NULL NULL NULL NULL 6 100.00 3 INTERSECT NULL NULL NULL NULL NULL NULL NULL NULL No tables used 4 INTERSECT ALL NULL NULL NULL NULL 10 100.00 5 DERIVED t1 ALL NULL NULL NULL NULL 5 100.00 6 UNION t1 ALL NULL NULL NULL NULL 5 100.00 NULL INTERSECT RESULT ALL NULL NULL NULL NULL NULL NULL 7 EXCEPT t3 ALL NULL NULL NULL NULL 5 100.00 9 UNION t2 ALL NULL NULL NULL NULL 6 100.00 10 UNION t3 ALL NULL NULL NULL NULL 5 100.00 11 EXCEPT t1 ALL NULL NULL NULL NULL 5 100.00 11 EXCEPT t2 ALL NULL NULL NULL NULL 6 100.00 Using join buffer (flat, BNL join) 12 UNION t1 ALL NULL NULL NULL NULL 5 100.00 Using where 12 UNION t2 ALL NULL NULL NULL NULL 6 100.00 Using join buffer (flat, BNL join) 13 UNION t3 ALL NULL NULL NULL NULL 5 100.00 NULL UNIT RESULT ALL NULL NULL NULL NULL NULL NULL Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` union all /* select#8 */ select `__8`.`c` AS `c`,`__8`.`d` AS `d` from (/* select#2 */ select `test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` intersect all (values (1,1),(2,2),(2,2),(5,5),(2,2)) intersect all /* select#4 */ select `sq`.`a` AS `a`,`sq`.`b` AS `b` from (/* select#5 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` union all /* select#6 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1`) `sq`) `__8` except all /* select#7 */ select `test`.`t3`.`e` AS `e`,`test`.`t3`.`f` AS `f` from `test`.`t3` union /* select#9 */ select `test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` union /* select#10 */ select `test`.`t3`.`e` AS `e`,`test`.`t3`.`f` AS `f` from `test`.`t3` except /* select#11 */ select `test`.`t1`.`a` AS `a`,`test`.`t2`.`c` AS `c` from `test`.`t1` join `test`.`t2` union all /* select#12 */ select `test`.`t1`.`a` AS `g`,`test`.`t2`.`c` AS `h` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`a` < 4 union all /* select#13 */ select `test`.`t3`.`e` AS `e`,`test`.`t3`.`f` AS `f` from `test`.`t3` PREPARE stmt from" select * from t1 UNION ALL select * from t2 INTERSECT ALL (values (1,1), (2,2), (2,2), (5,5), (2,2) ) INTERSECT ALL select * from (select * from t1 union all select * from t1) sq EXCEPT ALL select * from t3 UNION ALL select * from t2 UNION select * from t3 EXCEPT select a,c from t1,t2 UNION ALL select * from v0 where g < 4 UNION ALL select * from t3 "; EXECUTE stmt; a b 1 1 1 2 3 3 9 9 5 5 4 4 1 2 2 2 1 3 1 3 2 3 1 5 1 5 2 5 1 2 1 2 2 2 1 2 1 2 2 2 1 3 1 3 2 3 4 4 2 2 2 2 1 1 3 3 EXECUTE stmt; a b 1 1 1 2 3 3 9 9 5 5 4 4 1 2 2 2 1 3 1 3 2 3 1 5 1 5 2 5 1 2 1 2 2 2 1 2 1 2 2 2 1 3 1 3 2 3 4 4 2 2 2 2 1 1 3 3 deallocate prepare stmt; create view v1(i1, i2) as select * from t1 UNION ALL select * from t2 INTERSECT ALL (values (1,1), (2,2), (2,2), (5,5), (2,2) ) INTERSECT ALL select * from (select * from t1 union all select * from t1) sq EXCEPT ALL select * from t3 UNION ALL select * from t2 UNION select * from t3 EXCEPT select a,c from t1,t2 UNION ALL select * from v0 where g < 4 UNION ALL select * from t3; show create view v1; View Create View character_set_client collation_connection v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `i1`,`t1`.`b` AS `i2` from `t1` union all select `__9`.`c` AS `c`,`__9`.`d` AS `d` from (select `t2`.`c` AS `c`,`t2`.`d` AS `d` from `t2` intersect all (values (1,1),(2,2),(2,2),(5,5),(2,2)) intersect all select `sq`.`a` AS `a`,`sq`.`b` AS `b` from (select `t1`.`a` AS `a`,`t1`.`b` AS `b` from `t1` union all select `t1`.`a` AS `a`,`t1`.`b` AS `b` from `t1`) `sq`) `__9` except all select `t3`.`e` AS `e`,`t3`.`f` AS `f` from `t3` union all select `t2`.`c` AS `c`,`t2`.`d` AS `d` from `t2` union select `t3`.`e` AS `e`,`t3`.`f` AS `f` from `t3` except select `t1`.`a` AS `a`,`t2`.`c` AS `c` from (`t1` join `t2`) union all select `v0`.`g` AS `g`,`v0`.`h` AS `h` from `v0` where `v0`.`g` < 4 union all select `t3`.`e` AS `e`,`t3`.`f` AS `f` from `t3` latin1 latin1_swedish_ci select * from v1 limit 14; i1 i2 1 1 1 2 3 3 9 9 5 5 4 4 1 2 2 2 1 3 1 3 2 3 1 5 1 5 2 5 select * from v1 order by i1 limit 14; i1 i2 1 1 1 1 1 2 1 2 1 2 1 2 1 2 1 2 1 3 1 3 1 3 1 3 1 5 1 5 drop table t1,t2,t3; drop view v0,v1; # compare result create table t1 (a int, b int); create table t2 (c int, d int); create table t3 (e int, f int); create table t4 (g int, h int); insert into t1 values (1,1),(1,1),(2,2); insert into t2 values (1,1),(1,1),(2,2),(3,3); insert into t3 values (1,1); insert into t4 values (4,4); select * from t1 intersect all select * from t2 except select * from t3 union select * from t4; a b 4 4 2 2 select * from t1 intersect all select * from t2 except ALL select * from t3 union select * from t4; a b 1 1 2 2 4 4 select * from t1 intersect DISTINCT select * from t2 except select * from t3 union select * from t4; a b 4 4 2 2 select * from t1 intersect DISTINCT select * from t2 except ALL select * from t3 union select * from t4; a b 4 4 2 2 delete from t1; delete from t2; delete from t3; delete from t4; insert into t1 values (1,1),(1,1),(1,1),(2,2),(2,2),(4,4),(5,5); insert into t2 values (1,1),(1,1),(1,1),(2,2),(2,2),(3,3); insert into t3 values (1,1),(2,2),(2,2); select * from t1 intersect all select * from t2 intersect all select * from t3; a b 1 1 2 2 2 2 select * from t1 intersect all select * from t2 intersect select * from t3; a b 1 1 2 2 select * from t1 intersect all select * from t1 intersect all select * from t2 intersect select * from t3; a b 1 1 2 2 delete from t1; delete from t2; delete from t3; insert into t1 values (1,1),(1,1),(2,2); insert into t2 values (1,1),(1,1),(2,2),(3,3); insert into t3 values (1,1),(5,5); insert into t4 values (4,4),(4,4),(4,4); select * from t1 intersect all select * from t2 union all select * from t3 union select * from t4; a b 1 1 2 2 5 5 4 4 select * from t1 intersect DISTINCT select * from t2 union DISTINCT select * from t3 union select * from t4; a b 1 1 2 2 5 5 4 4 select * from t1 intersect all select * from t2 intersect all select * from t3 union select * from t4; a b 1 1 4 4 select * from t1 intersect all select * from t2 intersect DISTINCT select * from t3 union select * from t4; a b 1 1 4 4 select * from t1 intersect DISTINCT select * from t2 intersect DISTINCT select * from t3 union select * from t4; a b 1 1 4 4 select * from t1 intersect all select * from t2 EXCEPT select * from t3 union select * from t4; a b 4 4 2 2 select * from t1 intersect DISTINCT select * from t2 EXCEPT select * from t3 union select * from t4; a b 4 4 2 2 select * from t1 intersect all select * from t2 EXCEPT ALL select * from t3 union select * from t4; a b 1 1 2 2 4 4 select * from t1 EXCEPT select * from t2 union all select * from t3 union select * from t4; a b 5 5 1 1 4 4 select * from t1 EXCEPT select * from t2 union DISTINCT select * from t3 union select * from t4; a b 5 5 1 1 4 4 delete from t1; delete from t2; delete from t3; delete from t4; insert into t1 values (1,1),(2,2); insert into t2 values (1,1),(2,2); insert into t3 values (1,1),(3,3); select * from t1 union all select * from t2 except all select * from t3; a b 1 1 2 2 2 2 select * from t1 union all select * from t2 except DISTINCT select * from t3; a b 2 2 select * from t1 union DISTINCT select * from t2 except all select * from t3; a b 2 2 select * from t1 union DISTINCT select * from t2 except DISTINCT select * from t3; a b 2 2 drop table t1; drop table t2; drop table t3; drop table t4; select 1 intersect all select 2 intersect all select 3 intersect select 4 union select 5; 1 5 select 1 intersect all select 2 intersect all select 3 union select 4 except select 5; 1 4 select 1 union select 2 except all select 3 union select 4; 1 1 2 4 select 1 union all select 2 union all select 3 union select 4; 1 1 2 3 4 # test with limited resource set @@max_heap_table_size= 1024; Warnings: Warning 1292 Truncated incorrect max_heap_table_size value: '1024' set @@tmp_table_size= 1024; create table t1 (a int, b int); insert into t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(0,0); insert into t1 select * from t1; insert into t1 select * from t1; insert into t1 select * from t1; insert into t1 select a+100, b+100 from t1; create table t2 (a int, b int); insert into t2 values (10,10),(11,11),(12,12),(13,13),(14,14),(5,5),(6,6),(7,7),(8,8),(9,9); insert into t2 select * from t2; insert into t2 select * from t2; insert into t2 select * from t2; insert into t2 select a+100, b+100 from t2; select count(*) from ( select * from t1 INTERSECT ALL select * from t2 ) c; count(*) 80 select count(*) from ( select * from t1 EXCEPT ALL select * from t2 ) c; count(*) 80 select count(*) from ( select * from t1 INTERSECT ALL select * from t2 UNION ALL select * from t1 EXCEPT ALL select * from t2 ) c; count(*) 160 delete from t1; delete from t2; insert into t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(0,0); insert into t1 select a+10, b+10 from t1; insert into t1 select a+20, b+20 from t1; insert into t1 select a+40, b+40 from t1; insert into t1 select a+80, b+80 from t1; insert into t2 values (1110,1110),(1111,1111),(1112,1112),(1113,1113),(1114,1114),(1105,1105),(1106,1106),(1107,1107),(1108,1108),(1109,1109); insert into t2 select a+10, b+10 from t2; insert into t2 select a+20, b+20 from t2; insert into t2 select a+40, b+40 from t2; insert into t2 select a+80, b+80 from t2; select count(*) from ( select * from t1 UNION ALL select * from t2 EXCEPT ALL values (1,1) ) c; count(*) 319 drop table t1; drop table t2; # # MDEV-24242: set expression with empty intermediate result # when tmp_memory_table_size is set to 0 # create table t1 (a int, b int) engine=MyISAM; insert into t1 values (1,1), (2,2); create table t2 (a int, b int) engine=MyISAM; insert into t2 values (11,11), (12,12), (13,13); select * from t1 except all select * from t1 except select * from t1 union all select * from t2; a b 12 12 11 11 13 13 set tmp_memory_table_size=0; select * from t1 except all select * from t1 except select * from t1 union all select * from t2; a b 12 12 11 11 13 13 set tmp_memory_table_size=default; drop table t1,t2; # End of 10.4 tests