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.

1 files changed, 22 insertions, 0 deletions

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;