Fix bug lp:858038

Analysis:
The cause of the bug was the changed meaning of
subselect_partial_match_engine::has_covering_null_row.
Previously it meant that there is row with NULLs in
all nullable fields of the materialized subquery table.
Later it was changed to mean a row with NULLs in all
fields of this table.

At the same time there was a shortcut in
subselect_rowid_merge_engine::partial_match() that
detected a special case where:
- there is no match in any of the columns with NULLs, and
- there is no NULL-only row that covers all columns with
  NULLs.
With the change in the meaning of has_covering_null_row,
the condition that detected this special case was incomplete.
This resulted in an incorrect FALSE, when the result was a
partial match.

Solution:
Expand the condition that detected the special case with the
correct test for the existence of a row with NULL values in
all columns that contain NULLs (a kind of parially covering
NULL-row).

3 files changed, 58 insertions, 1 deletions

diff --git a/mysql-test/r/subselect_mat_cost_bugs.result b/mysql-test/r/subselect_mat_cost_bugs.result
index 79f53c1ff86..a19f69bd04d 100644
--- a/mysql-test/r/subselect_mat_cost_bugs.result
+++ b/mysql-test/r/subselect_mat_cost_bugs.result
@@ -354,3 +354,27 @@ b
 0
 set @@optimizer_switch='default';
 drop table t3, t4, t5;
+#
+# LP BUG#858038 The result of a query with NOT IN subquery depends on the state of the optimizer switch
+#
+create table t1 (c1 char(2) not null, c2 char(2));
+create table t2 (c3 char(2),          c4 char(2));
+insert into t1 values ('a1', 'b1');
+insert into t1 values ('a2', 'b2');
+insert into t2 values ('x1', 'y1');
+insert into t2 values ('a2', null);
+set @@optimizer_switch='in_to_exists=off,materialization=on,partial_match_rowid_merge=off,partial_match_table_scan=on';
+explain select * from t1 where c1 = 'a2' and (c1, c2) 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	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	
+select * from t1 where c1 = 'a2' and (c1, c2) not in (select * from t2);
+c1	c2
+set @@optimizer_switch='in_to_exists=off,materialization=on,partial_match_rowid_merge=on,partial_match_table_scan=off';
+explain select * from t1 where c1 = 'a2' and (c1, c2) 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	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	
+select * from t1 where c1 = 'a2' and (c1, c2) not in (select * from t2);
+c1	c2
+drop table t1, t2;
diff --git a/mysql-test/t/subselect_mat_cost_bugs.test b/mysql-test/t/subselect_mat_cost_bugs.test
index dbff9594309..eeb08a8abe7 100644
--- a/mysql-test/t/subselect_mat_cost_bugs.test
+++ b/mysql-test/t/subselect_mat_cost_bugs.test
@@ -385,3 +385,26 @@ WHERE t3.b > ALL (
 
 set @@optimizer_switch='default';
 drop table t3, t4, t5;
+
+--echo #
+--echo # LP BUG#858038 The result of a query with NOT IN subquery depends on the state of the optimizer switch
+--echo #
+
+create table t1 (c1 char(2) not null, c2 char(2));
+create table t2 (c3 char(2),          c4 char(2));
+
+insert into t1 values ('a1', 'b1');
+insert into t1 values ('a2', 'b2');
+
+insert into t2 values ('x1', 'y1');
+insert into t2 values ('a2', null);
+
+set @@optimizer_switch='in_to_exists=off,materialization=on,partial_match_rowid_merge=off,partial_match_table_scan=on';
+explain select * from t1 where c1 = 'a2' and (c1, c2) not in (select * from t2);
+select * from t1 where c1 = 'a2' and (c1, c2) not in (select * from t2);
+
+set @@optimizer_switch='in_to_exists=off,materialization=on,partial_match_rowid_merge=on,partial_match_table_scan=off';
+explain select * from t1 where c1 = 'a2' and (c1, c2) not in (select * from t2);
+select * from t1 where c1 = 'a2' and (c1, c2) not in (select * from t2);
+
+drop table t1, t2;
diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index a79d035160c..324cdabf7f6 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -5533,6 +5533,8 @@ 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=
+    ((select_materialize_with_stats *) result)->get_max_nulls_in_row();
   bool res= FALSE;
 
   /* If there is a non-NULL key, it must be the first key in the keys array. */
@@ -5598,8 +5600,16 @@ bool subselect_rowid_merge_engine::partial_match()
     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.
   */
-  if (pq.elements - test(non_null_key) == 0)
+  if ((pq.elements == 1 && non_null_key &&
+       max_covering_null_row_len < merge_keys_count - 1) ||
+      pq.elements == 0)
   {
+    if (pq.elements == 0)
+    {
+      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);
+    }
     res= FALSE;
     goto end;
   }