summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--include/my_bitmap.h4
-rw-r--r--mysql-test/r/subselect_partial_match.result610
-rw-r--r--mysql-test/t/subselect_partial_match.test435
-rw-r--r--mysys/my_bitmap.c90
-rw-r--r--sql/item_subselect.cc110
-rw-r--r--sql/item_subselect.h13
6 files changed, 1208 insertions, 54 deletions
diff --git a/include/my_bitmap.h b/include/my_bitmap.h
index 09a2ff3fe65..3d179f56e4c 100644
--- a/include/my_bitmap.h
+++ b/include/my_bitmap.h
@@ -56,6 +56,10 @@ extern my_bool bitmap_test_and_clear(MY_BITMAP *map, uint bitmap_bit);
extern my_bool bitmap_fast_test_and_set(MY_BITMAP *map, uint bitmap_bit);
extern my_bool bitmap_union_is_set_all(const MY_BITMAP *map1,
const MY_BITMAP *map2);
+extern my_bool bitmap_exists_intersection(const MY_BITMAP **bitmap_array,
+ uint bitmap_count,
+ uint start_bit, uint end_bit);
+
extern uint bitmap_set_next(MY_BITMAP *map);
extern uint bitmap_get_first(const MY_BITMAP *map);
extern uint bitmap_get_first_set(const MY_BITMAP *map);
diff --git a/mysql-test/r/subselect_partial_match.result b/mysql-test/r/subselect_partial_match.result
index 1b39381b42b..dddf5e2d8d6 100644
--- a/mysql-test/r/subselect_partial_match.result
+++ b/mysql-test/r/subselect_partial_match.result
@@ -3,11 +3,13 @@ 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 @@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');
@@ -26,9 +28,9 @@ 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');
@@ -55,17 +57,532 @@ 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 SUBQUERY 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 SUBQUERY 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 SUBQUERY 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 SUBQUERY 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 SUBQUERY 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 SUBQUERY 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 SUBQUERY 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 SUBQUERY 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 SUBQUERY 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 SUBQUERY 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 SUBQUERY 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 SUBQUERY 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 SUBQUERY 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 SUBQUERY 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 SUBQUERY 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 SUBQUERY 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');
@@ -98,9 +615,9 @@ 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);
@@ -149,9 +666,9 @@ 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');
@@ -174,9 +691,9 @@ 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');
@@ -198,6 +715,32 @@ 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.
-------------------------------
@@ -372,4 +915,37 @@ 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;
set @@optimizer_switch=@save_optimizer_switch;
diff --git a/mysql-test/t/subselect_partial_match.test b/mysql-test/t/subselect_partial_match.test
index cb25656e4fc..be78360c76b 100644
--- a/mysql-test/t/subselect_partial_match.test
+++ b/mysql-test/t/subselect_partial_match.test
@@ -10,12 +10,14 @@ set @save_optimizer_switch=@@optimizer_switch;
--echo -------------------------------
--echo Default for all tests.
-set @@optimizer_switch="materialization=on,in_to_exists=off,semijoin=off,subquery_cache=off";
+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";
---echo
+--echo -------------------------------------------------------------------------
--echo Schema requires partial matching, but data analysis discoveres there is
--echo no need. This is possible only if all outer columns are not NULL.
---echo
+--echo -------------------------------------------------------------------------
create table t1 (a1 char(8) not null, a2 char(8) not null);
create table t2 (b1 char(8), b2 char(8));
@@ -36,9 +38,9 @@ select a1, a2, (a1, a2) not in (select * from t2) as in_res from t1;
drop table t1, t2;
---echo
+--echo -------------------------------------------------------------------------
--echo NULLs in the outer columns, no NULLs in the suqbuery
---echo
+--echo -------------------------------------------------------------------------
create table t1 (a1 char(8), a2 char(8));
create table t2 (b1 char(8) not null, b2 char(8) not null);
@@ -63,21 +65,366 @@ select a1, a2, (a1, a2) in (select * from t2) as in_res from t1;
drop table t1, t2;
---echo
+--echo -------------------------------------------------------------------------
+--echo NULLs in the outer column, NULLs in the subquery, there is
+--echo no value match in any column, but there is a partial match
+--echo such that some of the matching NULLs are in the outer columns,
+--echo the other NULLs are in the inner columns.
+--echo -------------------------------------------------------------------------
+
+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;
+SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1;
+EXPLAIN
+SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2);
+SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2);
+
+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;
+SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1;
+EXPLAIN
+SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2);
+SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2);
+
+set @@optimizer_switch=@in_exists;
+EXPLAIN
+SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1;
+SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1;
+EXPLAIN
+SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2);
+SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2);
+
+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;
+SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1;
+EXPLAIN
+SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2);
+SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2);
+
+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;
+SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1;
+EXPLAIN
+SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2);
+SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2);
+
+set @@optimizer_switch=@in_exists;
+EXPLAIN
+SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1;
+SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1;
+EXPLAIN
+SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2);
+SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2);
+
+set @@optimizer_switch = @test_default_opt_switch;
+
+drop table t1, t2;
+
+--echo -------------------------------------------------------------------------
+--echo There is only one column in the subquery to complement the NULLs in the
+--echo outer reference. It is a NULL column, so a match is guaranteed.
+--echo -------------------------------------------------------------------------
+
+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;
+SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1;
+EXPLAIN
+SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2);
+SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2);
+
+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;
+SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1;
+EXPLAIN
+SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2);
+SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2);
+
+set @@optimizer_switch=@in_exists;
+EXPLAIN
+SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1;
+SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1;
+EXPLAIN
+SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2);
+SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2);
+
+set @@optimizer_switch = @test_default_opt_switch;
+
+drop table t1, t2;
+
+--echo -------------------------------------------------------------------------
+--echo The intersection of the NULL bitmaps is empty because the ranges
+--echo of NULL bits do not overlap.
+--echo -------------------------------------------------------------------------
+
+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;
+SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1;
+EXPLAIN
+SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2);
+SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2);
+
+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;
+SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1;
+EXPLAIN
+SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2);
+SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2);
+
+set @@optimizer_switch=@in_exists;
+EXPLAIN
+SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1;
+SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1;
+EXPLAIN
+SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2);
+SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2);
+
+drop table t1, t2;
+
+--echo -------------------------------------------------------------------------
+--echo The intersection of the NULL bitmaps is non-empty, and there is a
+--echo non-NULL column.
+--echo -------------------------------------------------------------------------
+
+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;
+SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1;
+EXPLAIN
+SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2);
+SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2);
+
+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;
+SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1;
+EXPLAIN
+SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2);
+SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2);
+
+set @@optimizer_switch=@in_exists;
+EXPLAIN
+SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1;
+SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1;
+EXPLAIN
+SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2);
+SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2);
+
+drop table t1, t2;
+
+--echo -------------------------------------------------------------------------
+--echo Value match in one row, but the NULL complement match in another.
+--echo The result must be false.
+--echo -------------------------------------------------------------------------
+
+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;
+SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1;
+EXPLAIN
+SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2);
+SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2);
+
+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;
+SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1;
+EXPLAIN
+SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2);
+SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2);
+
+set @@optimizer_switch=@in_exists;
+EXPLAIN
+SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1;
+SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1;
+EXPLAIN
+SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2);
+SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2);
+
+drop table t1, t2;
+
+--echo -------------------------------------------------------------------------
+--echo Test the intersection of larger number of rows with NULL, such that
+--echo the number is at the boundary 32. This test is based on the implementation
+--echo of MY_BITMAP which uses 32 bit words, and the intersection operation works
+--echo by intersecting the bitmap word by word.
+--echo -------------------------------------------------------------------------
+
+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);
+--echo 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;
+SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1;
+EXPLAIN
+SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2);
+SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2);
+
+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;
+SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1;
+EXPLAIN
+SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2);
+SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2);
+
+set @@optimizer_switch=@in_exists;
+EXPLAIN
+SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1;
+SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1;
+EXPLAIN
+SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2);
+SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2);
+
+--echo 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;
+SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1;
+EXPLAIN
+SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2);
+SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2);
+
+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;
+SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1;
+EXPLAIN
+SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2);
+SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2);
+
+set @@optimizer_switch=@in_exists;
+EXPLAIN
+SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1;
+SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1;
+EXPLAIN
+SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2);
+SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2);
+
+drop table t1, t2;
+
+
+--echo -------------------------------------------------------------------------
--echo All columns require partial matching (no non-null columns)
---echo
+--echo -------------------------------------------------------------------------
--echo TODO
---echo
+--echo -------------------------------------------------------------------------
--echo Both non-NULL columns and columns with NULLs
---echo
+--echo -------------------------------------------------------------------------
--echo TODO
---echo
+--echo -------------------------------------------------------------------------
--echo Covering NULL rows
---echo
+--echo -------------------------------------------------------------------------
create table t1 (a1 char(8), a2 char(8));
create table t2 (b1 char(8), b2 char(8));
@@ -110,9 +457,9 @@ select a1, a2, (a1, a2) in (select * from t2) as in_res from t1;
drop table t1, t2;
---echo
+--echo -------------------------------------------------------------------------
--echo Covering NULL columns
---echo
+--echo -------------------------------------------------------------------------
--echo this case affects only the rowid-merge algorithm
set @@optimizer_switch="partial_match_rowid_merge=on,partial_match_table_scan=off";
@@ -161,9 +508,9 @@ select *, (a1, a2, a3) in (select * from t2) as in_res from t1;
drop table t1, t2;
---echo
+--echo -------------------------------------------------------------------------
--echo Covering NULL row, and a NULL column
---echo
+--echo -------------------------------------------------------------------------
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));
@@ -188,9 +535,9 @@ select *, (a1, a2, a3) in (select * from t2) as in_res from t1;
drop table t1, t2;
---echo
+--echo -------------------------------------------------------------------------
--echo Covering NULL row, and covering NULL columns
---echo
+--echo -------------------------------------------------------------------------
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));
@@ -213,6 +560,34 @@ select *, (a1, a2, a3) in (select * from t2) as in_res from t1;
drop table t1, t2;
+--echo -------------------------------------------------------------------------
+--echo Small buffer for the rowid_merge partial match algorithm that forces
+--echo reverting to table scan partial match.
+--echo -------------------------------------------------------------------------
+
+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;
+SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1;
+
+drop table t1, t2;
+
+set @@rowid_merge_buff_size = @save_rowid_merge_buff_size;
+
--echo -------------------------------
--echo Part 2: Test cases for bugs.
@@ -374,4 +749,30 @@ SELECT * FROM t2 WHERE ( 3 , 1 ) NOT IN ( SELECT f1 , f2 FROM t1 );
drop table t1, t2;
+--echo #
+--echo # LP BUG#869036 Wrong result with in_to_exists=ON and NOT IN
+--echo #
+
+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);
+
+--echo 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;
+SELECT * from outer_sq where (f1, f2) NOT IN (select * from inner_sq);
+
+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;
+SELECT * from outer_sq where (f1, f2) NOT IN (select * from inner_sq);
+
+set @@optimizer_switch='in_to_exists=on,materialization=off';
+SELECT outer_sq.*, (f1, f2) IN (select * from inner_sq) from outer_sq;
+SELECT * from outer_sq where (f1, f2) NOT IN (select * from inner_sq);
+
+drop table outer_sq, inner_sq;
+
set @@optimizer_switch=@save_optimizer_switch;
diff --git a/mysys/my_bitmap.c b/mysys/my_bitmap.c
index 0e5421ec484..260289cf041 100644
--- a/mysys/my_bitmap.c
+++ b/mysys/my_bitmap.c
@@ -100,6 +100,44 @@ void create_last_word_mask(MY_BITMAP *map)
}
+static inline my_bitmap_map last_word_mask(uint bit)
+{
+ my_bitmap_map last_word_mask;
+ uint n_bits= bit + 1;
+ unsigned char const mask= invers_last_byte_mask(n_bits);
+
+ /*
+ The first bytes are to be set to zero since they represent real bits
+ in the bitvector. The last bytes are set to 0xFF since they represent
+ bytes not used by the bitvector. Finally the last byte contains bits
+ as set by the mask above.
+ */
+ unsigned char *ptr= (unsigned char*)&last_word_mask;
+
+ switch ((n_bits + 7)/8 & 3) {
+ case 1:
+ last_word_mask= ~0U;
+ ptr[0]= mask;
+ break;
+ case 2:
+ last_word_mask= ~0U;
+ ptr[0]= 0;
+ ptr[1]= mask;
+ break;
+ case 3:
+ last_word_mask= 0U;
+ ptr[2]= mask;
+ ptr[3]= 0xFFU;
+ break;
+ case 0:
+ last_word_mask= 0U;
+ ptr[3]= mask;
+ break;
+ }
+ return last_word_mask;
+}
+
+
static inline void bitmap_lock(MY_BITMAP *map __attribute__((unused)))
{
#ifdef THREAD
@@ -410,6 +448,58 @@ void bitmap_intersect(MY_BITMAP *map, const MY_BITMAP *map2)
}
}
+
+/*
+ Check if there is some bit index between start_bit and end_bit, such that
+ this is bit is set for all bitmaps in bitmap_list.
+
+ SYNOPSIS
+ bitmap_exists_intersection()
+ bitmpap_array [in] a set of MY_BITMAPs
+ bitmap_count [in] number of elements in bitmpap_array
+ start_bit [in] beginning (inclusive) of the range of bits to search
+ end_bit [in] end (inclusive) of the range of bits to search, must be
+ no bigger than the bits of the shortest bitmap.
+
+ NOTES
+ This function assumes that for at least one of the bitmaps in bitmap_array all
+ bits outside the range [start_bit, end_bit] are 0. As a result is not
+ necessary to take care of the bits outside the range [start_bit, end_bit].
+
+ RETURN
+ TRUE if an intersecion exists
+ FALSE no intersection
+*/
+
+my_bool bitmap_exists_intersection(const MY_BITMAP **bitmap_array,
+ uint bitmap_count,
+ uint start_bit, uint end_bit)
+{
+ uint i, j, start_idx, end_idx;
+ my_bitmap_map cur_res;
+
+ DBUG_ASSERT(bitmap_count && end_bit >= start_bit);
+ for (j= 0; j < bitmap_count; j++)
+ DBUG_ASSERT(end_bit < bitmap_array[j]->n_bits);
+
+ start_idx= start_bit/8/sizeof(my_bitmap_map);
+ end_idx= end_bit/8/sizeof(my_bitmap_map);
+
+ for (i= start_idx; i < end_idx; i++)
+ {
+ cur_res= ~0;
+ for (j= 0; cur_res && j < bitmap_count; j++)
+ cur_res &= bitmap_array[j]->bitmap[i];
+ if (cur_res)
+ return TRUE;
+ }
+ cur_res= ~last_word_mask(end_bit);
+ for (j= 0; cur_res && j < bitmap_count; j++)
+ cur_res &= bitmap_array[j]->bitmap[end_idx];
+ return cur_res != 0;
+}
+
+
/* True if union of bitmaps have all bits set */
my_bool bitmap_union_is_set_all(const MY_BITMAP *map1, const MY_BITMAP *map2)
diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index 71408528903..18374000dff 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -4849,14 +4849,14 @@ bool Ordered_key::init(MY_BITMAP *columns_to_index)
Item_func_lt *fn_less_than;
key_column_count= bitmap_bits_set(columns_to_index);
-
- // TIMOUR: check for mem allocation err, revert to scan
-
key_columns= (Item_field**) thd->alloc(key_column_count *
sizeof(Item_field*));
compare_pred= (Item_func_lt**) thd->alloc(key_column_count *
sizeof(Item_func_lt*));
+ if (!key_columns || !compare_pred)
+ return TRUE; /* Revert to table scan partial match. */
+
for (uint i= 0; i < columns_to_index->n_bits; i++)
{
if (!bitmap_is_set(columns_to_index, i))
@@ -5316,10 +5316,13 @@ subselect_rowid_merge_engine::init(MY_BITMAP *non_null_key_parts,
merge_keys_count == 1 && non_null_key_parts));
/*
Allocate buffers to hold the merged keys and the mapping between rowids and
- row numbers.
+ row numbers. All small buffers are allocated in the runtime memroot. Big
+ buffers are allocated from the OS via malloc.
*/
if (!(merge_keys= (Ordered_key**) thd->alloc(merge_keys_count *
sizeof(Ordered_key*))) ||
+ !(null_bitmaps= (MY_BITMAP**) thd->alloc(merge_keys_count *
+ sizeof(MY_BITMAP*))) ||
!(row_num_to_rowid= (uchar*) my_malloc((size_t)(row_count * rowid_length),
MYF(MY_WME))))
return TRUE;
@@ -5537,6 +5540,56 @@ bool subselect_rowid_merge_engine::test_null_row(rownum_t row_num)
}
+/**
+ Test if a subset of NULL-able columns contains a row of NULLs.
+*/
+
+bool subselect_rowid_merge_engine::
+exists_complementing_null_row(MY_BITMAP *keys_to_complement)
+{
+ rownum_t highest_min_row= 0;
+ rownum_t lowest_max_row= UINT_MAX;
+ uint count_null_keys, i, j;
+ Ordered_key *cur_key;
+
+ count_null_keys= keys_to_complement->n_bits -
+ bitmap_bits_set(keys_to_complement);
+ if (count_null_keys == 1)
+ {
+ /*
+ The caller guarantees that the complement to keys_to_complement
+ contains only columns with NULLs. Therefore if there is only one column,
+ it is guaranteed to contain NULLs.
+ */
+ return TRUE;
+ }
+
+ for (i= (non_null_key ? 1 : 0), j= 0; i < merge_keys_count; i++)
+ {
+ cur_key= merge_keys[i];
+ if (bitmap_is_set(keys_to_complement, cur_key->get_keyid()))
+ continue;
+ DBUG_ASSERT(cur_key->get_null_count());
+ if (cur_key->get_min_null_row() > highest_min_row)
+ highest_min_row= cur_key->get_min_null_row();
+ if (cur_key->get_max_null_row() < lowest_max_row)
+ lowest_max_row= cur_key->get_max_null_row();
+ null_bitmaps[j++]= cur_key->get_null_key();
+ }
+ DBUG_ASSERT(count_null_keys == j);
+
+ if (lowest_max_row < highest_min_row)
+ {
+ /* The intersection of NULL rows is empty. */
+ return FALSE;
+ }
+
+ return bitmap_exists_intersection((const MY_BITMAP**) null_bitmaps,
+ count_null_keys,
+ highest_min_row, lowest_max_row);
+}
+
+
/*
@retval TRUE there is a partial match (UNKNOWN)
@retval FALSE there is no match at all (FALSE)
@@ -5549,7 +5602,7 @@ bool subselect_rowid_merge_engine::partial_match()
Ordered_key *cur_key;
rownum_t cur_row_num;
uint count_nulls_in_search_key= 0;
- uint max_covering_null_row_len=
+ uint max_null_in_any_row=
((select_materialize_with_stats *) result)->get_max_nulls_in_row();
bool res= FALSE;
@@ -5602,29 +5655,52 @@ bool subselect_rowid_merge_engine::partial_match()
/*
If the outer reference consists of only NULLs, or if it has NULLs in all
- nullable columns, the result is UNKNOWN.
+ nullable columns (above we guarantee there is a match for the non-null
+ coumns), the result is UNKNOWN.
*/
- if (count_nulls_in_search_key ==
- ((Item_in_subselect *) item)->left_expr->cols() -
- (non_null_key ? non_null_key->get_column_count() : 0))
+ if (count_nulls_in_search_key == merge_keys_count - test(non_null_key))
{
res= TRUE;
goto end;
}
/*
+ If the outer row has NULLs in some columns, and
+ there is no match for any of the remaining columns, and
+ there is a subquery row with NULLs in all unmatched columns,
+ then there is a partial match, otherwise the result is FALSE.
+ */
+ if (count_nulls_in_search_key && !pq.elements)
+ {
+ DBUG_ASSERT(!non_null_key);
+ /*
+ Check if the intersection of all NULL bitmaps of all keys that
+ are not in matching_outer_cols is non-empty.
+ */
+ res= exists_complementing_null_row(&matching_outer_cols);
+ goto end;
+ }
+
+ /*
If there is no NULL (sub)row that covers all NULL columns, and there is no
- single match for any of the NULL columns, the result is FALSE.
+ match for any of the NULL columns, the result is FALSE. Notice that if there
+ is a non-null key, and there is only one matching key, the non-null key is
+ the matching key. This is so, because this method returns FALSE if the
+ non-null key doesn't have a match.
*/
- if ((pq.elements == 1 && non_null_key &&
- max_covering_null_row_len < merge_keys_count - 1) ||
- pq.elements == 0)
+ if (!count_nulls_in_search_key &&
+ (!pq.elements ||
+ (pq.elements == 1 && non_null_key &&
+ max_null_in_any_row < merge_keys_count-1)))
{
- if (pq.elements == 0)
+ if (!pq.elements)
{
- DBUG_ASSERT(!non_null_key); /* Must follow from the logic of this method */
- /* This case must be handled by subselect_partial_match_engine::exec() */
- DBUG_ASSERT(max_covering_null_row_len != tmp_table->s->fields);
+ DBUG_ASSERT(!non_null_key);
+ /*
+ The case of a covering null row is handled by
+ subselect_partial_match_engine::exec()
+ */
+ DBUG_ASSERT(max_null_in_any_row != tmp_table->s->fields);
}
res= FALSE;
goto end;
diff --git a/sql/item_subselect.h b/sql/item_subselect.h
index 2012306c0f7..28ce0061729 100644
--- a/sql/item_subselect.h
+++ b/sql/item_subselect.h
@@ -999,7 +999,7 @@ protected:
/*
- Distinguish the type od (0-based) row numbers from the type of the index into
+ Distinguish the type of (0-based) row numbers from the type of the index into
an array of row numbers.
*/
typedef ha_rows rownum_t;
@@ -1075,9 +1075,9 @@ protected:
/* Count of NULLs per column. */
ha_rows null_count;
/* The row number that contains the first NULL in a column. */
- ha_rows min_null_row;
+ rownum_t min_null_row;
/* The row number that contains the last NULL in a column. */
- ha_rows max_null_row;
+ rownum_t max_null_row;
protected:
bool alloc_keys_buffers();
@@ -1110,6 +1110,10 @@ public:
DBUG_ASSERT(i < key_column_count);
return key_columns[i]->field->field_index;
}
+ rownum_t get_min_null_row() { return min_null_row; }
+ rownum_t get_max_null_row() { return max_null_row; }
+ MY_BITMAP * get_null_key() { return &null_key; }
+ ha_rows get_null_count() { return null_count; }
/*
Get the search key element that corresponds to the i-th key part of this
index.
@@ -1280,6 +1284,8 @@ protected:
Ordered_key **merge_keys;
/* The number of elements in merge_keys. */
uint merge_keys_count;
+ /* The NULL bitmaps of merge keys.*/
+ MY_BITMAP **null_bitmaps;
/*
An index on all non-NULL columns of 'tmp_table'. The index has the
logical form: <[v_i1 | ... | v_ik], rownum>. It allows to find the row
@@ -1305,6 +1311,7 @@ protected:
static int cmp_keys_by_cur_rownum(void *arg, uchar *k1, uchar *k2);
bool test_null_row(rownum_t row_num);
+ bool exists_complementing_null_row(MY_BITMAP *keys_to_complement);
bool partial_match();
public:
subselect_rowid_merge_engine(THD *thd_arg,
View Lorry Controller Status