summaryrefslogtreecommitdiffstats
path: root/mysql-test/main/set_operation.result
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/main/set_operation.result')
-rw-r--r--mysql-test/main/set_operation.result1241
1 files changed, 1241 insertions, 0 deletions
diff --git a/mysql-test/main/set_operation.result b/mysql-test/main/set_operation.result
new file mode 100644
index 00000000..fa0fe21d
--- /dev/null
+++ b/mysql-test/main/set_operation.result
@@ -0,0 +1,1241 @@
+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 <intersect1,2,3> 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 <intersect1,2,3,4> 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 <unit1,2,3,4> 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 <unit1,2,3> 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 <unit1,2,3,4> 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 <unit1,2,3,4,5,6,7> 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 <derived8> ALL NULL NULL NULL NULL 4 100.00
+8 DERIVED <derived2> 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 <unit2,3,4> ALL NULL NULL NULL NULL NULL NULL
+9 UNION <derived5> 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 <intersect5,6,7> ALL NULL NULL NULL NULL NULL NULL
+12 EXCEPT <derived10> 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 <intersect10,11> ALL NULL NULL NULL NULL NULL NULL
+NULL UNIT RESULT <unit8,9,12> ALL NULL NULL NULL NULL NULL NULL
+15 UNION <derived13> 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 <unit1,15,16> 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 <derived2> 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 <derived4> 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 <union1,6> 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 <derived2> 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 <intersect2,3> ALL NULL NULL NULL NULL NULL NULL
+4 EXCEPT NULL NULL NULL NULL NULL NULL NULL NULL No tables used
+NULL EXCEPT RESULT <except1,4> 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 <derived2> 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 <intersect2,3,4> ALL NULL NULL NULL NULL NULL NULL
+7 INTERSECT <derived5> 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 <intersect5,6> ALL NULL NULL NULL NULL NULL NULL
+NULL INTERSECT RESULT <intersect1,7> 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 <derived2> 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 <derived5> 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 <intersect2,3,4> 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 <unit1,8,7,9,10,11,12,13> 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": "<unit1,8,7,9,10,11,12,13>",
+ "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": "<derived2>",
+ "access_type": "ALL",
+ "rows": 5,
+ "filtered": 100,
+ "materialized": {
+ "query_block": {
+ "union_result": {
+ "table_name": "<intersect2,3,4>",
+ "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": "<derived5>",
+ "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 <derived2> 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 <derived5> 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 <intersect2,3,4> 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 <unit1,8,7,9,10,11,12,13> 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
generated by cgit v1.2.3 (git 2.39.1) at 2024-12-23 05:39:12 +0000