Fixed bug #18360: Type aggregation for IN and CASE may lead to a wrong

result

The IN function aggregates result types of all expressions. It uses that 
type in comparison of left expression and expressions in right part. 
This approach works in most cases. But let's consider the case when the
right part contains both strings and integers. In that case this approach may
cause wrong results because all strings which do not start with a digit are
evaluated as 0.
CASE uses the same approach when a CASE expression is given thus it's also
affected.

The idea behind this fix is to make IN function to compare expressions with
different result types differently. For example a string in the left
part will be compared as string with strings specified in right part and
will be converted to real for comparison to int or real items in the right
part.

A new function called collect_cmp_types() is added. It collects different
result types for comparison of first item in the provided list with each 
other item in the list. 

The Item_func_in class now can refer up to 5 cmp_item objects: 1 for each
result type for comparison purposes. cmp_item objects are allocated according
to found result types. The comparison of the left expression with any
right part expression is now based only on result types of these expressions.

The Item_func_case class is modified in the similar way when a CASE
expression is specified. Now it can allocate up to 5 cmp_item objects
to compare CASE expression with WHEN expressions of different types.
The comparison of the CASE expression with any WHEN expression now based only 
on result types of these expressions.



sql/item.cc:
  Cleaned up an outdated comment.
sql/item_cmpfunc.cc:
      Fixed bug #18360: Type aggregation for IN and CASE may lead to a wrong result
      A new function called collect_cmp_types() is added. It collects different
      result types for comparison of first item in the provided list with each 
      other item in the list. 
      The Item_func_in class now can refer up to 5 cmp_item objects: 1 for each
      result type for comparison purposes. cmp_item objects are allocated according
      to found result types. The comparison of the left expression with any
      right part expression is now based only on result types of these expressions.
      The Item_func_case class is modified in the similar way when a CASE
      expression is specified. Now it can allocate up to 5 cmp_item objects
      to compare CASE expression with WHEN expressions of different types.
      The comparison of the CASE expression with any WHEN expression now based only 
      on result types of these expressions.
sql/item_cmpfunc.h:
      Fixed bug#18360: Type aggregation for IN and CASE may lead to a wrong result
      The Item_func_in class now can refer up to 5 cmp_item objects.
      The Item_func_case class is modified in the similar way.
sql/opt_range.cc:
      Fixed bug #18360: Type aggregation for IN and CASE may lead to a wrong
      resultSmall code changes due to changes in Item_func_in class.
mysql-test/t/view.test:
  Added a test comment
mysql-test/t/func_in.test:
  Added a test case for bug#18360: Type aggregation for IN and CASE may lead to a wrong
  result
mysql-test/r/func_in.result:
  Added a test case for bug#18360: Type aggregation for IN and CASE may lead to a wrong
  result

7 files changed, 402 insertions, 167 deletions

diff --git a/mysql-test/r/func_in.result b/mysql-test/r/func_in.result
index 0236cbfe26f..b88e5a66f96 100644
--- a/mysql-test/r/func_in.result
+++ b/mysql-test/r/func_in.result
@@ -343,3 +343,71 @@ some_id
 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	-
+create index t1f1_idx on t1(f1);
+select f1 from t1 where f1 in ('a',1);
+f1
+1
+a
+explain select f1 from t1 where f1 in ('a',1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	t1f1_idx	2	NULL	3	Using where; Using index
+select f1 from t1 where f1 in ('a','b');
+f1
+a
+b
+explain select f1 from t1 where f1 in ('a','b');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	t1f1_idx	t1f1_idx	2	NULL	3	Using where; Using index
+select f1 from t1 where f1 in (2,1);
+f1
+1
+explain select f1 from t1 where f1 in (2,1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	t1f1_idx	t1f1_idx	2	NULL	3	Using where; Using index
+create table t2(f2 int, index t2f2(f2));
+insert into t2 values(0),(1),(2);
+select f2 from t2 where f2 in ('a',2);
+f2
+0
+2
+Warnings:
+Warning	1292	Truncated incorrect DOUBLE value: 'a'
+Warning	1292	Truncated incorrect DOUBLE value: 'a'
+Warning	1292	Truncated incorrect DOUBLE value: 'a'
+explain select f2 from t2 where f2 in ('a',2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	index	NULL	t2f2	5	NULL	3	Using where; Using index
+select f2 from t2 where f2 in ('a','b');
+f2
+0
+Warnings:
+Warning	1292	Truncated incorrect DOUBLE value: 'a'
+Warning	1292	Truncated incorrect DOUBLE value: 'b'
+explain select f2 from t2 where f2 in ('a','b');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	index	t2f2	t2f2	5	NULL	3	Using where; Using index
+Warnings:
+Warning	1292	Truncated incorrect DOUBLE value: 'a'
+Warning	1292	Truncated incorrect DOUBLE value: 'b'
+select f2 from t2 where f2 in (1,'b');
+f2
+0
+1
+Warnings:
+Warning	1292	Truncated incorrect DOUBLE value: 'b'
+Warning	1292	Truncated incorrect DOUBLE value: 'b'
+explain select f2 from t2 where f2 in (1,'b');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	index	NULL	t2f2	5	NULL	3	Using where; Using index
+drop table t1, t2;
diff --git a/mysql-test/t/func_in.test b/mysql-test/t/func_in.test
index 8ddf1fbe314..906747c2f78 100644
--- a/mysql-test/t/func_in.test
+++ b/mysql-test/t/func_in.test
@@ -232,3 +232,27 @@ select some_id from t1 where some_id not in(2,-1);
 select some_id from t1 where some_id not in(-4,-1,-4);
 select some_id from t1 where some_id not in(-4,-1,3423534,2342342);
 drop table t1;
+
+#
+# Bug#18360: Type aggregation for IN and CASE may lead to a wrong result 
+#
+create table t1(f1 char(1));
+insert into t1 values ('a'),('b'),('1');
+select f1 from t1 where f1 in ('a',1);
+select f1, case f1 when 'a' then '+' when 1 then '-' end from t1;
+create index t1f1_idx on t1(f1);
+select f1 from t1 where f1 in ('a',1);
+explain select f1 from t1 where f1 in ('a',1);
+select f1 from t1 where f1 in ('a','b');
+explain select f1 from t1 where f1 in ('a','b');
+select f1 from t1 where f1 in (2,1);
+explain select f1 from t1 where f1 in (2,1);
+create table t2(f2 int, index t2f2(f2));
+insert into t2 values(0),(1),(2);
+select f2 from t2 where f2 in ('a',2);
+explain select f2 from t2 where f2 in ('a',2);
+select f2 from t2 where f2 in ('a','b');
+explain select f2 from t2 where f2 in ('a','b');
+select f2 from t2 where f2 in (1,'b');
+explain select f2 from t2 where f2 in (1,'b');
+drop table t1, t2;
diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test
index 7d1ddccba83..e1291390b93 100644
--- a/mysql-test/t/view.test
+++ b/mysql-test/t/view.test
@@ -2443,7 +2443,7 @@ DROP TABLE t1, t2;
 #
 # 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));
 
diff --git a/sql/item.cc b/sql/item.cc
index 62292a38245..f1c7a052988 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -5704,11 +5704,6 @@ void Item_trigger_field::cleanup()
 }
 
 
-/*
-  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)
diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index 0aa6d432966..135e4596996 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -66,12 +66,10 @@ static void agg_result_type(Item_result *type, Item **items, uint nitems)
 /*
   Aggregates result types from the array of items.
 
-  SYNOPSIS:
+  SYNOPSIS
     agg_cmp_type()
-    thd          thread handle
-    type   [out] the aggregated type
-    items        array of items to aggregate the type from
-    nitems       number of items in the array
+      items        array of items to aggregate the type from
+      nitems       number of items in the array
 
   DESCRIPTION
     This function aggregates result types from the array of items. Found type
@@ -79,12 +77,43 @@ static void agg_result_type(Item_result *type, Item **items, uint nitems)
     Aggregation itself is performed by the item_cmp_type() function.
 */
 
-static void agg_cmp_type(THD *thd, Item_result *type, Item **items, uint nitems)
+static Item_result agg_cmp_type(Item **items, uint nitems)
 {
   uint i;
-  type[0]= items[0]->result_type();
+  Item_result type= items[0]->result_type();
   for (i= 1 ; i < nitems ; i++)
-    type[0]= item_cmp_type(type[0], items[i]->result_type());
+    type= item_cmp_type(type, items[i]->result_type());
+  return type;
+}
+
+
+/*
+  Collects different types for comparison of first item with each other items
+
+  SYNOPSIS
+    collect_cmp_types()
+      items             Array of items to collect types from
+      nitems            Number of items in the array
+
+  DESCRIPTION
+    This function collects different result types for comparison of the first
+    item in the list with each of the remaining items in the 'items' array.
+
+  RETURN
+    Bitmap of collected types
+*/
+
+static uint collect_cmp_types(Item **items, uint nitems)
+{
+  uint i;
+  uint found_types;
+  Item_result left_result= items[0]->result_type();
+  DBUG_ASSERT(nitems > 1);
+  found_types= 0;
+  for (i= 1; i < nitems ; i++)
+    found_types|= 1<< (uint)item_cmp_type(left_result,
+                                           items[i]->result_type());
+  return found_types;
 }
 
 
@@ -1117,7 +1146,7 @@ void Item_func_between::fix_length_and_dec()
   */
   if (!args[0] || !args[1] || !args[2])
     return;
-  agg_cmp_type(thd, &cmp_type, args, 3);
+  cmp_type= agg_cmp_type(args, 3);
   if (cmp_type == STRING_RESULT &&
       agg_arg_charsets(cmp_collation, args, 3, MY_COLL_CMP_CONV, 1))
    return;
@@ -1597,94 +1626,65 @@ Item_func_nullif::is_null()
   return (null_value= (!cmp.compare() ? 1 : args[0]->null_value)); 
 }
 
+
 /*
-  CASE expression 
   Return the matching ITEM or NULL if all compares (including else) failed
+
+  SYNOPSIS
+    find_item()
+      str      Buffer string
+
+  DESCRIPTION
+    Find and return matching items for CASE or ELSE item if all compares
+    are failed or NULL if ELSE item isn't defined.
+
+  IMPLEMENTATION
+    In order to do correct comparisons of the CASE expression (the expression
+    between CASE and the first WHEN) with each WHEN expression several
+    comparators are used. One for each result type. CASE expression can be
+    evaluated up to # of different result types are used. To check whether
+    the CASE expression already was evaluated for a particular result type
+    a bit mapped variable value_added_map is used. Result types are mapped
+    to it according to their int values i.e. STRING_RESULT is mapped to bit
+    0, REAL_RESULT to bit 1, so on.
+
+  RETURN
+    NULL - Nothing found and there is no ELSE expression defined
+    item - Found item or ELSE item if defined and all comparisons are
+           failed
 */
 
 Item *Item_func_case::find_item(String *str)
 {
-  String *first_expr_str, *tmp;
-  my_decimal *first_expr_dec, first_expr_dec_val;
-  longlong first_expr_int;
-  double   first_expr_real;
-  char buff[MAX_FIELD_WIDTH];
-  String buff_str(buff,sizeof(buff),default_charset());
-
-  /* These will be initialized later */
-  LINT_INIT(first_expr_str);
-  LINT_INIT(first_expr_int);
-  LINT_INIT(first_expr_real);
-  LINT_INIT(first_expr_dec);
-
-  if (first_expr_num != -1)
-  {
-    switch (cmp_type)
-    {
-      case STRING_RESULT:
-      	// We can't use 'str' here as this may be overwritten
-	if (!(first_expr_str= args[first_expr_num]->val_str(&buff_str)))
-	  return else_expr_num != -1 ? args[else_expr_num] : 0;	// Impossible
-        break;
-      case INT_RESULT:
-	first_expr_int= args[first_expr_num]->val_int();
-	if (args[first_expr_num]->null_value)
-	  return else_expr_num != -1 ? args[else_expr_num] : 0;
-	break;
-      case REAL_RESULT:
-	first_expr_real= args[first_expr_num]->val_real();
-	if (args[first_expr_num]->null_value)
-	  return else_expr_num != -1 ? args[else_expr_num] : 0;
-	break;
-      case DECIMAL_RESULT:
-        first_expr_dec= args[first_expr_num]->val_decimal(&first_expr_dec_val);
-        if (args[first_expr_num]->null_value)
-          return else_expr_num != -1 ? args[else_expr_num] : 0;
-        break;
-      case ROW_RESULT:
-      default:
-        // This case should never be chosen
-	DBUG_ASSERT(0);
-	break;
-    }
-  }
+  uint value_added_map= 0;
 
-  // Compare every WHEN argument with it and return the first match
-  for (uint i=0 ; i < ncases ; i+=2)
+  if (first_expr_num == -1)
   {
-    if (first_expr_num == -1)
+    for (uint i=0 ; i < ncases ; i+=2)
     {
       // No expression between CASE and the first WHEN
       if (args[i]->val_bool())
 	return args[i+1];
       continue;
     }
-    switch (cmp_type) {
-    case STRING_RESULT:
-      if ((tmp=args[i]->val_str(str)))		// If not null
-	if (sortcmp(tmp,first_expr_str,cmp_collation.collation)==0)
-	  return args[i+1];
-      break;
-    case INT_RESULT:
-      if (args[i]->val_int()==first_expr_int && !args[i]->null_value) 
-        return args[i+1];
-      break;
-    case REAL_RESULT: 
-      if (args[i]->val_real() == first_expr_real && !args[i]->null_value)
-        return args[i+1];
-      break;
-    case DECIMAL_RESULT:
+  }
+  else
+  {
+    /* Compare every WHEN argument with it and return the first match */
+    for (uint i=0 ; i < ncases ; i+=2)
     {
-      my_decimal value;
-      if (my_decimal_cmp(args[i]->val_decimal(&value), first_expr_dec) == 0)
-        return args[i+1];
-      break;
-    }
-    case ROW_RESULT:
-    default:
-      // This case should never be chosen
-      DBUG_ASSERT(0);
-      break;
+      cmp_type= item_cmp_type(left_result_type, args[i]->result_type());
+      DBUG_ASSERT(cmp_type != ROW_RESULT);
+      DBUG_ASSERT(cmp_items[(uint)cmp_type]);
+      if (!(value_added_map & (1<<(uint)cmp_type)))
+      {
+        cmp_items[(uint)cmp_type]->store_value(args[first_expr_num]);
+        if ((null_value=args[first_expr_num]->null_value))
+          return else_expr_num != -1 ? args[else_expr_num] : 0;
+        value_added_map|= 1<<(uint)cmp_type;
+      }
+      if (!cmp_items[(uint)cmp_type]->cmp(args[i]) && !args[i]->null_value)
+        return args[i + 1];
     }
   }
   // No, WHEN clauses all missed, return ELSE expression
@@ -1791,7 +1791,7 @@ void Item_func_case::fix_length_and_dec()
   Item **agg;
   uint nagg;
   THD *thd= current_thd;
-  
+  uint found_types= 0;
   if (!(agg= (Item**) sql_alloc(sizeof(Item*)*(ncases+1))))
     return;
   
@@ -1818,16 +1818,31 @@ void Item_func_case::fix_length_and_dec()
   */
   if (first_expr_num != -1)
   {
+    uint i;
     agg[0]= args[first_expr_num];
+    left_result_type= agg[0]->result_type();
+
     for (nagg= 0; nagg < ncases/2 ; nagg++)
       agg[nagg+1]= args[nagg*2];
     nagg++;
-    agg_cmp_type(thd, &cmp_type, agg, nagg);
-    if ((cmp_type == STRING_RESULT) &&
-        agg_arg_charsets(cmp_collation, agg, nagg, MY_COLL_CMP_CONV, 1))
-      return;
+    found_types= collect_cmp_types(agg, nagg);
+
+    for (i= 0; i <= (uint)DECIMAL_RESULT; i++)
+    {
+      if (found_types & (1 << i) && !cmp_items[i])
+      {
+        DBUG_ASSERT((Item_result)i != ROW_RESULT);
+        if ((Item_result)i == STRING_RESULT &&
+            agg_arg_charsets(cmp_collation, agg, nagg, MY_COLL_CMP_CONV, 1))
+          return;
+        if (!(cmp_items[i]=
+            cmp_item::get_comparator((Item_result)i,
+                                     cmp_collation.collation)))
+          return;
+      }
+    }
   }
-  
+
   if (else_expr_num == -1 || args[else_expr_num]->maybe_null)
     maybe_null=1;
   
@@ -2412,16 +2427,14 @@ static int srtcmp_in(CHARSET_INFO *cs, const String *x,const String *y)
 void Item_func_in::fix_length_and_dec()
 {
   Item **arg, **arg_end;
-  uint const_itm= 1;
+  bool const_itm= 1;
   THD *thd= current_thd;
+  uint found_types= 0;
+  uint type_cnt= 0, i;
+  left_result_type= args[0]->result_type();
+  found_types= collect_cmp_types(args, arg_count);
   
-  agg_cmp_type(thd, &cmp_type, args, arg_count);
-
-  if (cmp_type == STRING_RESULT &&
-      agg_arg_charsets(cmp_collation, args, arg_count, MY_COLL_CMP_CONV, 1))
-    return;
-
-  for (arg=args+1, arg_end=args+arg_count; arg != arg_end ; arg++)
+  for (arg= args + 1, arg_end= args + arg_count; arg != arg_end ; arg++)
   {
     if (!arg[0]->const_item())
     {
@@ -2429,26 +2442,39 @@ void Item_func_in::fix_length_and_dec()
       break;
     }
   }
-
+  for (i= 0; i <= (uint)DECIMAL_RESULT; i++)
+  {
+    if (found_types & 1 << i)
+      (type_cnt)++;
+  }
   /*
-    Row item with NULLs inside can return NULL or FALSE => 
+    Row item with NULLs inside can return NULL or FALSE =>
     they can't be processed as static
   */
-  if (const_itm && !nulls_in_row())
+  if (type_cnt == 1 && const_itm && !nulls_in_row())
   {
+    uint tmp_type;
+    Item_result cmp_type;
+    /* Only one cmp type was found. Extract it here */
+    for (tmp_type= 0; found_types - 1; found_types>>= 1)
+      tmp_type++;
+    cmp_type= (Item_result)tmp_type;
+
     switch (cmp_type) {
     case STRING_RESULT:
-      array=new in_string(arg_count-1,(qsort2_cmp) srtcmp_in, 
+      if (agg_arg_charsets(cmp_collation, args, arg_count, MY_COLL_CMP_CONV, 1))
+        return;
+      array=new in_string(arg_count - 1,(qsort2_cmp) srtcmp_in,
 			  cmp_collation.collation);
       break;
     case INT_RESULT:
-      array= new in_longlong(arg_count-1);
+      array= new in_longlong(arg_count - 1);
       break;
     case REAL_RESULT:
-      array= new in_double(arg_count-1);
+      array= new in_double(arg_count - 1);
       break;
     case ROW_RESULT:
-      array= new in_row(arg_count-1, args[0]);
+      array= new in_row(arg_count - 1, args[0]);
       break;
     case DECIMAL_RESULT:
       array= new in_decimal(arg_count - 1);
@@ -2468,15 +2494,25 @@ void Item_func_in::fix_length_and_dec()
 	else
 	  have_null= 1;
       }
-      if ((array->used_count=j))
+      if ((array->used_count= j))
 	array->sort();
     }
   }
   else
   {
-    in_item= cmp_item::get_comparator(cmp_type, cmp_collation.collation);
-    if (cmp_type  == STRING_RESULT)
-      in_item->cmp_charset= cmp_collation.collation;
+    for (i= 0; i <= (uint) DECIMAL_RESULT; i++)
+    {
+      if (found_types & (1 << i) && !cmp_items[i])
+      {
+        if ((Item_result)i == STRING_RESULT &&
+            agg_arg_charsets(cmp_collation, args, arg_count, MY_COLL_CMP_CONV, 1))
+          return;
+        if (!(cmp_items[i]=
+            cmp_item::get_comparator((Item_result)i,
+                                     cmp_collation.collation)))
+          return;
+      }
+    }
   }
   maybe_null= args[0]->maybe_null;
   max_length= 1;
@@ -2495,25 +2531,61 @@ void Item_func_in::print(String *str)
 }
 
 
+/*
+  Evaluate the function and return its value.
+
+  SYNOPSIS
+    val_int()
+
+  DESCRIPTION
+    Evaluate the function and return its value.
+
+  IMPLEMENTATION
+    If the array object is defined then the value of the function is
+    calculated by means of this array.
+    Otherwise several cmp_item objects are used in order to do correct
+    comparison of left expression and an expression from the values list.
+    One cmp_item object correspond to one used comparison type. Left
+    expression can be evaluated up to number of different used comparison
+    types. A bit mapped variable value_added_map is used to check whether
+    the left expression already was evaluated for a particular result type.
+    Result types are mapped to it according to their integer values i.e.
+    STRING_RESULT is mapped to bit 0, REAL_RESULT to bit 1, so on.
+
+  RETURN
+    Value of the function
+*/
+
 longlong Item_func_in::val_int()
 {
+  cmp_item *in_item;
   DBUG_ASSERT(fixed == 1);
+  uint value_added_map= 0;
   if (array)
   {
     int tmp=array->find(args[0]);
     null_value=args[0]->null_value || (!tmp && have_null);
     return (longlong) (!null_value && tmp != negated);
   }
-  in_item->store_value(args[0]);
-  if ((null_value=args[0]->null_value))
-    return 0;
-  have_null= 0;
-  for (uint i=1 ; i < arg_count ; i++)
+
+  for (uint i= 1 ; i < arg_count ; i++)
   {
+    Item_result cmp_type= item_cmp_type(left_result_type, args[i]->result_type());
+    in_item= cmp_items[(uint)cmp_type];
+    DBUG_ASSERT(in_item);
+    if (!(value_added_map & (1 << (uint)cmp_type)))
+    {
+      in_item->store_value(args[0]);
+      if ((null_value=args[0]->null_value))
+        return 0;
+      have_null= 0;
+      value_added_map|= 1 << (uint)cmp_type;
+    }
     if (!in_item->cmp(args[i]) && !args[i]->null_value)
       return (longlong) (!negated);
     have_null|= args[i]->null_value;
   }
+
   null_value= have_null;
   return (longlong) (!null_value && negated);
 }
diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h
index a9149ade097..f2a8fd7db63 100644
--- a/sql/item_cmpfunc.h
+++ b/sql/item_cmpfunc.h
@@ -589,49 +589,6 @@ public:
 };
 
 
-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 @@ public:
   {
     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 @@ public:
     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 @@ public:
   {
     ((Item_int*)item)->value= ((longlong*)base)[pos];
   }
+  Item_result result_type() { return INT_RESULT; }
 };
 
 class in_double :public in_vector
@@ -746,6 +706,7 @@ public:
   {
     ((Item_float*)item)->value= ((double*) base)[pos];
   }
+  Item_result result_type() { return REAL_RESULT; }
 
 };
 
@@ -766,6 +727,8 @@ public:
     Item_decimal *item_dec= (Item_decimal*)item;
     item_dec->set_decimal_value(dec);
   }
+  Item_result result_type() { return DECIMAL_RESULT; }
+
 };
 
 
@@ -796,7 +759,9 @@ class cmp_item_string :public cmp_item
 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 @@ protected:
   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 @@ public:
     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 @@ public:
   ~in_row();
   void set(uint pos,Item *item);
   byte *get_value(Item *item);
+  Item_result result_type() { return ROW_RESULT; }
 };
 
 /* 
@@ -943,18 +916,109 @@ public:
   }
 };
 
+
+/*
+  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 @@ public:
   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
diff --git a/sql/opt_range.cc b/sql/opt_range.cc
index 13ffed4293e..fcdae26d98f 100644
--- a/sql/opt_range.cc
+++ b/sql/opt_range.cc
@@ -4917,9 +4917,17 @@ static SEL_TREE *get_func_mm_tree(RANGE_OPT_PARAM *param, Item_func *cond_func,
   {
     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, ...)",