MDEV-10141 & MDEV-10140 : Postreview changes10.3-MDEV-10141

13 files changed, 841 insertions, 118 deletions

diff --git a/mysql-test/r/except.result b/mysql-test/r/except.result
index 29fc32177f3..fdaa625ea65 100644
--- a/mysql-test/r/except.result
+++ b/mysql-test/r/except.result
@@ -1,5 +1,5 @@
-create table t1 (a int, b int);
-create table t2 (c int, d int);
+create table t1 (a int, b int) engine=MyISAM;
+create table t2 (c int, d int) engine=MyISAM;
 insert into t1 values (1,1),(2,2);
 insert into t2 values (2,2),(3,3);
 (select a,b from t1) except (select c,d from t2);
@@ -9,12 +9,12 @@ EXPLAIN (select a,b from t1) except (select c,d from t2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
 2	EXCEPT	t2	ALL	NULL	NULL	NULL	NULL	2	
-NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	
+NULL	EXCEPT RESULT	<except1,2>	ALL	NULL	NULL	NULL	NULL	NULL	
 EXPLAIN extended (select a,b from t1) except (select c,d from t2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
 2	EXCEPT	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	
-NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
+NULL	EXCEPT RESULT	<except1,2>	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`) except (/* select#2 */ select `test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2`)
 EXPLAIN extended select * from ((select a,b from t1) except (select c,d from t2)) a;
@@ -22,7 +22,7 @@ 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	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
 3	EXCEPT	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	
-NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
+NULL	EXCEPT RESULT	<except2,3>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
 Warnings:
 Note	1003	/* select#1 */ select `a`.`a` AS `a`,`a`.`b` AS `b` from ((/* select#2 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1`) except (/* select#3 */ select `test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2`)) `a`
 EXPLAIN format=json (select a,b from t1) except (select c,d from t2);
@@ -30,7 +30,7 @@ EXPLAIN
 {
   "query_block": {
     "union_result": {
-      "table_name": "<union1,2>",
+      "table_name": "<except1,2>",
       "access_type": "ALL",
       "query_specifications": [
         {
@@ -65,7 +65,7 @@ ANALYZE
 {
   "query_block": {
     "union_result": {
-      "table_name": "<union1,2>",
+      "table_name": "<except1,2>",
       "access_type": "ALL",
       "r_loops": 1,
       "r_rows": 1,
@@ -128,7 +128,7 @@ ANALYZE
       "materialized": {
         "query_block": {
           "union_result": {
-            "table_name": "<union2,3>",
+            "table_name": "<except2,3>",
             "access_type": "ALL",
             "r_loops": 1,
             "r_rows": 1,
@@ -193,6 +193,308 @@ execute stmt;
 a	b
 1	1
 drop tables t1,t2;
+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);
+insert into t2 values (2,2),(3,3);
+insert into t3 values (4,4),(5,5);
+insert into t4 values (4,4),(7,7);
+(select a,b,e,f from t1,t3) except (select c,d,g,h from t2,t4);
+a	b	e	f
+1	1	4	4
+1	1	5	5
+2	2	5	5
+EXPLAIN (select a,b,e,f from t1,t3) except (select c,d,g,h from t2,t4);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using join buffer (flat, BNL join)
+2	EXCEPT	t2	ALL	NULL	NULL	NULL	NULL	2	
+2	EXCEPT	t4	ALL	NULL	NULL	NULL	NULL	2	Using join buffer (flat, BNL join)
+NULL	EXCEPT RESULT	<except1,2>	ALL	NULL	NULL	NULL	NULL	NULL	
+EXPLAIN (select a,b,e,f from t1,t3) except (select c,d,g,h from t2,t4);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using join buffer (flat, BNL join)
+2	EXCEPT	t2	ALL	NULL	NULL	NULL	NULL	2	
+2	EXCEPT	t4	ALL	NULL	NULL	NULL	NULL	2	Using join buffer (flat, BNL join)
+NULL	EXCEPT RESULT	<except1,2>	ALL	NULL	NULL	NULL	NULL	NULL	
+EXPLAIN extended select * from ((select a,b,e,f from t1,t3) except (select c,d,g,h from t2,t4)) a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	4	100.00	
+2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
+2	DERIVED	t3	ALL	NULL	NULL	NULL	NULL	2	100.00	Using join buffer (flat, BNL join)
+3	EXCEPT	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	
+3	EXCEPT	t4	ALL	NULL	NULL	NULL	NULL	2	100.00	Using join buffer (flat, BNL join)
+NULL	EXCEPT RESULT	<except2,3>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
+Warnings:
+Note	1003	/* select#1 */ select `a`.`a` AS `a`,`a`.`b` AS `b`,`a`.`e` AS `e`,`a`.`f` AS `f` from ((/* select#2 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t3`.`e` AS `e`,`test`.`t3`.`f` AS `f` from `test`.`t1` join `test`.`t3`) except (/* select#3 */ select `test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d`,`test`.`t4`.`g` AS `g`,`test`.`t4`.`h` AS `h` from `test`.`t2` join `test`.`t4`)) `a`
+EXPLAIN format=json (select a,b,e,f from t1,t3) except (select c,d,g,h from t2,t4);
+EXPLAIN
+{
+  "query_block": {
+    "union_result": {
+      "table_name": "<except1,2>",
+      "access_type": "ALL",
+      "query_specifications": [
+        {
+          "query_block": {
+            "select_id": 1,
+            "table": {
+              "table_name": "t1",
+              "access_type": "ALL",
+              "rows": 2,
+              "filtered": 100
+            },
+            "block-nl-join": {
+              "table": {
+                "table_name": "t3",
+                "access_type": "ALL",
+                "rows": 2,
+                "filtered": 100
+              },
+              "buffer_type": "flat",
+              "buffer_size": "256Kb",
+              "join_type": "BNL"
+            }
+          }
+        },
+        {
+          "query_block": {
+            "select_id": 2,
+            "operation": "EXCEPT",
+            "table": {
+              "table_name": "t2",
+              "access_type": "ALL",
+              "rows": 2,
+              "filtered": 100
+            },
+            "block-nl-join": {
+              "table": {
+                "table_name": "t4",
+                "access_type": "ALL",
+                "rows": 2,
+                "filtered": 100
+              },
+              "buffer_type": "flat",
+              "buffer_size": "256Kb",
+              "join_type": "BNL"
+            }
+          }
+        }
+      ]
+    }
+  }
+}
+ANALYZE format=json (select a,b,e,f from t1,t3) except (select c,d,g,h from t2,t4);
+ANALYZE
+{
+  "query_block": {
+    "union_result": {
+      "table_name": "<except1,2>",
+      "access_type": "ALL",
+      "r_loops": 1,
+      "r_rows": 3,
+      "query_specifications": [
+        {
+          "query_block": {
+            "select_id": 1,
+            "r_loops": 1,
+            "r_total_time_ms": "REPLACED",
+            "table": {
+              "table_name": "t1",
+              "access_type": "ALL",
+              "r_loops": 1,
+              "rows": 2,
+              "r_rows": 2,
+              "r_total_time_ms": "REPLACED",
+              "filtered": 100,
+              "r_filtered": 100
+            },
+            "block-nl-join": {
+              "table": {
+                "table_name": "t3",
+                "access_type": "ALL",
+                "r_loops": 1,
+                "rows": 2,
+                "r_rows": 2,
+                "r_total_time_ms": "REPLACED",
+                "filtered": 100,
+                "r_filtered": 100
+              },
+              "buffer_type": "flat",
+              "buffer_size": "256Kb",
+              "join_type": "BNL",
+              "r_filtered": 100
+            }
+          }
+        },
+        {
+          "query_block": {
+            "select_id": 2,
+            "operation": "EXCEPT",
+            "r_loops": 1,
+            "r_total_time_ms": "REPLACED",
+            "table": {
+              "table_name": "t2",
+              "access_type": "ALL",
+              "r_loops": 1,
+              "rows": 2,
+              "r_rows": 2,
+              "r_total_time_ms": "REPLACED",
+              "filtered": 100,
+              "r_filtered": 100
+            },
+            "block-nl-join": {
+              "table": {
+                "table_name": "t4",
+                "access_type": "ALL",
+                "r_loops": 1,
+                "rows": 2,
+                "r_rows": 2,
+                "r_total_time_ms": "REPLACED",
+                "filtered": 100,
+                "r_filtered": 100
+              },
+              "buffer_type": "flat",
+              "buffer_size": "256Kb",
+              "join_type": "BNL",
+              "r_filtered": 100
+            }
+          }
+        }
+      ]
+    }
+  }
+}
+ANALYZE format=json select * from ((select a,b,e,f from t1,t3) except
+(select c,d,g,h from t2,t4)) a;
+ANALYZE
+{
+  "query_block": {
+    "select_id": 1,
+    "r_loops": 1,
+    "r_total_time_ms": "REPLACED",
+    "table": {
+      "table_name": "<derived2>",
+      "access_type": "ALL",
+      "r_loops": 1,
+      "rows": 4,
+      "r_rows": 3,
+      "r_total_time_ms": "REPLACED",
+      "filtered": 100,
+      "r_filtered": 100,
+      "materialized": {
+        "query_block": {
+          "union_result": {
+            "table_name": "<except2,3>",
+            "access_type": "ALL",
+            "r_loops": 1,
+            "r_rows": 3,
+            "query_specifications": [
+              {
+                "query_block": {
+                  "select_id": 2,
+                  "r_loops": 1,
+                  "r_total_time_ms": "REPLACED",
+                  "table": {
+                    "table_name": "t1",
+                    "access_type": "ALL",
+                    "r_loops": 1,
+                    "rows": 2,
+                    "r_rows": 2,
+                    "r_total_time_ms": "REPLACED",
+                    "filtered": 100,
+                    "r_filtered": 100
+                  },
+                  "block-nl-join": {
+                    "table": {
+                      "table_name": "t3",
+                      "access_type": "ALL",
+                      "r_loops": 1,
+                      "rows": 2,
+                      "r_rows": 2,
+                      "r_total_time_ms": "REPLACED",
+                      "filtered": 100,
+                      "r_filtered": 100
+                    },
+                    "buffer_type": "flat",
+                    "buffer_size": "256Kb",
+                    "join_type": "BNL",
+                    "r_filtered": 100
+                  }
+                }
+              },
+              {
+                "query_block": {
+                  "select_id": 3,
+                  "operation": "EXCEPT",
+                  "r_loops": 1,
+                  "r_total_time_ms": "REPLACED",
+                  "table": {
+                    "table_name": "t2",
+                    "access_type": "ALL",
+                    "r_loops": 1,
+                    "rows": 2,
+                    "r_rows": 2,
+                    "r_total_time_ms": "REPLACED",
+                    "filtered": 100,
+                    "r_filtered": 100
+                  },
+                  "block-nl-join": {
+                    "table": {
+                      "table_name": "t4",
+                      "access_type": "ALL",
+                      "r_loops": 1,
+                      "rows": 2,
+                      "r_rows": 2,
+                      "r_total_time_ms": "REPLACED",
+                      "filtered": 100,
+                      "r_filtered": 100
+                    },
+                    "buffer_type": "flat",
+                    "buffer_size": "256Kb",
+                    "join_type": "BNL",
+                    "r_filtered": 100
+                  }
+                }
+              }
+            ]
+          }
+        }
+      }
+    }
+  }
+}
+select * from ((select a,b,e,f from t1,t3) except (select c,d,g,h from t2,t4)) a;
+a	b	e	f
+1	1	4	4
+1	1	5	5
+2	2	5	5
+prepare stmt from "(select a,b,e,f from t1,t3) except (select c,d,g,h from t2,t4)";
+execute stmt;
+a	b	e	f
+1	1	4	4
+1	1	5	5
+2	2	5	5
+execute stmt;
+a	b	e	f
+1	1	4	4
+1	1	5	5
+2	2	5	5
+prepare stmt from "select * from ((select a,b,e,f from t1,t3) except (select c,d,g,h from t2,t4)) a";
+execute stmt;
+a	b	e	f
+1	1	4	4
+1	1	5	5
+2	2	5	5
+execute stmt;
+a	b	e	f
+1	1	4	4
+1	1	5	5
+2	2	5	5
+drop tables t1,t2,t3,t4;
 select 1 as a from dual except select 1 from dual;
 a
 (select 1 from dual) except (select 1 from dual);
@@ -207,8 +509,8 @@ a
 1
 select 1 from dual except all select 1 from dual;
 ERROR HY000: Incorrect usage of EXCEPT and ALL
-create table t1 (a int, b blob, a1 int, b1 blob);
-create table t2 (c int, d blob, c1 int, d1 blob);
+create table t1 (a int, b blob, a1 int, b1 blob) engine=MyISAM;
+create table t2 (c int, d blob, c1 int, d1 blob) engine=MyISAM;
 insert into t1 values (1,"ddd", 1, "sdfrrwwww"),(2, "fgh", 2, "dffggtt");
 insert into t2 values (2, "fgh", 2, "dffggtt"),(3, "ffggddd", 3, "dfgg");
 (select a,b,b1 from t1) except (select c,d,d1 from t2);
diff --git a/mysql-test/r/intersect.result b/mysql-test/r/intersect.result
index 3416aa1cc60..52bb5642be9 100644
--- a/mysql-test/r/intersect.result
+++ b/mysql-test/r/intersect.result
@@ -6,9 +6,9 @@ insert into t2 values (2,2),(3,3);
 a	b
 2	2
 drop tables t1,t2;
-create table t1 (a int, b int);
-create table t2 (c int, d int);
-create table t3 (e int, f int);
+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;
 insert into t1 values (1,1),(2,2),(3,3);
 insert into t2 values (2,2),(3,3),(4,4);
 insert into t3 values (1,1),(2,2),(5,5);
@@ -20,13 +20,13 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	
 2	INTERSECT	t2	ALL	NULL	NULL	NULL	NULL	3	
 3	INTERSECT	t3	ALL	NULL	NULL	NULL	NULL	3	
-NULL	UNION RESULT	<union1,2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
+NULL	INTERSECT RESULT	<intersect1,2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
 EXPLAIN extended (select a,b from t1) intersect (select c,d from t2) intersect (select e,f from t3);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	
 2	INTERSECT	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	
 3	INTERSECT	t3	ALL	NULL	NULL	NULL	NULL	3	100.00	
-NULL	UNION RESULT	<union1,2,3>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
+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 (/* 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`)
 EXPLAIN extended select * from ((select a,b from t1) intersect (select c,d from t2) intersect (select e,f from t3)) a;
@@ -35,7 +35,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	
 3	INTERSECT	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	
 4	INTERSECT	t3	ALL	NULL	NULL	NULL	NULL	3	100.00	
-NULL	UNION RESULT	<union2,3,4>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
+NULL	INTERSECT RESULT	<intersect2,3,4>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
 Warnings:
 Note	1003	/* select#1 */ select `a`.`a` AS `a`,`a`.`b` AS `b` from ((/* select#2 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1`) intersect (/* select#3 */ select `test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2`) intersect (/* select#4 */ select `test`.`t3`.`e` AS `e`,`test`.`t3`.`f` AS `f` from `test`.`t3`)) `a`
 EXPLAIN format=json (select a,b from t1) intersect (select c,d from t2) intersect (select e,f from t3);
@@ -43,7 +43,7 @@ EXPLAIN
 {
   "query_block": {
     "union_result": {
-      "table_name": "<union1,2,3>",
+      "table_name": "<intersect1,2,3>",
       "access_type": "ALL",
       "query_specifications": [
         {
@@ -90,7 +90,7 @@ ANALYZE
 {
   "query_block": {
     "union_result": {
-      "table_name": "<union1,2,3>",
+      "table_name": "<intersect1,2,3>",
       "access_type": "ALL",
       "r_loops": 1,
       "r_rows": 1,
@@ -171,7 +171,7 @@ ANALYZE
       "materialized": {
         "query_block": {
           "union_result": {
-            "table_name": "<union2,3,4>",
+            "table_name": "<intersect2,3,4>",
             "access_type": "ALL",
             "r_loops": 1,
             "r_rows": 1,
@@ -253,6 +253,242 @@ a	b
 execute stmt;
 a	b
 2	2
+(select a,b from t1) intersect (select c,e from t2,t3);
+a	b
+2	2
+EXPLAIN (select a,b from t1) intersect (select c,e from t2,t3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	
+2	INTERSECT	t2	ALL	NULL	NULL	NULL	NULL	3	
+2	INTERSECT	t3	ALL	NULL	NULL	NULL	NULL	3	Using join buffer (flat, BNL join)
+NULL	INTERSECT RESULT	<intersect1,2>	ALL	NULL	NULL	NULL	NULL	NULL	
+EXPLAIN extended (select a,b from t1) intersect (select c,e from t2,t3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	
+2	INTERSECT	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	
+2	INTERSECT	t3	ALL	NULL	NULL	NULL	NULL	3	100.00	Using join buffer (flat, BNL join)
+NULL	INTERSECT RESULT	<intersect1,2>	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`.`t3`.`e` AS `e` from `test`.`t2` join `test`.`t3`)
+EXPLAIN extended select * from ((select a,b from t1) intersect (select c,e from t2,t3)) a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	3	100.00	
+2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	
+3	INTERSECT	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	
+3	INTERSECT	t3	ALL	NULL	NULL	NULL	NULL	3	100.00	Using join buffer (flat, BNL join)
+NULL	INTERSECT RESULT	<intersect2,3>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
+Warnings:
+Note	1003	/* select#1 */ select `a`.`a` AS `a`,`a`.`b` AS `b` from ((/* select#2 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1`) intersect (/* select#3 */ select `test`.`t2`.`c` AS `c`,`test`.`t3`.`e` AS `e` from `test`.`t2` join `test`.`t3`)) `a`
+EXPLAIN format=json (select a,b from t1) intersect (select c,e from t2,t3);
+EXPLAIN
+{
+  "query_block": {
+    "union_result": {
+      "table_name": "<intersect1,2>",
+      "access_type": "ALL",
+      "query_specifications": [
+        {
+          "query_block": {
+            "select_id": 1,
+            "table": {
+              "table_name": "t1",
+              "access_type": "ALL",
+              "rows": 3,
+              "filtered": 100
+            }
+          }
+        },
+        {
+          "query_block": {
+            "select_id": 2,
+            "operation": "INTERSECT",
+            "table": {
+              "table_name": "t2",
+              "access_type": "ALL",
+              "rows": 3,
+              "filtered": 100
+            },
+            "block-nl-join": {
+              "table": {
+                "table_name": "t3",
+                "access_type": "ALL",
+                "rows": 3,
+                "filtered": 100
+              },
+              "buffer_type": "flat",
+              "buffer_size": "256Kb",
+              "join_type": "BNL"
+            }
+          }
+        }
+      ]
+    }
+  }
+}
+ANALYZE format=json (select a,b from t1) intersect (select c,e from t2,t3);
+ANALYZE
+{
+  "query_block": {
+    "union_result": {
+      "table_name": "<intersect1,2>",
+      "access_type": "ALL",
+      "r_loops": 1,
+      "r_rows": 1,
+      "query_specifications": [
+        {
+          "query_block": {
+            "select_id": 1,
+            "r_loops": 1,
+            "r_total_time_ms": "REPLACED",
+            "table": {
+              "table_name": "t1",
+              "access_type": "ALL",
+              "r_loops": 1,
+              "rows": 3,
+              "r_rows": 3,
+              "r_total_time_ms": "REPLACED",
+              "filtered": 100,
+              "r_filtered": 100
+            }
+          }
+        },
+        {
+          "query_block": {
+            "select_id": 2,
+            "operation": "INTERSECT",
+            "r_loops": 1,
+            "r_total_time_ms": "REPLACED",
+            "table": {
+              "table_name": "t2",
+              "access_type": "ALL",
+              "r_loops": 1,
+              "rows": 3,
+              "r_rows": 3,
+              "r_total_time_ms": "REPLACED",
+              "filtered": 100,
+              "r_filtered": 100
+            },
+            "block-nl-join": {
+              "table": {
+                "table_name": "t3",
+                "access_type": "ALL",
+                "r_loops": 1,
+                "rows": 3,
+                "r_rows": 3,
+                "r_total_time_ms": "REPLACED",
+                "filtered": 100,
+                "r_filtered": 100
+              },
+              "buffer_type": "flat",
+              "buffer_size": "256Kb",
+              "join_type": "BNL",
+              "r_filtered": 100
+            }
+          }
+        }
+      ]
+    }
+  }
+}
+ANALYZE format=json select * from ((select a,b from t1) intersect (select c,e from t2,t3)) a;
+ANALYZE
+{
+  "query_block": {
+    "select_id": 1,
+    "r_loops": 1,
+    "r_total_time_ms": "REPLACED",
+    "table": {
+      "table_name": "<derived2>",
+      "access_type": "ALL",
+      "r_loops": 1,
+      "rows": 3,
+      "r_rows": 1,
+      "r_total_time_ms": "REPLACED",
+      "filtered": 100,
+      "r_filtered": 100,
+      "materialized": {
+        "query_block": {
+          "union_result": {
+            "table_name": "<intersect2,3>",
+            "access_type": "ALL",
+            "r_loops": 1,
+            "r_rows": 1,
+            "query_specifications": [
+              {
+                "query_block": {
+                  "select_id": 2,
+                  "r_loops": 1,
+                  "r_total_time_ms": "REPLACED",
+                  "table": {
+                    "table_name": "t1",
+                    "access_type": "ALL",
+                    "r_loops": 1,
+                    "rows": 3,
+                    "r_rows": 3,
+                    "r_total_time_ms": "REPLACED",
+                    "filtered": 100,
+                    "r_filtered": 100
+                  }
+                }
+              },
+              {
+                "query_block": {
+                  "select_id": 3,
+                  "operation": "INTERSECT",
+                  "r_loops": 1,
+                  "r_total_time_ms": "REPLACED",
+                  "table": {
+                    "table_name": "t2",
+                    "access_type": "ALL",
+                    "r_loops": 1,
+                    "rows": 3,
+                    "r_rows": 3,
+                    "r_total_time_ms": "REPLACED",
+                    "filtered": 100,
+                    "r_filtered": 100
+                  },
+                  "block-nl-join": {
+                    "table": {
+                      "table_name": "t3",
+                      "access_type": "ALL",
+                      "r_loops": 1,
+                      "rows": 3,
+                      "r_rows": 3,
+                      "r_total_time_ms": "REPLACED",
+                      "filtered": 100,
+                      "r_filtered": 100
+                    },
+                    "buffer_type": "flat",
+                    "buffer_size": "256Kb",
+                    "join_type": "BNL",
+                    "r_filtered": 100
+                  }
+                }
+              }
+            ]
+          }
+        }
+      }
+    }
+  }
+}
+select * from ((select a,b from t1) intersect (select c,e from t2,t3)) a;
+a	b
+2	2
+prepare stmt from "(select a,b from t1) intersect (select c,e from t2,t3);";
+execute stmt;
+a	b
+2	2
+execute stmt;
+a	b
+2	2
+prepare stmt from "select * from ((select a,b from t1) intersect (select c,e from t2,t3)) a";
+execute stmt;
+a	b
+2	2
+execute stmt;
+a	b
+2	2
 drop tables t1,t2,t3;
 select 1 as a from dual intersect select 1 from dual;
 a
@@ -278,9 +514,9 @@ insert into t2 values (2, "fgh", 2, "dffggtt"),(3, "ffggddd", 3, "dfgg");
 a	b	b1
 2	fgh	dffggtt
 drop tables t1,t2;
-create table t1 (a int, b blob);
-create table t2 (c int, d blob);
-create table t3 (e int, f blob);
+create table t1 (a int, b blob) engine=MyISAM;
+create table t2 (c int, d blob) engine=MyISAM;
+create table t3 (e int, f blob) engine=MyISAM;
 insert into t1 values (1,1),(2,2),(3,3);
 insert into t2 values (2,2),(3,3),(4,4);
 insert into t3 values (1,1),(2,2),(5,5);
@@ -312,9 +548,9 @@ t4	CREATE TABLE `t4` (
   `b` blob DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 drop tables t1,t2,t3,t4;
-create table t1 (a int, b blob);
-create table t2 (c int, d blob);
-create table t3 (e int, f blob);
+create table t1 (a int, b blob) engine=MyISAM;
+create table t2 (c int, d blob) engine=MyISAM;
+create table t3 (e int, f blob) engine=MyISAM;
 insert into t1 values (1,1),(2,2),(3,3);
 insert into t2 values (2,2),(3,3);
 insert into t3 values (1,1),(2,2),(3,3);
@@ -347,9 +583,9 @@ drop tables t1,t2,t3;
 #
 # INTERSECT precedence
 #
-create table t1 (a int, b blob);
-create table t2 (c int, d blob);
-create table t3 (e int, f blob);
+create table t1 (a int, b blob) engine=MyISAM;
+create table t2 (c int, d blob) engine=MyISAM;
+create table t3 (e int, f blob) engine=MyISAM;
 insert into t1 values (5,5),(6,6);
 insert into t2 values (2,2),(3,3);
 insert into t3 values (1,1),(3,3);
@@ -366,7 +602,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 3	UNION	<derived2>	ALL	NULL	NULL	NULL	NULL	2	100.00	
 2	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	
 4	INTERSECT	t3	ALL	NULL	NULL	NULL	NULL	2	100.00	
-NULL	UNION RESULT	<union2,4>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
+NULL	INTERSECT RESULT	<intersect2,4>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
 5	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 NULL	UNION RESULT	<union1,3,5>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
 Warnings:
diff --git a/mysql-test/t/except.test b/mysql-test/t/except.test
index 11d4702012a..8bf22180731 100644
--- a/mysql-test/t/except.test
+++ b/mysql-test/t/except.test
@@ -1,5 +1,5 @@
-create table t1 (a int, b int);
-create table t2 (c int, d int);
+create table t1 (a int, b int) engine=MyISAM;
+create table t2 (c int, d int) engine=MyISAM;
 insert into t1 values (1,1),(2,2);
 insert into t2 values (2,2),(3,3);
 
@@ -26,6 +26,38 @@ execute stmt;
 drop tables t1,t2;
 
 
+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);
+insert into t2 values (2,2),(3,3);
+insert into t3 values (4,4),(5,5);
+insert into t4 values (4,4),(7,7);
+
+(select a,b,e,f from t1,t3) except (select c,d,g,h from t2,t4);
+EXPLAIN (select a,b,e,f from t1,t3) except (select c,d,g,h from t2,t4);
+EXPLAIN (select a,b,e,f from t1,t3) except (select c,d,g,h from t2,t4);
+EXPLAIN extended select * from ((select a,b,e,f from t1,t3) except (select c,d,g,h from t2,t4)) a;
+EXPLAIN format=json (select a,b,e,f from t1,t3) except (select c,d,g,h from t2,t4);
+
+--replace_regex /"r_total_time_ms": [0-9]*[.]?[0-9]*/"r_total_time_ms": "REPLACED"/
+ANALYZE format=json (select a,b,e,f from t1,t3) except (select c,d,g,h from t2,t4);
+--replace_regex /"r_total_time_ms": [0-9]*[.]?[0-9]*/"r_total_time_ms": "REPLACED"/
+ANALYZE format=json select * from ((select a,b,e,f from t1,t3) except
+(select c,d,g,h from t2,t4)) a;
+select * from ((select a,b,e,f from t1,t3) except (select c,d,g,h from t2,t4)) a;
+
+prepare stmt from "(select a,b,e,f from t1,t3) except (select c,d,g,h from t2,t4)";
+execute stmt;
+execute stmt;
+
+prepare stmt from "select * from ((select a,b,e,f from t1,t3) except (select c,d,g,h from t2,t4)) a";
+execute stmt;
+execute stmt;
+
+drop tables t1,t2,t3,t4;
+
 select 1 as a from dual except select 1 from dual;
 (select 1 from dual) except (select 1 from dual);
 --error ER_WRONG_USAGE
@@ -38,8 +70,8 @@ select 1 as a from dual union all select 1 from dual;
 select 1 from dual except all select 1 from dual;
 
 
-create table t1 (a int, b blob, a1 int, b1 blob);
-create table t2 (c int, d blob, c1 int, d1 blob);
+create table t1 (a int, b blob, a1 int, b1 blob) engine=MyISAM;
+create table t2 (c int, d blob, c1 int, d1 blob) engine=MyISAM;
 insert into t1 values (1,"ddd", 1, "sdfrrwwww"),(2, "fgh", 2, "dffggtt");
 insert into t2 values (2, "fgh", 2, "dffggtt"),(3, "ffggddd", 3, "dfgg");
 
diff --git a/mysql-test/t/intersect.test b/mysql-test/t/intersect.test
index 68ada126686..d7abf41a8b2 100644
--- a/mysql-test/t/intersect.test
+++ b/mysql-test/t/intersect.test
@@ -8,9 +8,9 @@ insert into t2 values (2,2),(3,3);
 drop tables t1,t2;
 
 
-create table t1 (a int, b int);
-create table t2 (c int, d int);
-create table t3 (e int, f int);
+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;
 insert into t1 values (1,1),(2,2),(3,3);
 insert into t2 values (2,2),(3,3),(4,4);
 insert into t3 values (1,1),(2,2),(5,5);
@@ -34,6 +34,26 @@ prepare stmt from "select * from ((select a,b from t1) intersect (select c,d fro
 execute stmt;
 execute stmt;
 
+(select a,b from t1) intersect (select c,e from t2,t3);
+EXPLAIN (select a,b from t1) intersect (select c,e from t2,t3);
+EXPLAIN extended (select a,b from t1) intersect (select c,e from t2,t3);
+EXPLAIN extended select * from ((select a,b from t1) intersect (select c,e from t2,t3)) a;
+EXPLAIN format=json (select a,b from t1) intersect (select c,e from t2,t3);
+--replace_regex /"r_total_time_ms": [0-9]*[.]?[0-9]*/"r_total_time_ms": "REPLACED"/
+ANALYZE format=json (select a,b from t1) intersect (select c,e from t2,t3);
+--replace_regex /"r_total_time_ms": [0-9]*[.]?[0-9]*/"r_total_time_ms": "REPLACED"/
+ANALYZE format=json select * from ((select a,b from t1) intersect (select c,e from t2,t3)) a;
+select * from ((select a,b from t1) intersect (select c,e from t2,t3)) a;
+
+prepare stmt from "(select a,b from t1) intersect (select c,e from t2,t3);";
+execute stmt;
+execute stmt;
+
+prepare stmt from "select * from ((select a,b from t1) intersect (select c,e from t2,t3)) a";
+execute stmt;
+execute stmt;
+
+
 drop tables t1,t2,t3;
 
 
@@ -59,9 +79,9 @@ insert into t2 values (2, "fgh", 2, "dffggtt"),(3, "ffggddd", 3, "dfgg");
 
 drop tables t1,t2;
 
-create table t1 (a int, b blob);
-create table t2 (c int, d blob);
-create table t3 (e int, f blob);
+create table t1 (a int, b blob) engine=MyISAM;
+create table t2 (c int, d blob) engine=MyISAM;
+create table t3 (e int, f blob) engine=MyISAM;
 insert into t1 values (1,1),(2,2),(3,3);
 insert into t2 values (2,2),(3,3),(4,4);
 insert into t3 values (1,1),(2,2),(5,5);
@@ -83,9 +103,9 @@ show create table t4;
 
 drop tables t1,t2,t3,t4;
 
-create table t1 (a int, b blob);
-create table t2 (c int, d blob);
-create table t3 (e int, f blob);
+create table t1 (a int, b blob) engine=MyISAM;
+create table t2 (c int, d blob) engine=MyISAM;
+create table t3 (e int, f blob) engine=MyISAM;
 insert into t1 values (1,1),(2,2),(3,3);
 insert into t2 values (2,2),(3,3);
 insert into t3 values (1,1),(2,2),(3,3);
@@ -116,9 +136,9 @@ drop tables t1,t2,t3;
 --echo #
 --echo # INTERSECT precedence
 --echo #
-create table t1 (a int, b blob);
-create table t2 (c int, d blob);
-create table t3 (e int, f blob);
+create table t1 (a int, b blob) engine=MyISAM;
+create table t2 (c int, d blob) engine=MyISAM;
+create table t3 (e int, f blob) engine=MyISAM;
 insert into t1 values (5,5),(6,6);
 insert into t2 values (2,2),(3,3);
 insert into t3 values (1,1),(3,3);
diff --git a/sql/sql_class.cc b/sql/sql_class.cc
index 6cf13447ccc..19846da8e8c 100644
--- a/sql/sql_class.cc
+++ b/sql/sql_class.cc
@@ -3918,7 +3918,7 @@ void select_materialize_with_stats::cleanup()
 
 
 /**
-  Override select_union::send_data to analyze each row for NULLs and to
+  Override select_unit::send_data to analyze each row for NULLs and to
   update null_statistics before sending data to the client.
 
   @return TRUE if fatal error when sending data to the client
diff --git a/sql/sql_class.h b/sql/sql_class.h
index eb9b8fe5c6e..d5afa0a89dd 100644
--- a/sql/sql_class.h
+++ b/sql/sql_class.h
@@ -4866,7 +4866,7 @@ public:
 class select_unit :public select_result_interceptor
 {
   uint curr_step, prev_step, curr_sel;
-  enum step_type {union_step, intersect_step, except_step} step;
+  enum sub_select_type step;
 public:
   Item_int *intersect_mark;
   TMP_TABLE_PARAM tmp_table_param;
@@ -4877,7 +4877,7 @@ public:
   select_unit(THD *thd_arg):
     select_result_interceptor(thd_arg),
     curr_step(0), prev_step(0), curr_sel(UINT_MAX),
-    step(union_step), intersect_mark(0), write_err(0), table(0),
+    step(UNION_TYPE), intersect_mark(0), write_err(0), table(0),
     records(0)
   { tmp_table_param.init(); }
   int prepare(List<Item> &list, SELECT_LEX_UNIT *u);
@@ -4903,6 +4903,7 @@ public:
                                    bool keep_row_order,
                                    uint hidden);
   TMP_TABLE_PARAM *get_tmp_table_param() { return &tmp_table_param; }
+  void change_select();
 };
 
 class select_union_recursive :public select_unit
diff --git a/sql/sql_explain.cc b/sql/sql_explain.cc
index 857c292630c..693235c79e6 100644
--- a/sql/sql_explain.cc
+++ b/sql/sql_explain.cc
@@ -29,6 +29,11 @@ const char * STR_DELETING_ALL_ROWS= "Deleting all rows";
 const char * STR_IMPOSSIBLE_WHERE= "Impossible WHERE";
 const char * STR_NO_ROWS_AFTER_PRUNING= "No matching rows after partition pruning";
 
+const char *unit_operation_text[4]=
+{
+   "UNIT RESULT","UNION RESULT","INTERSECT RESULT","EXCEPT RESULT"
+};
+
 static void write_item(Json_writer *writer, Item *item);
 static void append_item_to_str(String *out, Item *item);
 
@@ -418,8 +423,28 @@ int print_explain_row(select_result_sink *result,
 uint Explain_union::make_union_table_name(char *buf)
 {
   uint childno= 0;
-  uint len= 6, lastop= 0;
-  memcpy(buf, STRING_WITH_LEN("<union"));
+  uint len, lastop= 0;
+  switch (operation)
+  {
+    case OP_MIX:
+      len= 5;
+      memcpy(buf, STRING_WITH_LEN("<unit"));
+      break;
+    case OP_UNION:
+      len= 6;
+      memcpy(buf, STRING_WITH_LEN("<union"));
+      break;
+    case OP_INTERSECT:
+      len= 10;
+      memcpy(buf, STRING_WITH_LEN("<intersect"));
+      break;
+    case OP_EXCEPT:
+      len= 7;
+      memcpy(buf, STRING_WITH_LEN("<except"));
+      break;
+    default:
+      DBUG_ASSERT(0);
+  }
 
   for (; childno < union_members.elements() && len + lastop + 5 < NAME_LEN;
        childno++)
@@ -462,7 +487,7 @@ int Explain_union::print_explain(Explain_query *query,
   if (!using_tmp)
     return 0;
 
-  /* Print a line with "UNION RESULT" */
+  /* Print a line with "UNIT RESULT" */
   List<Item> item_list;
   Item *item_null= new (mem_root) Item_null(thd);
 
@@ -816,20 +841,24 @@ int Explain_basic_join::print_explain(Explain_query *query,
 
 void Explain_select::add_linkage(Json_writer *writer)
 {
+  const char *operation= NULL;
   switch (linkage)
   {
      case UNION_TYPE:
-       writer->add_member("operation").add_str("UNION");
+       operation= "UNION";
        break;
      case INTERSECT_TYPE:
-       writer->add_member("operation").add_str("INTERSECT");
+       operation= "INTERSECT";
        break;
      case EXCEPT_TYPE:
-       writer->add_member("operation").add_str("EXCEPT");
+       operation= "EXCEPT";
        break;
      default:
+       // It is the first or the only SELECT => no operation
        break;
   }
+  if (operation)
+    writer->add_member("operation").add_str(operation);
 }
 
 void Explain_select::print_explain_json(Explain_query *query, 
diff --git a/sql/sql_explain.h b/sql/sql_explain.h
index 02357acd873..6e1b55e2b00 100644
--- a/sql/sql_explain.h
+++ b/sql/sql_explain.h
@@ -320,7 +320,9 @@ public:
 
 /////////////////////////////////////////////////////////////////////////////
 
-/* 
+extern const char *unit_operation_text[4];
+
+/*
   Explain structure for a UNION.
 
   A UNION may or may not have "Using filesort".
@@ -336,6 +338,7 @@ public:
   {}
 
   enum explain_node_type get_type() { return EXPLAIN_UNION; }
+  unit_common_op operation;
 
   int get_select_id()
   {
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index 608eccbaff6..cc6e33263b8 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -4413,7 +4413,7 @@ void st_select_lex::set_explain_type(bool on_the_fly)
         {
           type= is_uncacheable ? "UNCACHEABLE UNION": "UNION";
           if (this == master_unit()->fake_select_lex)
-            type= "UNION RESULT";
+            type= unit_operation_text[master_unit()->common_op()];
           /*
             join below may be =NULL when this functions is called at an early
             stage. It will be later called again and we will set the correct
@@ -4484,6 +4484,7 @@ void SELECT_LEX::increase_derived_records(ha_rows records)
     // in worse case none of record will be removed
     break;
   default:
+    // usual UNION
     result->records+= records;
     break;
   }
@@ -4740,6 +4741,42 @@ void LEX::restore_set_statement_var()
   DBUG_VOID_RETURN;
 }
 
+unit_common_op st_select_lex_unit::common_op()
+{
+  SELECT_LEX *first= first_select();
+  bool first_op= TRUE;
+  unit_common_op operation= OP_MIX; // if no op
+  for (SELECT_LEX *sl= first; sl; sl= sl->next_select())
+  {
+    if (sl != first)
+    {
+      unit_common_op op;
+      switch (sl->linkage)
+      {
+      case INTERSECT_TYPE:
+        op= OP_INTERSECT;
+        break;
+      case EXCEPT_TYPE:
+        op= OP_EXCEPT;
+        break;
+      default:
+        op= OP_UNION;
+        break;
+      }
+      if (first_op)
+      {
+        operation= op;
+        first_op= TRUE;
+      }
+      else
+      {
+        if (operation != op)
+          operation= OP_MIX;
+      }
+    }
+  }
+  return operation;
+}
 /*
   Save explain structures of a UNION. The only variable member is whether the 
   union has "Using filesort".
@@ -4776,11 +4813,10 @@ int st_select_lex_unit::save_union_explain(Explain_query *output)
     Note: Non-merged semi-joins cannot be made out of UNIONs currently, so we
     dont ever set EXPLAIN_NODE_NON_MERGED_SJ.
   */
-
   for (SELECT_LEX *sl= first; sl; sl= sl->next_select())
     eu->add_select(sl->select_number);
 
-  eu->fake_select_type= "UNION RESULT";
+  eu->fake_select_type= unit_operation_text[eu->operation= common_op()];
   eu->using_filesort= MY_TEST(global_parameters()->order_list.first);
   eu->using_tmp= union_needs_tmp_table();
 
@@ -4836,6 +4872,12 @@ bool LEX::is_partition_management() const
 }
 
 
+/**
+  Exclude last added SELECT_LEX (current) in the UNIT and return pointer in it
+  (previous become currect)
+
+  @return detached SELECT_LEX or NULL in case of error
+*/
 
 SELECT_LEX *LEX::exclude_last_select()
 {
@@ -4857,6 +4899,26 @@ SELECT_LEX *LEX::exclude_last_select()
   DBUG_RETURN(exclude);
 }
 
+
+/**
+  Put given (new) SELECT_LEX level below after currect (last) SELECT
+
+  LAST SELECT -> DUMMY SELECT
+                     |
+                     V
+                 NEW UNIT
+                     |
+                     V
+                 NEW SELECT
+
+  SELECT (*LAST*) ... FROM (SELECT (*NEW*) ... )
+
+  @param nselect         Select to put one level below
+
+  @retval TRUE  Error
+  @retval FALSE OK
+*/
+
 bool LEX::add_unit_in_brackets(SELECT_LEX *nselect)
 {
   DBUG_ENTER("LEX::add_unit_in_brackets");
@@ -4872,7 +4934,7 @@ bool LEX::add_unit_in_brackets(SELECT_LEX *nselect)
   Name_resolution_context *context= &dummy_select->context;
   context->init();
 
-  /* add  SELECT list*/
+  /* add SELECT list*/
   Item *item= new (thd->mem_root)
     Item_field(thd, context, NULL, NULL, "*");
   if (item == NULL)
@@ -4895,8 +4957,11 @@ bool LEX::add_unit_in_brackets(SELECT_LEX *nselect)
   char buff[10];
   LEX_STRING alias;
   alias.length= my_snprintf(buff, sizeof(buff),
-                            "__%d", dummy_select->select_number);
-  alias.str= thd->strdup(buff);
+                            "__%u", dummy_select->select_number);
+  alias.str= thd->strmake(buff, alias.length);
+  if (!alias.str)
+    DBUG_RETURN(TRUE);
+
   TABLE_LIST *table_list;
   if (!(table_list= dummy_select->add_table_to_list(thd, ti, &alias,
                                                     0, TL_READ,
@@ -4912,6 +4977,15 @@ bool LEX::add_unit_in_brackets(SELECT_LEX *nselect)
   DBUG_RETURN(rc);
 }
 
+
+/**
+  Checks if we need finish "automatic brackets" mode
+
+  INTERSECT has higher priority then UNION and EXCEPT, so when it is need we
+  automatically create lower layer for INTERSECT (automatic brackets) and
+  here we check if we should return back one level up during parsing procedure.
+*/
+
 void LEX::check_automatic_up(enum sub_select_type type)
 {
   if (type != INTERSECT_TYPE &&
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index 3525e36aa5b..e2c6f4aaaaf 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -40,6 +40,7 @@ enum sub_select_type
   UNION_TYPE, INTERSECT_TYPE, EXCEPT_TYPE,
   GLOBAL_OPTIONS_TYPE, DERIVED_TABLE_TYPE, OLAP_TYPE
 };
+enum unit_common_op {OP_MIX, OP_UNION, OP_INTERSECT, OP_EXCEPT};
 /* These may not be declared yet */
 class Table_ident;
 class sql_exchange;
@@ -738,6 +739,7 @@ public:
   select_unit *get_union_result() { return union_result; }
   int save_union_explain(Explain_query *output);
   int save_union_explain_part2(Explain_query *output);
+  unit_common_op common_op();
 };
 
 typedef class st_select_lex_unit SELECT_LEX_UNIT;
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 814cf20979e..0bd787de1cd 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -24694,7 +24694,7 @@ bool mysql_explain_union(THD *thd, SELECT_LEX_UNIT *unit, select_result *result)
     if (unit->union_needs_tmp_table() && unit->fake_select_lex)
     {
       unit->fake_select_lex->select_number= FAKE_SELECT_LEX_ID; // just for initialization
-      unit->fake_select_lex->type= "UNION RESULT";
+      unit->fake_select_lex->type= unit_operation_text[unit->common_op()];
       unit->fake_select_lex->options|= SELECT_DESCRIBE;
     }
     if (!(res= unit->prepare(thd, result, SELECT_NO_UNLOCK | SELECT_DESCRIBE)))
diff --git a/sql/sql_union.cc b/sql/sql_union.cc
index dfe6f159703..3d9949a2e07 100644
--- a/sql/sql_union.cc
+++ b/sql/sql_union.cc
@@ -54,7 +54,38 @@ int select_unit::prepare(List<Item> &list, SELECT_LEX_UNIT *u)
   return 0;
 }
 
+/**
+  This called by SELECT_LEX_UNIT::exec when select chenged
+*/
+
+void select_unit::change_select()
+{
+  uint current_select_number= thd->lex->current_select->select_number;
+  DBUG_ENTER("select_unit::change_select");
+  DBUG_PRINT("enter", ("select in unit change: %u -> %u",
+                       curr_sel, current_select_number));
+  DBUG_ASSERT(curr_sel != current_select_number);
+  curr_sel= current_select_number;
+  /* New SELECT processing starts */
+  DBUG_ASSERT(table->file->inited == 0);
+  switch (thd->lex->current_select->linkage)
+  {
+  case INTERSECT_TYPE:
+  case EXCEPT_TYPE:
+    step= thd->lex->current_select->linkage;
+    break;
+  default:
+    step= UNION_TYPE;
+    break;
+  }
 
+  if (step == INTERSECT_TYPE)
+  {
+    intersect_mark->value= prev_step= curr_step;
+    curr_step= current_select_number;
+  }
+  DBUG_VOID_RETURN;
+}
 /**
   Fill temporary tables for UNION/EXCEPT/INTERSECT
 
@@ -82,31 +113,6 @@ int select_unit::send_data(List<Item> &values)
 {
   int rc;
   int not_reported_error= 0;
-  if (curr_sel != thd->lex->current_select->select_number)
-  {
-    curr_sel= thd->lex->current_select->select_number;
-    /* New SELECT processing starts */
-    DBUG_ASSERT(table->file->inited == 0);
-    switch (thd->lex->current_select->linkage)
-    {
-      case INTERSECT_TYPE:
-        step= intersect_step;
-        break;
-      case EXCEPT_TYPE:
-        step= except_step;
-        break;
-      default:
-        step= union_step;
-        break;
-    }
-
-    if (step == intersect_step)
-    {
-      prev_step= curr_step;
-      intersect_mark->value= prev_step;
-      curr_step= thd->lex->current_select->select_number;
-    }
-  }
   if (unit->offset_limit_cnt)
   {						// using limit offset,count
     unit->offset_limit_cnt--;
@@ -117,8 +123,12 @@ int select_unit::send_data(List<Item> &values)
   if (table->no_rows_with_nulls)
     table->null_catch_flags= CHECK_ROW_FOR_NULLS_TO_REJECT;
   if (intersect_mark)
-    values.push_front(intersect_mark);
-  fill_record(thd, table, table->field, values, TRUE, FALSE);
+  {
+    fill_record(thd, table, table->field + 1, values, TRUE, FALSE);
+    table->field[0]->store((ulonglong) curr_step, 1);
+  }
+  else
+    fill_record(thd, table, table->field, values, TRUE, FALSE);
   if (thd->is_error())
   {
     rc= 1;
@@ -134,9 +144,10 @@ int select_unit::send_data(List<Item> &values)
     }
   }
 
+  // select_unit::change_select() change step & Co correctly for each SELECT
   switch (step)
   {
-    case union_step:
+    case UNION_TYPE:
     {
       if ((write_err= table->file->ha_write_tmp_row(table->record[0])))
       {
@@ -169,7 +180,7 @@ int select_unit::send_data(List<Item> &values)
       }
       break;
     }
-    case except_step:
+    case EXCEPT_TYPE:
     {
       int find_res;
       /*
@@ -185,10 +196,13 @@ int select_unit::send_data(List<Item> &values)
         goto end;
       }
       else
-        DBUG_ASSERT(find_res == 1);
+      {
+        if ((rc= not_reported_error= (find_res != 1)))
+          goto end;
+      }
       break;
     }
-    case intersect_step:
+    case INTERSECT_TYPE:
     {
       int find_res;
       /*
@@ -200,10 +214,7 @@ int select_unit::send_data(List<Item> &values)
         DBUG_ASSERT(!table->triggers);
         if (table->field[0]->val_int() != prev_step)
         {
-          table->status|= STATUS_DELETED;
-          not_reported_error=
-            table->file->ha_delete_tmp_row(table->record[0]);
-          rc= MY_TEST(not_reported_error);
+          rc= 0;
           goto end;
         }
         store_record(table, record[1]);
@@ -215,9 +226,14 @@ int select_unit::send_data(List<Item> &values)
         goto end;
       }
       else
-        DBUG_ASSERT(find_res == 1);
+      {
+        if ((rc= not_reported_error= (find_res != 1)))
+          goto end;
+      }
       break;
     }
+    default:
+      DBUG_ASSERT(0);
   }
   rc= 0;
 
@@ -227,14 +243,12 @@ end:
     DBUG_ASSERT(rc);
     table->file->print_error(not_reported_error, MYF(0));
   }
-  if (intersect_mark)
-    values.pop();
   return rc;
 }
 
 bool select_unit::send_eof()
 {
-  if (step != intersect_step ||
+  if (step != INTERSECT_TYPE ||
       (thd->lex->current_select->next_select() &&
        thd->lex->current_select->next_select()->linkage == INTERSECT_TYPE))
   {
@@ -250,7 +264,7 @@ bool select_unit::send_eof()
     It is last select in the sequence of INTERSECTs so we should filter out
     all records except marked with actual counter.
 
-   TODO: as optimisation for simple case this could be moved to
+   TODO: as optimization for simple case this could be moved to
    'fake_select' WHERE condition
   */
   handler *file= table->file;
@@ -262,15 +276,18 @@ bool select_unit::send_eof()
   do
   {
     error= file->ha_rnd_next(table->record[0]);
-    if (error == HA_ERR_RECORD_DELETED)
-    {
-      error= 0;
-      continue;
-    }
     if (error)
     {
       if (error == HA_ERR_END_OF_FILE)
+      {
+        error= 0;
+        break;
+      }
+      if (unlikely(error == HA_ERR_RECORD_DELETED))
+      {
         error= 0;
+        continue;
+      }
       break;
     }
     if (table->field[0]->val_int() != curr_step)
@@ -943,6 +960,12 @@ bool st_select_lex_unit::prepare(THD *thd_arg, select_result *sel_result,
         hidden= 1;
         if (!intersect_mark)
         {
+          /*
+            For intersect we add a hidden column first that contains
+            the current select number of the time when the row was
+            added to the temporary table
+          */
+
           Query_arena *arena, backup_arena;
           arena= thd->activate_stmt_arena_if_needed(&backup_arena);
 
@@ -959,14 +982,16 @@ bool st_select_lex_unit::prepare(THD *thd_arg, select_result *sel_result,
         types.push_front(union_result->intersect_mark= intersect_mark);
         union_result->intersect_mark->name= (char *)"___";
       }
-      if (union_result->create_result_table(thd, &types,
-                                            MY_TEST(union_distinct),
-                                            create_options, "", false,
-                                            instantiate_tmp_table, false,
-                                            hidden))
-        goto err;
+      bool error=
+        union_result->create_result_table(thd, &types,
+                                          MY_TEST(union_distinct),
+                                          create_options, "", false,
+                                          instantiate_tmp_table, false,
+                                          hidden);
       if (intersect_mark)
         types.pop();
+      if (error)
+        goto err;
     }
     if (fake_select_lex && !fake_select_lex->first_cond_optimization)
     {
@@ -993,6 +1018,7 @@ bool st_select_lex_unit::prepare(THD *thd_arg, select_result *sel_result,
       arena= thd->activate_stmt_arena_if_needed(&backup_arena);
       
       saved_error= table->fill_item_list(&item_list);
+      // Item_list is inherited from 'types', so there could be the counter
       if (intersect_mark)
         item_list.pop(); // remove intersect counter
 
@@ -1182,6 +1208,8 @@ bool st_select_lex_unit::exec()
     {
       ha_rows records_at_start= 0;
       thd->lex->current_select= sl;
+      if (union_result)
+        union_result->change_select();
       if (fake_select_lex)
       {
         if (sl != &thd->lex->select_lex)
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 4ca96364834..e0739ea848e 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -701,7 +701,7 @@ bool LEX::add_select_to_union_list(bool is_union_distinct,
        current_select != current_select->master_unit()->first_select()))
   {
     /*
-      This and previous SELECTs should go one level down becaous of
+      This and previous SELECTs should go one level down because of
       priority
     */
     SELECT_LEX *prev= exclude_last_select();
@@ -11063,10 +11063,6 @@ table_primary_derived:
               lex->pop_context();
               lex->nest_level--;
             }
-            /*else if (($3->select_lex &&
-                      $3->select_lex->master_unit()->is_unit_op() &&
-                      ($3->select_lex->master_unit()->first_select() ==
-                       $3->select_lex || !$3->lifted)) || $5)*/
             else if ($5 != NULL)
             {
               /*