Removed compiler warnings

Fixed memory leak in new filesort code
Optimzed sub selects to use keys with outer references.
Increased max tables in join to 62


client/Makefile.am:
  Remve test programs
myisam/mi_check.c:
  Remove compiler warnings
myisam/mi_delete.c:
  Remove 'rnd' variable to make usage repeatable
myisam/mi_open.c:
  Remove 'rnd' variable to make usage repeatable
myisam/mi_write.c:
  Remove 'rnd' variable to make usage repeatable
myisam/myisamdef.h:
  Remove 'rnd' variable to make usage repeatable
myisam/myisamlog.c:
  Remove 'rnd' variable to make usage repeatable
mysql-test/r/subselect.result:
  new test
mysql-test/t/join.test:
  Updated test
mysql-test/t/subselect.test:
  new test
sql/filesort.cc:
  Added function to free buffers allocated by filesort
sql/item.cc:
  Sub select optimization
sql/item_cmpfunc.cc:
  Sub select optimization
sql/item_subselect.cc:
  Sub select optimization
sql/item_sum.cc:
  Removed compiler warnings
sql/item_sum.h:
  Simple code cleanup
sql/log.cc:
  Removed compiler warning
sql/mysql_priv.h:
  Made table_map ulonglong to allow 62 tables in join
sql/records.cc:
  Moved free of filesort buffers to own function
sql/sql_select.cc:
  subselect optimization
  Call filesort_free_buffers() to free memory from filesort
sql/unireg.h:
  Sub select optimization

11 files changed, 81 insertions, 73 deletions

diff --git a/sql/filesort.cc b/sql/filesort.cc
index 928138b8d48..a3d24cd9242 100644
--- a/sql/filesort.cc
+++ b/sql/filesort.cc
@@ -262,6 +262,22 @@ ha_rows filesort(THD *thd, TABLE *table, SORT_FIELD *sortorder, uint s_length,
 } /* filesort */
 
 
+void filesort_free_buffers(TABLE *table)
+{
+  if (table->sort.record_pointers)
+  {
+    my_free((gptr) table->sort.record_pointers,MYF(0));
+    table->sort.record_pointers=0;
+  }
+  if (table->sort.addon_buf)
+  {
+    my_free((char *) table->sort.addon_buf, MYF(0));
+    my_free((char *) table->sort.addon_field, MYF(MY_ALLOW_ZERO_PTR));
+    table->sort.addon_buf=0;
+    table->sort.addon_field=0;
+  }
+}
+
 	/* Make a array of string pointers */
 
 static char **make_char_array(register uint fields, uint length, myf my_flag)
diff --git a/sql/item.cc b/sql/item.cc
index 7dd8392b695..c7df0e5f00b 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -349,7 +349,7 @@ table_map Item_field::used_tables() const
 {
   if (field->table->const_table)
     return 0;					// const item
-  return (depended_from ? RAND_TABLE_BIT : field->table->map);
+  return (depended_from ? OUTER_REF_TABLE_BIT : field->table->map);
 }
 
 Item *Item_field::get_tmp_table_item(THD *thd)
diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index 0f21cf5a774..7cc07690fcc 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -346,7 +346,7 @@ bool Item_in_optimizer::fix_fields(THD *thd, struct st_table_list *tables,
   if (cache->cols() == 1)
   {
     if (args[0]->used_tables())
-      cache->set_used_tables(RAND_TABLE_BIT);
+      cache->set_used_tables(OUTER_REF_TABLE_BIT);
     else
       cache->set_used_tables(0);
   }
@@ -356,7 +356,7 @@ bool Item_in_optimizer::fix_fields(THD *thd, struct st_table_list *tables,
     for (uint i= 0; i < n; i++)
     {
       if (args[0]->el(i)->used_tables())
-	((Item_cache *)cache->el(i))->set_used_tables(RAND_TABLE_BIT);
+	((Item_cache *)cache->el(i))->set_used_tables(OUTER_REF_TABLE_BIT);
       else
 	((Item_cache *)cache->el(i))->set_used_tables(0);
     }
diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index 6c0b799b4de..c749fba616f 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -128,7 +128,7 @@ void Item_subselect::fix_length_and_dec()
 inline table_map Item_subselect::used_tables() const
 {
   return (table_map) (engine->dependent() ? 1L :
-		      (engine->uncacheable() ? RAND_TABLE_BIT : 0L));
+		      (engine->uncacheable() ? OUTER_REF_TABLE_BIT : 0L));
 }
 
 Item_singlerow_subselect::Item_singlerow_subselect(THD *thd,
diff --git a/sql/item_sum.cc b/sql/item_sum.cc
index 5a758edc6c3..6dbfc236158 100644
--- a/sql/item_sum.cc
+++ b/sql/item_sum.cc
@@ -1348,7 +1348,7 @@ static int group_concat_key_cmp_with_distinct(void* arg, byte* key1,
 					      byte* key2)
 {
   Item_func_group_concat* item= (Item_func_group_concat*)arg;
-  for (int i= 0; i<item->arg_count_field; i++)
+  for (uint i= 0; i < item->arg_count_field; i++)
   {
     Item *field_item= item->expr[i];
     Field *field= field_item->tmp_table_field();
@@ -1377,7 +1377,7 @@ static int group_concat_key_cmp_with_distinct(void* arg, byte* key1,
 static int group_concat_key_cmp_with_order(void* arg, byte* key1, byte* key2)
 {
   Item_func_group_concat* item= (Item_func_group_concat*)arg;
-  for (int i=0; i<item->arg_count_order; i++)
+  for (uint i=0; i < item->arg_count_order; i++)
   {
     ORDER *order_item= item->order[i];
     Item *item= *order_item->item;
@@ -1428,7 +1428,7 @@ static int dump_leaf_key(byte* key, uint32 count __attribute__((unused)),
 
   tmp.length(0);
   
-  for (int i= 0; i < group_concat_item->arg_show_fields; i++)
+  for (uint i= 0; i < group_concat_item->arg_show_fields; i++)
   {
     Item *show_item= group_concat_item->expr[i];
     if (!show_item->const_item())
@@ -1482,13 +1482,13 @@ static int dump_leaf_key(byte* key, uint32 count __attribute__((unused)),
   is_separator - string value of separator
 */
 
-Item_func_group_concat::Item_func_group_concat(int is_distinct,
+Item_func_group_concat::Item_func_group_concat(bool is_distinct,
 					       List<Item> *is_select,
 					       SQL_LIST *is_order,
 					       String *is_separator)
   :Item_sum(), tmp_table_param(0), warning_available(false),
-   separator(is_separator), tree(&tree_base), table(0), distinct(is_distinct),
-   tree_mode(0), count_cut_values(0)
+   separator(is_separator), tree(&tree_base), table(0), 
+   count_cut_values(0), tree_mode(0), distinct(is_distinct)
 {
   original= 0;
   quick_group= 0;
@@ -1507,39 +1507,31 @@ Item_func_group_concat::Item_func_group_concat(int is_distinct,
     expr - arg_count_field
     order - arg_count_order
   */
-  args= (Item**)sql_alloc(sizeof(Item*)*(arg_count+arg_count_order+arg_count_field)+
-                          sizeof(ORDER*)*arg_count_order);
+  args= (Item**) sql_alloc(sizeof(Item*)*(arg_count+arg_count_order+
+					  arg_count_field)+
+			   sizeof(ORDER*)*arg_count_order);
   if (!args)
-  {
-    my_error(ER_OUTOFMEMORY,MYF(0));
-  } 
+    return;					// thd->fatal is set
   expr= args;
   expr+= arg_count+arg_count_order;
-  if (arg_count_order) 
-  {
-    order= (ORDER**)(expr + arg_count_field);
-  }
-  /*
-    fill args items of show and sort
-  */
+
+  /* fill args items of show and sort */
   int i= 0;
   List_iterator_fast<Item> li(*is_select);
   Item *item_select;
 
-  while ((item_select= li++))
-  {
+  for ( ; (item_select= li++) ; i++)
     args[i]= expr[i]= item_select;
-    i++;
-  }
-      
-  if (order)
+
+  if (arg_count_order) 
   {
-    uint j= 0;	
-    for (ORDER *order_item= (ORDER*)is_order->first;
+    i= 0;
+    order= (ORDER**)(expr + arg_count_field);
+    for (ORDER *order_item= (ORDER*) is_order->first;
                 order_item != NULL;
                 order_item= order_item->next)
     {
-      order[j++]= order_item;
+      order[i++]= order_item;
     }
   }
 }
@@ -1562,8 +1554,7 @@ Item_func_group_concat::~Item_func_group_concat()
     }
     if (table)
       free_tmp_table(thd, table);
-    if (tmp_table_param)
-      delete tmp_table_param;
+    delete tmp_table_param;
     if (tree_mode)
       delete_tree(tree); 
   }
@@ -1594,14 +1585,17 @@ bool Item_func_group_concat::add()
   copy_funcs(tmp_table_param->items_to_copy);
 
   bool record_is_null= TRUE;
-  for (int i= 0; i < arg_show_fields; i++)
+  for (uint i= 0; i < arg_show_fields; i++)
   {
     Item *show_item= expr[i];
     if (!show_item->const_item())
     {
       Field *f= show_item->tmp_table_field();
       if (!f->is_null())
+      {
         record_is_null= FALSE;      
+	break;
+      }
     }
   }
   if (record_is_null)
diff --git a/sql/item_sum.h b/sql/item_sum.h
index 1b3e993fffc..37d7e7f79d0 100644
--- a/sql/item_sum.h
+++ b/sql/item_sum.h
@@ -643,21 +643,20 @@ class Item_func_group_concat : public Item_sum
  public:
   String result;
   String *separator;
-  uint show_elements;
   TREE tree_base;
   TREE *tree;
   TABLE *table;
-  int arg_count_order;
-  int arg_count_field;
-  int arg_show_fields;
-  int distinct;
   Item **expr;
   ORDER **order;
-  bool tree_mode;
-  int count_cut_values;
+  TABLE_LIST *tables_list;
   ulong group_concat_max_len;
+  uint show_elements;
+  uint arg_count_order;
+  uint arg_count_field;
+  uint arg_show_fields;
+  uint count_cut_values;
+  bool tree_mode, distinct;
   bool warning_for_row;
-  TABLE_LIST *tables_list;
   bool always_null;
   /*
     Following is 0 normal object and pointer to original one for copy 
@@ -665,7 +664,7 @@ class Item_func_group_concat : public Item_sum
   */
   Item_func_group_concat *original;
   
-  Item_func_group_concat(int is_distinct,List<Item> *is_select,
+  Item_func_group_concat(bool is_distinct,List<Item> *is_select,
                          SQL_LIST *is_order,String *is_separator);
 			 
   Item_func_group_concat(THD *thd, Item_func_group_concat &item)
@@ -675,20 +674,20 @@ class Item_func_group_concat : public Item_sum
      warning(item.warning),
      warning_available(item.warning_available),
      separator(item.separator),
-     show_elements(item.show_elements),
      tree(item.tree),      
      table(item.table),
+     expr(item.expr),
+     order(item.order),
+     tables_list(item.tables_list),
+     group_concat_max_len(item.group_concat_max_len),
+     show_elements(item.show_elements),
      arg_count_order(item.arg_count_order),
      arg_count_field(item.arg_count_field),
      arg_show_fields(item.arg_show_fields),
-     distinct(item.distinct),
-     expr(item.expr),
-     order(item.order),
-     tree_mode(0),
      count_cut_values(item.count_cut_values),
-     group_concat_max_len(item.group_concat_max_len),
+     tree_mode(0),
+     distinct(item.distinct),
      warning_for_row(item.warning_for_row),
-     tables_list(item.tables_list),
      original(&item)
     {
      quick_group = 0;
diff --git a/sql/log.cc b/sql/log.cc
index 50471041ee1..c9e20bc0cc9 100644
--- a/sql/log.cc
+++ b/sql/log.cc
@@ -767,6 +767,7 @@ int MYSQL_LOG::purge_logs(const char *to_log,
          !log_in_use(log_info.log_file_name))
   {
     ulong tmp;
+    LINT_INIT(tmp);
     if (decrease_log_space) //stat the file we want to delete
     {
       MY_STAT s;
diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h
index d17faa3cea5..4ecccbf4511 100644
--- a/sql/mysql_priv.h
+++ b/sql/mysql_priv.h
@@ -30,7 +30,7 @@
 #undef write  /* remove pthread.h macro definition for EMX */
 #endif
 
-typedef ulong table_map;		/* Used for table bits in join */
+typedef ulonglong table_map;		/* Used for table bits in join */
 typedef ulong key_map;			/* Used for finding keys */
 typedef ulong key_part_map;		/* Used for finding key parts */
 
@@ -846,6 +846,7 @@ void end_read_record(READ_RECORD *info);
 ha_rows filesort(THD *thd, TABLE *form,struct st_sort_field *sortorder,
 		 uint s_length, SQL_SELECT *select,
 		 ha_rows max_rows, ha_rows *examined_rows);
+void filesort_free_buffers(TABLE *table);
 void change_double_for_sort(double nr,byte *to);
 int get_quick_record(SQL_SELECT *select);
 int calc_weekday(long daynr,bool sunday_first_day_of_week);
diff --git a/sql/records.cc b/sql/records.cc
index e6c6e62a516..9d8627bc1fc 100644
--- a/sql/records.cc
+++ b/sql/records.cc
@@ -134,19 +134,7 @@ void end_read_record(READ_RECORD *info)
   }
   if (info->table)
   {
-    TABLE *table= info->table;
-    if (table->sort.record_pointers)
-    {
-      my_free((gptr) table->sort.record_pointers,MYF(0));
-      table->sort.record_pointers=0;
-    }
-    if (table->sort.addon_buf)
-    {
-      my_free((char *) table->sort.addon_buf, MYF(0));
-      my_free((char *) table->sort.addon_field, MYF(MY_ALLOW_ZERO_PTR));
-      table->sort.addon_buf=0;
-      table->sort.addon_field=0;
-    }
+    filesort_free_buffers(info->table);
     (void) info->file->extra(HA_EXTRA_NO_CACHE);
     (void) info->file->rnd_end();
     info->table=0;
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 18768099f0f..176b619b0e1 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -868,12 +868,14 @@ JOIN::reinit()
     exec_tmp_table1->file->extra(HA_EXTRA_RESET_STATE);
     exec_tmp_table1->file->delete_all_rows();
     free_io_cache(exec_tmp_table1);
+    filesort_free_buffers(exec_tmp_table1);
   }
   if (exec_tmp_table2)
   {
     exec_tmp_table2->file->extra(HA_EXTRA_RESET_STATE);
     exec_tmp_table2->file->delete_all_rows();
     free_io_cache(exec_tmp_table2);
+    filesort_free_buffers(exec_tmp_table2);
   }
   if (items0)
     memcpy(ref_pointer_array, items0, ref_pointer_array_size);
@@ -2319,7 +2321,8 @@ find_best(JOIN *join,table_map rest_tables,uint idx,double record_count,
 		  we don't make rec less than 100.
 		*/
 		if (keyuse->used_tables &
-		    (map=(keyuse->used_tables & ~join->const_table_map)))
+		    (map=(keyuse->used_tables & ~join->const_table_map &
+			  ~OUTER_REF_TABLE_BIT)))
 		{
 		  uint tablenr;
 		  for (tablenr=0 ; ! (map & 1) ; map>>=1, tablenr++) ;
@@ -2631,7 +2634,7 @@ static double
 prev_record_reads(JOIN *join,table_map found_ref)
 {
   double found=1.0;
-
+  found_ref&= ~OUTER_REF_TABLE_BIT;
   for (POSITION *pos=join->positions ; found_ref ; pos++)
   {
     if (pos->table->table->map & found_ref)
@@ -2665,7 +2668,7 @@ get_best_combination(JOIN *join)
 
   join->full_join=0;
 
-  used_tables=0;
+  used_tables= OUTER_REF_TABLE_BIT;		// Outer row is already read
   for (j=join_tab, tablenr=0 ; tablenr < table_count ; tablenr++,j++)
   {
     TABLE *form;
@@ -2936,7 +2939,8 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond)
 	DBUG_RETURN(1);				// Impossible const condition
       }
     }
-    used_tables=(select->const_tables=join->const_table_map) | RAND_TABLE_BIT;
+    used_tables=((select->const_tables=join->const_table_map) |
+		 OUTER_REF_TABLE_BIT | RAND_TABLE_BIT);
     for (uint i=join->const_tables ; i < join->tables ; i++)
     {
       JOIN_TAB *tab=join->join_tab+i;
@@ -2946,7 +2950,7 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond)
 	It solve problem with select like SELECT * FROM t1 WHERE rand() > 0.5
       */
       if (i == join->tables-1)
-	current_map|= RAND_TABLE_BIT;
+	current_map|= OUTER_REF_TABLE_BIT | RAND_TABLE_BIT;
       bool use_quick_range=0;
       used_tables|=current_map;
 
@@ -3265,7 +3269,10 @@ join_free(JOIN *join, bool full)
       first non const table in join->table
     */
     if (join->tables > join->const_tables) // Test for not-const tables
+    {
       free_io_cache(join->table[join->const_tables]);
+      filesort_free_buffers(join->table[join->const_tables]);
+    }
     if (join->select_lex->dependent && !full)
     {
       for (tab=join->join_tab,end=tab+join->tables ; tab != end ; tab++)
@@ -3450,7 +3457,8 @@ static void update_depend_map(JOIN *join, ORDER *order)
     table_map depend_map;
     order->item[0]->update_used_tables();
     order->depend_map=depend_map=order->item[0]->used_tables();
-    if (!(order->depend_map & RAND_TABLE_BIT))	// Not item_sum() or RAND()
+    // Not item_sum(), RAND() and no reference to table outside of sub select
+    if (!(order->depend_map & (OUTER_REF_TABLE_BIT | RAND_TABLE_BIT)))
     {
       for (JOIN_TAB **tab=join->map2table;
 	   depend_map ;
@@ -3497,7 +3505,7 @@ remove_const(JOIN *join,ORDER *first_order, COND *cond, bool *simple_order)
     }
     else
     {
-      if (order_tables & RAND_TABLE_BIT)
+      if (order_tables & (RAND_TABLE_BIT | OUTER_REF_TABLE_BIT))
 	*simple_order=0;
       else
       {
@@ -7428,7 +7436,7 @@ get_sort_by_table(ORDER *a,ORDER *b,TABLE_LIST *tables)
       DBUG_RETURN(0);
     map|=a->item[0]->used_tables();
   }
-  if (!map || (map & RAND_TABLE_BIT))
+  if (!map || (map & (RAND_TABLE_BIT | OUTER_REF_TABLE_BIT)))
     DBUG_RETURN(0);
 
   for (; !(map & tables->table->map) ; tables=tables->next) ;
diff --git a/sql/unireg.h b/sql/unireg.h
index 9430329e67a..e647606875f 100644
--- a/sql/unireg.h
+++ b/sql/unireg.h
@@ -57,7 +57,8 @@
 #endif
 
 #define MAX_FIELD_WIDTH 256			/* Max column width +1 */
-#define MAX_TABLES	(sizeof(table_map)*8-1)	/* Max tables in join */
+#define MAX_TABLES	(sizeof(table_map)*8-2)	/* Max tables in join */
+#define OUTER_REF_TABLE_BIT	(((table_map) 1) << (sizeof(table_map)*8-2))
 #define RAND_TABLE_BIT	(((table_map) 1) << (sizeof(table_map)*8-1))
 #define MAX_FIELDS	4096			/* Limit in the .frm file */