summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/subselect.result16
-rw-r--r--mysql-test/t/subselect.test37
-rw-r--r--sql/filesort.cc41
-rw-r--r--sql/item_subselect.h7
-rw-r--r--sql/mysql_priv.h2
-rw-r--r--sql/records.cc2
-rw-r--r--sql/sql_base.cc2
-rw-r--r--sql/sql_select.cc6
-rw-r--r--sql/sql_show.cc2
-rw-r--r--sql/sql_table.cc3
-rw-r--r--sql/table.cc17
-rw-r--r--sql/table.h5
12 files changed, 124 insertions, 16 deletions
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index 82c70e19f9c..131f652a178 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -3560,3 +3560,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 29316e6e6a0..051a64b9f9a 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -2443,3 +2443,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 b063b416191..12b17846fe4 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 9410dbc465e..0c6ba055096 100644
--- a/sql/item_subselect.h
+++ b/sql/item_subselect.h
@@ -117,6 +117,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
@@ -482,3 +483,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 1636b5a31e4..a556f190fdf 100644
--- a/sql/mysql_priv.h
+++ b/sql/mysql_priv.h
@@ -1475,7 +1475,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 3e254fa3648..78473659b22 100644
--- a/sql/records.cc
+++ b/sql/records.cc
@@ -194,7 +194,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 df333e7c9ab..f92217302f8 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -1299,14 +1299,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);
@@ -6150,7 +6150,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 4469bbacf90..d859db03437 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 f6dbba4aa79..3fc74ad1371 100644
--- a/sql/sql_table.cc
+++ b/sql/sql_table.cc
@@ -2267,7 +2267,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];
@@ -4257,8 +4256,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 3a76ba0cc1a..9aecf28ab8c 100644
--- a/sql/table.cc
+++ b/sql/table.cc
@@ -3032,6 +3032,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);
View Lorry Controller Status