diff options
Diffstat (limited to 'mysql-test/r/subselect_partial_match.result')
| -rw-r--r-- | mysql-test/r/subselect_partial_match.result | 50 |
1 files changed, 50 insertions, 0 deletions
diff --git a/mysql-test/r/subselect_partial_match.result b/mysql-test/r/subselect_partial_match.result new file mode 100644 index 00000000000..5887de2fff2 --- /dev/null +++ b/mysql-test/r/subselect_partial_match.result @@ -0,0 +1,50 @@ +drop table if exists t1, t2; +# +# LP BUG#608744 +# +set @save_optimizer_switch=@@optimizer_switch; +set @@optimizer_switch="materialization=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=off"; +create table t1 (a1 char(1), a2 char(1)); +insert into t1 values (NULL, 'b'); +create table t2 (b1 char(1), b2 char(2)); +insert into t2 values ('a','b'), ('c', 'd'); +select * from t1 where (a1, a2) NOT IN (select b1, b2 from t2); +a1 a2 +drop table t1,t2; +set @@optimizer_switch=@save_optimizer_switch; +# +# LP BUG#601156 +# +CREATE TABLE t1 (a1 int DEFAULT NULL, a2 int DEFAULT NULL); +INSERT INTO t1 VALUES (NULL,2); +INSERT INTO t1 VALUES (4,NULL); +CREATE TABLE t2 (b1 int DEFAULT NULL, b2 int DEFAULT NULL); +INSERT INTO t2 VALUES (6,NULL); +INSERT INTO t2 VALUES (NULL,0); +set @save_optimizer_switch=@@optimizer_switch; +set @@optimizer_switch='materialization=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on'; +EXPLAIN EXTENDED +SELECT * FROM (SELECT * FROM t1 WHERE a1 NOT IN (SELECT b2 FROM t2)) table1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <derived2> system NULL NULL NULL NULL 0 0.00 const row not found +2 DERIVED t1 ALL NULL NULL NULL NULL 2 100.00 Using where +3 SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 +Warnings: +Note 1003 select NULL AS `a1`,NULL AS `a2` from (select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where (not(<expr_cache><`test`.`t1`.`a1`>(<in_optimizer>(`test`.`t1`.`a1`,`test`.`t1`.`a1` in ()))))) `table1` +DROP TABLE t1, t2; +set @@optimizer_switch=@save_optimizer_switch; +# +# LP BUG#613009 Crash in Ordered_key::get_field_idx +# +set @save_optimizer_switch=@@optimizer_switch; +set @@optimizer_switch='materialization=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=off'; +create table t1 (a1 char(3) DEFAULT NULL, a2 char(3) DEFAULT NULL); +insert into t1 values (NULL, 'a21'), (NULL, 'a22'); +explain select * from t1 where (a1, a2) not in (select a1, a2 from t1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 +select * from t1 where (a1, a2) not in (select a1, a2 from t1); +a1 a2 +drop table t1; +set @@optimizer_switch=@save_optimizer_switch; |