revision-id: 5e2ccdd59279c80b65ab94146ba85a2391a0b976 (mariadb-10.4.4-286-g5e2ccdd) parent(s): afe969ba05faece41fdef1275e9b9c510081805b author: Ruihang Xia committer: Igor Babaev timestamp: 2019-08-31 17:44:47 -0700 message: Author: Ruihang Xia <waynestxia@gmail.com> Date: Fri Aug 30 14:47:12 2019 +0800 MDEV-18844 Implement EXCEPT ALL and INTERSECT ALL operations --- mysql-test/main/brackets.result | 2 +- mysql-test/main/except.result | 2 - mysql-test/main/except.test | 2 - mysql-test/main/except_all.result | 660 +++++++++++++++ mysql-test/main/except_all.test | 99 +++ mysql-test/main/intersect.result | 44 +- mysql-test/main/intersect.test | 22 +- mysql-test/main/intersect_all.result | 888 ++++++++++++++++++++ mysql-test/main/intersect_all.test | 328 ++++++++ mysql-test/main/set_operation.result | 1157 +++++++++++++++++++++++++++ mysql-test/main/set_operation.test | 526 ++++++++++++ mysql-test/main/set_operation_oracle.result | 75 ++ mysql-test/main/set_operation_oracle.test | 65 ++ sql/sql_class.h | 151 +++- sql/sql_lex.cc | 15 +- sql/sql_lex.h | 40 +- sql/sql_tvc.cc | 1 + sql/sql_union.cc | 947 ++++++++++++++++++---- sql/sql_yacc.yy | 8 +- storage/heap/hp_write.c | 26 +- 20 files changed, 4807 insertions(+), 251 deletions(-) diff --git a/mysql-test/main/brackets.result b/mysql-test/main/brackets.result index e14bef9..8fc54e6 100644 --- a/mysql-test/main/brackets.result +++ b/mysql-test/main/brackets.result @@ -54,7 +54,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 3 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used NULL UNION RESULT <union1,4> ALL NULL NULL NULL NULL NULL NULL Warnings: -Note 1003 /* select#1 */ select 1 AS `1` union /* select#4 */ select `__4`.`1` AS `1` from (/* select#2 */ select 1 AS `1` union all /* select#3 */ select 1 AS `1`) `__4` +Note 1003 /* select#1 */ select 1 AS `1` union /* select#4 */ select `__4`.`1` AS `1` from (/* select#2 */ select 1 AS `1` union /* select#3 */ select 1 AS `1`) `__4` select 1 union select 1 union all select 1; 1 1 diff --git a/mysql-test/main/except.result b/mysql-test/main/except.result index 9c5a3ea..3416ad0 100644 --- a/mysql-test/main/except.result +++ b/mysql-test/main/except.result @@ -507,8 +507,6 @@ select 1 as a from dual union all select 1 from dual; a 1 1 -select 1 from dual except 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) 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"); diff --git a/mysql-test/main/except.test b/mysql-test/main/except.test index 32aa0b9..de387cc 100644 --- a/mysql-test/main/except.test +++ b/mysql-test/main/except.test @@ -66,8 +66,6 @@ select 1 as a from dual except select 1 from dual; select 1 from dual ORDER BY 1 except select 1 from dual; select 1 as a from dual union all select 1 from dual; ---error ER_PARSE_ERROR -select 1 from dual except all select 1 from dual; create table t1 (a int, b blob, a1 int, b1 blob) engine=MyISAM; diff --git a/mysql-test/main/except_all.result b/mysql-test/main/except_all.result new file mode 100644 index 0000000..19ff9f3 --- /dev/null +++ b/mysql-test/main/except_all.result @@ -0,0 +1,660 @@ +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),(3,3),(2,2),(4,4),(4,4),(4,4); +insert into t2 values (1,1),(1,1),(1,1),(2,2),(3,3),(3,3),(5,5); +select * from t1 except select * from t2; +a b +4 4 +select * from t1 except all select * from t2; +a b +4 4 +2 2 +4 4 +4 4 +select * from t1 except all select c+1,d+1 from t2; +a b +1 1 +4 4 +(select * from t1) except all (select * from t2); +a b +4 4 +2 2 +4 4 +4 4 +select * from ((select * from t1) except all (select * from t2)) a; +a b +4 4 +2 2 +4 4 +4 4 +select * from ((select a from t1) except all (select c from t2)) a; +a +4 +2 +4 +4 +select * from t1 except all select * from t1 union all select * from t1 union all select * from t1 except select * from t2; +a b +4 4 +select * from t1 except all select * from t1 union all select * from t1 union all select * from t1 except all select * from t2; +a b +4 4 +2 2 +4 4 +4 4 +4 4 +4 4 +4 4 +2 2 +2 2 +select * from (select * from t1 except all select * from t2) q1 except all select * from (select * from t1 except all select * from t2) q2; +a b +EXPLAIN select * from t1 except all select * from t2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 7 +2 EXCEPT t2 ALL NULL NULL NULL NULL 7 +NULL EXCEPT RESULT <except1,2> ALL NULL NULL NULL NULL NULL +EXPLAIN format=json select * from t1 except all select * from t2; +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": 7, + "filtered": 100 + } + } + }, + { + "query_block": { + "select_id": 2, + "operation": "EXCEPT", + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 7, + "filtered": 100 + } + } + } + ] + } + } +} +EXPLAIN extended (select * from t1) except all (select * from t2); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 7 100.00 +2 EXCEPT t2 ALL NULL NULL NULL NULL 7 100.00 +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 all (/* select#2 */ select `test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2`) +EXPLAIN extended select * from ((select * from t1) except all (select * from t2)) a; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 7 100.00 +2 DERIVED t1 ALL NULL NULL NULL NULL 7 100.00 +3 EXCEPT t2 ALL NULL NULL NULL NULL 7 100.00 +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 all (/* select#3 */ select `test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2`)) `a` +ANALYZE format=json select * from ((select a,b from t1) except all (select c,d from t2)) 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": 7, + "r_rows": 4, + "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": 4, + "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": 7, + "r_rows": 7, + "r_total_time_ms": "REPLACED", + "filtered": 100, + "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": 7, + "r_rows": 7, + "r_total_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100 + } + } + } + ] + } + } + } + } + } +} +ANALYZE format=json select * from ((select a from t1) except all (select c from t2)) 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": 7, + "r_rows": 4, + "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": 4, + "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": 7, + "r_rows": 7, + "r_total_time_ms": "REPLACED", + "filtered": 100, + "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": 7, + "r_rows": 7, + "r_total_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100 + } + } + } + ] + } + } + } + } + } +} +select * from ((select a from t1) except all (select c from t2)) a; +a +4 +2 +4 +4 +prepare stmt from "(select a,b from t1) except all (select c,d from t2)"; +execute stmt; +a b +4 4 +2 2 +4 4 +4 4 +execute stmt; +a b +4 4 +2 2 +4 4 +4 4 +prepare stmt from "select * from ((select a,b from t1) except all (select c,d from t2)) a"; +execute stmt; +a b +4 4 +2 2 +4 4 +4 4 +execute stmt; +a b +4 4 +2 2 +4 4 +4 4 +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),(2,2); +insert into t2 values (2,2),(3,3); +insert into t3 values (4,4),(5,5),(4,4); +insert into t4 values (4,4),(7,7),(4,4); +(select a,b,e,f from t1,t3) except all (select c,d,g,h from t2,t4); +a b e f +1 1 4 4 +2 2 4 4 +1 1 5 5 +2 2 5 5 +1 1 4 4 +2 2 4 4 +2 2 5 5 +select * from ((select a,b,e,f from t1,t3) except all (select c,d,g,h from t2,t4)) t; +a b e f +1 1 4 4 +2 2 4 4 +1 1 5 5 +2 2 5 5 +1 1 4 4 +2 2 4 4 +2 2 5 5 +(select * from t1,t3) except all (select * from t2,t4); +a b e f +1 1 4 4 +2 2 4 4 +1 1 5 5 +2 2 5 5 +1 1 4 4 +2 2 4 4 +2 2 5 5 +(select a,b,e from t1,t3) except all (select c,d,g from t2,t4); +a b e +1 1 4 +2 2 4 +1 1 5 +2 2 5 +1 1 4 +2 2 4 +2 2 5 +EXPLAIN (select a,b,e,f from t1,t3) except all (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 3 +1 PRIMARY t3 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join) +2 EXCEPT t2 ALL NULL NULL NULL NULL 2 +2 EXCEPT t4 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join) +NULL EXCEPT RESULT <except1,2> ALL NULL NULL NULL NULL NULL +EXPLAIN select * from ((select a,b,e,f from t1,t3) except all (select c,d,g,h from t2,t4)) t; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 9 +2 DERIVED t1 ALL NULL NULL NULL NULL 3 +2 DERIVED t3 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join) +3 EXCEPT t2 ALL NULL NULL NULL NULL 2 +3 EXCEPT t4 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join) +NULL EXCEPT RESULT <except2,3> ALL NULL NULL NULL NULL NULL +EXPLAIN extended select * from ((select a,b,e,f from t1,t3) except all (select c,d,g,h from t2,t4)) t; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 9 100.00 +2 DERIVED t1 ALL NULL NULL NULL NULL 3 100.00 +2 DERIVED t3 ALL NULL NULL NULL NULL 3 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 3 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 `t`.`a` AS `a`,`t`.`b` AS `b`,`t`.`e` AS `e`,`t`.`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 all (/* 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`)) `t` +EXPLAIN format=json select * from ((select a,b,e,f from t1,t3) except all (select c,d,g,h from t2,t4)) t; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "<derived2>", + "access_type": "ALL", + "rows": 9, + "filtered": 100, + "materialized": { + "query_block": { + "union_result": { + "table_name": "<except2,3>", + "access_type": "ALL", + "query_specifications": [ + { + "query_block": { + "select_id": 2, + "table": { + "table_name": "t1", + "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": "119", + "join_type": "BNL" + } + } + }, + { + "query_block": { + "select_id": 3, + "operation": "EXCEPT", + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 2, + "filtered": 100 + }, + "block-nl-join": { + "table": { + "table_name": "t4", + "access_type": "ALL", + "rows": 3, + "filtered": 100 + }, + "buffer_type": "flat", + "buffer_size": "119", + "join_type": "BNL" + } + } + } + ] + } + } + } + } + } +} +ANALYZE format=json (select a,b,e,f from t1,t3) except all (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": 7, + "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 + }, + "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": "119", + "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": 3, + "r_rows": 3, + "r_total_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100 + }, + "buffer_type": "flat", + "buffer_size": "119", + "join_type": "BNL", + "r_filtered": 100 + } + } + } + ] + } + } +} +ANALYZE format=json select * from ((select a,b,e,f from t1,t3) except all (select c,d,g,h from t2,t4)) t; +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": 9, + "r_rows": 7, + "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": 7, + "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 + }, + "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": "119", + "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": 3, + "r_rows": 3, + "r_total_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100 + }, + "buffer_type": "flat", + "buffer_size": "119", + "join_type": "BNL", + "r_filtered": 100 + } + } + } + ] + } + } + } + } + } +} +prepare stmt from "(select a,b,e,f from t1,t3) except all (select c,d,g,h from t2,t4)"; +execute stmt; +a b e f +1 1 4 4 +2 2 4 4 +1 1 5 5 +2 2 5 5 +1 1 4 4 +2 2 4 4 +2 2 5 5 +execute stmt; +a b e f +1 1 4 4 +2 2 4 4 +1 1 5 5 +2 2 5 5 +1 1 4 4 +2 2 4 4 +2 2 5 5 +prepare stmt from "select * from ((select a,b,e,f from t1,t3) except all (select c,d,g,h from t2,t4)) a"; +execute stmt; +a b e f +1 1 4 4 +2 2 4 4 +1 1 5 5 +2 2 5 5 +1 1 4 4 +2 2 4 4 +2 2 5 5 +execute stmt; +a b e f +1 1 4 4 +2 2 4 4 +1 1 5 5 +2 2 5 5 +1 1 4 4 +2 2 4 4 +2 2 5 5 +drop tables t1,t2,t3,t4; +select 1 as a from dual except all select 1 from dual; +a +(select 1 from dual) except all (select 1 from dual); +1 +(select 1 from dual into @v) except 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 'into @v) except all (select 1 from dual)' at line 1 +select 1 from dual ORDER BY 1 except 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 'except all select 1 from dual' at line 1 +select 1 as a from dual union all select 1 from dual; +a +1 +1 +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"),(2, "fgh", 2, "dffggtt"); +insert into t2 values (2, "fgh", 2, "dffggtt"),(3, "ffggddd", 3, "dfgg"); +(select a,b,b1 from t1) except all (select c,d,d1 from t2); +a b b1 +1 ddd sdfrrwwww +2 fgh dffggtt +create table t3 (select a,b,b1 from t1) except all (select c,d,d1 from t2); +show create table t3; +Table Create Table +t3 CREATE TABLE `t3` ( + `a` int(11) DEFAULT NULL, + `b` blob DEFAULT NULL, + `b1` blob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop tables t1,t2,t3; +CREATE TABLE t (i INT); +INSERT INTO t VALUES (1),(2); +SELECT * FROM t WHERE i != ANY ( SELECT 3 EXCEPT ALL SELECT 3 ); +i +drop table t; diff --git a/mysql-test/main/except_all.test b/mysql-test/main/except_all.test new file mode 100644 index 0000000..f873b22 --- /dev/null +++ b/mysql-test/main/except_all.test @@ -0,0 +1,99 @@ +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),(3,3),(2,2),(4,4),(4,4),(4,4); +insert into t2 values (1,1),(1,1),(1,1),(2,2),(3,3),(3,3),(5,5); + +select * from t1 except select * from t2; +select * from t1 except all select * from t2; +select * from t1 except all select c+1,d+1 from t2; +(select * from t1) except all (select * from t2); +select * from ((select * from t1) except all (select * from t2)) a; +select * from ((select a from t1) except all (select c from t2)) a; +select * from t1 except all select * from t1 union all select * from t1 union all select * from t1 except select * from t2; + +select * from t1 except all select * from t1 union all select * from t1 union all select * from t1 except all select * from t2; + +select * from (select * from t1 except all select * from t2) q1 except all select * from (select * from t1 except all select * from t2) q2; + +EXPLAIN select * from t1 except all select * from t2; +EXPLAIN format=json select * from t1 except all select * from t2; +EXPLAIN extended (select * from t1) except all (select * from t2); +EXPLAIN extended select * from ((select * from t1) except all (select * from t2)) a; + +--source include/analyze-format.inc +ANALYZE format=json select * from ((select a,b from t1) except all (select c,d from t2)) a; +--source include/analyze-format.inc +ANALYZE format=json select * from ((select a from t1) except all (select c from t2)) a; +select * from ((select a from t1) except all (select c from t2)) a; + +prepare stmt from "(select a,b from t1) except all (select c,d from t2)"; +execute stmt; +execute stmt; + +prepare stmt from "select * from ((select a,b from t1) except all (select c,d from t2)) a"; +execute stmt; +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),(2,2); +insert into t2 values (2,2),(3,3); +insert into t3 values (4,4),(5,5),(4,4); +insert into t4 values (4,4),(7,7),(4,4); + +(select a,b,e,f from t1,t3) except all (select c,d,g,h from t2,t4); +select * from ((select a,b,e,f from t1,t3) except all (select c,d,g,h from t2,t4)) t; +(select * from t1,t3) except all (select * from t2,t4); +(select a,b,e from t1,t3) except all (select c,d,g from t2,t4); + +EXPLAIN (select a,b,e,f from t1,t3) except all (select c,d,g,h from t2,t4); +EXPLAIN select * from ((select a,b,e,f from t1,t3) except all (select c,d,g,h from t2,t4)) t; +EXPLAIN extended select * from ((select a,b,e,f from t1,t3) except all (select c,d,g,h from t2,t4)) t; +EXPLAIN format=json select * from ((select a,b,e,f from t1,t3) except all (select c,d,g,h from t2,t4)) t; + +--source include/analyze-format.inc +ANALYZE format=json (select a,b,e,f from t1,t3) except all (select c,d,g,h from t2,t4); +--source include/analyze-format.inc +ANALYZE format=json select * from ((select a,b,e,f from t1,t3) except all (select c,d,g,h from t2,t4)) t; + +prepare stmt from "(select a,b,e,f from t1,t3) except all (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 all (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 all select 1 from dual; +(select 1 from dual) except all (select 1 from dual); +--error ER_PARSE_ERROR +(select 1 from dual into @v) except all (select 1 from dual); +--error ER_PARSE_ERROR +select 1 from dual ORDER BY 1 except all select 1 from dual; +select 1 as a from dual union all select 1 from dual; + +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"),(2, "fgh", 2, "dffggtt"); +insert into t2 values (2, "fgh", 2, "dffggtt"),(3, "ffggddd", 3, "dfgg"); + + +(select a,b,b1 from t1) except all (select c,d,d1 from t2); +# make sure that blob is used +create table t3 (select a,b,b1 from t1) except all (select c,d,d1 from t2); +show create table t3; + +drop tables t1,t2,t3; + +CREATE TABLE t (i INT); +INSERT INTO t VALUES (1),(2); + +SELECT * FROM t WHERE i != ANY ( SELECT 3 EXCEPT ALL SELECT 3 ); + +drop table t; \ No newline at end of file diff --git a/mysql-test/main/intersect.result b/mysql-test/main/intersect.result index bd88243..034018d 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 diff --git a/mysql-test/main/intersect.test b/mysql-test/main/intersect.test index 616a833..a99aa92 100644 --- a/mysql-test/main/intersect.test +++ b/mysql-test/main/intersect.test @@ -65,8 +65,6 @@ select 1 as a from dual intersect select 1 from dual; select 1 from dual ORDER BY 1 intersect select 1 from dual; select 1 as a from dual union all select 1 from dual; ---error ER_PARSE_ERROR -select 1 from dual intersect all select 1 from dual; @@ -147,12 +145,6 @@ insert into t3 values (1,1),(3,3); (select a,b from t1) union (select c,d from t2) intersect (select e,f from t3) union (select 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); -set SQL_MODE=ORACLE; ---sorted_result -(select a,b from t1) union (select c,d from t2) intersect (select e,f from t3) union (select 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); -set SQL_MODE=default; # test result of linear mix operation @@ -160,12 +152,6 @@ 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); explain extended (select e,f from t3) intersect (select c,d from t2) union (select a,b from t1) union (select 4,4); -set SQL_MODE=ORACLE; ---sorted_result -(select e,f from t3) intersect (select c,d from t2) union (select a,b from t1) union (select 4,4); -explain extended -(select e,f from t3) intersect (select c,d from t2) union (select a,b from t1) union (select 4,4); -set SQL_MODE=default; --sorted_result (/* 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`); @@ -310,11 +296,7 @@ create table t234(c1 int); insert into t234 values(2); insert into t234 values(3); insert into t234 values(4); - -set SQL_MODE=oracle; ---sorted_result -select * from t13 union select * from t234 intersect select * from t12; -set SQL_MODE=default; + --sorted_result select * from t13 union select * from t234 intersect select * from t12; @@ -333,7 +315,7 @@ insert into t2 values (4), (5), (9), (1), (8), (9); create table t3 (a int); insert into t3 values (8), (1), (8), (2), (3), (7), (2); - +--sorted_result select * from t1 where a > 4 union all select * from t2 where a < 5 diff --git a/mysql-test/main/intersect_all.result b/mysql-test/main/intersect_all.result new file mode 100644 index 0000000..66ee060 --- /dev/null +++ b/mysql-test/main/intersect_all.result @@ -0,0 +1,888 @@ +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),(3,3),(2,2); +insert into t2 values (2,2),(2,2),(5,5); +select * from t1 intersect all select * from t2; +a b +2 2 +2 2 +(select a,b from t1) intersect all (select c,d from t2); +a b +2 2 +2 2 +select * from ((select a,b from t1) intersect all (select c,d from t2)) t; +a b +2 2 +2 2 +select * from ((select a from t1) intersect all (select c from t2)) t; +a +2 +2 +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; +insert into t1 values (1,1),(2,2),(3,3),(2,2); +insert into t2 values (2,2),(3,3),(4,4),(2,2); +insert into t3 values (1,1),(2,2),(5,5),(2,2); +(select a,b from t1) intersect all (select c,d from t2) intersect all (select e,f from t3); +a b +2 2 +2 2 +EXPLAIN (select a,b from t1) intersect all (select c,d from t2) intersect all (select e,f from t3); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 +2 INTERSECT t2 ALL NULL NULL NULL NULL 4 +3 INTERSECT t3 ALL NULL NULL NULL NULL 4 +NULL INTERSECT RESULT <intersect1,2,3> ALL NULL NULL NULL NULL NULL +EXPLAIN extended (select a,b from t1) intersect all (select c,d from t2) intersect all (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 4 100.00 +2 INTERSECT t2 ALL NULL NULL NULL NULL 4 100.00 +3 INTERSECT t3 ALL NULL NULL NULL NULL 4 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`) +EXPLAIN extended select * from ((select a,b from t1) intersect all (select c,d from t2) intersect all (select e,f from t3)) 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 4 100.00 +3 INTERSECT t2 ALL NULL NULL NULL NULL 4 100.00 +4 INTERSECT t3 ALL NULL NULL NULL NULL 4 100.00 +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 all (/* select#3 */ select `test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2`) intersect all (/* 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 all (select c,d from t2) intersect all (select e,f from t3); +EXPLAIN +{ + "query_block": { + "union_result": { + "table_name": "<intersect1,2,3>", + "access_type": "ALL", + "query_specifications": [ + { + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 4, + "filtered": 100 + } + } + }, + { + "query_block": { + "select_id": 2, + "operation": "INTERSECT", + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 4, + "filtered": 100 + } + } + }, + { + "query_block": { + "select_id": 3, + "operation": "INTERSECT", + "table": { + "table_name": "t3", + "access_type": "ALL", + "rows": 4, + "filtered": 100 + } + } + } + ] + } + } +} +ANALYZE format=json (select a,b from t1) intersect all (select c,d from t2) intersect all (select e,f from t3); +ANALYZE +{ + "query_block": { + "union_result": { + "table_name": "<intersect1,2,3>", + "access_type": "ALL", + "r_loops": 1, + "r_rows": 2, + "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": 4, + "r_rows": 4, + "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": 4, + "r_rows": 4, + "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": "t3", + "access_type": "ALL", + "r_loops": 1, + "rows": 4, + "r_rows": 4, + "r_total_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100 + } + } + } + ] + } + } +} +ANALYZE format=json select * from ((select a,b from t1) intersect all (select c,d from t2) intersect all (select e,f from 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": 4, + "r_rows": 2, + "r_total_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100, + "materialized": { + "query_block": { + "union_result": { + "table_name": "<intersect2,3,4>", + "access_type": "ALL", + "r_loops": 1, + "r_rows": 2, + "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": 4, + "r_rows": 4, + "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": 4, + "r_rows": 4, + "r_total_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100 + } + } + }, + { + "query_block": { + "select_id": 4, + "operation": "INTERSECT", + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "table": { + "table_name": "t3", + "access_type": "ALL", + "r_loops": 1, + "rows": 4, + "r_rows": 4, + "r_total_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100 + } + } + } + ] + } + } + } + } + } +} +select * from ((select a,b from t1) intersect all (select c,d from t2) intersect all (select e,f from t3)) a; +a b +2 2 +2 2 +prepare stmt from "(select a,b from t1) intersect all (select c,d from t2) intersect all (select e,f from t3);"; +execute stmt; +a b +2 2 +2 2 +execute stmt; +a b +2 2 +2 2 +prepare stmt from "select * from ((select a,b from t1) intersect all (select c,d from t2) intersect all (select e,f from t3)) a"; +execute stmt; +a b +2 2 +2 2 +execute stmt; +a b +2 2 +2 2 +insert into t1 values (2,2),(3,3); +insert into t2 values (2,2),(2,2),(2,2); +(select a,b from t1) intersect all (select c,d from t2) intersect all (select e,f from t3); +a b +2 2 +2 2 +(select a,b from t1) intersect (select c,d from t2) intersect all (select e,f from t3); +a b +2 2 +insert into t3 values (2,2); +(select a,b from t1) intersect all (select c,d from t2) intersect (select e,f from t3); +a b +2 2 +(select a,b from t1) intersect all (select c,e from t2,t3); +a b +2 2 +2 2 +2 2 +EXPLAIN (select a,b from t1) intersect all (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 6 +2 INTERSECT t3 ALL NULL NULL NULL NULL 5 +2 INTERSECT t2 ALL NULL NULL NULL NULL 7 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 all (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 6 100.00 +2 INTERSECT t3 ALL NULL NULL NULL NULL 5 100.00 +2 INTERSECT t2 ALL NULL NULL NULL NULL 7 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 all (/* 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 all (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 6 100.00 +2 DERIVED t1 ALL NULL NULL NULL NULL 6 100.00 +3 INTERSECT t3 ALL NULL NULL NULL NULL 5 100.00 +3 INTERSECT t2 ALL NULL NULL NULL NULL 7 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 all (/* 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 all (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": 6, + "filtered": 100 + } + } + }, + { + "query_block": { + "select_id": 2, + "operation": "INTERSECT", + "table": { + "table_name": "t3", + "access_type": "ALL", + "rows": 5, + "filtered": 100 + }, + "block-nl-join": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 7, + "filtered": 100 + }, + "buffer_type": "flat", + "buffer_size": "65", + "join_type": "BNL" + } + } + } + ] + } + } +} +ANALYZE format=json (select a,b from t1) intersect all (select c,e from t2,t3); +ANALYZE +{ + "query_block": { + "union_result": { + "table_name": "<intersect1,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": 6, + "r_rows": 6, + "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": "t3", + "access_type": "ALL", + "r_loops": 1, + "rows": 5, + "r_rows": 5, + "r_total_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100 + }, + "block-nl-join": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "r_loops": 1, + "rows": 7, + "r_rows": 7, + "r_total_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100 + }, + "buffer_type": "flat", + "buffer_size": "65", + "join_type": "BNL", + "r_filtered": 100 + } + } + } + ] + } + } +} +ANALYZE format=json select * from ((select a,b from t1) intersect all (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": 6, + "r_rows": 3, + "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": 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": 6, + "r_rows": 6, + "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": "t3", + "access_type": "ALL", + "r_loops": 1, + "rows": 5, + "r_rows": 5, + "r_total_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100 + }, + "block-nl-join": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "r_loops": 1, + "rows": 7, + "r_rows": 7, + "r_total_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100 + }, + "buffer_type": "flat", + "buffer_size": "65", + "join_type": "BNL", + "r_filtered": 100 + } + } + } + ] + } + } + } + } + } +} +select * from ((select a,b from t1) intersect all (select c,e from t2,t3)) a; +a b +2 2 +2 2 +2 2 +prepare stmt from "(select a,b from t1) intersect all (select c,e from t2,t3);"; +execute stmt; +a b +2 2 +2 2 +2 2 +execute stmt; +a b +2 2 +2 2 +2 2 +prepare stmt from "select * from ((select a,b from t1) intersect all (select c,e from t2,t3)) a"; +execute stmt; +a b +2 2 +2 2 +2 2 +execute stmt; +a b +2 2 +2 2 +2 2 +drop tables t1,t2,t3; +select 1 as a from dual intersect all select 1 from dual; +a +1 +(select 1 from dual) intersect all (select 1 from dual); +1 +1 +(select 1 from dual into @v) 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 'into @v) intersect all (select 1 from dual)' at line 1 +select 1 from dual ORDER BY 1 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 'intersect all select 1 from dual' at line 1 +select 1 as a from dual union all select 1 from dual; +a +1 +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"),(2, "fgh", 2, "dffggtt"); +insert into t2 values (2, "fgh", 2, "dffggtt"),(3, "ffggddd", 3, "dfgg"),(2, "fgh", 2, "dffggtt"); +(select a,b,b1 from t1) intersect all (select c,d,d1 from t2); +a b b1 +2 fgh dffggtt +2 fgh dffggtt +drop tables t1,t2; +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),(2,2),(3,3); +insert into t2 values (2,2),(3,3),(4,4),(2,2),(2,2),(2,2); +insert into t3 values (1,1),(2,2),(5,5),(2,2),(5,5); +(select a,b from t1) intersect all (select c,d from t2) intersect all (select e,f from t3); +a b +2 2 +2 2 +select * from ((select a,b from t1) intersect all (select c,d from t2) intersect (select e,f from t3)) a; +a b +2 2 +prepare stmt from "(select a,b from t1) intersect all (select c,d from t2) intersect all (select e,f from t3);"; +execute stmt; +a b +2 2 +2 2 +execute stmt; +a b +2 2 +2 2 +prepare stmt from "select * from ((select a,b from t1) intersect all (select c,d from t2) intersect all (select e,f from t3)) a"; +execute stmt; +a b +2 2 +2 2 +execute stmt; +a b +2 2 +2 2 +create table t4 (select a,b from t1) intersect all (select c,d from t2) intersect all (select e,f from t3); +show create table t4; +Table Create Table +t4 CREATE TABLE `t4` ( + `a` int(11) DEFAULT NULL, + `b` blob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop tables t4; +(select a,b from t1) intersect all (select c,d from t2) intersect all (select e,f from t3) union all (select 4,4); +a b +4 4 +2 2 +2 2 +(select a,b from t1) intersect all (select c,d from t2) intersect all (select e,f from t3) union all (select 4,4) except all (select 2,2); +a b +4 4 +2 2 +drop tables t1,t2,t3; +create table t1 (a int, b int); +create table t2 (c int, d int); +create table t3 (e int, f int); +insert into t1 values (1,1),(2,2),(3,3),(2,2),(3,3); +insert into t2 values (2,2),(3,3),(4,4),(2,2),(2,2),(2,2); +insert into t3 values (1,1),(2,2),(5,5),(2,2),(5,5); +(select a,b from t1) intersect all (select c,d from t2) intersect all (select e,f from t3) union all (select 4,4); +a b +4 4 +2 2 +2 2 +(select a,b from t1) intersect all (select c,d from t2) intersect all (select e,f from t3) union all (select 4,4) except all (select 2,2); +a b +4 4 +2 2 +drop tables t1,t2,t3; +# +# INTERSECT precedence +# +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); +(select a,b from t1) union all (select c,d from t2) intersect (select e,f from t3) union all (select 4,4); +a b +5 5 +6 6 +3 3 +4 4 +(select a,b from t1) union all (select c,d from t2) intersect all (select e,f from t3) union all (select 4,4); +a b +5 5 +6 6 +3 3 +4 4 +explain extended (select a,b from t1) union all (select c,d from t2) intersect all (select e,f from t3) union all (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 +5 UNION <derived2> ALL NULL NULL NULL NULL 2 100.00 +2 DERIVED t2 ALL NULL NULL NULL NULL 2 100.00 +3 INTERSECT t3 ALL NULL NULL NULL NULL 2 100.00 +NULL INTERSECT RESULT <intersect2,3> ALL NULL NULL NULL NULL NULL NULL +4 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 (/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1`) union all /* 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 all (/* select#3 */ select `test`.`t3`.`e` AS `e`,`test`.`t3`.`f` AS `f` from `test`.`t3`)) `__5` union all (/* select#4 */ select 4 AS `4`,4 AS `4`) +insert into t2 values (3,3); +insert into t3 values (3,3); +(select e,f from t3) intersect all (select c,d from t2) union all (select a,b from t1) union all (select 4,4); +e f +3 3 +3 3 +5 5 +6 6 +4 4 +explain extended (select e,f from t3) intersect all (select c,d from t2) union all (select a,b from t1) union all (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 3 100.00 +2 INTERSECT t2 ALL NULL NULL NULL NULL 3 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 all (/* select#2 */ select `test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2`) union all (/* select#3 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1`) union all (/* select#4 */ select 4 AS `4`,4 AS `4`) +(/* 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 all (/* 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 +5 5 +6 6 +3 3 +4 4 +prepare stmt from "(select a,b from t1) union all (select c,d from t2) intersect all (select e,f from t3) union all (select 4,4)"; +execute stmt; +a b +5 5 +6 6 +3 3 +3 3 +4 4 +execute stmt; +a b +5 5 +6 6 +3 3 +3 3 +4 4 +create view v1 as (select a,b from t1) union all (select c,d from t2) intersect all (select e,f from t3) union all (select 4,4); +select b,a,b+1 from v1; +b a b+1 +5 5 6 +6 6 7 +3 3 4 +3 3 4 +4 4 5 +select b,a,b+1 from v1 where a > 3; +b a b+1 +5 5 6 +6 6 7 +4 4 5 +create procedure p1() +select * from v1; +call p1(); +a b +5 5 +6 6 +3 3 +3 3 +4 4 +call p1(); +a b +5 5 +6 6 +3 3 +3 3 +4 4 +drop procedure p1; +create procedure p1() +(select a,b from t1) union all (select c,d from t2) intersect all (select e,f from t3) union all (select 4,4); +call p1(); +a b +5 5 +6 6 +3 3 +3 3 +4 4 +call p1(); +a b +5 5 +6 6 +3 3 +3 3 +4 4 +drop procedure p1; +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 `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1`) union all select `__6`.`c` AS `c`,`__6`.`d` AS `d` from (select `test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` intersect all (select `test`.`t3`.`e` AS `e`,`test`.`t3`.`f` AS `f` from `test`.`t3`)) `__6` union all (select 4 AS `4`,4 AS `4`) latin1 latin1_swedish_ci +drop view v1; +drop tables t1,t2,t3; +CREATE TABLE t (i INT); +INSERT INTO t VALUES (1),(2); +SELECT * FROM t WHERE i != ANY ( SELECT 6 INTERSECT ALL SELECT 3 ); +i +select i from t where +exists ((select 6 as r from dual having t.i <> 6) +intersect all +(select 3 from dual having t.i <> 3)); +i +drop table t; +CREATE TABLE t1 (a varchar(32)) ENGINE=MyISAM; +INSERT INTO t1 VALUES +('Jakarta'),('Lisbon'),('Honolulu'),('Lusaka'),('Barcelona'),('Taipei'), +('Brussels'),('Orlando'),('Osaka'),('Quito'),('Lima'),('Tunis'), +('Unalaska'),('Rotterdam'),('Zagreb'),('Ufa'),('Ryazan'),('Xiamen'), +('London'),('Izmir'),('Samara'),('Bern'),('Zhengzhou'),('Vladivostok'), +('Yangon'),('Victoria'),('Warsaw'),('Luanda'),('Leon'),('Bangkok'), +('Wellington'),('Zibo'),('Qiqihar'),('Delhi'),('Hamburg'),('Ottawa'), +('Vaduz'); +CREATE TABLE t2 (b varchar(32)) ENGINE=MyISAM; +INSERT INTO t2 VALUES +('Gaza'),('Jeddah'),('Beirut'),('Incheon'),('Tbilisi'),('Izmir'), +('Quito'),('Riga'),('Freetown'),('Zagreb'),('Caracas'),('Orlando'), +('Kingston'),('Turin'),('Xinyang'),('Osaka'),('Albany'),('Geneva'), +('Omsk'),('Kazan'),('Quezon'),('Indore'),('Odessa'),('Xiamen'), +('Winnipeg'),('Yakutsk'),('Nairobi'),('Ufa'),('Helsinki'),('Vilnius'), +('Aden'),('Liverpool'),('Honolulu'),('Frankfurt'),('Glasgow'), +('Vienna'),('Jackson'),('Jakarta'),('Sydney'),('Oslo'),('Novgorod'), +('Norilsk'),('Izhevsk'),('Istanbul'),('Nice'); +CREATE TABLE t3 (c varchar(32)) ENGINE=MyISAM; +INSERT INTO t3 VALUES +('Nicosia'),('Istanbul'),('Richmond'),('Stockholm'),('Dublin'), +('Wichita'),('Warsaw'),('Glasgow'),('Winnipeg'),('Irkutsk'),('Quito'), +('Xiamen'),('Berlin'),('Rome'),('Denver'),('Dallas'),('Kabul'), +('Prague'),('Izhevsk'),('Tirana'),('Sofia'),('Detroit'),('Sorbonne'); +select count(*) from ( +SELECT * FROM t1 LEFT OUTER JOIN t2 LEFT OUTER JOIN t3 ON b < c ON a > b +INTERSECT +SELECT * FROM t1 LEFT OUTER JOIN t2 LEFT OUTER JOIN t3 ON b < c ON a > b +) a; +count(*) +14848 +select count(*) from ( +SELECT * FROM t1 LEFT OUTER JOIN t2 LEFT OUTER JOIN t3 ON b < c ON a > b +INTERSECT ALL +SELECT * FROM t1 LEFT OUTER JOIN t2 LEFT OUTER JOIN t3 ON b < c ON a > b +) a; +count(*) +14848 +insert into t1 values ('Xiamen'); +insert into t2 values ('Xiamen'),('Xiamen'); +insert into t3 values ('Xiamen'); +select count(*) from ( +SELECT * FROM t1 LEFT OUTER JOIN t2 LEFT OUTER JOIN t3 ON b < c ON a > b +INTERSECT ALL +SELECT * FROM t1 LEFT OUTER JOIN t2 LEFT OUTER JOIN t3 ON b < c ON a > b +) a; +count(*) +16430 +drop table t1,t2,t3; +CREATE TABLE t1 (a varchar(32) not null) ENGINE=MyISAM; +INSERT INTO t1 VALUES +('Jakarta'),('Lisbon'),('Honolulu'),('Lusaka'),('Barcelona'),('Taipei'), +('Brussels'),('Orlando'),('Osaka'),('Quito'),('Lima'),('Tunis'), +('Unalaska'),('Rotterdam'),('Zagreb'),('Ufa'),('Ryazan'),('Xiamen'), +('London'),('Izmir'),('Samara'),('Bern'),('Zhengzhou'),('Vladivostok'), +('Yangon'),('Victoria'),('Warsaw'),('Luanda'),('Leon'),('Bangkok'), +('Wellington'),('Zibo'),('Qiqihar'),('Delhi'),('Hamburg'),('Ottawa'), +('Vaduz'),('Detroit'),('Detroit'); +CREATE TABLE t2 (b varchar(32) not null) ENGINE=MyISAM; +INSERT INTO t2 VALUES +('Gaza'),('Jeddah'),('Beirut'),('Incheon'),('Tbilisi'),('Izmir'), +('Quito'),('Riga'),('Freetown'),('Zagreb'),('Caracas'),('Orlando'), +('Kingston'),('Turin'),('Xinyang'),('Osaka'),('Albany'),('Geneva'), +('Omsk'),('Kazan'),('Quezon'),('Indore'),('Odessa'),('Xiamen'), +('Winnipeg'),('Yakutsk'),('Nairobi'),('Ufa'),('Helsinki'),('Vilnius'), +('Aden'),('Liverpool'),('Honolulu'),('Frankfurt'),('Glasgow'), +('Vienna'),('Jackson'),('Jakarta'),('Sydney'),('Oslo'),('Novgorod'), +('Norilsk'),('Izhevsk'),('Istanbul'),('Nice'),('Detroit'),('Detroit'); +CREATE TABLE t3 (c varchar(32) not null) ENGINE=MyISAM; +INSERT INTO t3 VALUES +('Nicosia'),('Istanbul'),('Richmond'),('Stockholm'),('Dublin'), +('Wichita'),('Warsaw'),('Glasgow'),('Winnipeg'),('Irkutsk'),('Quito'), +('Xiamen'),('Berlin'),('Rome'),('Denver'),('Dallas'),('Kabul'), +('Prague'),('Izhevsk'),('Tirana'),('Sofia'),('Detroit'),('Sorbonne'), +('Detroit'); +select count(*) from ( +SELECT * FROM t1 LEFT OUTER JOIN t2 LEFT OUTER JOIN t3 ON b < c ON a > b +INTERSECT +SELECT * FROM t1 LEFT OUTER JOIN t2 LEFT OUTER JOIN t3 ON b < c ON a > b +) a; +count(*) +15547 +drop table t1,t2,t3; +create table t12(c1 int); +insert into t12 values(1); +insert into t12 values(2); +create table t13(c1 int); +insert into t13 values(1); +insert into t13 values(3); +create table t234(c1 int); +insert into t234 values(2); +insert into t234 values(3); +insert into t234 values(4); +select * from t13 union select * from t234 intersect all select * from t12; +c1 +1 +3 +2 +drop table t12,t13,t234; +create table t1 (a int); +insert into t1 values (3), (1), (7), (3), (2), (7), (4); +create table t2 (a int); +insert into t2 values (4), (5), (9), (1), (8), (9), (2), (2); +create table t3 (a int); +insert into t3 values (8), (1), (8), (2), (3), (7), (2); +select * from t1 where a > 4 +union all +select * from t2 where a < 5 +intersect all +select * from t3 where a < 5; +a +7 +7 +2 +1 +2 +explain extended +select * from t1 where a > 4 +union all +select * from t2 where a < 5 +intersect all +select * from t3 where a < 5; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 7 100.00 Using where +4 UNION <derived2> ALL NULL NULL NULL NULL 7 100.00 +2 DERIVED t2 ALL NULL NULL NULL NULL 8 100.00 Using where +3 INTERSECT t3 ALL NULL NULL NULL NULL 7 100.00 Using where +NULL INTERSECT RESULT <intersect2,3> ALL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` > 4 union all /* select#4 */ select `__4`.`a` AS `a` from (/* select#2 */ select `test`.`t2`.`a` AS `a` from `test`.`t2` where `test`.`t2`.`a` < 5 intersect all /* select#3 */ select `test`.`t3`.`a` AS `a` from `test`.`t3` where `test`.`t3`.`a` < 5) `__4` +drop table t1,t2,t3; diff --git a/mysql-test/main/intersect_all.test b/mysql-test/main/intersect_all.test new file mode 100644 index 0000000..5d2b038 --- /dev/null +++ b/mysql-test/main/intersect_all.test @@ -0,0 +1,328 @@ +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),(3,3),(2,2); +insert into t2 values (2,2),(2,2),(5,5); + +select * from t1 intersect all select * from t2; +(select a,b from t1) intersect all (select c,d from t2); +select * from ((select a,b from t1) intersect all (select c,d from t2)) t; +select * from ((select a from t1) intersect all (select c from t2)) t; + +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; +insert into t1 values (1,1),(2,2),(3,3),(2,2); +insert into t2 values (2,2),(3,3),(4,4),(2,2); +insert into t3 values (1,1),(2,2),(5,5),(2,2); + +(select a,b from t1) intersect all (select c,d from t2) intersect all (select e,f from t3); + +EXPLAIN (select a,b from t1) intersect all (select c,d from t2) intersect all (select e,f from t3); +EXPLAIN extended (select a,b from t1) intersect all (select c,d from t2) intersect all (select e,f from t3); +EXPLAIN extended select * from ((select a,b from t1) intersect all (select c,d from t2) intersect all (select e,f from t3)) a; +EXPLAIN format=json (select a,b from t1) intersect all (select c,d from t2) intersect all (select e,f from t3); +--source include/analyze-format.inc +ANALYZE format=json (select a,b from t1) intersect all (select c,d from t2) intersect all (select e,f from t3); +--source include/analyze-format.inc +ANALYZE format=json select * from ((select a,b from t1) intersect all (select c,d from t2) intersect all (select e,f from t3)) a; +select * from ((select a,b from t1) intersect all (select c,d from t2) intersect all (select e,f from t3)) a; + +prepare stmt from "(select a,b from t1) intersect all (select c,d from t2) intersect all (select e,f from t3);"; +execute stmt; +execute stmt; + +prepare stmt from "select * from ((select a,b from t1) intersect all (select c,d from t2) intersect all (select e,f from t3)) a"; +execute stmt; +execute stmt; + +insert into t1 values (2,2),(3,3); +insert into t2 values (2,2),(2,2),(2,2); +(select a,b from t1) intersect all (select c,d from t2) intersect all (select e,f from t3); +(select a,b from t1) intersect (select c,d from t2) intersect all (select e,f from t3); + +insert into t3 values (2,2); +(select a,b from t1) intersect all (select c,d from t2) intersect (select e,f from t3); + +(select a,b from t1) intersect all (select c,e from t2,t3); + +EXPLAIN (select a,b from t1) intersect all (select c,e from t2,t3); +EXPLAIN extended (select a,b from t1) intersect all (select c,e from t2,t3); +EXPLAIN extended select * from ((select a,b from t1) intersect all (select c,e from t2,t3)) a; +EXPLAIN format=json (select a,b from t1) intersect all (select c,e from t2,t3); +--source include/analyze-format.inc +ANALYZE format=json (select a,b from t1) intersect all (select c,e from t2,t3); +--source include/analyze-format.inc +ANALYZE format=json select * from ((select a,b from t1) intersect all (select c,e from t2,t3)) a; +select * from ((select a,b from t1) intersect all (select c,e from t2,t3)) a; + +prepare stmt from "(select a,b from t1) intersect all (select c,e from t2,t3);"; +execute stmt; +execute stmt; + +prepare stmt from "select * from ((select a,b from t1) intersect all (select c,e from t2,t3)) a"; +execute stmt; +execute stmt; + +drop tables t1,t2,t3; + +select 1 as a from dual intersect all select 1 from dual; +(select 1 from dual) intersect all (select 1 from dual); +--error ER_PARSE_ERROR +(select 1 from dual into @v) intersect all (select 1 from dual); +--error ER_PARSE_ERROR +select 1 from dual ORDER BY 1 intersect all select 1 from dual; +select 1 as a from dual union 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); +insert into t1 values (1,"ddd", 1, "sdfrrwwww"),(2, "fgh", 2, "dffggtt"),(2, "fgh", 2, "dffggtt"); +insert into t2 values (2, "fgh", 2, "dffggtt"),(3, "ffggddd", 3, "dfgg"),(2, "fgh", 2, "dffggtt"); + +(select a,b,b1 from t1) intersect all (select c,d,d1 from t2); + +drop tables t1,t2; + +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),(2,2),(3,3); +insert into t2 values (2,2),(3,3),(4,4),(2,2),(2,2),(2,2); +insert into t3 values (1,1),(2,2),(5,5),(2,2),(5,5); + +(select a,b from t1) intersect all (select c,d from t2) intersect all (select e,f from t3); +select * from ((select a,b from t1) intersect all (select c,d from t2) intersect (select e,f from t3)) a; + +prepare stmt from "(select a,b from t1) intersect all (select c,d from t2) intersect all (select e,f from t3);"; +execute stmt; +execute stmt; + +prepare stmt from "select * from ((select a,b from t1) intersect all (select c,d from t2) intersect all (select e,f from t3)) a"; +execute stmt; +execute stmt; + +# make sure that blob is used +create table t4 (select a,b from t1) intersect all (select c,d from t2) intersect all (select e,f from t3); +show create table t4; +drop tables t4; + + +(select a,b from t1) intersect all (select c,d from t2) intersect all (select e,f from t3) union all (select 4,4); + +(select a,b from t1) intersect all (select c,d from t2) intersect all (select e,f from t3) union all (select 4,4) except all (select 2,2); + +drop tables t1,t2,t3; + +create table t1 (a int, b int); +create table t2 (c int, d int); +create table t3 (e int, f int); +insert into t1 values (1,1),(2,2),(3,3),(2,2),(3,3); +insert into t2 values (2,2),(3,3),(4,4),(2,2),(2,2),(2,2); +insert into t3 values (1,1),(2,2),(5,5),(2,2),(5,5); + + +(select a,b from t1) intersect all (select c,d from t2) intersect all (select e,f from t3) union all (select 4,4); + +(select a,b from t1) intersect all (select c,d from t2) intersect all (select e,f from t3) union all (select 4,4) except all (select 2,2); + +drop tables t1,t2,t3; + +--echo # +--echo # INTERSECT precedence +--echo # +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); + + + +(select a,b from t1) union all (select c,d from t2) intersect (select e,f from t3) union all (select 4,4); + +(select a,b from t1) union all (select c,d from t2) intersect all (select e,f from t3) union all (select 4,4); +explain extended (select a,b from t1) union all (select c,d from t2) intersect all (select e,f from t3) union all (select 4,4); + +# test result of linear mix operation +insert into t2 values (3,3); +insert into t3 values (3,3); + +(select e,f from t3) intersect all (select c,d from t2) union all (select a,b from t1) union all (select 4,4); +explain extended (select e,f from t3) intersect all (select c,d from t2) union all (select a,b from t1) union all (select 4,4); + + +(/* 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 all (/* 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`); + +prepare stmt from "(select a,b from t1) union all (select c,d from t2) intersect all (select e,f from t3) union all (select 4,4)"; + +execute stmt; + +execute stmt; + +create view v1 as (select a,b from t1) union all (select c,d from t2) intersect all (select e,f from t3) union all (select 4,4); + + +select b,a,b+1 from v1; + +select b,a,b+1 from v1 where a > 3; + +create procedure p1() + select * from v1; + +call p1(); + +call p1(); +drop procedure p1; + +create procedure p1() + (select a,b from t1) union all (select c,d from t2) intersect all (select e,f from t3) union all (select 4,4); + +call p1(); + +call p1(); +drop procedure p1; + +show create view v1; + +drop view v1; +drop tables t1,t2,t3; + +CREATE TABLE t (i INT); +INSERT INTO t VALUES (1),(2); +SELECT * FROM t WHERE i != ANY ( SELECT 6 INTERSECT ALL SELECT 3 ); + +select i from t where + exists ((select 6 as r from dual having t.i <> 6) + intersect all + (select 3 from dual having t.i <> 3)); + +drop table t; + +CREATE TABLE t1 (a varchar(32)) ENGINE=MyISAM; +INSERT INTO t1 VALUES +('Jakarta'),('Lisbon'),('Honolulu'),('Lusaka'),('Barcelona'),('Taipei'), +('Brussels'),('Orlando'),('Osaka'),('Quito'),('Lima'),('Tunis'), +('Unalaska'),('Rotterdam'),('Zagreb'),('Ufa'),('Ryazan'),('Xiamen'), +('London'),('Izmir'),('Samara'),('Bern'),('Zhengzhou'),('Vladivostok'), +('Yangon'),('Victoria'),('Warsaw'),('Luanda'),('Leon'),('Bangkok'), +('Wellington'),('Zibo'),('Qiqihar'),('Delhi'),('Hamburg'),('Ottawa'), +('Vaduz'); + +CREATE TABLE t2 (b varchar(32)) ENGINE=MyISAM; +INSERT INTO t2 VALUES +('Gaza'),('Jeddah'),('Beirut'),('Incheon'),('Tbilisi'),('Izmir'), +('Quito'),('Riga'),('Freetown'),('Zagreb'),('Caracas'),('Orlando'), +('Kingston'),('Turin'),('Xinyang'),('Osaka'),('Albany'),('Geneva'), +('Omsk'),('Kazan'),('Quezon'),('Indore'),('Odessa'),('Xiamen'), +('Winnipeg'),('Yakutsk'),('Nairobi'),('Ufa'),('Helsinki'),('Vilnius'), +('Aden'),('Liverpool'),('Honolulu'),('Frankfurt'),('Glasgow'), +('Vienna'),('Jackson'),('Jakarta'),('Sydney'),('Oslo'),('Novgorod'), +('Norilsk'),('Izhevsk'),('Istanbul'),('Nice'); + +CREATE TABLE t3 (c varchar(32)) ENGINE=MyISAM; +INSERT INTO t3 VALUES +('Nicosia'),('Istanbul'),('Richmond'),('Stockholm'),('Dublin'), +('Wichita'),('Warsaw'),('Glasgow'),('Winnipeg'),('Irkutsk'),('Quito'), +('Xiamen'),('Berlin'),('Rome'),('Denver'),('Dallas'),('Kabul'), +('Prague'),('Izhevsk'),('Tirana'),('Sofia'),('Detroit'),('Sorbonne'); + +select count(*) from ( + SELECT * FROM t1 LEFT OUTER JOIN t2 LEFT OUTER JOIN t3 ON b < c ON a > b + INTERSECT + SELECT * FROM t1 LEFT OUTER JOIN t2 LEFT OUTER JOIN t3 ON b < c ON a > b +) a; + +select count(*) from ( + SELECT * FROM t1 LEFT OUTER JOIN t2 LEFT OUTER JOIN t3 ON b < c ON a > b + INTERSECT ALL + SELECT * FROM t1 LEFT OUTER JOIN t2 LEFT OUTER JOIN t3 ON b < c ON a > b +) a; + +insert into t1 values ('Xiamen'); +insert into t2 values ('Xiamen'),('Xiamen'); +insert into t3 values ('Xiamen'); +select count(*) from ( + SELECT * FROM t1 LEFT OUTER JOIN t2 LEFT OUTER JOIN t3 ON b < c ON a > b + INTERSECT ALL + SELECT * FROM t1 LEFT OUTER JOIN t2 LEFT OUTER JOIN t3 ON b < c ON a > b +) a; + +drop table t1,t2,t3; + +CREATE TABLE t1 (a varchar(32) not null) ENGINE=MyISAM; +INSERT INTO t1 VALUES +('Jakarta'),('Lisbon'),('Honolulu'),('Lusaka'),('Barcelona'),('Taipei'), +('Brussels'),('Orlando'),('Osaka'),('Quito'),('Lima'),('Tunis'), +('Unalaska'),('Rotterdam'),('Zagreb'),('Ufa'),('Ryazan'),('Xiamen'), +('London'),('Izmir'),('Samara'),('Bern'),('Zhengzhou'),('Vladivostok'), +('Yangon'),('Victoria'),('Warsaw'),('Luanda'),('Leon'),('Bangkok'), +('Wellington'),('Zibo'),('Qiqihar'),('Delhi'),('Hamburg'),('Ottawa'), +('Vaduz'),('Detroit'),('Detroit'); + +CREATE TABLE t2 (b varchar(32) not null) ENGINE=MyISAM; +INSERT INTO t2 VALUES +('Gaza'),('Jeddah'),('Beirut'),('Incheon'),('Tbilisi'),('Izmir'), +('Quito'),('Riga'),('Freetown'),('Zagreb'),('Caracas'),('Orlando'), +('Kingston'),('Turin'),('Xinyang'),('Osaka'),('Albany'),('Geneva'), +('Omsk'),('Kazan'),('Quezon'),('Indore'),('Odessa'),('Xiamen'), +('Winnipeg'),('Yakutsk'),('Nairobi'),('Ufa'),('Helsinki'),('Vilnius'), +('Aden'),('Liverpool'),('Honolulu'),('Frankfurt'),('Glasgow'), +('Vienna'),('Jackson'),('Jakarta'),('Sydney'),('Oslo'),('Novgorod'), +('Norilsk'),('Izhevsk'),('Istanbul'),('Nice'),('Detroit'),('Detroit'); + +CREATE TABLE t3 (c varchar(32) not null) ENGINE=MyISAM; +INSERT INTO t3 VALUES +('Nicosia'),('Istanbul'),('Richmond'),('Stockholm'),('Dublin'), +('Wichita'),('Warsaw'),('Glasgow'),('Winnipeg'),('Irkutsk'),('Quito'), +('Xiamen'),('Berlin'),('Rome'),('Denver'),('Dallas'),('Kabul'), +('Prague'),('Izhevsk'),('Tirana'),('Sofia'),('Detroit'),('Sorbonne'), +('Detroit'); + +select count(*) from ( + SELECT * FROM t1 LEFT OUTER JOIN t2 LEFT OUTER JOIN t3 ON b < c ON a > b + INTERSECT + SELECT * FROM t1 LEFT OUTER JOIN t2 LEFT OUTER JOIN t3 ON b < c ON a > b +) a; + +drop table t1,t2,t3; + +create table t12(c1 int); +insert into t12 values(1); +insert into t12 values(2); +create table t13(c1 int); +insert into t13 values(1); +insert into t13 values(3); +create table t234(c1 int); +insert into t234 values(2); +insert into t234 values(3); +insert into t234 values(4); + + +select * from t13 union select * from t234 intersect all select * from t12; + +drop table t12,t13,t234; + +create table t1 (a int); +insert into t1 values (3), (1), (7), (3), (2), (7), (4); +create table t2 (a int); +insert into t2 values (4), (5), (9), (1), (8), (9), (2), (2); +create table t3 (a int); +insert into t3 values (8), (1), (8), (2), (3), (7), (2); + + +select * from t1 where a > 4 +union all +select * from t2 where a < 5 +intersect all +select * from t3 where a < 5; + +explain extended +select * from t1 where a > 4 +union all +select * from t2 where a < 5 +intersect all +select * from t3 where a < 5; + +drop table t1,t2,t3; \ No newline at end of file diff --git a/mysql-test/main/set_operation.result b/mysql-test/main/set_operation.result new file mode 100644 index 0000000..a021033 --- /dev/null +++ b/mysql-test/main/set_operation.result @@ -0,0 +1,1157 @@ +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, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100 + } + } + }, + { + "query_block": { + "select_id": 8, + "operation": "UNION", + "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, + "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", + "table": { + "table_name": "<derived5>", + "access_type": "ALL", + "rows": 10, + "filtered": 100, + "materialized": { + "query_block": { + "union_result": { + "table_name": "<union5,6>", + "access_type": "ALL", + "query_specifications": [ + { + "query_block": { + "select_id": 5, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100 + } + } + }, + { + "query_block": { + "select_id": 6, + "operation": "UNION", + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100 + } + } + } + ] + } + } + } + } + } + } + ] + } + } + } + } + } + }, + { + "query_block": { + "select_id": 7, + "operation": "EXCEPT", + "table": { + "table_name": "t3", + "access_type": "ALL", + "rows": 5, + "filtered": 100 + } + } + }, + { + "query_block": { + "select_id": 9, + "operation": "UNION", + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 6, + "filtered": 100 + } + } + }, + { + "query_block": { + "select_id": 10, + "operation": "UNION", + "table": { + "table_name": "t3", + "access_type": "ALL", + "rows": 5, + "filtered": 100 + } + } + }, + { + "query_block": { + "select_id": 11, + "operation": "EXCEPT", + "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", + "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", + "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 */ se lect `te st`.`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 `test`.`t1`.`a` AS `i1`,`test`.`t1`.`b` AS `i2` from `test`.`t1` union all select `__9`.`c` AS `c`,`__9`.`d` AS `d` from (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 `sq`.`a` AS `a`,`sq`.`b` AS `b` from (select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` union all select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1`) `sq`) `__9` except all select `test`.`t3`.`e` AS `e`,`test`.`t3`.`f` AS `f` from `test`.`t3` union all select `test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` union select `test`.`t3`.`e` AS `e`,`test`.`t3`.`f` AS `f` from `test`.`t3` except select `test`.`t1`.`a` AS `a`,`test`.`t2`.`c` AS `c` from (`test`.`t1` join `test`.`t2`) union all select `v0`.`g` AS `g`,`v0`.`h` AS `h` from `test`.`v0` where `v0`.`g` < 4 union all sele ct `test`.`t3`.`e` AS `e`,`test`.`t3`.`f` AS `f` from `test`.`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; diff --git a/mysql-test/main/set_operation.test b/mysql-test/main/set_operation.test new file mode 100644 index 0000000..c43725c --- /dev/null +++ b/mysql-test/main/set_operation.test @@ -0,0 +1,526 @@ +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; + +--echo # test optimization + +let $q= + select * from t1 + INTERSECT ALL + select * from t2 + INTERSECT ALL + select * from t3; +eval $q; +eval EXPLAIN EXTENDED $q; + +let $q= + select * from t1 + INTERSECT ALL + select * from t2 + INTERSECT ALL + select * from t3 + INTERSECT + select * from t1; +eval $q; +eval EXPLAIN EXTENDED $q; + +let $q= + select * from t1 + INTERSECT ALL + select * from t2 + INTERSECT ALL + select * from t3 + EXCEPT ALL + select * from t4; +eval $q; +eval EXPLAIN EXTENDED $q; + +let $q= + select * from t1 + INTERSECT + select * from t2 + EXCEPT ALL + select * from t4; +eval $q; +eval EXPLAIN EXTENDED $q; + +insert into t4 values (1,1),(9,9); +let $q= + select * from t1 + UNION ALL + select * from t2 + UNION ALL + select * from t3 + EXCEPT + select * from t4; +eval $q; +eval EXPLAIN EXTENDED $q; + +delete from t4; +insert into t4 values (3,3),(3,3); +let $q= + 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; + +eval $q; +eval EXPLAIN EXTENDED $q; +drop table t4; + +--echo # test optimization with brackets + +let $q= +( + (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; +eval $q; +eval EXPLAIN EXTENDED $q; + +let $q= +(select 1 union all select 2) + union +(select 3 union all select 4); +eval $q; +eval EXPLAIN EXTENDED $q; + +let $q= +(select 1 intersect all select 2) + except +select 3; +eval $q; +eval EXPLAIN EXTENDED $q; + +let $q= +(select 1 intersect all select 2 intersect all select 3) + intersect +(select 4 intersect all select 5); +eval $q; +eval EXPLAIN EXTENDED $q; + + +--echo # 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)); + +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)); + +--echo # 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; + +select * from ( + select * from t1 + UNION ALL + select * from t3 +)dt1 +EXCEPT ALL +select * from ( + select * from t2 + INTERSECT ALL + select * from t2 +)dt2; + +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; + +--echo # 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; + +--sorted_result +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; + + +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; + +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; + +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 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; + +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; + +EXECUTE stmt; +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; + +select * from v1 limit 14; +--sorted_result +select * from v1 order by i1 limit 14; + +drop table t1,t2,t3; +drop view v0,v1; + +--echo # 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; +select * from t1 intersect all select * from t2 except ALL select * from t3 union select * from t4; + +select * from t1 intersect DISTINCT select * from t2 except select * from t3 union select * from t4; +select * from t1 intersect DISTINCT select * from t2 except ALL select * from t3 union select * from t4; + +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; +select * from t1 intersect all select * from t2 intersect select * from t3; +select * from t1 intersect all select * from t1 intersect all select * from t2 intersect select * from t3; + +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; +select * from t1 intersect DISTINCT select * from t2 union DISTINCT select * from t3 union select * from t4; + +select * from t1 intersect all select * from t2 intersect all select * from t3 union select * from t4; +select * from t1 intersect all select * from t2 intersect DISTINCT select * from t3 union select * from t4; +select * from t1 intersect DISTINCT select * from t2 intersect DISTINCT select * from t3 union select * from t4; + +select * from t1 intersect all select * from t2 EXCEPT select * from t3 union select * from t4; +select * from t1 intersect DISTINCT select * from t2 EXCEPT select * from t3 union select * from t4; +select * from t1 intersect all select * from t2 EXCEPT ALL select * from t3 union select * from t4; + +select * from t1 EXCEPT select * from t2 union all select * from t3 union select * from t4; +select * from t1 EXCEPT select * from t2 union DISTINCT select * from t3 union select * from t4; + +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; +select * from t1 union all select * from t2 except DISTINCT select * from t3; +select * from t1 union DISTINCT select * from t2 except all select * from t3; +select * from t1 union DISTINCT select * from t2 except DISTINCT select * from t3; + +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; +select 1 intersect all select 2 intersect all select 3 union select 4 except select 5; +select 1 union select 2 except all select 3 union select 4; +select 1 union all select 2 union all select 3 union select 4; + +--echo # test with limited resource + +set @@max_heap_table_size= 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; + +select count(*) from +( + select * from t1 + EXCEPT ALL + select * from t2 +) c; + +select count(*) from +( + select * from t1 + INTERSECT ALL + select * from t2 + UNION ALL + select * from t1 + EXCEPT ALL + select * from t2 +) c; + +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; + +drop table t1; +drop table t2; diff --git a/mysql-test/main/set_operation_oracle.result b/mysql-test/main/set_operation_oracle.result new file mode 100644 index 0000000..28f6e31 --- /dev/null +++ b/mysql-test/main/set_operation_oracle.result @@ -0,0 +1,75 @@ +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); +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 +4 4 +3 3 +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") +(select e,f from t3) intersect (select c,d from t2) union (select a,b from t1) union (select 4,4); +e f +5 5 +3 3 +6 6 +4 4 +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") +create table t12(c1 int); +insert into t12 values(1); +insert into t12 values(2); +create table t13(c1 int); +insert into t13 values(1); +insert into t13 values(3); +create table t234(c1 int); +insert into t234 values(2); +insert into t234 values(3); +insert into t234 values(4); +select * from t13 union select * from t234 intersect select * from t12; +c1 +1 +2 +set SQL_MODE=default; +drop table t1,t2,t3; +drop table t12,t13, t234; +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); +set SQL_MODE=ORACLE; +(select a,b from t1) union all (select c,d from t2) intersect all (select e,f from t3) union all (select 4,4); +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 e,f from t3) union all (select 4,4)' at line 1 +explain extended (select a,b from t1) union all (select c,d from t2) intersect all (select e,f from t3) union all (select 4,4); +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 e,f from t3) union all (select 4,4)' at line 1 +(select e,f from t3) intersect all (select c,d from t2) union all (select a,b from t1) union all (select 4,4); +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 c,d from t2) union all (select a,b from t1) union all (select 4,4)' at line 1 +explain extended (select e,f from t3) intersect all (select c,d from t2) union all (select a,b from t1) union all (select 4,4); +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 c,d from t2) union all (select a,b from t1) union all (select 4,4)' at line 1 +set SQL_MODE=default; +drop table t1,t2,t3; +set SQL_MODE=oracle; +select * from t13 union select * from t234 intersect all select * from t12; +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 * from t12' at line 1 +set SQL_MODE=default; diff --git a/mysql-test/main/set_operation_oracle.test b/mysql-test/main/set_operation_oracle.test new file mode 100644 index 0000000..bd2a4d5 --- /dev/null +++ b/mysql-test/main/set_operation_oracle.test @@ -0,0 +1,65 @@ +# from intersect.test +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); + +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); +explain extended +(select a,b from t1) union (select c,d from t2) intersect (select e,f from t3) union (select 4,4); + + +(select e,f from t3) intersect (select c,d from t2) union (select a,b from t1) union (select 4,4); +explain extended +(select e,f from t3) intersect (select c,d from t2) union (select a,b from t1) union (select 4,4); + +create table t12(c1 int); +insert into t12 values(1); +insert into t12 values(2); +create table t13(c1 int); +insert into t13 values(1); +insert into t13 values(3); +create table t234(c1 int); +insert into t234 values(2); +insert into t234 values(3); +insert into t234 values(4); + + +select * from t13 union select * from t234 intersect select * from t12; +set SQL_MODE=default; + +drop table t1,t2,t3; +drop table t12,t13, t234; + +#from intersect_all.test +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); + +set SQL_MODE=ORACLE; + +#(select a,b from t1) union all (select c,d from t2) intersect (select e,f from t3) union all (select 4,4); +--error ER_PARSE_ERROR +(select a,b from t1) union all (select c,d from t2) intersect all (select e,f from t3) union all (select 4,4); +--error ER_PARSE_ERROR +explain extended (select a,b from t1) union all (select c,d from t2) intersect all (select e,f from t3) union all (select 4,4); + +--error ER_PARSE_ERROR +(select e,f from t3) intersect all (select c,d from t2) union all (select a,b from t1) union all (select 4,4); +--error ER_PARSE_ERROR +explain extended (select e,f from t3) intersect all (select c,d from t2) union all (select a,b from t1) union all (select 4,4); +set SQL_MODE=default; + +drop table t1,t2,t3; + +set SQL_MODE=oracle; +--error ER_PARSE_ERROR +select * from t13 union select * from t234 intersect all select * from t12; +set SQL_MODE=default; \ No newline at end of file diff --git a/sql/sql_class.h b/sql/sql_class.h index 152bf06..54d6541 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -5708,17 +5708,18 @@ class TMP_TABLE_PARAM :public Sql_alloc class select_unit :public select_result_interceptor { +protected: uint curr_step, prev_step, curr_sel; enum sub_select_type step; public: - Item_int *intersect_mark; TMP_TABLE_PARAM tmp_table_param; + /* Number of additional (hidden) field of the used temporary table */ + int addon_cnt; int write_err; /* Error code from the last send_data->ha_write_row call. */ TABLE *table; select_unit(THD *thd_arg): - select_result_interceptor(thd_arg), - intersect_mark(0), table(0) + select_result_interceptor(thd_arg), addon_cnt(0), table(0) { init(); tmp_table_param.init(); @@ -5735,6 +5736,9 @@ class select_unit :public select_result_interceptor virtual bool postponed_prepare(List<Item> &types) { return false; } int send_data(List<Item> &items); + int write_record(); + int update_counter(Field *counter, longlong value); + int delete_record(); bool send_eof(); virtual bool flush(); void cleanup(); @@ -5753,7 +5757,148 @@ class select_unit :public select_result_interceptor step= UNION_TYPE; write_err= 0; } + virtual void change_select(); + virtual bool force_enable_index_if_needed() { return false; } +}; + + +/** + @class select_unit_ext + + The class used when processing rows produced by operands of query expressions + containing INTERSECT ALL and/or EXCEPT all operations. One or two extra fields + of the temporary to store the rows of the partial and final result can be employed. + Both of them contain counters. The second additional field is used only when + the processed query expression contains INTERSECT ALL. + + Consider how these extra fields are used. + + Let + table t1 (f char(8)) + table t2 (f char(8)) + table t3 (f char(8)) + contain the following sets: + ("b"),("a"),("d"),("c"),("b"),("a"),("c"),("a") + ("c"),("b"),("c"),("c"),("a"),("b"),("g") + ("c"),("a"),("b"),("d"),("b"),("e") + + - Let's demonstrate how the the set operation INTERSECT ALL is proceesed + for the query + SELECT f FROM t1 INTERSECT ALL SELECT f FROM t2 + + When send_data() is called for the rows of the first operand we put + the processed record into the temporary table if there was no such record + setting dup_cnt field to 1 and add_cnt field to 0 and increment the + counter in the dup_cnt field by one otherwise. We get + + |add_cnt|dup_cnt| f | + |0 |2 |b | + |0 |3 |a | + |0 |1 |d | + |0 |2 |c | + + The call of send_eof() for the first operand swaps the values stored in + dup_cnt and add_cnt. After this, we'll see the following rows in the + temporary table + + |add_cnt|dup_cnt| f | + |2 |0 |b | + |3 |0 |a | + |1 |0 |d | + |2 |0 |c | + + When send_data() is called for the rows of the second operand we increment + the counter in dup_cnt if the processed row is found in the table and do + nothing otherwise. As a result we get + + |add_cnt|dup_cnt| f | + |2 |2 |b | + |3 |1 |a | + |1 |0 |d | + |2 |3 |c | + + At the call of send_eof() for the second operand first we disable index. + Then for each record, the minimum of counters from dup_cnt and add_cnt m is + taken. If m == 0 then the record is deleted. Otherwise record is replaced + with m copies of it. Yet the counter in this copies are set to 1 for + dup_cnt and to 0 for add_cnt + + |add_cnt|dup_cnt| f | + |0 |1 |b | + |0 |1 |b | + |0 |1 |a | + |0 |1 |c | + |0 |1 |c | + + - Let's demonstrate how the the set operation EXCEPT ALL is proceesed + for the query + SELECT f FROM t1 EXCEPT ALL SELECT f FROM t3 + + Only one additional counter field dup_cnt is used for EXCEPT ALL. + After the first operand has been processed we have in the temporary table + + |dup_cnt| f | + |2 |b | + |3 |a | + |1 |d | + |2 |c | + + When send_data() is called for the rows of the second operand we decrement + the counter in dup_cnt if the processed row is found in the table and do + nothing otherwise. If the counter becomes 0 we delete the record + + |dup_cnt| f | + |2 |a | + |1 |c | + + Finally at the call of send_eof() for the second operand we disable index + unfold rows adding duplicates + + |dup_cnt| f | + |1 |a | + |1 |a | + |1 |c | + */ + +class select_unit_ext :public select_unit +{ +public: + select_unit_ext(THD *thd_arg): + select_unit(thd_arg), increment(0), is_index_enabled(TRUE), + curr_op_type(UNSPECIFIED) + { + }; + int send_data(List<Item> &items); void change_select(); + int unfold_record(ha_rows cnt); + bool send_eof(); + bool force_enable_index_if_needed() + { + is_index_enabled= true; + return true; + } + bool disable_index_if_needed(SELECT_LEX *curr_sl); + + /* + How to change increment/decrement the counter in duplicate_cnt field + when processing a record produced by the current operand in send_data(). + The value can be 1 or -1 + */ + int increment; + /* TRUE <=> the index of the result temporary table is enabled */ + bool is_index_enabled; + /* The type of the set operation currently executed */ + enum set_op_type curr_op_type; + /* + Points to the extra field of the temporary table where + duplicate counters are stored + */ + Field *duplicate_cnt; + /* + Points to the extra field of the temporary table where additional + counters used only for INTERSECT ALL operations are stored + */ + Field *additional_cnt; }; class select_union_recursive :public select_unit diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index 8f6e86f..e77f173 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -2354,6 +2354,7 @@ void st_select_lex_unit::init_query() offset_limit_cnt= 0; union_distinct= 0; prepared= optimized= optimized_2= executed= 0; + bag_set_op_optimized= 0; optimize_started= 0; item= 0; union_result= 0; @@ -2369,8 +2370,8 @@ void st_select_lex_unit::init_query() with_clause= 0; with_element= 0; columns_are_renamed= false; - intersect_mark= NULL; with_wrapped_tvc= false; + have_except_all_or_intersect_all= false; } void st_select_lex::init_query() @@ -2468,6 +2469,7 @@ void st_select_lex::init_select() curr_tvc_name= 0; in_tvc= false; versioned_tables= 0; + nest_flags= 0; } /* @@ -2986,7 +2988,6 @@ bool st_select_lex::setup_ref_array(THD *thd, uint order_group_num) void st_select_lex_unit::print(String *str, enum_query_type query_type) { - bool union_all= !union_distinct; if (with_clause) with_clause->print(str, query_type); for (SELECT_LEX *sl= first_select(); sl; sl= sl->next_select()) @@ -2999,8 +3000,6 @@ void st_select_lex_unit::print(String *str, enum_query_type query_type) DBUG_ASSERT(0); case UNION_TYPE: str->append(STRING_WITH_LEN(" union ")); - if (union_all) - str->append(STRING_WITH_LEN("all ")); break; case INTERSECT_TYPE: str->append(STRING_WITH_LEN(" intersect ")); @@ -3009,8 +3008,8 @@ void st_select_lex_unit::print(String *str, enum_query_type query_type) str->append(STRING_WITH_LEN(" except ")); break; } - if (sl == union_distinct) - union_all= TRUE; + if (!sl->distinct) + str->append(STRING_WITH_LEN("all ")); } if (sl->braces) str->append('('); @@ -3523,6 +3522,8 @@ bool st_select_lex_unit::union_needs_tmp_table() with_wrapped_tvc= true; break; } + if (sl != first_select() && sl->linkage != UNION_TYPE) + return true; } } if (with_wrapped_tvc) @@ -5394,7 +5395,7 @@ LEX::wrap_unit_into_derived(SELECT_LEX_UNIT *unit) Name_resolution_context *context= &wrapping_sel->context; context->init(); wrapping_sel->automatic_brackets= FALSE; - + wrapping_sel->mark_as_unit_nest(); wrapping_sel->register_unit(unit, context); /* stuff dummy SELECT * FROM (...) */ diff --git a/sql/sql_lex.h b/sql/sql_lex.h index b916d07..cd6c068 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -207,6 +207,14 @@ enum sub_select_type GLOBAL_OPTIONS_TYPE, DERIVED_TABLE_TYPE, OLAP_TYPE }; +enum set_op_type +{ + UNSPECIFIED, + UNION_DISTINCT, UNION_ALL, + EXCEPT_DISTINCT, EXCEPT_ALL, + INTERSECT_DISTINCT, INTERSECT_ALL +}; + inline int cmp_unit_op(enum sub_select_type op1, enum sub_select_type op2) { DBUG_ASSERT(op1 >= UNION_TYPE && op1 <= EXCEPT_TYPE); @@ -841,8 +849,8 @@ class st_select_lex_unit: public st_select_lex_node { // Ensures that at least all members used during cleanup() are initialized. st_select_lex_unit() : union_result(NULL), table(NULL), result(NULL), - cleaned(false), - fake_select_lex(NULL) + cleaned(false), bag_set_op_optimized(false), + have_except_all_or_intersect_all(false), fake_select_lex(NULL) { } @@ -853,9 +861,11 @@ class st_select_lex_unit: public st_select_lex_node { optimized, // optimize phase already performed for UNION (unit) optimized_2, executed, // already executed - cleaned; + cleaned, + bag_set_op_optimized; bool optimize_started; + bool have_except_all_or_intersect_all; // list of fields which points to temporary table for union List<Item> item_list; @@ -868,11 +878,6 @@ class st_select_lex_unit: public st_select_lex_node { */ List<Item> types; /** - There is INTERSECT and it is item used in creating temporary - table for it - */ - Item_int *intersect_mark; - /** TRUE if the unit contained TVC at the top level that has been wrapped into SELECT: VALUES (v1) ... (vn) => SELECT * FROM (VALUES (v1) ... (vn)) as tvc @@ -928,8 +933,9 @@ class st_select_lex_unit: public st_select_lex_node { fake_select_lex is used. */ st_select_lex *saved_fake_select_lex; - - st_select_lex *union_distinct; /* pointer to the last UNION DISTINCT */ + + /* pointer to the last node before last subsequence of UNION ALL */ + st_select_lex *union_distinct; bool describe; /* union exec() called for EXPLAIN */ Procedure *last_procedure; /* Pointer to procedure, if such exists */ @@ -955,6 +961,7 @@ class st_select_lex_unit: public st_select_lex_node { bool prepare(TABLE_LIST *derived_arg, select_result *sel_result, ulong additional_options); bool optimize(); + void optimize_bag_operation(bool is_outer_distinct); bool exec(); bool exec_recursive(); bool cleanup(); @@ -1025,7 +1032,7 @@ Field_pair *find_matching_field_pair(Item *item, List<Field_pair> pair_list); #define TOUCHED_SEL_COND 1/* WHERE/HAVING/ON should be reinited before use */ #define TOUCHED_SEL_DERIVED (1<<1)/* derived should be reinited before use */ - +#define UNIT_NEST_FL 1 /* SELECT_LEX - store information of parsed SELECT statment */ @@ -1048,7 +1055,7 @@ class st_select_lex: public st_select_lex_node select1->first_nested points to select1. */ st_select_lex *first_nested; - + uint8 nest_flags; Name_resolution_context context; LEX_CSTRING db; Item *where, *having; /* WHERE & HAVING clauses */ @@ -1524,6 +1531,13 @@ class st_select_lex: public st_select_lex_node select_handler *find_select_handler(THD *thd); + bool is_set_op() + { + return linkage == UNION_TYPE || + linkage == EXCEPT_TYPE || + linkage == INTERSECT_TYPE; + } + private: bool m_non_agg_field_used; bool m_agg_func_used; @@ -1570,6 +1584,8 @@ class st_select_lex: public st_select_lex_node void add_statistics(SELECT_LEX_UNIT *unit); bool make_unique_derived_name(THD *thd, LEX_CSTRING *alias); void lex_start(LEX *plex); + bool is_unit_nest() { return (nest_flags & UNIT_NEST_FL); } + void mark_as_unit_nest() { nest_flags= UNIT_NEST_FL; } }; typedef class st_select_lex SELECT_LEX; diff --git a/sql/sql_tvc.cc b/sql/sql_tvc.cc index 9eefd03..47f50ae 100644 --- a/sql/sql_tvc.cc +++ b/sql/sql_tvc.cc @@ -751,6 +751,7 @@ st_select_lex *wrap_tvc_with_tail(THD *thd, st_select_lex *tvc_sl) { wrapper_sl->master_unit()->union_distinct= wrapper_sl; } + wrapper_sl->distinct= tvc_sl->distinct; thd->lex->current_select= wrapper_sl; return wrapper_sl; } diff --git a/sql/sql_union.cc b/sql/sql_union.cc index 41f4234..1abc81b 100644 --- a/sql/sql_union.cc +++ b/sql/sql_union.cc @@ -72,7 +72,7 @@ void select_unit::change_select() switch (step) { case INTERSECT_TYPE: - intersect_mark->value= prev_step= curr_step; + prev_step= curr_step; curr_step= current_select_number; break; case EXCEPT_TYPE: @@ -83,6 +83,7 @@ void select_unit::change_select() } DBUG_VOID_RETURN; } + /** Fill temporary tables for UNION/EXCEPT/INTERSECT @@ -93,7 +94,7 @@ void select_unit::change_select() EXCEPT: looks for the record in the table (with 'counter' field first if INTERSECT present in the sequence) and delete it if found -INTESECT: +INTERSECT: looks for the same record with 'counter' field of previous operation, put as a 'counter' number of the current SELECT. We scan the table and remove all records which marked with not last @@ -108,7 +109,7 @@ void select_unit::change_select() */ int select_unit::send_data(List<Item> &values) { - int rc; + int rc= 0; int not_reported_error= 0; if (unit->offset_limit_cnt) { // using limit offset,count @@ -119,17 +120,24 @@ int select_unit::send_data(List<Item> &values) return 0; if (table->no_rows_with_nulls) table->null_catch_flags= CHECK_ROW_FOR_NULLS_TO_REJECT; - if (intersect_mark) + + fill_record(thd, table, table->field + addon_cnt, values, true, false); + /* set up initial values for records to be written */ + if (addon_cnt && step == UNION_TYPE) { - fill_record(thd, table, table->field + 1, values, TRUE, FALSE); - table->field[0]->store((ulonglong) curr_step, 1); + DBUG_ASSERT(addon_cnt == 1); + table->field[0]->store((longlong) curr_step, 1); } - else - fill_record(thd, table, table->field, values, TRUE, FALSE); + if (unlikely(thd->is_error())) { rc= 1; - goto end; + if (unlikely(not_reported_error)) + { + DBUG_ASSERT(rc); + table->file->print_error(not_reported_error, MYF(0)); + } + return rc; } if (table->no_rows_with_nulls) { @@ -137,105 +145,58 @@ int select_unit::send_data(List<Item> &values) if (table->null_catch_flags) { rc= 0; - goto end; + if (unlikely(not_reported_error)) + { + DBUG_ASSERT(rc); + table->file->print_error(not_reported_error, MYF(0)); + } + return rc; } } - // select_unit::change_select() change step & Co correctly for each SELECT + /* select_unit::change_select() change step & Co correctly for each SELECT */ + int find_res; switch (step) { - case UNION_TYPE: - { - if (unlikely((write_err= - table->file->ha_write_tmp_row(table->record[0])))) - { - if (write_err == HA_ERR_FOUND_DUPP_KEY) - { - /* - Inform upper level that we found a duplicate key, that should not - be counted as part of limit - */ - rc= -1; - goto end; - } - bool is_duplicate= FALSE; - /* create_internal_tmp_table_from_heap will generate error if needed */ - if (table->file->is_fatal_error(write_err, HA_CHECK_DUP) && - create_internal_tmp_table_from_heap(thd, table, - tmp_table_param.start_recinfo, - &tmp_table_param.recinfo, - write_err, 1, &is_duplicate)) - { - rc= 1; - goto end; - } + case UNION_TYPE: + rc= write_record(); + /* no reaction with conversion */ + if (rc == -2) + rc= 0; + break; - if (is_duplicate) - { - rc= -1; - goto end; - } - } - break; - } - case EXCEPT_TYPE: - { - int find_res; - /* - The temporary table uses very first index or constrain for - checking unique constrain. - */ - if (!(find_res= table->file->find_unique_row(table->record[0], 0))) - { - DBUG_ASSERT(!table->triggers); - table->status|= STATUS_DELETED; - not_reported_error= table->file->ha_delete_tmp_row(table->record[0]); - rc= MY_TEST(not_reported_error); - goto end; - } - else - { - if ((rc= not_reported_error= (find_res != 1))) - goto end; - } - break; - } - case INTERSECT_TYPE: + case EXCEPT_TYPE: + /* + The temporary table uses very first index or constrain for + checking unique constrain. + */ + if (!(find_res= table->file->find_unique_row(table->record[0], 0))) + rc= delete_record(); + else + rc= not_reported_error= (find_res != 1); + break; + case INTERSECT_TYPE: + /* + The temporary table uses very first index or constrain for + checking unique constrain. + */ + if (!(find_res= table->file->find_unique_row(table->record[0], 0))) { - int find_res; - /* - The temporary table uses very first index or constrain for - checking unique constrain. - */ - if (!(find_res= table->file->find_unique_row(table->record[0], 0))) + DBUG_ASSERT(!table->triggers); + if (table->field[0]->val_int() == prev_step) { - DBUG_ASSERT(!table->triggers); - if (table->field[0]->val_int() != prev_step) - { - rc= 0; - goto end; - } - store_record(table, record[1]); - table->field[0]->store(curr_step, 0); - not_reported_error= table->file->ha_update_tmp_row(table->record[1], - table->record[0]); + not_reported_error= update_counter(table->field[0], curr_step); rc= MY_TEST(not_reported_error); DBUG_ASSERT(rc != HA_ERR_RECORD_IS_THE_SAME); - goto end; } - else - { - if ((rc= not_reported_error= (find_res != 1))) - goto end; - } - break; } - default: - DBUG_ASSERT(0); + else + rc= not_reported_error= (find_res != 1); + break; + default: + DBUG_ASSERT(0); } - rc= 0; -end: if (unlikely(not_reported_error)) { DBUG_ASSERT(rc); @@ -251,7 +212,7 @@ bool select_unit::send_eof() thd->lex->current_select->next_select()->get_linkage() == INTERSECT_TYPE)) { /* - it is not INTESECT or next SELECT in the sequence is INTERSECT so no + it is not INTERSECT or next SELECT in the sequence is INTERSECT so no need filtering (the last INTERSECT in this sequence of intersects will filter). */ @@ -265,15 +226,14 @@ bool select_unit::send_eof() TODO: as optimization for simple case this could be moved to 'fake_select' WHERE condition */ - handler *file= table->file; int error; - if (unlikely(file->ha_rnd_init_with_error(1))) + if (table->file->ha_rnd_init_with_error(1)) return 1; - do { - if (unlikely(error= file->ha_rnd_next(table->record[0]))) + error= table->file->ha_rnd_next(table->record[0]); + if (unlikely(error)) { if (error == HA_ERR_END_OF_FILE) { @@ -283,9 +243,9 @@ bool select_unit::send_eof() break; } if (table->field[0]->val_int() != curr_step) - error= file->ha_delete_tmp_row(table->record[0]); - } while (likely(!error)); - file->ha_rnd_end(); + error= delete_record(); + } while (!error); + table->file->ha_rnd_end(); if (unlikely(error)) table->file->print_error(error, MYF(0)); @@ -345,6 +305,7 @@ bool select_unit::flush() create_table whether to physically create result table keep_row_order keep rows in order as they were inserted hidden number of hidden fields (for INTERSECT) + plus one for `ALL` DESCRIPTION Create a temporary table that is used to store the result of a UNION, @@ -433,6 +394,143 @@ select_union_recursive::create_result_table(THD *thd_arg, } +/* + @brief + Write a record + + @retval + -2 conversion happened + -1 found a duplicate key + 0 no error + 1 if an error is reported +*/ + +int select_unit::write_record() +{ + if (unlikely((write_err= table->file->ha_write_tmp_row(table->record[0])))) + { + if (write_err == HA_ERR_FOUND_DUPP_KEY) + { + /* + Inform upper level that we found a duplicate key, that should not + be counted as part of limit + */ + return -1; + } + bool is_duplicate= false; + /* create_internal_tmp_table_from_heap will generate error if needed */ + if (table->file->is_fatal_error(write_err, HA_CHECK_DUP)) + { + if (!create_internal_tmp_table_from_heap(thd, table, + tmp_table_param.start_recinfo, + &tmp_table_param.recinfo, + write_err, 1, &is_duplicate)) + { + return -2; + } + else + { + return 1; + } + } + if (is_duplicate) + { + return -1; + } + } + return 0; +} + + +/* + @brief + Update counter for a record + + @retval + 0 no error + -1 error occurred +*/ + +int select_unit::update_counter(Field* counter, longlong value) +{ + store_record(table, record[1]); + counter->store(value, 0); + int error= table->file->ha_update_tmp_row(table->record[1], + table->record[0]); + return error; +} + + +/* + @brief + Try to disable index + + @retval + true index is disabled this time + false this time did not disable the index +*/ + +bool select_unit_ext::disable_index_if_needed(SELECT_LEX *curr_sl) +{ + if (is_index_enabled && + (curr_sl == curr_sl->master_unit()->union_distinct || + !curr_sl->next_select()) ) + { + is_index_enabled= false; + if (table->file->ha_disable_indexes(HA_KEY_SWITCH_ALL)) + return false; + table->no_keyread=1; + return true; + } + return false; +} + +/* + @brief + Unfold a record + + @retval + 0 no error + -1 conversion happened +*/ + +int select_unit_ext::unfold_record(ha_rows cnt) +{ + + DBUG_ASSERT(cnt > 0); + int error= 0; + bool is_convertion_happened= false; + while (--cnt) + { + error= write_record(); + if (error == -2) + { + is_convertion_happened= true; + error= -1; + } + } + if (is_convertion_happened) + return -1; + return error; +} + +/* + @brief + Delete a record + + @retval + 0 no error + 1 if an error is reported +*/ + +int select_unit::delete_record() +{ + DBUG_ASSERT(!table->triggers); + table->status|= STATUS_DELETED; + int not_reported_error= table->file->ha_delete_tmp_row(table->record[0]); + return MY_TEST(not_reported_error); +} + /** Reset and empty the temporary table that stores the materialized query result. @@ -448,6 +546,357 @@ void select_unit::cleanup() } +/* + @brief + Set up value needed by send_data() and send_eof() + + @detail + - For EXCEPT we will decrease the counter by one + and INTERSECT / UNION we increase the counter. + + - For INTERSECT we will modify the second extra field (intersect counter) + and for EXCEPT / UNION we modify the first (duplicate counter) +*/ + +void select_unit_ext::change_select() +{ + select_unit::change_select(); + switch(step){ + case UNION_TYPE: + increment= 1; + curr_op_type= UNION_DISTINCT; + break; + case EXCEPT_TYPE: + increment= -1; + curr_op_type= EXCEPT_DISTINCT; + break; + case INTERSECT_TYPE: + increment= 1; + curr_op_type= INTERSECT_DISTINCT; + break; + default: DBUG_ASSERT(0); + } + if (!thd->lex->current_select->distinct) + /* change type from DISTINCT to ALL */ + curr_op_type= (set_op_type)(curr_op_type + 1); + + duplicate_cnt= table->field[addon_cnt - 1]; + if (addon_cnt == 2) + additional_cnt= table->field[addon_cnt - 2]; + else + additional_cnt= NULL; +} + + +/* + @brief + Fill temporary tables for operations need extra fields + + @detail + - If this operation is not distinct, we try to find it and increase the + counter by "increment" setted in select_unit_ext::change_select(). + + - If it is distinct, for UNION we write this record; for INTERSECT we + try to find it and increase the intersect counter if found; for EXCEPT + we try to find it and delete that record if found. + +*/ + +int select_unit_ext::send_data(List<Item> &values) +{ + int rc= 0; + int not_reported_error= 0; + int find_res; + if (unit->offset_limit_cnt) + { + /* using limit offset,count */ + unit->offset_limit_cnt--; + return 0; + } + if (thd->killed == ABORT_QUERY) + return 0; + if (table->no_rows_with_nulls) + table->null_catch_flags= CHECK_ROW_FOR_NULLS_TO_REJECT; + + fill_record(thd, table, table->field + addon_cnt, values, true, false); + /* set up initial values for records to be written */ + if ( step == UNION_TYPE ) + { + /* set duplicate counter to 1 */ + duplicate_cnt->store((longlong) 1, 1); + /* set the other counter to 0 */ + if (curr_op_type == INTERSECT_ALL) + additional_cnt->store((longlong) 0, 1); + } + + if (unlikely(thd->is_error())) + { + rc= 1; + if (unlikely(not_reported_error)) + { + DBUG_ASSERT(rc); + table->file->print_error(not_reported_error, MYF(0)); + } + return rc; + } + if (table->no_rows_with_nulls) + { + table->null_catch_flags&= ~CHECK_ROW_FOR_NULLS_TO_REJECT; + if (table->null_catch_flags) + { + if (unlikely(not_reported_error)) + { + DBUG_ASSERT(rc); + table->file->print_error(not_reported_error, MYF(0)); + } + return rc; + } + } + + switch(curr_op_type) + { + case UNION_ALL: + if (!is_index_enabled || + (find_res= table->file->find_unique_row(table->record[0], 0))) + { + rc= write_record(); + /* no reaction with conversion */ + if (rc == -2) + rc= 0; + } + else + { + longlong cnt= duplicate_cnt->val_int() + increment; + not_reported_error= update_counter(duplicate_cnt, cnt); + DBUG_ASSERT(!table->triggers); + rc= MY_TEST(not_reported_error); + } + break; + + case EXCEPT_ALL: + if (!(find_res= table->file->find_unique_row(table->record[0], 0))) + { + longlong cnt= duplicate_cnt->val_int() + increment; + if (cnt == 0) + rc= delete_record(); + else + { + not_reported_error= update_counter(duplicate_cnt, cnt); + DBUG_ASSERT(!table->triggers); + rc= MY_TEST(not_reported_error); + } + } + break; + + case INTERSECT_ALL: + if (!(find_res= table->file->find_unique_row(table->record[0], 0))) + { + longlong cnt= duplicate_cnt->val_int() + increment; + if (cnt <= additional_cnt->val_int()) + { + not_reported_error= update_counter(duplicate_cnt, cnt); + DBUG_ASSERT(!table->triggers); + rc= MY_TEST(not_reported_error); + } + } + break; + + case UNION_DISTINCT: + rc= write_record(); + /* no reaction with conversion */ + if (rc == -2) + rc= 0; + break; + + case EXCEPT_DISTINCT: + if (!(find_res= table->file->find_unique_row(table->record[0], 0))) + rc= delete_record(); + else + rc= not_reported_error= (find_res != 1); + break; + + case INTERSECT_DISTINCT: + if (!(find_res= table->file->find_unique_row(table->record[0], 0))) + { + if (additional_cnt->val_int() == prev_step) + { + not_reported_error= update_counter(additional_cnt, curr_step); + rc= MY_TEST(not_reported_error); + DBUG_ASSERT(rc != HA_ERR_RECORD_IS_THE_SAME); + } + else if (additional_cnt->val_int() != curr_step) + rc= delete_record(); + } + else + rc= not_reported_error= (find_res != 1); + break; + + default: + DBUG_ASSERT(0); + } + + if (unlikely(not_reported_error)) + { + DBUG_ASSERT(rc); + table->file->print_error(not_reported_error, MYF(0)); + } + return rc; +} + + +/* + @brief + Do post-operation after a operator + + @detail + We need to scan in these cases: + - If this operation is DISTINCT and next is ALL, + duplicate counter needs to be set to 1. + - If this operation is INTERSECT ALL and counter needs to be updated. + - If next operation is INTERSECT ALL, + set up the second extra field (called "intersect_counter") to 0. + this extra field counts records in the second operand. + + If this operation is equal to "union_distinct" or is the last operation, + we'll disable index. Then if this operation is ALL we'll unfold records. +*/ + +bool select_unit_ext::send_eof() +{ + int error= 0; + SELECT_LEX *curr_sl= thd->lex->current_select; + SELECT_LEX *next_sl= curr_sl->next_select(); + bool is_next_distinct= next_sl && next_sl->distinct; + bool is_next_intersect_all= + next_sl && + next_sl->get_linkage() == INTERSECT_TYPE && + !next_sl->distinct; + bool need_unfold= (disable_index_if_needed(curr_sl) && + !curr_sl->distinct); + + if (((curr_sl->distinct && !is_next_distinct) || + curr_op_type == INTERSECT_ALL || + is_next_intersect_all) && + !need_unfold) + { + if (!next_sl) + DBUG_ASSERT(curr_op_type != INTERSECT_ALL); + bool need_update_row; + if (unlikely(table->file->ha_rnd_init_with_error(1))) + return 1; + do + { + need_update_row= false; + if (unlikely(error= table->file->ha_rnd_next(table->record[0]))) + { + if (error == HA_ERR_END_OF_FILE) + { + error= 0; + break; + } + break; + } + store_record(table, record[1]); + + if (curr_sl->distinct && !is_next_distinct) + { + /* set duplicate counter to 1 if next operation is ALL */ + duplicate_cnt->store(1, 0); + need_update_row= true; + } + + if (is_next_intersect_all) + { + longlong d_cnt_val= duplicate_cnt->val_int(); + if (d_cnt_val == 0) + error= delete_record(); + else + { + if (curr_op_type == INTERSECT_ALL) + { + longlong a_cnt_val= additional_cnt->val_int(); + if (a_cnt_val < d_cnt_val) + d_cnt_val= a_cnt_val; + } + additional_cnt->store(d_cnt_val, 0); + duplicate_cnt->store((longlong)0, 0); + need_update_row= true; + } + } + + if (need_update_row) + error= table->file->ha_update_tmp_row(table->record[1], + table->record[0]); + } while (likely(!error)); + table->file->ha_rnd_end(); + } + + /* unfold */ + else if (need_unfold) + { + /* unfold if is ALL operation */ + ha_rows dup_cnt; + if (unlikely(table->file->ha_rnd_init_with_error(1))) + return 1; + do + { + if (unlikely(error= table->file->ha_rnd_next(table->record[0]))) + { + if (error == HA_ERR_END_OF_FILE) + { + error= 0; + break; + } + break; + } + dup_cnt= (ha_rows)duplicate_cnt->val_int(); + /* delete record if not exist in the second operand */ + if (dup_cnt == 0) + { + error= delete_record(); + continue; + } + if (curr_op_type == INTERSECT_ALL) + { + longlong add_cnt= additional_cnt->val_int(); + if (dup_cnt > add_cnt && add_cnt > 0) + dup_cnt= (ha_rows)add_cnt; + } + + if (dup_cnt == 1) + continue; + + duplicate_cnt->store((longlong)1, 0); + if (additional_cnt) + additional_cnt->store((longlong)0, 0); + error= table->file->ha_update_tmp_row(table->record[1], + table->record[0]); + if (unlikely(error)) + break; + + if (unfold_record(dup_cnt) == -1) + { + /* restart the scan */ + if (unlikely(table->file->ha_rnd_init_with_error(1))) + return 1; + + duplicate_cnt= table->field[addon_cnt - 1]; + if (addon_cnt == 2) + additional_cnt= table->field[addon_cnt - 2]; + else + additional_cnt= NULL; + continue; + } + } while (likely(!error)); + table->file->ha_rnd_end(); + } + + if (unlikely(error)) + table->file->print_error(error, MYF(0)); + + return (MY_TEST(error)); +} + void select_union_recursive::cleanup() { if (table) @@ -818,6 +1267,29 @@ bool st_select_lex_unit::join_union_item_types(THD *thd_arg, } +bool init_item_int(THD* thd, Item_int* &item) +{ + if (!item) + { + Query_arena *arena, backup_arena; + arena= thd->activate_stmt_arena_if_needed(&backup_arena); + + item= new (thd->mem_root) Item_int(thd, 0); + + if (arena) + thd->restore_active_arena(arena, &backup_arena); + + if (!item) + return false; + } + else + { + item->value= 0; + } + return true; +} + + bool st_select_lex_unit::prepare(TABLE_LIST *derived_arg, select_result *sel_result, ulong additional_options) @@ -829,7 +1301,8 @@ bool st_select_lex_unit::prepare(TABLE_LIST *derived_arg, uint union_part_count= 0; select_result *tmp_result; bool is_union_select; - bool have_except= FALSE, have_intersect= FALSE; + bool have_except= false, have_intersect= false, + have_except_all_or_intersect_all= false; bool instantiate_tmp_table= false; bool single_tvc= !first_sl->next_select() && first_sl->tvc && !fake_select_lex; @@ -867,7 +1340,7 @@ bool st_select_lex_unit::prepare(TABLE_LIST *derived_arg, max/min subquery (ALL/ANY optimization) */ result= sel_result; - + if (prepared) { if (describe) @@ -906,15 +1379,27 @@ bool st_select_lex_unit::prepare(TABLE_LIST *derived_arg, found_rows_for_union= first_sl->options & OPTION_FOUND_ROWS; is_union_select= is_unit_op() || fake_select_lex || single_tvc; + /* will only optimize once */ + if (!bag_set_op_optimized && !is_recursive) + { + optimize_bag_operation(false); + } + for (SELECT_LEX *s= first_sl; s; s= s->next_select()) { switch (s->linkage) { case INTERSECT_TYPE: have_intersect= TRUE; + if (!s->distinct){ + have_except_all_or_intersect_all= true; + } break; case EXCEPT_TYPE: have_except= TRUE; + if (!s->distinct){ + have_except_all_or_intersect_all= TRUE; + } break; default: break; @@ -940,7 +1425,19 @@ bool st_select_lex_unit::prepare(TABLE_LIST *derived_arg, else { if (!is_recursive) - union_result= new (thd->mem_root) select_unit(thd); + /* + class "select_unit_ext" handles query contains EXCEPT ALL and / or + INTERSECT ALL. Others are handled by class "select_unit" + If have EXCEPT ALL or INTERSECT ALL in the query. First operand + should be UNION ALL + */ + if (have_except_all_or_intersect_all) + { + union_result= new (thd->mem_root) select_unit_ext(thd); + first_sl->distinct= false; + } + else + union_result= new (thd->mem_root) select_unit(thd); else { with_element->rec_result= @@ -1080,6 +1577,10 @@ bool st_select_lex_unit::prepare(TABLE_LIST *derived_arg, instantiate_tmp_table, false, 0)) goto err; + if (have_except_all_or_intersect_all) + { + union_result->init(); + } if (!derived_arg->table) { derived_arg->table= with_element->rec_result->rec_tables.head(); @@ -1091,6 +1592,7 @@ bool st_select_lex_unit::prepare(TABLE_LIST *derived_arg, } } } + // In case of a non-recursive UNION, join data types for all UNION parts. if (!is_recursive && join_union_item_types(thd, types, union_part_count)) goto err; @@ -1166,48 +1668,42 @@ bool st_select_lex_unit::prepare(TABLE_LIST *derived_arg, if (global_parameters()->ftfunc_list->elements) create_options= create_options | TMP_TABLE_FORCE_MYISAM; + /* extra field counter */ + uint hidden= 0; + Item_int *addon_fields[2]= {0}; if (!is_recursive) { - uint hidden= 0; - if (have_intersect) + if (have_except_all_or_intersect_all) { - 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); - - intersect_mark= new (thd->mem_root) Item_int(thd, 0); - - if (arena) - thd->restore_active_arena(arena, &backup_arena); + /* add duplicate_count */ + ++hidden; + } + /* add intersect_count */ + if (have_intersect) + ++hidden; - if (!intersect_mark) - goto err; - } - else - intersect_mark->value= 0; //reset - types.push_front(union_result->intersect_mark= intersect_mark); - union_result->intersect_mark->name.str= "___"; - union_result->intersect_mark->name.length= 3; + for(uint i= 0; i< hidden; i++) + { + init_item_int(thd, addon_fields[i]); + types.push_front(addon_fields[i]); + addon_fields[i]->name.str= i ? "__CNT_1" : "__CNT_2"; + addon_fields[i]->name.length= 7; } bool error= union_result->create_result_table(thd, &types, - MY_TEST(union_distinct), + MY_TEST(union_distinct) || + have_except_all_or_intersect_all || + have_intersect, create_options, &empty_clex_str, false, instantiate_tmp_table, false, hidden); - if (intersect_mark) + union_result->addon_cnt= hidden; + for (uint i= 0; i < hidden; i++) types.pop(); if (unlikely(error)) goto err; } + if (fake_select_lex && !fake_select_lex->first_cond_optimization) { save_tablenr= result_table_list.tablenr_exec; @@ -1235,9 +1731,8 @@ bool st_select_lex_unit::prepare(TABLE_LIST *derived_arg, 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 + for (uint i= 0; i < hidden; i++) + item_list.pop(); if (arena) thd->restore_active_arena(arena, &backup_arena); @@ -1282,7 +1777,7 @@ bool st_select_lex_unit::prepare(TABLE_LIST *derived_arg, We're in execution of a prepared statement or stored procedure: reset field items to point at fields from the created temporary table. */ - table->reset_item_list(&item_list, intersect_mark ? 1 : 0); + table->reset_item_list(&item_list, hidden); } if (fake_select_lex != NULL && (thd->stmt_arena->is_stmt_prepare() || @@ -1316,9 +1811,170 @@ bool st_select_lex_unit::prepare(TABLE_LIST *derived_arg, /** + @brief + Optimize a sequence of set operations + + @param first_sl first select of the level now under processing + + @details + The method optimizes with the following rules: + - (1)If a subsequence of INTERSECT contains at least one INTERSECT DISTINCT + or this subsequence is followed by UNION/EXCEPT DISTINCT then all + elements in the subsequence can changed for INTERSECT DISTINCT + - (2)If previous set operation is DISTINCT then EXCEPT ALL can be replaced + for EXCEPT DISTINCT + - (3)If UNION DISTINCT / EXCEPT DISTINCT follows a subsequence of UNION ALL + then all set operations of this subsequence can be replaced for + UNION DISTINCT + + For derived table it will look up outer select, and do optimize based on + outer select. + + Variable "union_distinct" will be updated in the end. + Not compatible with Oracle Mode. +*/ + +void st_select_lex_unit::optimize_bag_operation(bool is_outer_distinct) +{ + /* + skip run optimize for: + ORACLE MODE + CREATE VIEW + PREPARE ... FROM + recursive + */ + if ((thd->variables.sql_mode & MODE_ORACLE) || + (thd->lex->context_analysis_only & CONTEXT_ANALYSIS_ONLY_VIEW) || + (fake_select_lex != NULL && thd->stmt_arena->is_stmt_prepare()) || + (with_element && with_element->is_recursive )) + return; + DBUG_ASSERT(!bag_set_op_optimized); + + SELECT_LEX *sl; + /* INTERSECT subsequence can occur only at the very beginning */ + /* The first select with linkage == INTERSECT_TYPE */ + SELECT_LEX *intersect_start= NULL; + /* The first select after the INTERSECT subsequence */ + SELECT_LEX *intersect_end= NULL; + /* + Will point to the last node before UNION ALL subsequence. + Index can be disable there. + */ + SELECT_LEX *disable_index= NULL; + /* + True if there is a select with: + linkage == INTERSECT_TYPE && distinct==true + */ + bool any_intersect_distinct= false; + SELECT_LEX *prev_sl= first_select(); + + /* process INTERSECT subsequence in the begining */ + for (sl= prev_sl->next_select(); sl; prev_sl= sl, sl= sl->next_select()) + { + if (sl->linkage != INTERSECT_TYPE) + { + intersect_end= sl; + break; + } + else + { + if (!intersect_start) + intersect_start= sl; + if (sl->distinct) + { + any_intersect_distinct= true; + disable_index= sl; + } + } + } + + /* if subquery only contains INTERSECT and outer is UNION DISTINCT*/ + if (!sl && is_outer_distinct) + any_intersect_distinct= true; + + /* The first select of the current UNION ALL subsequence */ + SELECT_LEX *union_all_start= NULL; + for ( ; sl; prev_sl= sl, sl= sl->next_select()) + { + DBUG_ASSERT (sl->linkage != INTERSECT_TYPE); + if (!sl->distinct) + { + if (sl->linkage == UNION_TYPE) + { + if (!union_all_start) + { + union_all_start= sl; + } + } + else + { + DBUG_ASSERT (sl->linkage == EXCEPT_TYPE); + union_all_start= NULL; + if (prev_sl->distinct && prev_sl->is_set_op()) + { + sl->distinct= true; + disable_index= sl; + } + } + } + else + { /* sl->distinct == true */ + for (SELECT_LEX *si= union_all_start; si && si != sl; si= si->next_select()) + { + si->distinct= true; + } + union_all_start= NULL; + disable_index= sl; + } + } + + if (is_outer_distinct) + { + for (SELECT_LEX *si= union_all_start; si && si != sl; si= si->next_select()) + { + si->distinct= true; + } + union_all_start= NULL; + } + + if (any_intersect_distinct || + (intersect_end != NULL && intersect_end->distinct)) + { + for (sl= intersect_start; sl && sl != intersect_end; sl= sl->next_select()) + { + sl->distinct= true; + if (disable_index && disable_index->linkage == INTERSECT_TYPE) + disable_index= sl; + } + } + /* + if disable_index points to a INTERSECT, based on rule 1 we can set it + to the last INTERSECT node. + */ + if (disable_index && disable_index->linkage == INTERSECT_TYPE && + intersect_end && intersect_end->distinct) + disable_index= intersect_end; + /* union_distinct controls when to disable index */ + union_distinct= disable_index; + + /* recursive call this function for whole lex tree */ + for(sl= first_select(); sl; sl= sl->next_select()) + { + if (sl->is_unit_nest() && + sl->first_inner_unit() && + !sl->first_inner_unit()->bag_set_op_optimized) + sl->first_inner_unit()->optimize_bag_operation(sl->distinct); + } + + /* mark as optimized */ + bag_set_op_optimized= true; +} + + +/** Run optimization phase. - @return FALSE unit successfully passed optimization phase. + @return false unit successfully passed optimization phase. @return TRUE an error occur. */ bool st_select_lex_unit::optimize() @@ -1328,10 +1984,10 @@ bool st_select_lex_unit::optimize() DBUG_ENTER("st_select_lex_unit::optimize"); if (optimized && !uncacheable && !describe) - DBUG_RETURN(FALSE); + DBUG_RETURN(false); if (with_element && with_element->is_recursive && optimize_started) - DBUG_RETURN(FALSE); + DBUG_RETURN(false); optimize_started= true; if (uncacheable || !item || !item->assigned() || describe) @@ -1351,9 +2007,12 @@ bool st_select_lex_unit::optimize() table->file->info(HA_STATUS_VARIABLE); } /* re-enabling indexes for next subselect iteration */ - if (union_distinct && table->file->ha_enable_indexes(HA_KEY_SWITCH_ALL)) + if ((union_result->force_enable_index_if_needed() || union_distinct)) { - DBUG_ASSERT(0); + if(table->file->ha_enable_indexes(HA_KEY_SWITCH_ALL)) + DBUG_ASSERT(0); + else + table->no_keyread= 0; } } for (SELECT_LEX *sl= select_cursor; sl; sl= sl->next_select()) @@ -1499,7 +2158,7 @@ bool st_select_lex_unit::exec() } else { - sl->join->select_options= + sl->join->select_options= (select_limit_cnt == HA_POS_ERROR || sl->braces) ? sl->options & ~OPTION_FOUND_ROWS : sl->options | found_rows_for_union; saved_error= sl->join->optimize(); @@ -1512,7 +2171,7 @@ bool st_select_lex_unit::exec() sl->tvc->exec(sl); else sl->join->exec(); - if (sl == union_distinct && !(with_element && with_element->is_recursive)) + if (sl == union_distinct && !have_except_all_or_intersect_all) { // This is UNION DISTINCT, so there should be a fake_select_lex DBUG_ASSERT(fake_select_lex != NULL); diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index edf6b76..a09af12 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -17577,10 +17577,10 @@ release: unit_type_decl: UNION_SYM union_option { $$.unit_type= UNION_TYPE; $$.distinct= $2; } - | INTERSECT_SYM - { $$.unit_type= INTERSECT_TYPE; $$.distinct= 1; } - | EXCEPT_SYM - { $$.unit_type= EXCEPT_TYPE; $$.distinct= 1; } + | INTERSECT_SYM union_option + { $$.unit_type= INTERSECT_TYPE; $$.distinct= $2; } + | EXCEPT_SYM union_option + { $$.unit_type= EXCEPT_TYPE; $$.distinct= $2; } ; /* diff --git a/storage/heap/hp_write.c b/storage/heap/hp_write.c index 877c1bc..670f628 100644 --- a/storage/heap/hp_write.c +++ b/storage/heap/hp_write.c @@ -145,21 +145,21 @@ static uchar *next_free_record_pos(HP_SHARE *info) DBUG_PRINT("exit",("Used old position: %p", pos)); DBUG_RETURN(pos); } + if ((info->records > info->max_records && info->max_records) || + (info->data_length + info->index_length >= info->max_table_size)) + { + DBUG_PRINT("error", + ("record file full. records: %lu max_records: %lu " + "data_length: %llu index_length: %llu " + "max_table_size: %llu", + info->records, info->max_records, + info->data_length, info->index_length, + info->max_table_size)); + my_errno=HA_ERR_RECORD_FILE_FULL; + DBUG_RETURN(NULL); + } if (!(block_pos=(info->records % info->block.records_in_block))) { - if ((info->records > info->max_records && info->max_records) || - (info->data_length + info->index_length >= info->max_table_size)) - { - DBUG_PRINT("error", - ("record file full. records: %lu max_records: %lu " - "data_length: %llu index_length: %llu " - "max_table_size: %llu", - info->records, info->max_records, - info->data_length, info->index_length, - info->max_table_size)); - my_errno=HA_ERR_RECORD_FILE_FULL; - DBUG_RETURN(NULL); - } if (hp_get_new_block(info, &info->block,&length)) DBUG_RETURN(NULL); info->data_length+=length;