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