diff options
Diffstat (limited to 'mysql-test/main/subselect_partial_match.result')
| -rw-r--r-- | mysql-test/main/subselect_partial_match.result | 985 |
1 files changed, 985 insertions, 0 deletions
diff --git a/mysql-test/main/subselect_partial_match.result b/mysql-test/main/subselect_partial_match.result new file mode 100644 index 00000000000..5f444457794 --- /dev/null +++ b/mysql-test/main/subselect_partial_match.result @@ -0,0 +1,985 @@ +set @save_optimizer_switch=@@optimizer_switch; +------------------------------- +Part 1: Feature tests. +------------------------------- +Default for all tests. +set @@optimizer_switch = "materialization=on,in_to_exists=off,semijoin=off,subquery_cache=off"; +set @test_default_opt_switch = @@optimizer_switch; +set @in_exists = "materialization=off,in_to_exists=on,semijoin=off,subquery_cache=off"; +------------------------------------------------------------------------- +Schema requires partial matching, but data analysis discoveres there is +no need. This is possible only if all outer columns are not NULL. +------------------------------------------------------------------------- +create table t1 (a1 char(8) not null, a2 char(8) not null); +create table t2 (b1 char(8), b2 char(8)); +insert into t1 values ('1 - 00', '2 - 00'); +insert into t1 values ('1 - 01', '2 - 01'); +insert into t2 values ('1 - 00', '2 - 00'); +insert into t2 values ('1 - 01', NULL ); +insert into t2 values (NULL , '2 - 02'); +insert into t2 values (NULL , NULL ); +insert into t2 values ('1 - 02', '2 - 02'); +select * from t1 +where (a1, a2) not in (select * from t2 where b1 is not null and b2 is not null); +a1 a2 +1 - 01 2 - 01 +select a1, a2, (a1, a2) not in (select * from t2) as in_res from t1; +a1 a2 in_res +1 - 00 2 - 00 0 +1 - 01 2 - 01 NULL +drop table t1, t2; +------------------------------------------------------------------------- +NULLs in the outer columns, no NULLs in the suqbuery +------------------------------------------------------------------------- +create table t1 (a1 char(8), a2 char(8)); +create table t2 (b1 char(8) not null, b2 char(8) not null); +insert into t1 values (NULL , '2 - 00'); +insert into t1 values ('1 - 01', '2 - 01'); +insert into t1 values (NULL , NULL ); +insert into t2 values ('1 - 00', '2 - 00'); +insert into t2 values ('1 - 01', '2 - 01'); +insert into t2 values ('1 - 02', '2 - 00'); +select * from t1 +where (a1, a2) not in (select * from t2 where b1 is not null and b2 is not null); +a1 a2 +select a1, a2, (a1, a2) not in (select * from t2) as in_res from t1; +a1 a2 in_res +NULL 2 - 00 NULL +1 - 01 2 - 01 0 +NULL NULL NULL +select * from t1 +where (a1, a2) in (select * from t2 where b1 is not null and b2 is not null); +a1 a2 +1 - 01 2 - 01 +select a1, a2, (a1, a2) in (select * from t2) as in_res from t1; +a1 a2 in_res +NULL 2 - 00 NULL +1 - 01 2 - 01 1 +NULL NULL NULL +drop table t1, t2; +------------------------------------------------------------------------- +NULLs in the outer column, NULLs in the subquery, there is +no value match in any column, but there is a partial match +such that some of the matching NULLs are in the outer columns, +the other NULLs are in the inner columns. +------------------------------------------------------------------------- +create table t1 (a1 char(1), a2 char(1), a3 char(1)); +create table t2 (b1 char(1), b2 char(1), b3 char(1)); +insert into t1 values ('c', NULL, 'a'); +insert into t2 values (NULL, 'x', NULL); +insert into t2 values (NULL, 'y', NULL); +insert into t2 values ('o', 'z', 'p'); +set @@optimizer_switch='partial_match_rowid_merge=on,partial_match_table_scan=off'; +EXPLAIN +SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 +SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1; +a1 a2 a3 in_res +c NULL a NULL +EXPLAIN +SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 +SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2); +a1 a2 a3 +set @@optimizer_switch='partial_match_rowid_merge=off,partial_match_table_scan=on'; +EXPLAIN +SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 +SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1; +a1 a2 a3 in_res +c NULL a NULL +EXPLAIN +SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 +SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2); +a1 a2 a3 +set @@optimizer_switch=@in_exists; +EXPLAIN +SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 3 Using where +SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1; +a1 a2 a3 in_res +c NULL a NULL +EXPLAIN +SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 3 Using where +SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2); +a1 a2 a3 +set @@optimizer_switch = @test_default_opt_switch; +drop table t1, t2; +create table t1 (a1 char(1), a2 char(1) not null, a3 char(1)); +create table t2 (b1 char(1), b2 char(1), b3 char(1)); +insert into t1 values (NULL, 'y', NULL); +insert into t2 values ('v', 'x', NULL); +insert into t2 values (NULL, 'y', 'w'); +set @@optimizer_switch='partial_match_rowid_merge=on,partial_match_table_scan=off'; +EXPLAIN +SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 +SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1; +a1 a2 a3 in_res +NULL y NULL NULL +EXPLAIN +SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 +SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2); +a1 a2 a3 +set @@optimizer_switch='partial_match_rowid_merge=off,partial_match_table_scan=on'; +EXPLAIN +SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 +SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1; +a1 a2 a3 in_res +NULL y NULL NULL +EXPLAIN +SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 +SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2); +a1 a2 a3 +set @@optimizer_switch=@in_exists; +EXPLAIN +SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where +SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1; +a1 a2 a3 in_res +NULL y NULL NULL +EXPLAIN +SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where +SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2); +a1 a2 a3 +set @@optimizer_switch = @test_default_opt_switch; +drop table t1, t2; +------------------------------------------------------------------------- +There is only one column in the subquery to complement the NULLs in the +outer reference. It is a NULL column, so a match is guaranteed. +------------------------------------------------------------------------- +create table t1 (a1 char(1), a2 char(1), a3 char(1)); +create table t2 (b1 char(1), b2 char(1), b3 char(1)); +insert into t1 values (NULL, 'g', NULL); +insert into t2 values ('z', NULL, 'y'); +insert into t2 values (NULL, 'z', 'y'); +set @@optimizer_switch='partial_match_rowid_merge=on,partial_match_table_scan=off'; +EXPLAIN +SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 +SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1; +a1 a2 a3 in_res +NULL g NULL NULL +EXPLAIN +SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 +SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2); +a1 a2 a3 +set @@optimizer_switch='partial_match_rowid_merge=off,partial_match_table_scan=on'; +EXPLAIN +SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 +SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1; +a1 a2 a3 in_res +NULL g NULL NULL +EXPLAIN +SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 +SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2); +a1 a2 a3 +set @@optimizer_switch=@in_exists; +EXPLAIN +SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where +SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1; +a1 a2 a3 in_res +NULL g NULL NULL +EXPLAIN +SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where +SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2); +a1 a2 a3 +set @@optimizer_switch = @test_default_opt_switch; +drop table t1, t2; +------------------------------------------------------------------------- +The intersection of the NULL bitmaps is empty because the ranges +of NULL bits do not overlap. +------------------------------------------------------------------------- +create table t1 (a1 char(1), a2 char(1), a3 char(1)); +create table t2 (b1 char(1), b2 char(1), b3 char(1)); +insert into t1 values ('b', 'g', NULL); +insert into t2 values ('z', NULL, 'y'); +insert into t2 values (NULL, 'z', 'y'); +set @@optimizer_switch='partial_match_rowid_merge=on,partial_match_table_scan=off'; +EXPLAIN +SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 +SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1; +a1 a2 a3 in_res +b g NULL 0 +EXPLAIN +SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 +SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2); +a1 a2 a3 +b g NULL +set @@optimizer_switch='partial_match_rowid_merge=off,partial_match_table_scan=on'; +EXPLAIN +SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 +SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1; +a1 a2 a3 in_res +b g NULL 0 +EXPLAIN +SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 +SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2); +a1 a2 a3 +b g NULL +set @@optimizer_switch=@in_exists; +EXPLAIN +SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where +SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1; +a1 a2 a3 in_res +b g NULL 0 +EXPLAIN +SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where +SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2); +a1 a2 a3 +b g NULL +drop table t1, t2; +------------------------------------------------------------------------- +The intersection of the NULL bitmaps is non-empty, and there is a +non-NULL column. +------------------------------------------------------------------------- +create table t1 (a1 char(1), a2 char(1), a3 char(1) not null, a4 char(10)); +create table t2 (b1 char(1), b2 char(1), b3 char(1), b4 char(1)); +insert into t1 values ('a', 'g', 'x', NULL); +insert into t2 values ('z', NULL, 'y', 'x'); +insert into t2 values (NULL, NULL, 'x', 'y'); +insert into t2 values ('x', 'w', 'z', NULL); +set @@optimizer_switch='partial_match_rowid_merge=on,partial_match_table_scan=off'; +EXPLAIN +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 3 Using where +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +a1 a2 a3 a4 in_res +a g x NULL NULL +EXPLAIN +SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 3 Using where +SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2); +a1 a2 a3 a4 +set @@optimizer_switch='partial_match_rowid_merge=off,partial_match_table_scan=on'; +EXPLAIN +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 3 Using where +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +a1 a2 a3 a4 in_res +a g x NULL NULL +EXPLAIN +SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 3 Using where +SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2); +a1 a2 a3 a4 +set @@optimizer_switch=@in_exists; +EXPLAIN +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 3 Using where +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +a1 a2 a3 a4 in_res +a g x NULL NULL +EXPLAIN +SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 3 Using where +SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2); +a1 a2 a3 a4 +drop table t1, t2; +------------------------------------------------------------------------- +Value match in one row, but the NULL complement match in another. +The result must be false. +------------------------------------------------------------------------- +create table t1 (a1 char(2), a2 char(1), a3 char(1) not null, a4 char(10)); +create table t2 (b1 char(2), b2 char(1), b3 char(1), b4 char(1)); +insert into t1 values ('99', NULL, 'j', 'f'); +insert into t2 values ('01', NULL, 'y', NULL); +insert into t2 values ('02', NULL, 'x', 'y'); +insert into t2 values ('03', 'q', 'y', 'x'); +insert into t2 values (NULL, 'q', 'm', 'p'); +insert into t2 values ('m', 'z', 'j', NULL); +insert into t2 values (NULL, 'z', 'b', NULL); +insert into t2 values (NULL, 'z', 'a', NULL); +insert into t2 values ('34', 'q', 'y', 'x'); +set @@optimizer_switch='partial_match_rowid_merge=on,partial_match_table_scan=off'; +EXPLAIN +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 8 Using where +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +a1 a2 a3 a4 in_res +99 NULL j f 0 +EXPLAIN +SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 8 Using where +SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2); +a1 a2 a3 a4 +99 NULL j f +set @@optimizer_switch='partial_match_rowid_merge=off,partial_match_table_scan=on'; +EXPLAIN +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 8 Using where +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +a1 a2 a3 a4 in_res +99 NULL j f 0 +EXPLAIN +SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 8 Using where +SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2); +a1 a2 a3 a4 +99 NULL j f +set @@optimizer_switch=@in_exists; +EXPLAIN +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 8 Using where +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +a1 a2 a3 a4 in_res +99 NULL j f 0 +EXPLAIN +SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 8 Using where +SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2); +a1 a2 a3 a4 +99 NULL j f +drop table t1, t2; +------------------------------------------------------------------------- +Test the intersection of larger number of rows with NULL, such that +the number is at the boundary 32. This test is based on the implementation +of MY_BITMAP which uses 32 bit words, and the intersection operation works +by intersecting the bitmap word by word. +------------------------------------------------------------------------- +create table t1 (a1 char(2), a2 char(2), a3 char(1), a4 char(10)) ENGINE=MyISAM; +create table t2 (b1 char(2), b2 char(2), b3 char(1), b4 char(1)) ENGINE=MyISAM; +insert into t1 values (NULL, '98', NULL, 'b'); +insert into t1 values (NULL, '99', NULL, 'c'); +insert into t2 values ('00', 'rr', 'y', NULL); +insert into t2 values ('01', 'rr', 'y', NULL); +insert into t2 values ('02', NULL, 'x', 'y'); +insert into t2 values ('03', 'qq', 'y', 'x'); +insert into t2 values ('04', 'qq', 'm', 'p'); +insert into t2 values ('05', 'rr', 'y', NULL); +insert into t2 values ('06', NULL, 'x', 'y'); +insert into t2 values ('07', 'qq', 'y', 'x'); +insert into t2 values ('08', 'qq', 'm', 'q'); +insert into t2 values ('09', 'rr', 'y', NULL); +insert into t2 values ('10', NULL, 'x', 'y'); +insert into t2 values ('11', 'qq', 'y', 'x'); +insert into t2 values ('12', 'qq', 'm', 'k'); +insert into t2 values ('13', 'rr', 'y', NULL); +insert into t2 values ('14', NULL, 'x', 'y'); +insert into t2 values ('15', 'qq', 'y', 'x'); +insert into t2 values ('16', 'qq', 'm', 's'); +insert into t2 values ('17', 'rr', 'y', NULL); +insert into t2 values ('18', NULL, 'x', 'y'); +insert into t2 values ('19', 'qq', 'y', 'x'); +insert into t2 values ('20', 'qq', 'm', 't'); +insert into t2 values ('21', 'rr', 'y', NULL); +insert into t2 values ('22', NULL, 'x', 'y'); +insert into t2 values ('23', 'qq', 'y', 'x'); +insert into t2 values ('24', 'qq', 'm', 'u'); +insert into t2 values ('25', 'rr', 'y', NULL); +insert into t2 values ('26', NULL, 'x', 'y'); +insert into t2 values ('27', 'qq', 'y', 'x'); +insert into t2 values ('28', 'qq', 'm', 'y'); +insert into t2 values ('29', 'rr', 'y', NULL); +insert into t2 values ('30', NULL, 'x', 'z'); +insert into t2 values ('31', 'ss', 'h', NULL); +insert into t2 values ('32', 'vv', 'i', NULL); +the only partial matching row +insert into t2 values ('33', NULL, 'j', NULL); +insert into t2 values ('34', 'qq', 'y', 'x'); +set @@optimizer_switch='partial_match_rowid_merge=on,partial_match_table_scan=off'; +EXPLAIN +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 35 Using where +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +a1 a2 a3 a4 in_res +NULL 98 NULL b NULL +NULL 99 NULL c NULL +EXPLAIN +SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2); +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 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 35 Using where +SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2); +a1 a2 a3 a4 +set @@optimizer_switch='partial_match_rowid_merge=off,partial_match_table_scan=on'; +EXPLAIN +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 35 Using where +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +a1 a2 a3 a4 in_res +NULL 98 NULL b NULL +NULL 99 NULL c NULL +EXPLAIN +SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2); +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 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 35 Using where +SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2); +a1 a2 a3 a4 +set @@optimizer_switch=@in_exists; +EXPLAIN +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 35 Using where +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +a1 a2 a3 a4 in_res +NULL 98 NULL b NULL +NULL 99 NULL c NULL +EXPLAIN +SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2); +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 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 35 Using where +SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2); +a1 a2 a3 a4 +change the mathcing row to be the last one in the first bitmap word +update t2 set b2 = 'zz' where b1 = 33; +update t2 set b2 = NULL where b1 = 31; +set @@optimizer_switch='partial_match_rowid_merge=on,partial_match_table_scan=off'; +EXPLAIN +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 35 Using where +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +a1 a2 a3 a4 in_res +NULL 98 NULL b NULL +NULL 99 NULL c NULL +EXPLAIN +SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2); +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 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 35 Using where +SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2); +a1 a2 a3 a4 +set @@optimizer_switch='partial_match_rowid_merge=off,partial_match_table_scan=on'; +EXPLAIN +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 35 Using where +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +a1 a2 a3 a4 in_res +NULL 98 NULL b NULL +NULL 99 NULL c NULL +EXPLAIN +SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2); +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 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 35 Using where +SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2); +a1 a2 a3 a4 +set @@optimizer_switch=@in_exists; +EXPLAIN +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 35 Using where +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +a1 a2 a3 a4 in_res +NULL 98 NULL b NULL +NULL 99 NULL c NULL +EXPLAIN +SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2); +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 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 35 Using where +SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2); +a1 a2 a3 a4 +drop table t1, t2; +------------------------------------------------------------------------- +All columns require partial matching (no non-null columns) +------------------------------------------------------------------------- +TODO +------------------------------------------------------------------------- +Both non-NULL columns and columns with NULLs +------------------------------------------------------------------------- +TODO +------------------------------------------------------------------------- +Covering NULL rows +------------------------------------------------------------------------- +create table t1 (a1 char(8), a2 char(8)); +create table t2 (b1 char(8), b2 char(8)); +insert into t1 values ('1 - 00', '2 - 00'); +insert into t1 values ('1 - 01', '2 - 01'); +insert into t2 values ('1 - 01', NULL ); +insert into t2 values (NULL , '2 - 02'); +insert into t2 values (NULL , NULL ); +insert into t2 values ('1 - 02', '2 - 02'); +select * from t1 +where (a1, a2) not in (select * from t2); +a1 a2 +select a1, a2, (a1, a2) not in (select * from t2) as in_res from t1; +a1 a2 in_res +1 - 00 2 - 00 NULL +1 - 01 2 - 01 NULL +insert into t2 values ('1 - 01', '2 - 01'); +select * from t1 +where (a1, a2) not in (select * from t2); +a1 a2 +select a1, a2, (a1, a2) not in (select * from t2) as in_res from t1; +a1 a2 in_res +1 - 00 2 - 00 NULL +1 - 01 2 - 01 0 +select * from t1 +where (a1, a2) in (select * from t2); +a1 a2 +1 - 01 2 - 01 +select a1, a2, (a1, a2) in (select * from t2) as in_res from t1; +a1 a2 in_res +1 - 00 2 - 00 NULL +1 - 01 2 - 01 1 +drop table t1, t2; +------------------------------------------------------------------------- +Covering NULL columns +------------------------------------------------------------------------- +this case affects only the rowid-merge algorithm +set @@optimizer_switch="partial_match_rowid_merge=on,partial_match_table_scan=off"; +create table t1 (a1 char(8) not null, a2 char(8), a3 char(8) not null); +create table t2 (b1 char(8) not null, b2 char(8), b3 char(8) not null); +insert into t1 values ('1 - 00', '2 - 00', '3 - 00'); +insert into t1 values ('1 - 01', '2 - 01', '3 - 01'); +insert into t2 values ('1 - 01', NULL, '3 - x1'); +insert into t2 values ('1 - 02', NULL, '3 - 02'); +insert into t2 values ('1 - 00', NULL, '3 - 00'); +select * from t1 +where (a1, a2, a3) not in (select * from t2); +a1 a2 a3 +1 - 01 2 - 01 3 - 01 +select *, (a1, a2, a3) not in (select * from t2) as in_res from t1; +a1 a2 a3 in_res +1 - 00 2 - 00 3 - 00 NULL +1 - 01 2 - 01 3 - 01 1 +select * from t1 +where (a1, a2, a3) in (select * from t2); +a1 a2 a3 +select *, (a1, a2, a3) in (select * from t2) as in_res from t1; +a1 a2 a3 in_res +1 - 00 2 - 00 3 - 00 NULL +1 - 01 2 - 01 3 - 01 0 +drop table t1, t2; +create table t1 (a1 char(8), a2 char(8), a3 char(8) not null); +create table t2 (b1 char(8), b2 char(8), b3 char(8) not null); +insert into t1 values ('1 - 00', '2 - 00', '3 - 00'); +insert into t1 values ('1 - 01', '2 - 01', '3 - 01'); +insert into t2 values (NULL, NULL, '3 - x1'); +insert into t2 values (NULL, NULL, '3 - 02'); +insert into t2 values (NULL, NULL, '3 - 00'); +select * from t1 +where (a1, a2, a3) not in (select * from t2); +a1 a2 a3 +1 - 01 2 - 01 3 - 01 +select *, (a1, a2, a3) not in (select * from t2) as in_res from t1; +a1 a2 a3 in_res +1 - 00 2 - 00 3 - 00 NULL +1 - 01 2 - 01 3 - 01 1 +select * from t1 +where (a1, a2, a3) in (select * from t2); +a1 a2 a3 +select *, (a1, a2, a3) in (select * from t2) as in_res from t1; +a1 a2 a3 in_res +1 - 00 2 - 00 3 - 00 NULL +1 - 01 2 - 01 3 - 01 0 +drop table t1, t2; +------------------------------------------------------------------------- +Covering NULL row, and a NULL column +------------------------------------------------------------------------- +create table t1 (a1 char(8) not null, a2 char(8), a3 char(8)); +create table t2 (b1 char(8), b2 char(8), b3 char(8)); +insert into t1 values ('1 - 00', '2 - 00', '3 - 00'); +insert into t1 values ('1 - 01', '2 - 01', '3 - 01'); +insert into t2 values ('1 - 01', NULL, '3 - x1'); +insert into t2 values (NULL , NULL, NULL ); +insert into t2 values ('1 - 00', NULL, '3 - 00'); +select * from t1 +where (a1, a2, a3) not in (select * from t2); +a1 a2 a3 +select *, (a1, a2, a3) not in (select * from t2) as in_res from t1; +a1 a2 a3 in_res +1 - 00 2 - 00 3 - 00 NULL +1 - 01 2 - 01 3 - 01 NULL +select * from t1 +where (a1, a2, a3) in (select * from t2); +a1 a2 a3 +select *, (a1, a2, a3) in (select * from t2) as in_res from t1; +a1 a2 a3 in_res +1 - 00 2 - 00 3 - 00 NULL +1 - 01 2 - 01 3 - 01 NULL +drop table t1, t2; +------------------------------------------------------------------------- +Covering NULL row, and covering NULL columns +------------------------------------------------------------------------- +create table t1 (a1 char(8) not null, a2 char(8), a3 char(8)); +create table t2 (b1 char(8), b2 char(8), b3 char(8)); +insert into t1 values ('1 - 00', '2 - 00', '3 - 00'); +insert into t1 values ('1 - 01', '2 - 01', '3 - 01'); +insert into t2 values (NULL, NULL, NULL); +insert into t2 values (NULL, NULL, NULL); +select * from t1 +where (a1, a2, a3) not in (select * from t2); +a1 a2 a3 +select *, (a1, a2, a3) not in (select * from t2) as in_res from t1; +a1 a2 a3 in_res +1 - 00 2 - 00 3 - 00 NULL +1 - 01 2 - 01 3 - 01 NULL +select * from t1 +where (a1, a2, a3) in (select * from t2); +a1 a2 a3 +select *, (a1, a2, a3) in (select * from t2) as in_res from t1; +a1 a2 a3 in_res +1 - 00 2 - 00 3 - 00 NULL +1 - 01 2 - 01 3 - 01 NULL +drop table t1, t2; +------------------------------------------------------------------------- +Small buffer for the rowid_merge partial match algorithm that forces +reverting to table scan partial match. +------------------------------------------------------------------------- +set @save_rowid_merge_buff_size = @@rowid_merge_buff_size; +set @@rowid_merge_buff_size = 0; +create table t1 (a1 char(2), a2 char(2), a3 char(1), a4 char(10)); +create table t2 (b1 char(2), b2 char(2), b3 char(1), b4 char(1)); +insert into t1 values (NULL, '98', NULL, 'b'); +insert into t1 values (NULL, '99', NULL, 'c'); +insert into t2 values ('00', 'rr', 'y', NULL); +insert into t2 values ('01', 'rr', 'y', NULL); +insert into t2 values ('02', NULL, 'x', 'y'); +insert into t2 values ('03', 'qq', 'y', 'x'); +set @@optimizer_switch='partial_match_rowid_merge=on,partial_match_table_scan=off'; +EXPLAIN +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 4 Using where +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +a1 a2 a3 a4 in_res +NULL 98 NULL b 0 +NULL 99 NULL c 0 +drop table t1, t2; +set @@rowid_merge_buff_size = @save_rowid_merge_buff_size; +------------------------------- +Part 2: Test cases for bugs. +------------------------------- +drop table if exists t1, t2; +# +# LP BUG#608744 +# +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; +# +# 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 @@optimizer_switch='materialization=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on'; +set @tmp_optimizer_switch=@@optimizer_switch; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; +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> ALL NULL NULL NULL NULL 2 100.00 +2 DERIVED t1 ALL NULL NULL NULL NULL 2 100.00 Using where +3 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 100.00 +Warnings: +Note 1003 /* select#1 */ select `table1`.`a1` AS `a1`,`table1`.`a2` AS `a2` from (/* select#2 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where !<in_optimizer>(`test`.`t1`.`a1`,`test`.`t1`.`a1` in ( <materialize> (/* select#3 */ select `test`.`t2`.`b2` from `test`.`t2` ), <primary_index_lookup>(`test`.`t1`.`a1` in <temporary table> on distinct_key where `test`.`t1`.`a1` = `<subquery3>`.`b2`)))) `table1` +set optimizer_switch=@tmp_optimizer_switch; +DROP TABLE t1, t2; +# +# LP BUG#613009 Crash in Ordered_key::get_field_idx +# +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 MATERIALIZED 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; +# +# LP BUG#680058 void Ordered_key::add_key(rownum_t): +# Assertion `key_buff_elements && cur_key_idx < key_buff_elements' failed +# +create table t1 (f1 char(1), f2 char(1)); +insert into t1 values ('t', '0'), ('0', 't'); +create table t2 (f3 char(1), f4 char(1)); +insert into t2 values ('t', NULL), ('t', NULL), ('d', 'y'); +set @@optimizer_switch='materialization=on,partial_match_rowid_merge=on,partial_match_table_scan=off,semijoin=off'; +select * from t1 where (f1, f2) not in (select * from t2); +f1 f2 +0 t +drop table t1, t2; +# +# LP BUG#809245 Second assertion `bit < (map)->n_bits' with partial_match_merge +# +CREATE TABLE t1 (d varchar(32)) ; +INSERT INTO t1 VALUES ('r'); +CREATE TABLE t2 ( a int, c varchar(32)) ; +INSERT INTO t2 VALUES (5,'r'); +CREATE TABLE t3 ( a int NOT NULL , d varchar(32)) ; +INSERT INTO t3 VALUES (10,'g'); +set @@optimizer_switch='materialization=on,partial_match_rowid_merge=on,partial_match_table_scan=off,in_to_exists=off'; +EXPLAIN SELECT * +FROM t1 +WHERE (t1.d , t1.d) NOT IN ( +SELECT t3.d , t2.c +FROM t3 LEFT JOIN t2 ON t3.a = t2.a); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +2 MATERIALIZED t3 system NULL NULL NULL NULL 1 +2 MATERIALIZED t2 system NULL NULL NULL NULL 1 +SELECT * +FROM t1 +WHERE (t1.d , t1.d) NOT IN ( +SELECT t3.d , t2.c +FROM t3 LEFT JOIN t2 ON t3.a = t2.a); +d +r +set @@optimizer_switch='materialization=off,in_to_exists=on'; +EXPLAIN SELECT * +FROM t1 +WHERE (t1.d , t1.d) NOT IN ( +SELECT t3.d , t2.c +FROM t3 LEFT JOIN t2 ON t3.a = t2.a); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +2 DEPENDENT SUBQUERY t3 system NULL NULL NULL NULL 1 +2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 1 +SELECT * +FROM t1 +WHERE (t1.d , t1.d) NOT IN ( +SELECT t3.d , t2.c +FROM t3 LEFT JOIN t2 ON t3.a = t2.a); +d +r +drop table t1, t2, t3; +# +# LP BUG#809266 Diverging results with partial_match_rowid_merge=on +# +CREATE TABLE t1 (c int) ; +INSERT INTO t1 VALUES (0),(0); +CREATE TABLE t2 (a int, b int) ; +INSERT INTO t2 VALUES (6,3), (9,NULL); +set @@optimizer_switch='materialization=on,partial_match_rowid_merge=on,partial_match_table_scan=off,in_to_exists=off'; +EXPLAIN +SELECT * FROM t1 WHERE (6, 4 ) NOT IN (SELECT b, a 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 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 +SELECT * FROM t1 WHERE (6, 4 ) NOT IN (SELECT b, a FROM t2); +c +0 +0 +EXPLAIN +SELECT * FROM t1 WHERE (6, 4 ) NOT IN (SELECT a, b 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 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 +SELECT * FROM t1 WHERE (6, 4 ) NOT IN (SELECT a, b FROM t2); +c +0 +0 +set @@optimizer_switch='materialization=off,in_to_exists=on'; +EXPLAIN +SELECT * FROM t1 WHERE (6, 4 ) NOT IN (SELECT b, a 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 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where +SELECT * FROM t1 WHERE (6, 4 ) NOT IN (SELECT b, a FROM t2); +c +0 +0 +EXPLAIN +SELECT * FROM t1 WHERE (6, 4 ) NOT IN (SELECT a, b 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 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where +SELECT * FROM t1 WHERE (6, 4 ) NOT IN (SELECT a, b FROM t2); +c +0 +0 +drop table t1, t2; +# +# LP BUG#856152 Wrong result with NOT IN subquery and partial_match_rowid_merge +# +CREATE TABLE t1 ( f1 integer NOT NULL , f2 integer) ; +INSERT INTO t1 VALUES (3,3),(48,NULL),(49,1); +CREATE TABLE t2 ( f3 int) ; +INSERT INTO t2 VALUES (5); +set @@optimizer_switch='in_to_exists=off,materialization=on,partial_match_rowid_merge=on,partial_match_table_scan=off'; +EXPLAIN SELECT * FROM t2 WHERE ( 3 , 1 ) NOT IN ( SELECT f1 , f2 FROM t1 ); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 system NULL NULL NULL NULL 1 +2 MATERIALIZED t1 ALL NULL NULL NULL NULL 3 +SELECT * FROM t2 WHERE ( 3 , 1 ) NOT IN ( SELECT f1 , f2 FROM t1 ); +f3 +5 +set @@optimizer_switch='in_to_exists=on,materialization=off'; +EXPLAIN SELECT * FROM t2 WHERE ( 3 , 1 ) NOT IN ( SELECT f1 , f2 FROM t1 ); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 system NULL NULL NULL NULL 1 +2 SUBQUERY t1 ALL NULL NULL NULL NULL 3 Using where +SELECT * FROM t2 WHERE ( 3 , 1 ) NOT IN ( SELECT f1 , f2 FROM t1 ); +f3 +5 +drop table t1, t2; +# +# LP BUG#869036 Wrong result with in_to_exists=ON and NOT IN +# +create table outer_sq (f1 char(1), f2 char(1)); +insert into outer_sq values (NULL, 'c'), ('g', 'c'); +create table inner_sq (f3 char(1), f4 char(1)); +insert into inner_sq values(null, 'i'), ('v', null); +All three strategies below must produce the same result. +set @@optimizer_switch='in_to_exists=off,materialization=on,partial_match_rowid_merge=on,partial_match_table_scan=off'; +SELECT outer_sq.*, (f1, f2) IN (select * from inner_sq) from outer_sq; +f1 f2 (f1, f2) IN (select * from inner_sq) +NULL c NULL +g c 0 +SELECT * from outer_sq where (f1, f2) NOT IN (select * from inner_sq); +f1 f2 +g c +set @@optimizer_switch='in_to_exists=off,materialization=on,partial_match_rowid_merge=off,partial_match_table_scan=on'; +SELECT outer_sq.*, (f1, f2) IN (select * from inner_sq) from outer_sq; +f1 f2 (f1, f2) IN (select * from inner_sq) +NULL c NULL +g c 0 +SELECT * from outer_sq where (f1, f2) NOT IN (select * from inner_sq); +f1 f2 +g c +set @@optimizer_switch='in_to_exists=on,materialization=off'; +SELECT outer_sq.*, (f1, f2) IN (select * from inner_sq) from outer_sq; +f1 f2 (f1, f2) IN (select * from inner_sq) +NULL c NULL +g c 0 +SELECT * from outer_sq where (f1, f2) NOT IN (select * from inner_sq); +f1 f2 +g c +drop table outer_sq, inner_sq; +# +# LP BUG#893486 Wrong result with partial_match_rowid_merge , NOT IN , NULLs +# +CREATE TABLE t1 (a int, b int); +INSERT INTO t1 VALUES (0,NULL),(2,NULL); +CREATE TABLE t2 (c int, d int); +INSERT INTO t2 VALUES (2,3),(4,5),(6, NULL); +set @@optimizer_switch='materialization=on,partial_match_rowid_merge=on,partial_match_table_scan=off,in_to_exists=off'; +EXPLAIN SELECT * FROM t1 WHERE (a, b) NOT IN (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 Using where +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 +SELECT * FROM t1 WHERE (a, b) NOT IN (SELECT c, d FROM t2); +a b +0 NULL +SELECT a, b, (a, b) NOT IN (SELECT c, d FROM t2) subq_res FROM t1; +a b subq_res +0 NULL 1 +2 NULL NULL +EXPLAIN SELECT * FROM t1 WHERE (a, b) NOT IN (SELECT c, d FROM t2 WHERE d is not NULL); +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 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where +SELECT * FROM t1 WHERE (a, b) NOT IN (SELECT c, d FROM t2 WHERE d is not NULL); +a b +0 NULL +SELECT a, b, (a, b) NOT IN (SELECT c, d FROM t2 WHERE d is not NULL) subq_res FROM t1; +a b subq_res +0 NULL 1 +2 NULL NULL +drop table t1,t2; +set @@optimizer_switch=@save_optimizer_switch; |