BUG#24127: (a,b) IN (SELECT c,d ...) can produce wrong results if a and/or b are NULLs:

- Make the code produce correct result: use an array of triggers to turn on/off equalities for each
  compared column. Also turn on/off optimizations based on those equalities.
- Make EXPLAIN output show "Full scan on NULL key" for tables for which we switch between
  ref/unique_subquery/index_subquery and ALL access.
- index_subquery engine now has HAVING clause when it is needed, and it is
  displayed in EXPLAIN EXTENDED
- Fix incorrect presense of "Using index" for index/unique-based subqueries (BUG#22930)
// bk trigger note: this commit refers to BUG#24127


mysql-test/r/ndb_subquery.result:
  BUG#24127: wrong result for (null,not-null) IN (SELECT a,b ...)
  - Updated test results (checked)
mysql-test/r/subselect.result:
  BUG#24127: wrong result for (null,not-null) IN (SELECT a,b ...)
  - Updated test results (checked)
mysql-test/r/subselect2.result:
  BUG#24127: wrong result for (null,not-null) IN (SELECT a,b ...)
  - Updated test results (checked)
mysql-test/r/subselect3.result:
  BUG#24127: wrong result for (null,not-null) IN (SELECT a,b ...)
  - Testcases
mysql-test/t/subselect3.test:
  BUG#24127: wrong result for (null,not-null) IN (SELECT a,b ...)
  - Testcases
sql/item_cmpfunc.cc:
  BUG#24127: wrong result for (null,not-null) IN (SELECT a,b ...)
  - For row-based IN subqueries, use one flag per each column. Set the flags appropriately before
    running the subquery.
sql/item_cmpfunc.h:
  BUG#24127: wrong result for (null,not-null) IN (SELECT a,b ...)
   - Added Item_func_trig_cond::get_triv_var()
sql/item_subselect.cc:
  BUG#24127: wrong result for (null,not-null) IN (SELECT a,b ...)
  - Item_subselect::exec() and subselect_*_engine::exec() don't have parameter
    anymore - now Item_subselect owns the pushed down predicates guard flags.
  - A correct set of conditional predicates is now pushed into row-based IN 
    subquery.
  - select_indexsubquery_engine now has "HAVING clause" (needed for correct query
    results), and it is shown in EXPLAIN EXTENDED
sql/item_subselect.h:
  BUG#24127: wrong result for (null,not-null) IN (SELECT a,b ...)
  - Item_subselect::exec() and subselect_*_engine::exec() don't have parameter
    anymore - now Item_subselect owns the pushed down predicates guard flags.
  - A correct set of conditional predicates is now pushed into row-based IN 
    subquery.
  - select_indexsubquery_engine now has "HAVING clause" (needed for correct query
    results), and it is shown in EXPLAIN EXTENDED
sql/mysql_priv.h:
  BUG#24127: wrong result for (null,not-null) IN (SELECT a,b ...)
  - Added "in_having_cond" special Item name
sql/mysqld.cc:
  BUG#24127: wrong result for (null,not-null) IN (SELECT a,b ...)
  - Added "in_having_cond" special Item name
sql/sql_lex.h:
  BUG#24127: wrong result for (null,not-null) IN (SELECT a,b ...)
sql/sql_select.cc:
  BUG#24127: wrong result for (null,not-null) IN (SELECT a,b ...)
  - Make "ref" analyzer be able to work with conditional equalities
  - Fix subquery optimization code to match the changes in what kinds of 
    conditions are pushed down into subqueries 
  - Fix wrong EXPLAIN output in some queries with subquery (BUG#22390)
sql/sql_select.h:
  BUG#24127: wrong result for (null,not-null) IN (SELECT a,b ...)
  - Make "ref" analyzer be able to work with conditional equalities
  - Fix wrong EXPLAIN output in some queries with subquery (BUG#22390)

1 files changed, 281 insertions, 10 deletions

diff --git a/mysql-test/t/subselect3.test b/mysql-test/t/subselect3.test
index d10e8d1e469..7910a88c5d6 100644
--- a/mysql-test/t/subselect3.test
+++ b/mysql-test/t/subselect3.test
@@ -16,13 +16,14 @@ insert into t1 (oref, grp, ie) values
  (3, 1, 4),
  (3, 2, NULL);
 
-#  Ok, for  
+# Ok, for
 #   select max(ie) from t1 where oref=PARAM group by grp
 # we'll have:
-#  1  ->  (1, NULL)    matching + NULL
-#  2  ->  (3)          non-matching
-#  3  ->  (3, NULL)    non-matching + NULL
-#  4  ->  ()           nothing.
+# PARAM      subquery result  
+#   1   ->   {(1), (NULL)}    matching + NULL
+#   2   ->   {(3)}            non-matching
+#   3   ->   {(3), (NULL)}    non-matching + NULL
+#   4   ->   {}               empty set
 
 create table t2 (oref int, a int);
 insert into t2 values 
@@ -141,7 +142,7 @@ drop table t1, t2, t3;
 
 
 #
-# BUG#24085
+# BUG#24085: Wrong query result for "NULL IN (SELECT ... UNION SELECT ...)"
 #
 
 # case 1: NULL IN (SELECT not_null_val FROM ...) w/o HAVING/GROUP-BY/etc
@@ -172,11 +173,13 @@ create table t1 (oref int, grp int);
 insert into t1 (oref, grp) values
  (1, 1),
  (1, 1);
-#  Ok, for  
-#   select count(*) from t1 group by grp having grp=$PARAM$
+
+# Ok, for  
+#   select count(*) from t1 group by grp having grp=PARAM
 # we'll have:
-#  1  ->  (2)   
-#  2  ->  ()  -  nothing
+#  PARAM    subuqery result
+#    1  ->    {(2)}   
+#    2  ->    {} - empty set
 create table t2 (oref int, a int);
 insert into t2 values 
   (1, NULL),
@@ -202,3 +205,271 @@ t1 where t1.b = t2.b) Z from t2 ;
 select a,b, a in (select a from t1 where t1.b = t2.b) Z from t2 ;
 drop table t1, t2;
 
+
+#
+# BUG#24127: Incorrect results of row-based subqueries with NULLs on the left side.
+#
+create table t3 (a int);
+insert into t3 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t2 (a int, b int, oref int);
+insert into t2 values (NULL,1, 100), (NULL,2, 100);
+
+create table t1 (a int, b int, c int, key(a,b));
+insert into t1 select 2*A, 2*A, 100 from t3;
+
+# First test index subquery engine
+explain extended select a,b, oref, (a,b) in (select a,b from t1 where c=t2.oref) Z from t2;
+select a,b, oref, (a,b) in (select a,b from t1 where c=t2.oref) Z from t2;
+
+# Then check that we do turn off 'ref' scans in the subquery
+create table t4 (x int);
+insert into t4 select A.a + 10*B.a from t1 A, t1 B;
+explain extended 
+  select a,b, oref, 
+         (a,b) in (select a,b from t1,t4 where c=t2.oref) Z 
+  from t2;
+select a,b, oref, 
+       (a,b) in (select a,b from t1,t4 where c=t2.oref) Z 
+from t2;
+
+drop table t1,t2,t3,t4;
+
+# More tests for tricky multi-column cases, where some of pushed-down
+# equalities are used for index lookups and some arent.
+create table t1 (oref char(4), grp int, ie1 int, ie2 int);
+insert into t1 (oref, grp, ie1, ie2) values
+ ('aa', 10, 2, 1),
+ ('aa', 10, 1, 1),
+ ('aa', 20, 2, 1),
+ ('bb', 10, 3, 1),
+ ('cc', 10, 4, 2),
+ ('cc', 20, 3, 2),
+ 
+ ('ee', 10, 2, 1),
+ ('ee', 10, 1, 2),
+ 
+ ('ff', 20, 2, 2),
+ ('ff', 20, 1, 2);
+create table t2 (oref char(4), a int, b int);
+insert into t2 values 
+  ('ee', NULL, 1),
+  ('bb', 2, 1),
+  ('ff', 2, 2),
+  ('cc', 3, NULL),
+  ('bb', NULL, NULL),
+  ('aa', 1, 1),
+  ('dd', 1, NULL);
+alter table t1 add index idx(ie1,ie2);
+
+--cc 3 NULL NULL
+select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2 where a=3 and b is null ;
+insert into t2 values ('new1', 10,10);
+insert into t1 values ('new1', 1234, 10, NULL); 
+-- new1, 10, 10, NULL,
+select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2 where a=10 and b=10; 
+explain extended
+select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2 where a=10 and b=10; 
+drop table t1, t2;
+
+# Now test different column types:
+create table t1 (oref char(4), grp int, ie int);
+insert into t1 (oref, grp, ie) values
+ ('aa', 10, 2),
+ ('aa', 10, 1),
+ ('aa', 20, NULL),
+
+ ('bb', 10, 3),
+
+ ('cc', 10, 4),
+ ('cc', 20, NULL),
+ 
+ ('ee', 10, NULL),
+ ('ee', 10, NULL),
+ 
+ ('ff', 20, 2),
+ ('ff', 20, 1);
+
+create table t2 (oref char(4), a int);
+insert into t2 values 
+  ('ee', NULL),
+  ('bb', 2),
+  ('ff', 2),
+  ('cc', 3),
+  ('aa', 1),
+  ('dd', NULL),
+  ('bb', NULL);
+
+select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2;
+
+select oref, a from t2 where a in (select ie from t1 where oref=t2.oref); 
+
+select oref, a from t2 where a not in (select ie from t1 where oref=t2.oref);
+
+
+select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by grp) Z from t2;
+
+select oref, a from t2 where 
+  a in (select min(ie) from t1 where oref=t2.oref group by grp);
+  
+select oref, a from t2 where 
+  a not in (select min(ie) from t1 where oref=t2.oref group by grp);
+
+#
+update t1 set ie=3 where oref='ff' and ie=1;
+
+select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by
+grp) Z from t2;
+
+
+select oref, a from t2 where a in (select min(ie) from t1 where
+oref=t2.oref group by grp);
+
+select oref, a from t2 where a not in (select min(ie) from t1 where
+oref=t2.oref group by grp);
+
+select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by
+grp having min(ie) > 1) Z from t2;
+
+select oref, a from t2 where a in (select min(ie) from t1 where
+oref=t2.oref group by grp having min(ie) > 1);
+  
+select oref, a from t2 where a not in (select min(ie) from t1 where
+oref=t2.oref group by grp having min(ie) > 1);
+
+#
+alter table t1 add index idx(ie);
+
+explain select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2;
+
+select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2;
+
+select oref, a from t2 where a in (select ie from t1 where oref=t2.oref); 
+
+select oref, a from t2 where a not in (select ie from t1 where oref=t2.oref);
+
+
+alter table t1 drop index idx;
+alter table t1 add index idx(oref,ie);
+
+explain select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2;
+
+select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2;
+
+select oref, a from t2 where a in (select ie from t1 where oref=t2.oref); 
+
+select oref, a from t2 where a not in (select ie from t1 where oref=t2.oref);
+
+explain 
+select oref, a, 
+       a in (select min(ie) from t1 where oref=t2.oref 
+             group by grp having min(ie) > 1) Z 
+from t2;
+
+select oref, a, 
+       a in (select min(ie) from t1 where oref=t2.oref 
+             group by grp having min(ie) > 1) Z 
+from t2;
+
+select oref, a from t2 where a in (select min(ie) from t1 where oref=t2.oref 
+                                   group by grp having min(ie) > 1);
+  
+select oref, a from t2 where a not in (select min(ie) from t1 where oref=t2.oref 
+                                       group by grp having min(ie) > 1);
+
+drop table t1,t2;
+
+create table t1 (oref char(4), grp int, ie1 int, ie2 int);
+insert into t1 (oref, grp, ie1, ie2) values
+ ('aa', 10, 2, 1),
+ ('aa', 10, 1, 1),
+ ('aa', 20, 2, 1),
+
+ ('bb', 10, 3, 1),
+
+ ('cc', 10, 4, 2),
+ ('cc', 20, 3, 2),
+ 
+ ('ee', 10, 2, 1),
+ ('ee', 10, 1, 2),
+ 
+ ('ff', 20, 2, 2),
+ ('ff', 20, 1, 2);
+
+create table t2 (oref char(4), a int, b int);
+insert into t2 values 
+  ('ee', NULL, 1),
+  ('bb', 2, 1),
+  ('ff', 2, 2),
+  ('cc', 3, NULL),
+  ('bb', NULL, NULL),
+  ('aa', 1, 1),
+  ('dd', 1, NULL);
+
+select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2;
+
+select oref, a, b from t2 where (a,b) in (select ie1,ie2 from t1 where oref=t2.oref); 
+
+select oref, a, b from t2 where (a,b) not in (select ie1,ie2 from t1 where oref=t2.oref);
+
+select oref, a, b, 
+             (a,b) in (select min(ie1),max(ie2) from t1 
+                       where oref=t2.oref group by grp) Z 
+from t2;
+
+select oref, a, b from t2 where 
+  (a,b) in (select min(ie1), max(ie2) from t1 where oref=t2.oref group by grp);
+  
+select oref, a, b from t2 where
+  (a,b) not in (select min(ie1), max(ie2) from t1 where oref=t2.oref group by grp);
+
+alter table t1 add index idx(ie1,ie2);
+
+explain select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2;
+
+select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2;
+
+select oref, a, b from t2 where (a,b) in (select ie1,ie2 from t1 where oref=t2.oref); 
+
+select oref, a, b from t2 where (a,b) not in (select ie1,ie2 from t1 where oref=t2.oref);
+
+explain extended 
+select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2;
+
+drop table t1,t2;
+
+create table t1 (oref char(4), grp int, ie int primary key);
+insert into t1 (oref, grp, ie) values
+ ('aa', 10, 2),
+ ('aa', 10, 1),
+
+ ('bb', 10, 3),
+
+ ('cc', 10, 4),
+ ('cc', 20, 5),
+ ('cc', 10, 6);
+ 
+create table t2 (oref char(4), a int);
+insert into t2 values 
+  ('ee', NULL),
+  ('bb', 2),
+  ('cc', 5),
+  ('cc', 2),
+  ('cc', NULL),
+  ('aa', 1),
+  ('bb', NULL);
+
+explain select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2;
+
+select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2;
+
+select oref, a from t2 where a in (select ie from t1 where oref=t2.oref);
+
+select oref, a from t2 where a not in (select ie from t1 where oref=t2.oref);
+
+explain 
+select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by grp) Z from t2;
+
+select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by grp) Z from t2;
+
+drop table t1,t2;
+