bk commit into 5.1 tree (evgen:1.2337)

[eugene]

Below is the list of changes that have just been committed into a local
5.1 repository of evgen. When evgen 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@stripped, 2006-09-25 23:05:00+04:00, evgen@stripped +8 -0
  Merge epotemkin@stripped:/home/bk/mysql-5.1-opt
  into  moonbone.local:/work/18360-bug-5.1-opt-mysql
  MERGE: 1.2181.183.1

  mysql-test/r/func_in.result@stripped, 2006-09-25 22:52:04+04:00, evgen@stripped +0 -0
    Auto merged
    MERGE: 1.28.1.1

  mysql-test/r/func_time.result@stripped, 2006-09-25 23:04:57+04:00, evgen@stripped +0
-12
    Manual merge
    MERGE: 1.60.1.1

  mysql-test/r/view.result@stripped, 2006-09-25 22:52:04+04:00, evgen@stripped +0 -2
    Auto merged
    MERGE: 1.166.2.1

  mysql-test/t/func_time.test@stripped, 2006-09-25 23:04:57+04:00, evgen@stripped +0 -11
    Manual merge
    MERGE: 1.48.1.1

  mysql-test/t/view.test@stripped, 2006-09-25 22:52:04+04:00, evgen@stripped +0 -2
    Auto merged
    MERGE: 1.151.1.1

  sql/item.cc@stripped, 2006-09-25 22:52:04+04:00, evgen@stripped +0 -0
    Auto merged
    MERGE: 1.195.1.1

  sql/item_cmpfunc.h@stripped, 2006-09-25 22:52:04+04:00, evgen@stripped +0 -0
    Auto merged
    MERGE: 1.128.1.1

  sql/opt_range.cc@stripped, 2006-09-25 22:52:05+04:00, evgen@stripped +0 -0
    Auto merged
    MERGE: 1.223.2.1

# 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:	evgen
# Host:	moonbone.local
# Root:	/work/18360-bug-5.1-opt-mysql/RESYNC

--- 1.210/sql/item.cc	2006-09-25 23:05:07 +04:00
+++ 1.211/sql/item.cc	2006-09-25 23:05:07 +04:00
@@ -5704,11 +5704,6 @@
 }
 
 
-/*
-  If item is a const function, calculate it and return a const item
-  The original item is freed if not returned
-*/
-
 Item_result item_cmp_type(Item_result a,Item_result b)
 {
   if (a == STRING_RESULT && b == STRING_RESULT)

--- 1.134/sql/item_cmpfunc.h	2006-09-25 23:05:07 +04:00
+++ 1.135/sql/item_cmpfunc.h	2006-09-25 23:05:07 +04:00
@@ -589,49 +589,6 @@
 };
 
 
-class Item_func_case :public Item_func
-{
-  int first_expr_num, else_expr_num;
-  enum Item_result cached_result_type;
-  String tmp_value;
-  uint ncases;
-  Item_result cmp_type;
-  DTCollation cmp_collation;
-public:
-  Item_func_case(List<Item> &list, Item *first_expr_arg, Item *else_expr_arg)
-    :Item_func(), first_expr_num(-1), else_expr_num(-1),
-    cached_result_type(INT_RESULT)
-  {
-    ncases= list.elements;
-    if (first_expr_arg)
-    {
-      first_expr_num= list.elements;
-      list.push_back(first_expr_arg);
-    }
-    if (else_expr_arg)
-    {
-      else_expr_num= list.elements;
-      list.push_back(else_expr_arg);
-    }
-    set_arguments(list);
-  }
-  double val_real();
-  longlong val_int();
-  String *val_str(String *);
-  my_decimal *val_decimal(my_decimal *);
-  bool fix_fields(THD *thd, Item **ref);
-  void fix_length_and_dec();
-  uint decimal_precision() const;
-  table_map not_null_tables() const { return 0; }
-  enum Item_result result_type () const { return cached_result_type; }
-  const char *func_name() const { return "case"; }
-  void print(String *str);
-  Item *find_item(String *str);
-  CHARSET_INFO *compare_collation() { return cmp_collation.collation; }
-  bool check_partition_func_processor(byte *bool_arg) { return 0;}
-};
-
-
 /* Functions to handle the optimized IN */
 
 
@@ -686,6 +643,7 @@
   {
     return test(compare(collation, base + pos1*size, base + pos2*size));
   }
+  virtual Item_result result_type()= 0;
 };
 
 class in_string :public in_vector
@@ -707,6 +665,7 @@
     Item_string *to= (Item_string*)item;
     to->str_value= *str;
   }
+  Item_result result_type() { return STRING_RESULT; }
 };
 
 class in_longlong :public in_vector
@@ -729,6 +688,7 @@
   {
     ((Item_int*)item)->value= ((longlong*)base)[pos];
   }
+  Item_result result_type() { return INT_RESULT; }
 };
 
 class in_double :public in_vector
@@ -746,6 +706,7 @@
   {
     ((Item_float*)item)->value= ((double*) base)[pos];
   }
+  Item_result result_type() { return REAL_RESULT; }
 
 };
 
@@ -766,6 +727,8 @@
     Item_decimal *item_dec= (Item_decimal*)item;
     item_dec->set_decimal_value(dec);
   }
+  Item_result result_type() { return DECIMAL_RESULT; }
+
 };
 
 
@@ -796,7 +759,9 @@
 protected:
   String *value_res;
 public:
+  cmp_item_string () {}
   cmp_item_string (CHARSET_INFO *cs) { cmp_charset= cs; }
+  void set_charset(CHARSET_INFO *cs) { cmp_charset= cs; }
   friend class cmp_item_sort_string;
   friend class cmp_item_sort_string_in_static;
 };
@@ -807,6 +772,8 @@
   char value_buff[STRING_BUFFER_USUAL_SIZE];
   String value;
 public:
+  cmp_item_sort_string():
+    cmp_item_string() {}
   cmp_item_sort_string(CHARSET_INFO *cs):
     cmp_item_string(cs),
     value(value_buff, sizeof(value_buff), cs) {}
@@ -828,6 +795,11 @@
     return sortcmp(value_res, cmp->value_res, cmp_charset);
   } 
   cmp_item *make_same();
+  void set_charset(CHARSET_INFO *cs)
+  {
+    cmp_charset= cs;
+    value.set_quick(value_buff, sizeof(value_buff), cs);
+  }
 };
 
 class cmp_item_int :public cmp_item
@@ -908,6 +880,7 @@
   ~in_row();
   void set(uint pos,Item *item);
   byte *get_value(Item *item);
+  Item_result result_type() { return ROW_RESULT; }
 };
 
 /* 
@@ -943,18 +916,109 @@
   }
 };
 
+
+/*
+  The class Item_func_case is the CASE ... WHEN ... THEN ... END function
+  implementation.
+
+  When there is no expression between CASE and the first WHEN 
+  (the CASE expression) then this function simple checks all WHEN expressions
+  one after another. When some WHEN expression evaluated to TRUE then the
+  value of the corresponding THEN expression is returned.
+
+  When the CASE expression is specified then it is compared to each WHEN
+  expression individually. When an equal WHEN expression is found
+  corresponding THEN expression is returned.
+  In order to do correct comparisons several comparators are used. One for
+  each result type. Different result types that are used in particular
+  CASE ... END expression are collected in the fix_length_and_dec() member
+  function and only comparators for there result types are used.
+*/
+
+class Item_func_case :public Item_func
+{
+  int first_expr_num, else_expr_num;
+  enum Item_result cached_result_type, left_result_type;
+  String tmp_value;
+  uint ncases;
+  Item_result cmp_type;
+  DTCollation cmp_collation;
+  cmp_item *cmp_items[5]; /* For all result types */
+  cmp_item *case_item;
+public:
+  Item_func_case(List<Item> &list, Item *first_expr_arg, Item *else_expr_arg)
+    :Item_func(), first_expr_num(-1), else_expr_num(-1),
+    cached_result_type(INT_RESULT), left_result_type(INT_RESULT), case_item(0)
+  {
+    ncases= list.elements;
+    if (first_expr_arg)
+    {
+      first_expr_num= list.elements;
+      list.push_back(first_expr_arg);
+    }
+    if (else_expr_arg)
+    {
+      else_expr_num= list.elements;
+      list.push_back(else_expr_arg);
+    }
+    set_arguments(list);
+    bzero(&cmp_items, sizeof(cmp_items));
+  }
+  double val_real();
+  longlong val_int();
+  String *val_str(String *);
+  my_decimal *val_decimal(my_decimal *);
+  bool fix_fields(THD *thd, Item **ref);
+  void fix_length_and_dec();
+  uint decimal_precision() const;
+  table_map not_null_tables() const { return 0; }
+  enum Item_result result_type () const { return cached_result_type; }
+  const char *func_name() const { return "case"; }
+  void print(String *str);
+  Item *find_item(String *str);
+  CHARSET_INFO *compare_collation() { return cmp_collation.collation; }
+  bool check_partition_func_processor(byte *bool_arg) { return 0;}
+  void cleanup()
+  {
+    uint i;
+    DBUG_ENTER("Item_func_case::cleanup");
+    Item_func::cleanup();
+    for (i= 0; i <= (uint)DECIMAL_RESULT; i++)
+    {
+      delete cmp_items[i];
+      cmp_items[i]= 0;
+    }
+    DBUG_VOID_RETURN;
+  }
+};
+
+/*
+  The Item_func_in class implements the in_expr IN(values_list) function.
+
+  The current implementation distinguishes 2 cases:
+  1) all items in the value_list are constants and have the same
+    result type. This case is handled by in_vector class.
+  2) items in the value_list have different result types or there is some
+    non-constant items.
+    In this case Item_func_in employs several cmp_item objects to performs
+    comparisons of in_expr and an item from the values_list. One cmp_item
+    object for each result type. Different result types are collected in the
+    fix_length_and_dec() member function by means of collect_cmp_types()
+    function.
+*/
 class Item_func_in :public Item_func_opt_neg
 {
 public:
-  Item_result cmp_type;
   in_vector *array;
-  cmp_item *in_item;
   bool have_null;
+  Item_result left_result_type;
+  cmp_item *cmp_items[5]; /* One cmp_item for each result type */
   DTCollation cmp_collation;
 
   Item_func_in(List<Item> &list)
-    :Item_func_opt_neg(list), array(0), in_item(0), have_null(0)
+    :Item_func_opt_neg(list), array(0), have_null(0)
   {
+    bzero(&cmp_items, sizeof(cmp_items));
     allowed_arg_cols= 0;  // Fetch this value from first argument
   }
   longlong val_int();
@@ -963,12 +1027,16 @@
   uint decimal_precision() const { return 1; }
   void cleanup()
   {
+    uint i;
     DBUG_ENTER("Item_func_in::cleanup");
     Item_int_func::cleanup();
     delete array;
-    delete in_item;
     array= 0;
-    in_item= 0;
+    for (i= 0; i <= (uint)DECIMAL_RESULT; i++)
+    {
+      delete cmp_items[i];
+      cmp_items[i]= 0;
+    }
     DBUG_VOID_RETURN;
   }
   optimize_type select_optimize() const

--- 1.233/sql/opt_range.cc	2006-09-25 23:05:07 +04:00
+++ 1.234/sql/opt_range.cc	2006-09-25 23:05:07 +04:00
@@ -4917,9 +4917,17 @@
   {
     Item_func_in *func=(Item_func_in*) cond_func;
 
+    /*
+      Array for IN() is constructed when all values have the same result
+      type. Tree won't be built for values with different result types,
+      so we check it here to avoid unnecessary work.
+    */
+    if (!func->array)
+      break;
+
     if (inv)
     {
-      if (func->array && func->cmp_type != ROW_RESULT)
+      if (func->array->result_type() != ROW_RESULT)
       {
         /*
           We get here for conditions in form "t.key NOT IN (c1, c2, ...)",

--- 1.160/mysql-test/t/view.test	2006-09-25 23:05:07 +04:00
+++ 1.161/mysql-test/t/view.test	2006-09-25 23:05:07 +04:00
@@ -2443,7 +2443,7 @@
 #
 # Bug #16069: VIEW does return the same results as underlying SELECT
 #             with WHERE condition containing BETWEEN over dates 
-
+# Dates as strings should be casted to date type
 CREATE TABLE t1 (id int NOT NULL PRIMARY KEY,
                  td date DEFAULT NULL, KEY idx(td));
 

--- 1.29/mysql-test/r/func_in.result	2006-09-25 23:05:07 +04:00
+++ 1.30/mysql-test/r/func_in.result	2006-09-25 23:05:07 +04:00
@@ -343,3 +343,15 @@
 1
 2
 drop table t1;
+create table t1(f1 char(1));
+insert into t1 values ('a'),('b'),('1');
+select f1 from t1 where f1 in ('a',1);
+f1
+a
+1
+select f1, case f1 when 'a' then '+' when 1 then '-' end from t1;
+f1	case f1 when 'a' then '+' when 1 then '-' end
+a	+
+b	NULL
+1	-
+drop table t1;

--- 1.77/mysql-test/r/func_time.result	2006-09-25 23:05:07 +04:00
+++ 1.78/mysql-test/r/func_time.result	2006-09-25 23:05:07 +04:00
@@ -840,7 +840,7 @@
 create table t1(f1 date, f2 time, f3 datetime);
 insert into t1 values ("2006-01-01", "12:01:01", "2006-01-01 12:01:01");
 insert into t1 values ("2006-01-02", "12:01:02", "2006-01-02 12:01:02");
-select f1 from t1 where f1 between CAST("2006-1-1" as date) and CAST(20060101 as date);
+select f1 from t1 where f1 between cast("2006-1-1" as date) and cast(20060101 as date);
 f1
 2006-01-01
 select f1 from t1 where f1 between cast("2006-1-1" as date) and cast("2006.1.1" as date);

--- 1.62/mysql-test/t/func_time.test	2006-09-25 23:05:07 +04:00
+++ 1.63/mysql-test/t/func_time.test	2006-09-25 23:05:07 +04:00
@@ -419,11 +419,11 @@
 #
 # Bug#16377 result of DATE/TIME functions were compared as strings which
 #           can lead to a wrong result.
-# Now wrong dates should be compared only with CAST()
+# Date/time in strings should be casted to date/time type
 create table t1(f1 date, f2 time, f3 datetime);
 insert into t1 values ("2006-01-01", "12:01:01", "2006-01-01 12:01:01");
 insert into t1 values ("2006-01-02", "12:01:02", "2006-01-02 12:01:02");
-select f1 from t1 where f1 between CAST("2006-1-1" as date) and CAST(20060101 as date);
+select f1 from t1 where f1 between cast("2006-1-1" as date) and cast(20060101 as date);
 select f1 from t1 where f1 between cast("2006-1-1" as date) and cast("2006.1.1" as date);
 select f1 from t1 where date(f1) between cast("2006-1-1" as date) and cast("2006.1.1" as
date);
 select f2 from t1 where f2 between cast("12:1:2" as time) and cast("12:2:2" as time);