diff options
author | Oleksandr Byelkin <sanja@mariadb.com> | 2017-01-22 12:31:32 +0100 |
---|---|---|
committer | Oleksandr Byelkin <sanja@mariadb.com> | 2017-03-12 09:02:14 +0100 |
commit | 1e47dece126cfab481d81bda3eedf71c0e12119c (patch) | |
tree | c2f58f9f1cd09e0dd80b1eda719e49dfe9d32ac8 /mysql-test/r/except.result | |
parent | a4652c3b1a2a366065dbac3a7570e3cac09870ad (diff) | |
download | mariadb-git-1e47dece126cfab481d81bda3eedf71c0e12119c.tar.gz |
MDEV-10141: Add support for INTERSECT (and common parts for EXCEPT)
MDEV-10140: Add support for EXCEPT
Diffstat (limited to 'mysql-test/r/except.result')
-rw-r--r-- | mysql-test/r/except.result | 225 |
1 files changed, 225 insertions, 0 deletions
diff --git a/mysql-test/r/except.result b/mysql-test/r/except.result new file mode 100644 index 00000000000..29fc32177f3 --- /dev/null +++ b/mysql-test/r/except.result @@ -0,0 +1,225 @@ +create table t1 (a int, b int); +create table t2 (c int, d int); +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); +a b +1 1 +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 +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 +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; +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 +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); +EXPLAIN +{ + "query_block": { + "union_result": { + "table_name": "<union1,2>", + "access_type": "ALL", + "query_specifications": [ + { + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 2, + "filtered": 100 + } + } + }, + { + "query_block": { + "select_id": 2, + "operation": "EXCEPT", + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 2, + "filtered": 100 + } + } + } + ] + } + } +} +ANALYZE format=json (select a,b from t1) except (select c,d from t2); +ANALYZE +{ + "query_block": { + "union_result": { + "table_name": "<union1,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": 2, + "r_rows": 2, + "r_total_time_ms": "REPLACED", + "filtered": 100, + "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 + } + } + } + ] + } + } +} +ANALYZE format=json select * from ((select a,b from t1) except (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": 2, + "r_rows": 1, + "r_total_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100, + "materialized": { + "query_block": { + "union_result": { + "table_name": "<union2,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": 2, + "r_rows": 2, + "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": 2, + "r_rows": 2, + "r_total_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100 + } + } + } + ] + } + } + } + } + } +} +select * from ((select a,b from t1) except (select c,d from t2)) a; +a b +1 1 +prepare stmt from "(select a,b from t1) except (select c,d from t2)"; +execute stmt; +a b +1 1 +execute stmt; +a b +1 1 +prepare stmt from "select * from ((select a,b from t1) except (select c,d from t2)) a"; +execute stmt; +a b +1 1 +execute stmt; +a b +1 1 +drop tables t1,t2; +select 1 as a from dual except select 1 from dual; +a +(select 1 from dual) except (select 1 from dual); +1 +(select 1 from dual into @v) except (select 1 from dual); +ERROR HY000: Incorrect usage of EXCEPT and INTO +select 1 from dual ORDER BY 1 except 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 select 1 from dual' at line 1 +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 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); +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); +a b b1 +1 ddd sdfrrwwww +create table t3 (select a,b,b1 from t1) except (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; |