bk commit into 4.1 tree (sergefp:1.2357) BUG#11869

[Sergey Petrunia]

Below is the list of changes that have just been committed into a local
4.1 repository of psergey. When psergey 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
  1.2357 05/08/06 05:19:57 sergefp@stripped +5 -0
  BUG#11869 (cont'd, eliminating "table type doesn't support FULLTEXT" error):
  * added order_uses_ft_func() function that checks if ORDER BY expression uses fulltext 
    functions.
  * When creating temporary table for UNION, force MyISAM table to be created if 
    order_uses_ft_func(<ORDER BY applied after UNION>) == TRUE.

  sql/sql_union.cc
    1.142 05/08/06 05:19:54 sergefp@stripped +46 -0
    BUG#11869 (cont'd, eliminating "table type doesn't support FULLTEXT" error):
    * added order_uses_ft_func() function that checks if ORDER BY expression uses fulltext

      functions.
    * When creating temporary table for UNION, force MyISAM table to be created if 
      order_uses_ft_func(<ORDER BY applied after UNION>) == TRUE.

  sql/sql_select.cc
    1.427 05/08/06 05:19:54 sergefp@stripped +1 -1
    BUG#11869 (cont'd, eliminating "table type doesn't support FULLTEXT" error):
    Added TMP_TABLE_PARAM::force_myisam.

  sql/sql_class.h
    1.282 05/08/06 05:19:54 sergefp@stripped +4 -1
    BUG#11869 (cont'd, eliminating "table type doesn't support FULLTEXT" error):
    In create_tmp_table(), honor TMP_TABLE_PARAM::force_myisam flag.

  mysql-test/t/fulltext_order_by.test
    1.18 05/08/06 05:19:54 sergefp@stripped +11 -0
    Testcase for BUG#11869, part2

  mysql-test/r/fulltext_order_by.result
    1.16 05/08/06 05:19:54 sergefp@stripped +10 -0
    Testcase for BUG#11869, part2

# 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:	sergefp
# Host:	newbox.mylan
# Root:	/home/psergey/mysql-4.1-bug11869-part2

--- 1.281/sql/sql_class.h	2005-08-02 18:57:54 +00:00
+++ 1.282/sql/sql_class.h	2005-08-06 05:19:54 +00:00
@@ -1326,9 +1326,12 @@
   /* If >0 convert all blob fields to varchar(convert_blob_length) */
   uint  convert_blob_length; 
 
+  bool force_myisam;
+
   TMP_TABLE_PARAM()
     :copy_field(0), group_parts(0),
-    group_length(0), group_null_parts(0), convert_blob_length(0)
+    group_length(0), group_null_parts(0), convert_blob_length(0),
+    force_myisam(0)
   {}
   ~TMP_TABLE_PARAM()
   {

--- 1.426/sql/sql_select.cc	2005-08-02 18:58:21 +00:00
+++ 1.427/sql/sql_select.cc	2005-08-06 05:19:54 +00:00
@@ -5290,7 +5290,7 @@
   /* If result table is small; use a heap */
   if (blob_count || using_unique_constraint ||
       (select_options & (OPTION_BIG_TABLES | SELECT_SMALL_RESULT)) ==
-      OPTION_BIG_TABLES)
+      OPTION_BIG_TABLES || param->force_myisam)
   {
     table->file=get_new_handler(table,table->db_type=DB_TYPE_MYISAM);
     if (group &&

--- 1.141/sql/sql_union.cc	2005-03-30 07:13:21 +00:00
+++ 1.142/sql/sql_union.cc	2005-08-06 05:19:54 +00:00
@@ -141,6 +141,44 @@
 }
 
 
+/*
+  Check if the passed item, or any of its arguments use fulltext functions.
+*/
+
+bool item_uses_ft_func(Item *item)
+{
+  if (item->type() == Item::FUNC_ITEM)
+  {
+    Item_func *item_func= (Item_func*)item;
+    if (item_func->functype() == Item_func::FT_FUNC)
+      return TRUE;
+    Item** args, **end;
+    for (args= item_func->arguments(), end= args + item_func->argument_count();
+         args != args; args++)
+    {
+      if (item_uses_ft_func(*args))
+        return TRUE;
+    }
+  }
+  return FALSE;
+}
+
+
+/*
+  Check if the passed ORDER BY ordering uses fulltext functions.
+*/
+
+bool order_uses_ft_func(ORDER* order)
+{
+  for (; order; order= order->next)
+  {
+    if (item_uses_ft_func(order->item_ptr))
+      return TRUE;
+  }
+  return FALSE;
+}
+
+
 int st_select_lex_unit::prepare(THD *thd_arg, select_result *sel_result,
 				ulong additional_options,
                                 const char *tmp_table_alias)
@@ -296,6 +334,14 @@
         goto err;
       }
     }
+    
+    /*
+      Force the temporary table to be a MyISAM table if we're going to use
+      fullext functions (MATCH ... AGAINST .. IN BOOLEAN MODE) when reading
+      from it.
+    */
+    if (order_uses_ft_func((ORDER*)global_parameters->order_list.first))
+      union_result->tmp_table_param.force_myisam= TRUE;
 
     union_result->tmp_table_param.field_count= types.elements;
     if (!(table= create_tmp_table(thd_arg,

--- 1.15/mysql-test/r/fulltext_order_by.result	2005-07-14 15:19:12 +00:00
+++ 1.16/mysql-test/r/fulltext_order_by.result	2005-08-06 05:19:54 +00:00
@@ -159,4 +159,14 @@
 order by 
 match(betreff) against ('+abc' in boolean mode) desc;
 text	id	betreff
+(select b.id, b.betreff from t3 b) union 
+(select b.id, b.betreff from t3 b) 
+order by match(betreff) against ('+abc' in boolean mode) desc;
+id	betreff
+select distinct b.id, b.betreff from t3 b 
+order by match(betreff) against ('+abc' in boolean mode) desc;
+id	betreff
+select b.id, b.betreff from t3 b group by b.id+1 
+order by match(betreff) against ('+abc' in boolean mode) desc;
+id	betreff
 drop table t1,t2,t3;

--- 1.17/mysql-test/t/fulltext_order_by.test	2005-07-28 00:21:41 +00:00
+++ 1.18/mysql-test/t/fulltext_order_by.test	2005-08-06 05:19:54 +00:00
@@ -133,6 +133,17 @@
 order by 
   match(betreff) against ('+abc' in boolean mode) desc;
 
+# BUG#11869 part2: used table type doesn't support FULLTEXT indexes error
+(select b.id, b.betreff from t3 b) union 
+(select b.id, b.betreff from t3 b) 
+order by match(betreff) against ('+abc' in boolean mode) desc;
+
+select distinct b.id, b.betreff from t3 b 
+order by match(betreff) against ('+abc' in boolean mode) desc;
+
+select b.id, b.betreff from t3 b group by b.id+1 
+order by match(betreff) against ('+abc' in boolean mode) desc;
+
 drop table t1,t2,t3;
 
 # End of 4.1 tests