Bug #44399: crash with statement using TEXT columns, aggregates, GROUP BY,

  and HAVING

When calculating GROUP BY the server caches some expressions. It does
that by allocating a string slot (Item_copy_string) and assigning the 
value of the expression to it. This effectively means that the result
type of the expression can be changed from whatever it was to a string.
As this substitution takes place after the compile-time result type 
calculation for IN but before the run-time type calculations, 
it causes the type calculations in the IN function done at run time 
to get unexpected results different from what was prepared at compile time.

In the CASE ... WHEN ... THEN ... statement there was a similar problem
and it was solved by artificially adding a STRING argument to the matrix
at compile time, so if any of the arguments of the CASE function changes 
its type to a string it will still be covered by the information prepared 
at compile time.
Extended the CASE fix for cover the IN case.
An alternative way of fixing this problem is by caching the result type of 
the arguments at compile time and using the cached information at run time
instead of re-calculating the result types.
Preferred the CASE approach for uniformity and fix localization.

mysql-test/r/func_in.result:
  Bug #44399: test case
mysql-test/t/func_in.test:
  Bug #44399: test case
sql/item_cmpfunc.cc:
  Bug #44399: assume at compile time there's an extra string argument
  in the IN function (similar to CASE) to cater for possible string 
  conversions in the process of calculating the GROUP BY/aggregates.

3 files changed, 29 insertions, 14 deletions

diff --git a/mysql-test/r/func_in.result b/mysql-test/r/func_in.result
index 1e967b668c5..b19edbd1a00 100644
--- a/mysql-test/r/func_in.result
+++ b/mysql-test/r/func_in.result
@@ -587,4 +587,9 @@ SELECT CASE c1 WHEN c1 + 1 THEN 1 END, ABS(AVG(c0)) FROM t1;
 CASE c1 WHEN c1 + 1 THEN 1 END	ABS(AVG(c0))
 NULL	1.0000
 DROP TABLE t1;
+CREATE TABLE t1(a TEXT);
+INSERT INTO t1 VALUES('iynfj');
+SELECT SUM( DISTINCT a ) FROM t1 GROUP BY a HAVING a IN ( AVG( 1 ), 1 + a );
+SUM( DISTINCT a )
+DROP TABLE t1;
 End of 5.1 tests
diff --git a/mysql-test/t/func_in.test b/mysql-test/t/func_in.test
index 3fc1697f146..795a92180db 100644
--- a/mysql-test/t/func_in.test
+++ b/mysql-test/t/func_in.test
@@ -439,4 +439,14 @@ SELECT CASE c1 WHEN c1 + 1 THEN 1 END, ABS(AVG(c0)) FROM t1;
 
 DROP TABLE t1;
 
+#
+# Bug #44399: crash with statement using TEXT columns, aggregates, GROUP BY, 
+# and HAVING
+#
+
+CREATE TABLE t1(a TEXT);
+INSERT INTO t1 VALUES('iynfj');
+SELECT SUM( DISTINCT a ) FROM t1 GROUP BY a HAVING a IN ( AVG( 1 ), 1 + a );
+DROP TABLE t1;
+
 --echo End of 5.1 tests
diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index a9bfea1b806..5017464f968 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -189,6 +189,7 @@ enum_field_types agg_field_type(Item **items, uint nitems)
     collect_cmp_types()
       items             Array of items to collect types from
       nitems            Number of items in the array
+      with_sum_func     a sum function is referenced
 
   DESCRIPTION
     This function collects different result types for comparison of the first
@@ -199,7 +200,7 @@ enum_field_types agg_field_type(Item **items, uint nitems)
     Bitmap of collected types - otherwise
 */
 
-static uint collect_cmp_types(Item **items, uint nitems)
+static uint collect_cmp_types(Item **items, uint nitems, my_bool with_sum_func)
 {
   uint i;
   uint found_types;
@@ -215,6 +216,16 @@ static uint collect_cmp_types(Item **items, uint nitems)
     found_types|= 1<< (uint)item_cmp_type(left_result,
                                            items[i]->result_type());
   }
+  if (with_sum_func || current_thd->lex->current_select->group_list.elements)
+  {
+    /*
+      See TODO commentary in the setup_copy_fields function:
+      item in a group may be wrapped with an Item_copy_string item.
+      That item has a STRING_RESULT result type, so we need
+      to take this type into account.
+     */
+    found_types |= (1 << item_cmp_type(left_result, STRING_RESULT));
+  }
   return found_types;
 }
 
@@ -2722,19 +2733,8 @@ void Item_func_case::fix_length_and_dec()
     for (nagg= 0; nagg < ncases/2 ; nagg++)
       agg[nagg+1]= args[nagg*2];
     nagg++;
-    if (!(found_types= collect_cmp_types(agg, nagg)))
+    if (!(found_types= collect_cmp_types(agg, nagg, with_sum_func)))
       return;
-    if (with_sum_func || current_thd->lex->current_select->group_list.elements)
-    {
-      /*
-        See TODO commentary in the setup_copy_fields function:
-        item in a group may be wrapped with an Item_copy_string item.
-        That item has a STRING_RESULT result type, so we need
-        to take this type into account.
-      */
-      found_types |= (1 << item_cmp_type(left_result_type, STRING_RESULT));
-    }
-
     for (i= 0; i <= (uint)DECIMAL_RESULT; i++)
     {
       if (found_types & (1 << i) && !cmp_items[i])
@@ -3525,7 +3525,7 @@ void Item_func_in::fix_length_and_dec()
   uint type_cnt= 0, i;
   Item_result cmp_type= STRING_RESULT;
   left_result_type= args[0]->result_type();
-  if (!(found_types= collect_cmp_types(args, arg_count)))
+  if (!(found_types= collect_cmp_types(args, arg_count, with_sum_func)))
     return;
   
   for (arg= args + 1, arg_end= args + arg_count; arg != arg_end ; arg++)