Fixed bug #21727.

This is a performance issue for queries with subqueries evaluation
of which requires filesort.
Allocation of memory for the sort buffer at each evaluation of a
subquery may take a significant amount of time if the buffer is rather big.
With the fix we allocate the buffer at the first evaluation of the
subquery and reuse it at each subsequent evaluation.


mysql-test/r/subselect.result:
  Added a test case for bug #21727.
mysql-test/t/subselect.test:
  Added a test case for bug #21727.
sql/item_subselect.h:
  Fixed bug #21727.
  This is a performance issue for queries with subqueries evaluation
  of which requires filesort.
  Added an implementation for Item_subselect::is_uncacheable()
  returning TRUE if the engine if the subselect is uncacheable.
sql/mysql_priv.h:
  Fixed bug #21727.
  This is a performance issue for queries with subqueries evaluation
  of which requires filesort.
  Added a new boolean parameter to the filesort_free_buffers procedure.
  If the value of this parameter is TRUE the procedure frees the sort_keys
  buffpek buffers.
sql/records.cc:
  Fixed bug #21727.
  This is a performance issue for queries with subqueries evaluation
  of which requires filesort.
  Added a new boolean parameter to the filesort_free_buffers procedure.
  If the value of this parameter is TRUE the procedure frees the sort_keys
  buffpek buffers.
sql/sql_base.cc:
  Fixed bug #21727.
  Made sure that st_table::pos_in_table_list would be always initialized.
sql/sql_select.cc:
  Fixed bug #21727.
  This is a performance issue for queries with subqueries evaluation
  of which requires filesort.
  Added a new boolean parameter to the filesort_free_buffers procedure.
  If the value of this parameter is TRUE the procedure frees the sort_keys
  buffpek buffers.
sql/sql_show.cc:
  Fixed bug #21727.
  This is a performance issue for queries with subqueries evaluation
  of which requires filesort.
  Added a new boolean parameter to the filesort_free_buffers procedure.
  If the value of this parameter is TRUE the procedure frees the sort_keys
  buffpek buffers.
sql/sql_table.cc:
  Fixed bug #21727.
  This is a performance issue for queries with subqueries evaluation
  of which requires filesort.
  Cleanup.
sql/table.cc:
  Fixed bug #21727.
  This is a performance issue for queries with subqueries evaluation
  of which requires filesort.
  Added st_table_list::in_subselect() returning for a table the subselect that 
  contains the FROM list this table is taken from (if there is any).
sql/table.h:
  Fixed bug #21727.
  This is a performance issue for queries with subqueries evaluation
  of which requires filesort.
  Added fields for sort_keys and buffpek buffers to the FILESORT_INFO structure.

12 files changed, 124 insertions, 16 deletions

diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index 57d6199675d..d05ec36a24f 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -3545,3 +3545,19 @@ FROM t1 GROUP BY t1.a LIMIT 1)
 2
 2
 DROP TABLE t1,t2;
+CREATE TABLE t1 (a int, b int auto_increment, PRIMARY KEY (b));
+CREATE TABLE t2 (x int auto_increment, y int, z int,
+PRIMARY KEY (x), FOREIGN KEY (y) REFERENCES t1 (b));
+SET SESSION sort_buffer_size = 32 * 1024;
+SELECT SQL_NO_CACHE COUNT(*) 
+FROM (SELECT  a, b, (SELECT x FROM t2 WHERE y=b ORDER BY z DESC LIMIT 1) c
+FROM t1) t;
+COUNT(*)
+3000
+SET SESSION sort_buffer_size = 8 * 1024 * 1024;
+SELECT SQL_NO_CACHE COUNT(*) 
+FROM (SELECT  a, b, (SELECT x FROM t2 WHERE y=b ORDER BY z DESC LIMIT 1) c
+FROM t1) t;
+COUNT(*)
+3000
+DROP TABLE t1,t2;
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index 6d5082c360b..2f3ae3347e8 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -2426,3 +2426,40 @@ SELECT (
 FROM t1 t2
 GROUP BY t2.a;
 DROP TABLE t1,t2;  
+
+#
+# Bug #21727: Correlated subquery that requires filesort:
+#             slow with big sort_buffer_size 
+#
+
+CREATE TABLE t1 (a int, b int auto_increment, PRIMARY KEY (b));
+CREATE TABLE t2 (x int auto_increment, y int, z int,
+                 PRIMARY KEY (x), FOREIGN KEY (y) REFERENCES t1 (b));
+
+disable_query_log;
+let $1=3000;
+while ($1)
+{
+  eval INSERT INTO t1(a) VALUES(RAND()*1000);
+  eval SELECT MAX(b) FROM t1 INTO @id;
+  let $2=10;
+  while ($2)
+  {
+    eval INSERT INTO t2(y,z) VALUES(@id,RAND()*1000);
+    dec $2;
+  } 
+  dec $1;
+}
+enable_query_log;
+
+SET SESSION sort_buffer_size = 32 * 1024;
+SELECT SQL_NO_CACHE COUNT(*) 
+  FROM (SELECT  a, b, (SELECT x FROM t2 WHERE y=b ORDER BY z DESC LIMIT 1) c
+          FROM t1) t;
+
+SET SESSION sort_buffer_size = 8 * 1024 * 1024;
+SELECT SQL_NO_CACHE COUNT(*) 
+  FROM (SELECT  a, b, (SELECT x FROM t2 WHERE y=b ORDER BY z DESC LIMIT 1) c
+          FROM t1) t;
+
+DROP TABLE t1,t2;
diff --git a/sql/filesort.cc b/sql/filesort.cc
index f13354d5c72..6e74d978eda 100644
--- a/sql/filesort.cc
+++ b/sql/filesort.cc
@@ -109,6 +109,8 @@ ha_rows filesort(THD *thd, TABLE *table, SORT_FIELD *sortorder, uint s_length,
   DBUG_PUSH("");		/* No DBUG here */
 #endif
   FILESORT_INFO table_sort;
+  TABLE_LIST *tab= table->pos_in_table_list;
+  Item_subselect *subselect= tab ? tab->containing_subselect() : 0;
   /* 
     Don't use table->sort in filesort as it is also used by 
     QUICK_INDEX_MERGE_SELECT. Work with a copy and put it back at the end 
@@ -121,7 +123,6 @@ ha_rows filesort(THD *thd, TABLE *table, SORT_FIELD *sortorder, uint s_length,
   my_b_clear(&tempfile);
   my_b_clear(&buffpek_pointers);
   buffpek=0;
-  sort_keys= (uchar **) NULL;
   error= 1;
   bzero((char*) &param,sizeof(param));
   param.sort_length= sortlength(thd, sortorder, s_length, &multi_byte_charset);
@@ -202,13 +203,15 @@ ha_rows filesort(THD *thd, TABLE *table, SORT_FIELD *sortorder, uint s_length,
     ulong old_memavl;
     ulong keys= memavl/(param.rec_length+sizeof(char*));
     param.keys=(uint) min(records+1, keys);
-    if ((sort_keys= (uchar **) make_char_array(param.keys, param.rec_length,
-					       MYF(0))))
+    if (table_sort.sort_keys ||
+        (table_sort.sort_keys= (uchar **) make_char_array(param.keys, param.rec_length,
+                                               MYF(0))))
       break;
     old_memavl=memavl;
     if ((memavl=memavl/4*3) < min_sort_memory && old_memavl > min_sort_memory)
       memavl= min_sort_memory;
   }
+  sort_keys= table_sort.sort_keys;
   if (memavl < min_sort_memory)
   {
     my_error(ER_OUTOFMEMORY,MYF(ME_ERROR+ME_WAITTANG),
@@ -235,8 +238,12 @@ ha_rows filesort(THD *thd, TABLE *table, SORT_FIELD *sortorder, uint s_length,
   }
   else
   {
-    if (!(buffpek=read_buffpek_from_file(&buffpek_pointers, maxbuffer)))
+    if (!table_sort.buffpek && table_sort.buffpek_len < maxbuffer &&
+        !(table_sort.buffpek=
+          (byte *) read_buffpek_from_file(&buffpek_pointers, maxbuffer)))
       goto err;
+    buffpek= (BUFFPEK *) table_sort.buffpek;
+    table_sort.buffpek_len= maxbuffer;
     close_cached_file(&buffpek_pointers);
 	/* Open cached file if it isn't open */
     if (! my_b_inited(outfile) &&
@@ -269,8 +276,14 @@ ha_rows filesort(THD *thd, TABLE *table, SORT_FIELD *sortorder, uint s_length,
  err:
   if (param.tmp_buffer)
     x_free(param.tmp_buffer);
-  x_free((gptr) sort_keys);
-  x_free((gptr) buffpek);
+  if (!subselect || !subselect->is_uncacheable())
+  {
+    x_free((gptr) sort_keys);
+    table_sort.sort_keys= 0;
+    x_free((gptr) buffpek);
+    table_sort.buffpek= 0;
+    table_sort.buffpek_len= 0;
+  }
   close_cached_file(&tempfile);
   close_cached_file(&buffpek_pointers);
   if (my_b_inited(outfile))
@@ -301,13 +314,27 @@ ha_rows filesort(THD *thd, TABLE *table, SORT_FIELD *sortorder, uint s_length,
 } /* filesort */
 
 
-void filesort_free_buffers(TABLE *table)
+void filesort_free_buffers(TABLE *table, bool full)
 {
   if (table->sort.record_pointers)
   {
     my_free((gptr) table->sort.record_pointers,MYF(0));
     table->sort.record_pointers=0;
   }
+  if (full)
+  {
+    if (table->sort.sort_keys )
+    {
+      x_free((gptr) table->sort.sort_keys);
+      table->sort.sort_keys= 0;
+    }
+    if (table->sort.buffpek)
+    {
+      x_free((gptr) table->sort.buffpek);
+      table->sort.buffpek= 0;
+      table->sort.buffpek_len= 0;
+    }
+  }
   if (table->sort.addon_buf)
   {
     my_free((char *) table->sort.addon_buf, MYF(0));
diff --git a/sql/item_subselect.h b/sql/item_subselect.h
index 45df4f3880d..1a8111069e6 100644
--- a/sql/item_subselect.h
+++ b/sql/item_subselect.h
@@ -114,6 +114,7 @@ public:
     single select and union subqueries only.
   */
   bool is_evaluated() const;
+  bool is_uncacheable() const;
 
   /*
     Used by max/min subquery to initialize value presence registration
@@ -428,3 +429,9 @@ inline bool Item_subselect::is_evaluated() const
   return engine->is_executed();
 }
 
+inline bool Item_subselect::is_uncacheable() const
+{
+  return engine->uncacheable();
+}
+
+
diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h
index ea3b3a9bd83..13e44b49b53 100644
--- a/sql/mysql_priv.h
+++ b/sql/mysql_priv.h
@@ -1465,7 +1465,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 filesort_free_buffers(TABLE *table, bool full);
 void change_double_for_sort(double nr,byte *to);
 double my_double_round(double value, int dec, bool truncate);
 int get_quick_record(SQL_SELECT *select);
diff --git a/sql/records.cc b/sql/records.cc
index b352f9f395a..4fcbc25c10f 100644
--- a/sql/records.cc
+++ b/sql/records.cc
@@ -193,7 +193,7 @@ void end_read_record(READ_RECORD *info)
   }
   if (info->table)
   {
-    filesort_free_buffers(info->table);
+    filesort_free_buffers(info->table,0);
     (void) info->file->extra(HA_EXTRA_NO_CACHE);
     if (info->read_record != rr_quick) // otherwise quick_range does it
       (void) info->file->ha_index_or_rnd_end();
diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index 0939fb3a47e..3984ceac6a9 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -1478,6 +1478,7 @@ TABLE *open_table(THD *thd, TABLE_LIST *table_list, MEM_ROOT *mem_root,
   table->file->ft_handler= 0;
   if (table->timestamp_field)
     table->timestamp_field_type= table->timestamp_field->get_auto_set_type();
+  table->pos_in_table_list= table_list;
   table_list->updatable= 1; // It is not derived table nor non-updatable VIEW
   DBUG_ASSERT(table->key_read == 0);
   DBUG_RETURN(table);
@@ -2762,6 +2763,7 @@ TABLE *open_temporary_table(THD *thd, const char *path, const char *db,
     if (thd->slave_thread)
       slave_open_temp_tables++;
   }
+  tmp_table->pos_in_table_list= 0;
   DBUG_RETURN(tmp_table);
 }
 
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 1dce7390ef1..d00795e0b14 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -1260,14 +1260,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);
+    filesort_free_buffers(exec_tmp_table1,0);
   }
   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);
+    filesort_free_buffers(exec_tmp_table2,0);
   }
   if (items0)
     set_items_ref_array(items0);
@@ -6066,7 +6066,7 @@ void JOIN::cleanup(bool full)
     if (tables > const_tables) // Test for not-const tables
     {
       free_io_cache(table[const_tables]);
-      filesort_free_buffers(table[const_tables]);
+      filesort_free_buffers(table[const_tables],full);
     }
 
     if (full)
diff --git a/sql/sql_show.cc b/sql/sql_show.cc
index eb78f4fbdae..6367be4a1d4 100644
--- a/sql/sql_show.cc
+++ b/sql/sql_show.cc
@@ -3977,7 +3977,7 @@ bool get_schema_tables_result(JOIN *join)
         table_list->table->file->extra(HA_EXTRA_RESET_STATE);
         table_list->table->file->delete_all_rows();
         free_io_cache(table_list->table);
-        filesort_free_buffers(table_list->table);
+        filesort_free_buffers(table_list->table,1);
         table_list->table->null_row= 0;
       }
       else
diff --git a/sql/sql_table.cc b/sql/sql_table.cc
index 8864cf3c8bc..2803bfb9917 100644
--- a/sql/sql_table.cc
+++ b/sql/sql_table.cc
@@ -2266,7 +2266,6 @@ static bool mysql_admin_table(THD* thd, TABLE_LIST* tables,
       goto send_result;
     }
 
-    table->table->pos_in_table_list= table;
     if ((table->table->db_stat & HA_READ_ONLY) && open_for_modify)
     {
       char buff[FN_REFLEN + MYSQL_ERRMSG_SIZE];
@@ -4256,8 +4255,6 @@ bool mysql_checksum_table(THD *thd, TABLE_LIST *tables, HA_CHECK_OPT *check_opt)
     }
     else
     {
-      t->pos_in_table_list= table;
-
       if (t->file->table_flags() & HA_HAS_CHECKSUM &&
 	  !(check_opt->flags & T_EXTEND))
 	protocol->store((ulonglong)t->file->checksum());
diff --git a/sql/table.cc b/sql/table.cc
index 4dd3494f834..851b747dc83 100644
--- a/sql/table.cc
+++ b/sql/table.cc
@@ -3018,6 +3018,23 @@ void st_table_list::reinit_before_use(THD *thd)
          embedding->nested_join->join_list.head() == embedded);
 }
 
+/*
+  Return subselect that contains the FROM list this table is taken from
+
+  SYNOPSIS
+    st_table_list::containing_subselect()
+ 
+  RETURN
+    Subselect item for the subquery that contains the FROM list
+    this table is taken from if there is any
+    0 - otherwise
+
+*/
+
+Item_subselect *st_table_list::containing_subselect()
+{    
+  return (select_lex ? select_lex->master_unit()->item : 0);
+}
 
 /*****************************************************************************
 ** Instansiate templates
diff --git a/sql/table.h b/sql/table.h
index 5136ac2c4db..f0190353328 100644
--- a/sql/table.h
+++ b/sql/table.h
@@ -18,6 +18,7 @@
 /* Structs that defines the TABLE */
 
 class Item;				/* Needed by ORDER */
+class Item_subselect;
 class GRANT_TABLE;
 class st_select_lex_unit;
 class st_select_lex;
@@ -68,6 +69,9 @@ enum frm_type_enum
 typedef struct st_filesort_info
 {
   IO_CACHE *io_cache;           /* If sorted through filebyte                */
+  uchar   **sort_keys;          /* Buffer for sorting keys                   */
+  byte     *buffpek;            /* Buffer for buffpek structures             */
+  uint      buffpek_len;        /* Max number of buffpeks in the buffer      */
   byte     *addon_buf;          /* Pointer to a buffer if sorted with fields */
   uint      addon_length;       /* Length of the buffer                      */
   struct st_sort_addon_field *addon_field;     /* Pointer to the fields info */
@@ -678,6 +682,7 @@ typedef struct st_table_list
     procedure.
   */
   void reinit_before_use(THD *thd);
+  Item_subselect *containing_subselect();
 
 private:
   bool prep_check_option(THD *thd, uint8 check_opt_type);