MySQL Lists: internals: bk commit into 4.1 tree (aivanov:1.2390)

List:	Internals	« Previous Message Next Message »
From:	Alex Ivanov	Date:	August 25 2005 10:56pm
Subject:	bk commit into 4.1 tree (aivanov:1.2390)
View as plain text
Below is the list of changes that have just been committed into a local
4.1 repository of alexi. When alexi does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet
  1.2390 05/08/26 00:56:14 aivanov@stripped +6 -0
  join_outer.test:
    Added testcases for bugs #12101, #12102
  join_outer.result:
    Fixed some testcases results (bugs #12101, #12102)
  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 expressions.
  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.
  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_if::fix_fields(). The correct generic calculation of the
     not_null_tables attribute when it is needed.
     Modified Item_func_between::val_int(), Item_func_in::val_int().
  item_cmpfunc.h:
    Fixed bugs #12101, 12102: wrong calculation of not_null_tables()
     for some expressions.
     The classes Item_func_between, Item_func_in, and Item_func_if are modified.
     Item_func_between/in objects can represent now [NOT]BETWEEN/IN expressions.
     The class Item_opt_neg is added to factor out the functionality common
     for the modified classes Item_func_between and Item_func_in.

  mysql-test/t/join_outer.test
    1.20 05/08/26 00:50:44 aivanov@stripped +46 -0
    Added testcases for bugs #12101, #12102

  mysql-test/r/join_outer.result
    1.35 05/08/26 00:49:49 aivanov@stripped +133 -0
    Fixed some testcases results (bugs #12101, #12102)

  sql/sql_yacc.yy
    1.391 05/08/26 00:47:24 aivanov@stripped +27 -5
    Fixed bugs #12101, #12102: wrong calculation of not_null_tables()
     for some expressions.
     Item_func_between/in objects can represent now [NOT]Between/IN expressions.

  sql/opt_range.cc
    1.142 05/08/26 00:44:08 aivanov@stripped +3 -2
    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
    1.193 05/08/26 00:39:47 aivanov@stripped +150 -8
    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_if::fix_fields(). The 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
    1.109 05/08/26 00:35:35 aivanov@stripped +38 -17
    Fixed bugs #12101, 12102: wrong calculation of not_null_tables()
     for some expressions.
     The classes Item_func_between, Item_func_in, and Item_func_if are modified.
     Item_func_between/in objects can represent now [NOT]BETWEEN/IN expressions.
     The class Item_opt_neg is added to factor out the functionality common
     for the modified classes Item_func_between and Item_func_in.

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	aivanov
# Host:	mysql.creware.com
# Root:	/home/alexi/dev/mysql-4.1-0

--- 1.192/sql/item_cmpfunc.cc	2005-08-09 03:46:50 +04:00
+++ 1.193/sql/item_cmpfunc.cc	2005-08-26 00:39:47 +04:00
@@ -820,6 +820,54 @@
   return i-1;
 }
 
+
+/*
+  Perform context analysis of a BETWEEN item tree
+
+  SYNOPSIS:
+    fix_fields()
+    thd     reference to the global context of the query thread
+    tables  list of all open tables involved in the query
+    ref     pointer to Item* variable where pointer to resulting "fixed"
+            item is to be assigned
+
+  DESCRIPTION
+    This function performs context analysis (name resolution) and calculates
+    various attributes of the item tree with Item_func_between as its root.
+    The function saves in ref the pointer to the item or to a newly created
+    item that is considered as a replacement for the original one.
+
+  NOTES
+    Let T0(e)/T1(e) be the value of not_null_tables(e) when e is used on
+    a predicate/function level. Then it's easy to show that:
+      T0(e BETWEEN e1 AND e2)     = union(T1(e),T1(e1),T1(e2))
+      T1(e BETWEEN e1 AND e2)     = union(T1(e),intersection(T1(e1),T1(e2)))
+      T0(e NOT BETWEEN e1 AND e2) = union(T1(e),intersection(T1(e1),T1(e2)))
+      T1(e NOT BETWEEN e1 AND e2) = union(T1(e),intersection(T1(e1),T1(e2)))
+
+  RETURN
+    0   ok
+    1   got error
+*/
+
+bool
+Item_func_between::fix_fields(THD *thd, struct st_table_list *tables, Item **ref)
+{
+  if (Item_func_opt_neg::fix_fields(thd, tables, ref))
+    return 1;
+
+  /* not_null_tables_cache == union(T1(e),T1(e1),T1(e2)) */
+  if (pred_level && !negated)
+    return 0;
+
+  /* not_null_tables_cache == union(T1(e), intersect(T1(e1),T1(e2))) */
+  not_null_tables_cache= args[0]->not_null_tables() |
+    (args[1]->not_null_tables() & args[2]->not_null_tables());
+
+  return 0;
+}
+
+
 void Item_func_between::fix_length_and_dec()
 {
    max_length= 1;
@@ -871,8 +919,9 @@
     a=args[1]->val_str(&value1);
     b=args[2]->val_str(&value2);
     if (!args[1]->null_value && !args[2]->null_value)
-      return (sortcmp(value,a,cmp_collation.collation) >= 0 && 
-	      sortcmp(value,b,cmp_collation.collation) <= 0) ? 1 : 0;
+       return (longlong) ((sortcmp(value,a,cmp_collation.collation) >= 0 &&
+                           sortcmp(value,b,cmp_collation.collation) <= 0) !=
+                          negated);
     if (args[1]->null_value && args[2]->null_value)
       null_value=1;
     else if (args[1]->null_value)
@@ -894,7 +943,7 @@
     a=args[1]->val_int();
     b=args[2]->val_int();
     if (!args[1]->null_value && !args[2]->null_value)
-      return (value >= a && value <= b) ? 1 : 0;
+      return (longlong) ((value >= a && value <= b) != negated);
     if (args[1]->null_value && args[2]->null_value)
       null_value=1;
     else if (args[1]->null_value)
@@ -914,7 +963,7 @@
     a=args[1]->val();
     b=args[2]->val();
     if (!args[1]->null_value && !args[2]->null_value)
-      return (value >= a && value <= b) ? 1 : 0;
+      return (longlong) ((value >= a && value <= b) != negated);
     if (args[1]->null_value && args[2]->null_value)
       null_value=1;
     else if (args[1]->null_value)
@@ -926,7 +975,7 @@
       null_value= value >= a;
     }
   }
-  return 0;
+  return (longlong) (!null_value && negated);
 }
 
 
@@ -1019,6 +1068,49 @@
 }
 
 
+/*
+  Perform context analysis of an IF item tree
+
+  SYNOPSIS:
+    fix_fields()
+    thd     reference to the global context of the query thread
+    tables  list of all open tables involved in the query
+    ref     pointer to Item* variable where pointer to resulting "fixed"
+            item is to be assigned
+
+  DESCRIPTION
+    This function performs context analysis (name resolution) and calculates
+    various attributes of the item tree with Item_func_if as its root.
+    The function saves in ref the pointer to the item or to a newly created
+    item that is considered as a replacement for the original one.
+
+  NOTES
+    Let T0(e)/T1(e) be the value of not_null_tables(e) when e is used on
+    a predicate/function level. Then it's easy to show that:
+      T0(IF(e,e1,e2)  = T1(IF(e,e1,e2))
+      T1(IF(e,e1,e2)) = intersection(T1(e1),T1(e2))
+
+  RETURN
+    0   ok
+    1   got error
+*/
+
+bool
+Item_func_if::fix_fields(THD *thd, struct st_table_list *tlist, Item **ref)
+{
+  DBUG_ASSERT(fixed == 0);
+  args[0]->top_level_item();
+
+  if (Item_func::fix_fields(thd, tlist, ref))
+    return 1;
+
+  not_null_tables_cache= (args[1]->not_null_tables()
+                        & args[2]->not_null_tables());
+
+  return 0;
+}
+
+
 void
 Item_func_if::fix_length_and_dec()
 {
@@ -1750,6 +1842,56 @@
 }
 
 
+/*
+  Perform context analysis of an IN item tree
+
+  SYNOPSIS:
+    fix_fields()
+    thd     reference to the global context of the query thread
+    tables  list of all open tables involved in the query
+    ref     pointer to Item* variable where pointer to resulting "fixed"
+            item is to be assigned
+
+  DESCRIPTION
+    This function performs context analysis (name resolution) and calculates
+    various attributes of the item tree with Item_func_in as its root.
+    The function saves in ref the pointer to the item or to a newly created
+    item that is considered as a replacement for the original one.
+
+  NOTES
+    Let T0(e)/T1(e) be the value of not_null_tables(e) when e is used on
+    a predicate/function level. Then it's easy to show that:
+      T0(e IN(e1,...,en))     = union(T1(e),intersection(T1(ei)))
+      T1(e IN(e1,...,en))     = union(T1(e),intersection(T1(ei)))
+      T0(e NOT IN(e1,...,en)) = union(T1(e),union(T1(ei)))
+      T1(e NOT IN(e1,...,en)) = union(T1(e),intersection(T1(ei)))
+
+  RETURN
+    0   ok
+    1   got error
+*/
+
+bool
+Item_func_in::fix_fields(THD *thd, TABLE_LIST *tables, Item **ref)
+{
+  Item **arg, **arg_end;
+
+  if (Item_func_opt_neg::fix_fields(thd, tables, ref))
+    return 1;
+
+  /* not_null_tables_cache == union(T1(e),union(T1(ei))) */
+  if (pred_level && negated)
+    return 0;
+
+  /* not_null_tables_cache = union(T1(e),intersection(T1(ei))) */
+  not_null_tables_cache= ~(table_map) 0;
+  for (arg= args + 1, arg_end= args + arg_count; arg != arg_end; arg++)
+    not_null_tables_cache&= (*arg)->not_null_tables();
+  not_null_tables_cache|= (*args)->not_null_tables();
+  return 0;
+}
+
+
 static int srtcmp_in(CHARSET_INFO *cs, const String *x,const String *y)
 {
   return cs->coll->strnncollsp(cs,
@@ -1840,7 +1982,7 @@
   {
     int tmp=array->find(args[0]);
     null_value=args[0]->null_value || (!tmp && have_null);
-    return tmp;
+    return (longlong) (!null_value && tmp != negated);
   }
   in_item->store_value(args[0]);
   if ((null_value=args[0]->null_value))
@@ -1849,11 +1991,11 @@
   for (uint i=1 ; i < arg_count ; i++)
   {
     if (!in_item->cmp(args[i]) && !args[i]->null_value)
-      return 1;					// Would maybe be nice with i ?
+      return (longlong) (!negated);
     have_null|= args[i]->null_value;
   }
   null_value= have_null;
-  return 0;
+  return (longlong) (!null_value && negated);
 }
 
 

--- 1.108/sql/item_cmpfunc.h	2005-08-13 08:45:07 +04:00
+++ 1.109/sql/item_cmpfunc.h	2005-08-26 00:35:35 +04:00
@@ -358,17 +358,49 @@
 };
 
 
-class Item_func_between :public Item_int_func
+/*
+  The class Item_func_opt_neg is defined to factor out the functionality
+  common for the classes Item_func_between and Item_func_in. The objects
+  of these classes can express predicates or there negations.
+  The alternative approach would be to create pairs Item_func_between,
+  Item_func_notbetween and Item_func_in, Item_func_notin.
+
+*/
+
+class Item_func_opt_neg :public Item_int_func
+{
+public:
+  bool negated;     /* <=> the item represents NOT <func> */
+  bool pred_level;  /* <=> [NOT] <func> is used on a predicate level */
+public:
+  Item_func_opt_neg(Item *a, Item *b, Item *c)
+    :Item_int_func(a, b, c), negated(0), pred_level(0) {}
+  Item_func_opt_neg(List<Item> &list)
+    :Item_int_func(list), negated(0), pred_level(0) {}
+public:
+  inline void negate() { negated= !negated; }
+  inline void top_level_item() { pred_level= 1; }
+  Item *neg_transformer(THD *thd)
+  {
+    negated= !negated;
+    return this;
+  }
+};
+
+
+class Item_func_between :public Item_func_opt_neg
 {
   DTCollation cmp_collation;
 public:
   Item_result cmp_type;
   String value0,value1,value2;
-  Item_func_between(Item *a,Item *b,Item *c) :Item_int_func(a,b,c) {}
+  Item_func_between(Item *a, Item *b, Item *c)
+    :Item_func_opt_neg(a, b, c) {}
   longlong val_int();
   optimize_type select_optimize() const { return OPTIMIZE_KEY; }
   enum Functype functype() const   { return BETWEEN; }
   const char *func_name() const { return "between"; }
+  bool fix_fields(THD *, struct st_table_list *, Item **);
   void fix_length_and_dec();
   void print(String *str);
   CHARSET_INFO *compare_collation() { return cmp_collation.collation; }
@@ -433,15 +465,9 @@
   longlong val_int();
   String *val_str(String *str);
   enum Item_result result_type () const { return cached_result_type; }
-  bool fix_fields(THD *thd,struct st_table_list *tlist, Item **ref)
-  {
-    DBUG_ASSERT(fixed == 0);
-    args[0]->top_level_item();
-    return Item_func::fix_fields(thd, tlist, ref);
-  }
+  bool fix_fields(THD *, struct st_table_list *, Item **);
   void fix_length_and_dec();
   const char *func_name() const { return "if"; }
-  table_map not_null_tables() const { return 0; }
 };
 
 
@@ -736,7 +762,7 @@
   }
 };
 
-class Item_func_in :public Item_int_func
+class Item_func_in :public Item_func_opt_neg
 {
   Item_result cmp_type;
   in_vector *array;
@@ -745,11 +771,12 @@
   DTCollation cmp_collation;
  public:
   Item_func_in(List<Item> &list)
-    :Item_int_func(list), array(0), in_item(0), have_null(0)
+    :Item_func_opt_neg(list), array(0), in_item(0), have_null(0)
   {
     allowed_arg_cols= 0;  // Fetch this value from first argument
   }
   longlong val_int();
+  bool fix_fields(THD *, struct st_table_list *, Item **);
   void fix_length_and_dec();
   void cleanup()
   {
@@ -769,12 +796,6 @@
   bool nulls_in_row();
   bool is_bool_func() { return 1; }
   CHARSET_INFO *compare_collation() { return cmp_collation.collation; }
-  /*
-    IN() protect from NULL only first argument, if construction like
-    "expression IN ()" will be allowed, we will need to check number of
-    argument here, because "NOT(NULL IN ())" is TRUE.
-  */
-  table_map not_null_tables() const { return args[0]->not_null_tables(); }
 };
 
 /* Functions used by where clause */

--- 1.141/sql/opt_range.cc	2005-06-28 16:06:12 +04:00
+++ 1.142/sql/opt_range.cc	2005-08-26 00:44:08 +04:00
@@ -849,7 +849,8 @@
 
   if (cond_func->functype() == Item_func::BETWEEN)
   {
-    if (cond_func->arguments()[0]->type() == Item::FIELD_ITEM)
+    if (!((Item_func_between *)(cond_func))->negated &&
+        cond_func->arguments()[0]->type() == Item::FIELD_ITEM)
     {
       Field *field=((Item_field*) (cond_func->arguments()[0]))->field;
       Item_result cmp_type=field->cmp_type();
@@ -866,7 +867,7 @@
   if (cond_func->functype() == Item_func::IN_FUNC)
   {						// COND OR
     Item_func_in *func=(Item_func_in*) cond_func;
-    if (func->key_item()->type() == Item::FIELD_ITEM)
+    if (!func->negated && func->key_item()->type() == Item::FIELD_ITEM)
     {
       Field *field=((Item_field*) (func->key_item()))->field;
       Item_result cmp_type=field->cmp_type();

--- 1.390/sql/sql_yacc.yy	2005-07-17 20:42:13 +04:00
+++ 1.391/sql/sql_yacc.yy	2005-08-26 00:47:24 +04:00
@@ -2598,7 +2598,12 @@
 	 expr IN_SYM '(' expr_list ')'
 	  { $4->push_front($1); $$= new Item_func_in(*$4); }
 	| expr NOT IN_SYM '(' expr_list ')'
-	  { $5->push_front($1); $$= new Item_func_not(new Item_func_in(*$5)); }
+    {
+      $5->push_front($1);
+      Item_func_in *item= new Item_func_in(*$5);
+      item->negate();
+      $$= item;
+    }
         | expr IN_SYM in_subselect
           { $$= new Item_in_subselect($1, $3); }
 	| expr NOT IN_SYM in_subselect
@@ -2608,7 +2613,11 @@
 	| expr BETWEEN_SYM no_and_expr AND_SYM expr
 	  { $$= new Item_func_between($1,$3,$5); }
 	| expr NOT BETWEEN_SYM no_and_expr AND_SYM expr
-	  { $$= new Item_func_not(new Item_func_between($1,$4,$6)); }
+    {
+      Item_func_between *item= new Item_func_between($1,$4,$6);
+      item->negate();
+      $$= item;
+    }
 	| expr OR_OR_CONCAT expr { $$= or_or_concat(YYTHD, $1,$3); }
 	| expr OR_SYM expr	{ $$= new Item_cond_or($1,$3); }
         | expr XOR expr		{ $$= new Item_cond_xor($1,$3); }
@@ -2656,7 +2665,11 @@
 	no_in_expr BETWEEN_SYM no_and_expr AND_SYM expr
 	  { $$= new Item_func_between($1,$3,$5); }
 	| no_in_expr NOT BETWEEN_SYM no_and_expr AND_SYM expr
-	  { $$= new Item_func_not(new Item_func_between($1,$4,$6)); }
+    {
+      Item_func_between *item= new Item_func_between($1,$4,$6);
+      item->negate();
+      $$= item;
+    }
 	| no_in_expr OR_OR_CONCAT expr	{ $$= or_or_concat(YYTHD, $1,$3); }
 	| no_in_expr OR_SYM expr	{ $$= new Item_cond_or($1,$3); }
         | no_in_expr XOR expr		{ $$= new Item_cond_xor($1,$3); }
@@ -2704,7 +2717,12 @@
 	  no_and_expr IN_SYM '(' expr_list ')'
 	  { $4->push_front($1); $$= new Item_func_in(*$4); }
 	| no_and_expr NOT IN_SYM '(' expr_list ')'
-	  { $5->push_front($1); $$= new Item_func_not(new Item_func_in(*$5)); }
+    {
+      $5->push_front($1);
+      Item_func_in *item= new Item_func_in(*$5);
+      item->negate();
+      $$= item;
+    }
         | no_and_expr IN_SYM in_subselect
           { $$= new Item_in_subselect($1, $3); }
 	| no_and_expr NOT IN_SYM in_subselect
@@ -2714,7 +2732,11 @@
 	| no_and_expr BETWEEN_SYM no_and_expr AND_SYM expr
 	  { $$= new Item_func_between($1,$3,$5); }
 	| no_and_expr NOT BETWEEN_SYM no_and_expr AND_SYM expr
-	  { $$= new Item_func_not(new Item_func_between($1,$4,$6)); }
+    {
+      Item_func_between *item= new Item_func_between($1,$4,$6);
+      item->negate();
+      $$= item;
+    }
 	| no_and_expr OR_OR_CONCAT expr	{ $$= or_or_concat(YYTHD, $1,$3); }
 	| no_and_expr OR_SYM expr	{ $$= new Item_cond_or($1,$3); }
         | no_and_expr XOR expr		{ $$= new Item_cond_xor($1,$3); }

--- 1.34/mysql-test/r/join_outer.result	2005-04-05 05:29:15 +04:00
+++ 1.35/mysql-test/r/join_outer.result	2005-08-26 00:49:49 +04:00
@@ -883,3 +883,136 @@
 Warning	1260	2 line(s) were cut by GROUP_CONCAT()
 drop table t1, t2;
 set group_concat_max_len=default;
+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;
+a	b	a	b
+7	8	7	5
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a BETWEEN t2.b AND t1.b;
+a	b	a	b
+7	8	7	5
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a NOT BETWEEN t2.b AND t1.b);
+a	b	a	b
+7	8	7	5
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t2.b > t1.a OR t1.a > t1.b;
+a	b	a	b
+2	1	NULL	NULL
+3	2	3	0
+4	3	4	1
+6	5	6	4
+8	7	NULL	NULL
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a NOT BETWEEN t2.b AND t1.b;
+a	b	a	b
+2	1	NULL	NULL
+3	2	3	0
+4	3	4	1
+6	5	6	4
+8	7	NULL	NULL
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a BETWEEN t2.b AND t1.b);
+a	b	a	b
+2	1	NULL	NULL
+3	2	3	0
+4	3	4	1
+6	5	6	4
+8	7	NULL	NULL
+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;
+a	b	a	b
+2	1	NULL	NULL
+3	2	3	0
+4	3	4	1
+6	5	6	4
+7	8	7	5
+8	7	NULL	NULL
+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);
+a	b	a	b
+2	1	NULL	NULL
+3	2	3	0
+4	3	4	1
+6	5	6	4
+7	8	7	5
+8	7	NULL	NULL
+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);
+a	b	a	b
+3	2	3	0
+4	3	4	1
+6	5	6	4
+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);
+a	b	a	b
+3	2	3	0
+4	3	4	1
+6	5	6	4
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a = t2.a OR t1.a = t2.b;
+a	b	a	b
+3	2	3	0
+4	3	4	1
+6	5	6	4
+7	8	7	5
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a IN(t2.a, t2.b);
+a	b	a	b
+3	2	3	0
+4	3	4	1
+6	5	6	4
+7	8	7	5
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a NOT IN(t2.a, t2.b));
+a	b	a	b
+3	2	3	0
+4	3	4	1
+6	5	6	4
+7	8	7	5
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a != t1.b AND t1.a != t2.b;
+a	b	a	b
+3	2	3	0
+4	3	4	1
+6	5	6	4
+7	8	7	5
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a NOT IN(t1.b, t2.b);
+a	b	a	b
+3	2	3	0
+4	3	4	1
+6	5	6	4
+7	8	7	5
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a IN(t1.b, t2.b));
+a	b	a	b
+3	2	3	0
+4	3	4	1
+6	5	6	4
+7	8	7	5
+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);
+a	b	a	b
+3	2	3	0
+4	3	4	1
+6	5	6	4
+7	8	7	5
+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));
+a	b	a	b
+3	2	3	0
+4	3	4	1
+6	5	6	4
+7	8	7	5
+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;
+a	b	a	b
+3	2	3	0
+4	3	4	1
+6	5	6	4
+7	8	7	5
+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));
+a	b	a	b
+3	2	3	0
+4	3	4	1
+6	5	6	4
+7	8	7	5
+EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a = t2.a OR t1.a = t2.b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ALL	PRIMARY	NULL	NULL	NULL	4	
+1	SIMPLE	t1	eq_ref	PRIMARY	PRIMARY	4	test.t2.a	1	
+EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a IN(t2.a, t2.b);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ALL	PRIMARY	NULL	NULL	NULL	4	
+1	SIMPLE	t1	eq_ref	PRIMARY	PRIMARY	4	test.t2.a	1	Using where
+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);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ALL	PRIMARY	NULL	NULL	NULL	4	
+1	SIMPLE	t1	eq_ref	PRIMARY	PRIMARY	4	test.t2.a	1	Using where
+DROP TABLE t1,t2;

--- 1.19/mysql-test/t/join_outer.test	2005-07-28 04:21:43 +04:00
+++ 1.20/mysql-test/t/join_outer.test	2005-08-26 00:50:44 +04:00
@@ -625,4 +625,50 @@
 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
Thread
• bk commit into 4.1 tree (aivanov:1.2390)	Alex Ivanov	26 Aug