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

1 files changed, 0 insertions, 5 deletions

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)