diff options
Diffstat (limited to 'mysql-test/r/except.result')
| -rw-r--r-- | mysql-test/r/except.result | 322 |
1 files changed, 312 insertions, 10 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); |