MDEV-18844 Implement EXCEPT ALL and INTERSECT ALL operations

1 files changed, 1 insertions, 43 deletions

diff --git a/mysql-test/main/intersect.result b/mysql-test/main/intersect.result
index bd88243f151..034018de2e2 100644
--- a/mysql-test/main/intersect.result
+++ b/mysql-test/main/intersect.result
@@ -504,8 +504,6 @@ select 1 as a from dual union all select 1 from dual;
 a
 1
 1
-select 1 from dual intersect all select 1 from dual;
-ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'all select 1 from dual' at line 1
 create table t1 (a int, b blob, a1 int, b1 blob);
 create table t2 (c int, d blob, c1 int, d1 blob);
 insert into t1 values (1,"ddd", 1, "sdfrrwwww"),(2, "fgh", 2, "dffggtt");
@@ -607,22 +605,6 @@ NULL	INTERSECT RESULT	<intersect2,3>	ALL	NULL	NULL	NULL	NULL	NULL	NULL
 NULL	UNION RESULT	<union1,5,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#5 */ select `__5`.`c` AS `c`,`__5`.`d` AS `d` from ((/* 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`)) `__5` union (/* select#4 */ select 4 AS `4`,4 AS `4`)
-set SQL_MODE=ORACLE;
-(select a,b from t1) union (select c,d from t2) intersect (select e,f from t3) union (select 4,4);
-a	b
-3	3
-4	4
-explain extended
-(select a,b from t1) union (select c,d from t2) intersect (select e,f from t3) union (select 4,4);
-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	UNION	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	
-3	INTERSECT	t3	ALL	NULL	NULL	NULL	NULL	2	100.00	
-4	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
-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") intersect (/* select#3 */ select "test"."t3"."e" AS "e","test"."t3"."f" AS "f" from "test"."t3") union (/* select#4 */ select 4 AS "4",4 AS "4")
-set SQL_MODE=default;
 (select e,f from t3) intersect (select c,d from t2) union (select a,b from t1) union (select 4,4);
 e	f
 3	3
@@ -639,24 +621,6 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 NULL	UNIT RESULT	<unit1,2,3,4>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
 Warnings:
 Note	1003	(/* select#1 */ select `test`.`t3`.`e` AS `e`,`test`.`t3`.`f` AS `f` from `test`.`t3`) intersect (/* select#2 */ select `test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2`) union (/* select#3 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1`) union (/* select#4 */ select 4 AS `4`,4 AS `4`)
-set SQL_MODE=ORACLE;
-(select e,f from t3) intersect (select c,d from t2) union (select a,b from t1) union (select 4,4);
-e	f
-3	3
-4	4
-5	5
-6	6
-explain extended
-(select e,f from t3) intersect (select c,d from t2) union (select a,b from t1) union (select 4,4);
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	100.00	
-2	INTERSECT	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	
-3	UNION	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
-4	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
-NULL	UNIT RESULT	<unit1,2,3,4>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
-Warnings:
-Note	1003	(/* select#1 */ select "test"."t3"."e" AS "e","test"."t3"."f" AS "f" from "test"."t3") intersect (/* select#2 */ select "test"."t2"."c" AS "c","test"."t2"."d" AS "d" from "test"."t2") union (/* select#3 */ select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from "test"."t1") union (/* select#4 */ select 4 AS "4",4 AS "4")
-set SQL_MODE=default;
 (/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1`) union /* select#3 */ select `__3`.`c` AS `c`,`__3`.`d` AS `d` from ((/* select#2 */ 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`)) `__3` union (/* select#5 */ select 4 AS `4`,4 AS `4`);
 a	b
 3	3
@@ -820,12 +784,6 @@ create table t234(c1 int);
 insert into t234 values(2);
 insert into t234 values(3);
 insert into t234 values(4);
-set SQL_MODE=oracle;
-select * from t13 union select * from t234 intersect select * from t12;
-c1
-1
-2
-set SQL_MODE=default;
 select * from t13 union select * from t234 intersect select * from t12;
 c1
 1
@@ -848,9 +806,9 @@ select * from t2 where a < 5
 intersect
 select * from t3 where a < 5;
 a
+1
 7
 7
-1
 explain extended
 select * from t1 where a > 4
 union all