diff options
Diffstat (limited to 'mysql-test/r/intersect.result')
-rw-r--r-- | mysql-test/r/intersect.result | 274 |
1 files changed, 255 insertions, 19 deletions
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: |