BUG#11869:part 2: post-review fixes: merging into 5.0

We're out of bits in st_select_lex->options so make TMP_TABLE_FORCE_MYISAM ==
OPTION_FOUND_COMMENT (the latter is not used by create_tmp_table).


mysql-test/r/create.result:
  Updated the test result
mysql-test/r/fulltext_order_by.result:
  Drop all tables this test uses
mysql-test/t/fulltext_order_by.test:
  Drop all tables this test uses

9 files changed, 24 insertions, 19 deletions

diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result
index 293be36e5ab..82a5ccc3e82 100644
--- a/mysql-test/r/create.result
+++ b/mysql-test/r/create.result
@@ -606,6 +606,6 @@ create database mysqltest;
 use mysqltest;
 create view v1 as select 'foo' from dual;
 create table t1 like v1;
-ERROR HY000: 'mysqltest.v1' is not a table
+ERROR HY000: 'mysqltest.v1' is not BASE TABLE
 drop view v1;
 drop database mysqltest;
diff --git a/mysql-test/r/fulltext_order_by.result b/mysql-test/r/fulltext_order_by.result
index 5f40e43f0c0..adcceea408e 100644
--- a/mysql-test/r/fulltext_order_by.result
+++ b/mysql-test/r/fulltext_order_by.result
@@ -1,4 +1,4 @@
-DROP TABLE IF EXISTS t1;
+DROP TABLE IF EXISTS t1,t2,t3;
 CREATE TABLE t1 (
 a INT AUTO_INCREMENT PRIMARY KEY,
 message CHAR(20),
diff --git a/mysql-test/t/fulltext_order_by.test b/mysql-test/t/fulltext_order_by.test
index 5bad8161a0a..dcda00fecba 100644
--- a/mysql-test/t/fulltext_order_by.test
+++ b/mysql-test/t/fulltext_order_by.test
@@ -1,5 +1,5 @@
 --disable_warnings
-DROP TABLE IF EXISTS t1;
+DROP TABLE IF EXISTS t1,t2,t3;
 --enable_warnings
 
 CREATE TABLE t1 (
@@ -117,8 +117,6 @@ where
 order by 
   match(b.betreff) against ('+abc' in boolean mode) desc;
 
--- todo psergey: fix
---error 1214 
 select a.text, b.id, b.betreff
 from 
   t2 a inner join t3 b on a.id = b.forum inner join
diff --git a/sql/item_sum.cc b/sql/item_sum.cc
index b669a8c426d..3ba9cd10831 100644
--- a/sql/item_sum.cc
+++ b/sql/item_sum.cc
@@ -2275,7 +2275,8 @@ bool Item_sum_count_distinct::setup(THD *thd)
   DBUG_ASSERT(table == 0);
   if (!(table= create_tmp_table(thd, tmp_table_param, list, (ORDER*) 0, 1,
 				0,
-				select_lex->options | thd->options,
+				(select_lex->options | thd->options) & 
+                                ~TMP_TABLE_FORCE_MYISAM,
 				HA_POS_ERROR, (char*)"")))
     return TRUE;
   table->file->extra(HA_EXTRA_NO_ROWS);		// Don't update rows
@@ -3057,7 +3058,8 @@ bool Item_func_group_concat::setup(THD *thd)
   */
   if (!(table= create_tmp_table(thd, tmp_table_param, all_fields,
                                 (ORDER*) 0, 0, TRUE,
-                                select_lex->options | thd->options,
+                                (select_lex->options | thd->options) & 
+                                ~TMP_TABLE_FORCE_MYISAM,
                                 HA_POS_ERROR, (char*) "")))
     DBUG_RETURN(TRUE);
   table->file->extra(HA_EXTRA_NO_ROWS);
diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h
index 20898a4b654..ef7d6a1da96 100644
--- a/sql/mysql_priv.h
+++ b/sql/mysql_priv.h
@@ -256,6 +256,13 @@ extern CHARSET_INFO *national_charset_info, *table_alias_charset;
 #define OPTION_WARNINGS         (1L << 13)      // THD, user
 #define OPTION_AUTO_IS_NULL     (1L << 14)      // THD, user, binlog
 #define OPTION_FOUND_COMMENT    (1L << 15)      // SELECT, intern, parser
+/* 
+  Force the used temporary table to be a MyISAM table (because we will use
+  fulltext functions when reading from it. This uses the same constant as
+  OPTION_FOUND_COMMENT because we've run out of bits and these two values 
+  are not used together.
+*/
+#define TMP_TABLE_FORCE_MYISAM          (1L << 15)
 #define OPTION_SAFE_UPDATES     (1L << 16)      // THD, user
 #define OPTION_BUFFER_RESULT    (1L << 17)      // SELECT, user
 #define OPTION_BIN_LOG          (1L << 18)      // THD, user
@@ -284,11 +291,6 @@ extern CHARSET_INFO *national_charset_info, *table_alias_charset;
 #define OPTION_SETUP_TABLES_DONE        (1L << 30) // intern
 /* If not set then the thread will ignore all warnings with level notes. */
 #define OPTION_SQL_NOTES                (1L << 31) // THD, user
-/* 
-  Force the used temporary table to be a MyISAM table (because we will use
-  fulltext functions when reading from it.
-*/
-#define TMP_TABLE_FORCE_MYISAM          (1L << 30) 
 
 /*
   Maximum length of time zone name that we support
diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc
index fc9d15e94c4..afcf7dbd93f 100644
--- a/sql/sql_derived.cc
+++ b/sql/sql_derived.cc
@@ -142,7 +142,8 @@ int mysql_derived_prepare(THD *thd, LEX *lex, TABLE_LIST *orig_table_list)
 				  unit->types, (ORDER*) 0,
 				  FALSE, 1,
 				  (first_select->options | thd->options |
-				   TMP_TABLE_ALL_COLUMNS),
+				   TMP_TABLE_ALL_COLUMNS) &
+                                   ~TMP_TABLE_FORCE_MYISAM,
 				  HA_POS_ERROR,
 				  orig_table_list->alias)))
     {
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 3f2f72fb768..a5527d8684c 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -1016,7 +1016,7 @@ JOIN::optimize()
 			    group_list : (ORDER*) 0),
 			   group_list ? 0 : select_distinct,
 			   group_list && simple_group,
-			   select_options,
+			   select_options & ~TMP_TABLE_FORCE_MYISAM,
 			   (order == 0 || skip_sort_order) ? select_limit :
 			   HA_POS_ERROR,
 			   (char *) "")))
@@ -1396,7 +1396,8 @@ JOIN::exec()
 						(ORDER*) 0,
 						curr_join->select_distinct && 
 						!curr_join->group_list,
-						1, curr_join->select_options,
+						1, curr_join->select_options
+                                                & ~TMP_TABLE_FORCE_MYISAM,
 						HA_POS_ERROR,
 						(char *) "")))
 	  DBUG_VOID_RETURN;
diff --git a/sql/sql_show.cc b/sql/sql_show.cc
index 56272b4fdaf..a63ef8d689c 100644
--- a/sql/sql_show.cc
+++ b/sql/sql_show.cc
@@ -3319,7 +3319,8 @@ TABLE *create_schema_table(THD *thd, TABLE_LIST *table_list)
   if (!(table= create_tmp_table(thd, tmp_table_param,
                                 field_list, (ORDER*) 0, 0, 0, 
                                 (select_lex->options | thd->options |
-                                 TMP_TABLE_ALL_COLUMNS),
+                                 TMP_TABLE_ALL_COLUMNS) &
+                                ~TMP_TABLE_FORCE_MYISAM,
                                 HA_POS_ERROR, table_list->alias)))
     DBUG_RETURN(0);
   table_list->schema_table_param= tmp_table_param;
diff --git a/sql/sql_union.cc b/sql/sql_union.cc
index 55c2524ca42..ed77de87fe4 100644
--- a/sql/sql_union.cc
+++ b/sql/sql_union.cc
@@ -296,8 +296,8 @@ bool st_select_lex_unit::prepare(THD *thd_arg, select_result *sel_result,
       }
     }
     
-    ulong create_options= first_select_in_union()->options | thd_arg->options |
-                          TMP_TABLE_ALL_COLUMNS;
+    ulong create_options= (first_select_in_union()->options | thd_arg->options |
+                          TMP_TABLE_ALL_COLUMNS) & ~TMP_TABLE_FORCE_MYISAM;
     /*
       Force the temporary table to be a MyISAM table if we're going to use
       fullext functions (MATCH ... AGAINST .. IN BOOLEAN MODE) when reading
@@ -310,7 +310,7 @@ bool st_select_lex_unit::prepare(THD *thd_arg, select_result *sel_result,
     if (!(table= create_tmp_table(thd_arg,
 				  &union_result->tmp_table_param, types,
 				  (ORDER*) 0, (bool) union_distinct, 1, 
-                                  create_options, HA_POS_ERROR, 
+                                  create_options, HA_POS_ERROR,
                                   (char *) tmp_table_alias)))
       goto err;
     table->file->extra(HA_EXTRA_WRITE_CACHE);