item_cmpfunc.h:

  Fixed bugs #12101, #12102: wrong calculation of not_null_tables()
   for some expressions.
   The classes Item_func_between, Item_func_if, Item_func_in are modified.
   Item_func_between/in objects can represent now [NOT]BETWEEN/IN expressions.
   The class Item_func_opt_neg is added to factor out the functionality
   common for the modified classes Item_func_between and Item_func_in.
item_cmpfunc.cc:
  Fixed bugs #12101, #12102: wrong calculation of not_null_tables()
   for some expressions.
   Added Item_func_between::fix_fields(), Item_func_if::fix_fields(),
   Item_func_in::fix_fields(). They correct generic calculation of
   the not_null_tables attribute when it is needed.
   Modified Item_func_between::val_int(), Item_func_in::val_int().
opt_range.cc:
  Fixed bugs #12101, #12102: wrong calculation of not_null_tables()
   for some expressions.
   The function get_mm_tree() is modified. There cannot be NOT before
   BETWEEN/IN anymore. Rather Item_func_between/in objects can represent
   now [NOT]BETWEEN/IN expressions.
sql_yacc.yy:
  Fixed bugs #12101, #12102: wrong calculation of not_null_tables()
   for some expressions.
   Item_func_between/in objects can represent now [NOT]BETWEEN/IN expresions.
join_outer.result:
  Fixed some testcases results (bugs #12101, #12102)
join_outer.test:
  Added testcases for bugs #12101, #12102


mysql-test/t/join_outer.test:
  Added testcases for bugs #12101, #12102
mysql-test/r/join_outer.result:
  Fixed some testcases results (bugs #12101, #12102)
sql/sql_yacc.yy:
  Fixed bugs #12101, #12102: wrong calculation of not_null_tables()
   for some expressions.
   Item_func_between/in objects can represent now [NOT]BETWEEN/IN expresions.
sql/opt_range.cc:
  Fixed bugs #12101, #12102: wrong calculation of not_null_tables()
   for some expressions.
   The function get_mm_tree() is modified. There cannot be NOT before
   BETWEEN/IN anymore. Rather Item_func_between/in objects can represent
   now [NOT]BETWEEN/IN expressions.
sql/item_cmpfunc.cc:
  Fixed bugs #12101, #12102: wrong calculation of not_null_tables()
   for some expressions.
   Added Item_func_between::fix_fields(), Item_func_if::fix_fields(),
   Item_func_in::fix_fields(). They correct generic calculation of
   the not_null_tables attribute when it is needed.
   Modified Item_func_between::val_int(), Item_func_in::val_int().
sql/item_cmpfunc.h:
  Fixed bugs #12101, #12102: wrong calculation of not_null_tables()
   for some expressions.
   The classes Item_func_between, Item_func_if, Item_func_in are modified.
   Item_func_between/in objects can represent now [NOT]BETWEEN/IN expressions.
   The class Item_func_opt_neg is added to factor out the functionality
   common for the modified classes Item_func_between and Item_func_in.

1 files changed, 46 insertions, 0 deletions

diff --git a/mysql-test/t/join_outer.test b/mysql-test/t/join_outer.test
index fa2dd93c9ba..05cd2fb152e 100644
--- a/mysql-test/t/join_outer.test
+++ b/mysql-test/t/join_outer.test
@@ -625,4 +625,50 @@ select group_concat(t1.b,t2.c) from t1 inner join t2 using(a) group by t1.a;
 drop table t1, t2;
 set group_concat_max_len=default;
 
+#
+# Test for bugs
+# #12101: erroneously applied outer join elimination in case of WHERE NOT BETWEEN
+# #12102: erroneously missing outer join elimination in case of WHERE IN/IF
+#
+
+CREATE TABLE t1 (a int PRIMARY KEY, b int);
+CREATE TABLE t2 (a int PRIMARY KEY, b int);
+
+INSERT INTO t1 VALUES (1,2), (2,1), (3,2), (4,3), (5,6), (6,5), (7,8), (8,7), (9,10);
+INSERT INTO t2 VALUES (3,0), (4,1), (6,4), (7,5);
+
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t2.b <= t1.a AND t1.a <= t1.b;
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a BETWEEN t2.b AND t1.b;
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a NOT BETWEEN t2.b AND t1.b);
+
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t2.b > t1.a OR t1.a > t1.b;
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a NOT BETWEEN t2.b AND t1.b;
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a BETWEEN t2.b AND t1.b);
+
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a = t2.a OR t2.b > t1.a OR t1.a > t1.b;
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a != t2.a AND t1.a BETWEEN t2.b AND t1.b);
+
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a = t2.a AND (t2.b > t1.a OR t1.a > t1.b);
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a != t2.a OR t1.a BETWEEN t2.b AND t1.b);
+
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a = t2.a OR t1.a = t2.b;
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a IN(t2.a, t2.b);
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a NOT IN(t2.a, t2.b));
+
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a != t1.b AND t1.a != t2.b;
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a NOT IN(t1.b, t2.b);
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a IN(t1.b, t2.b));
+
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t2.a != t2.b OR (t1.a != t2.a AND t1.a != t2.b);
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t2.a = t2.b AND t1.a IN(t2.a, t2.b));
+
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t2.a != t2.b AND t1.a != t1.b AND t1.a != t2.b;
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t2.a = t2.b OR t1.a IN(t1.b, t2.b));
+
+EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a = t2.a OR t1.a = t2.b;
+EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a IN(t2.a, t2.b);
+EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a > IF(t1.a = t2.b-2, t2.b, t2.b-1);
+
+DROP TABLE t1,t2;
+
 # End of 4.1 tests