Adding upstream version 1:10.11.6.upstream/1%10.11.6upstream

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

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