Fix bug lp:893486

Analysis:
The bug is a result of an incomplete fix for bug lp:869036.
That fix didn't take into account that there may be a case
when ther are no NULLs in the materialized subquery, however
all columns without NULLs may not be grouped in the only
non-null index. This is the case when the left subquery expression
has nullable columns.

Solution:
The patch handles two missing sub-cases of the case when there are
no value (non-null matches) for any outer expression, and there are
both NULLs and non-NUll values in the outer reference.
a) If the materialized subquery contains no NULLs there cannot be a
   partial match, because there are no NULLs in those columns where
   the outer reference has no NULLs.
b) If the materialized subquery contains NULLs, but there exists a
   column, such that its corresponding outer expression has no NULL,
   and this column also has no NULL. Then there cannot be a partial
   match either.

3 files changed, 67 insertions, 12 deletions

diff --git a/mysql-test/r/subselect_partial_match.result b/mysql-test/r/subselect_partial_match.result
index dddf5e2d8d6..d23f9885f44 100644
--- a/mysql-test/r/subselect_partial_match.result
+++ b/mysql-test/r/subselect_partial_match.result
@@ -948,4 +948,35 @@ 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	SUBQUERY	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	SUBQUERY	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;
diff --git a/mysql-test/t/subselect_partial_match.test b/mysql-test/t/subselect_partial_match.test
index be78360c76b..45386efd266 100644
--- a/mysql-test/t/subselect_partial_match.test
+++ b/mysql-test/t/subselect_partial_match.test
@@ -775,4 +775,26 @@ SELECT * from outer_sq where (f1, f2) NOT IN (select * from inner_sq);
 
 drop table outer_sq, inner_sq;
 
+--echo #
+--echo # LP BUG#893486 Wrong result with partial_match_rowid_merge , NOT IN , NULLs
+--echo #
+
+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);
+SELECT * FROM t1 WHERE (a, b) NOT IN (SELECT c, d FROM t2);
+SELECT a, b, (a, b) NOT IN (SELECT c, d FROM t2) subq_res FROM t1;
+
+EXPLAIN SELECT * FROM t1 WHERE (a, b) NOT IN (SELECT c, d FROM t2 WHERE d is not NULL);
+SELECT * FROM t1 WHERE (a, b) NOT IN (SELECT c, d FROM t2 WHERE d is not NULL);
+SELECT a, b, (a, b) NOT IN (SELECT c, d FROM t2 WHERE d is not NULL) subq_res FROM t1;
+
+drop table t1,t2;
+
 set @@optimizer_switch=@save_optimizer_switch;
diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index 18374000dff..16b23045cca 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -5542,6 +5542,8 @@ 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.
+  @retval TRUE  if such a row exists
+  @retval FALSE no complementing null row
 */
 
 bool subselect_rowid_merge_engine::
@@ -5549,34 +5551,34 @@ 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;
+  uint count_null_keys, i;
   Ordered_key *cur_key;
 
-  count_null_keys= keys_to_complement->n_bits -
-                   bitmap_bits_set(keys_to_complement);
-  if (count_null_keys == 1)
+  if (!count_columns_with_nulls)
   {
     /*
-      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.
+      If there are both NULLs and non-NUll values in the outer reference, and
+      the subquery contains no NULLs, a complementing NULL row cannot exist.
     */
-    return TRUE;
+    return FALSE;
   }
 
-  for (i= (non_null_key ? 1 : 0), j= 0; i < merge_keys_count; i++)
+  for (i= (non_null_key ? 1 : 0), count_null_keys= 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_null_count())
+    {
+      /* If there is column without NULLs, there cannot be a partial match. */
+      return FALSE;
+    }
     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();
+    null_bitmaps[count_null_keys++]= cur_key->get_null_key();
   }
-  DBUG_ASSERT(count_null_keys == j);
 
   if (lowest_max_row < highest_min_row)
   {