bzr commit into mysql-5.1-bugteam branch (joro:2893) Bug#44399

[Georgi Kodinov]

#At file:///Users/kgeorge/mysql/work/B44399-5.1-bugteam/ based on revid:satya.bn@stripped

 2893 Georgi Kodinov	2009-05-12
      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.

    modified:
      mysql-test/r/func_in.result
      mysql-test/t/func_in.test
      sql/item_cmpfunc.cc
=== modified file 'mysql-test/r/func_in.result'
--- a/mysql-test/r/func_in.result	2008-12-31 11:55:04 +0000
+++ b/mysql-test/r/func_in.result	2009-05-12 13:59:17 +0000
@@ -587,4 +587,9 @@ SELECT CASE c1 WHEN c1 + 1 THEN 1 END, A
 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

=== modified file 'mysql-test/t/func_in.test'
--- a/mysql-test/t/func_in.test	2008-12-31 11:55:04 +0000
+++ b/mysql-test/t/func_in.test	2009-05-12 13:59:17 +0000
@@ -439,4 +439,14 @@ SELECT CASE c1 WHEN c1 + 1 THEN 1 END, A
 
 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

=== modified file 'sql/item_cmpfunc.cc'
--- a/sql/item_cmpfunc.cc	2009-05-15 12:57:51 +0000
+++ b/sql/item_cmpfunc.cc	2009-05-12 13:59:17 +0000
@@ -189,6 +189,7 @@ enum_field_types agg_field_type(Item **i
     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 **i
     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 **ite
     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++)


Attachment: [text/bzr-bundle] bzr/[e-mail]