From: kgeorge Date: October 17 2006 1:20pm Subject: bk commit into 5.0 tree (gkodinov:1.2261) BUG#21798 List-Archive: http://lists.mysql.com/commits/13802 X-Bug: 21798 Message-Id: <20061017132045.E610866E13E@macbook.gmz> Below is the list of changes that have just been committed into a local 5.0 repository of kgeorge. When kgeorge 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@stripped, 2006-10-17 16:20:26+03:00, gkodinov@stripped +8 -0 Bug#21798: memory leak during query execution with subquery in column list using a function When executing dependent subqueries they are re-inited and re-exec() for each row of the outer context. The cause for the bug is that during subquery reinitialization/re-execution, the optimizer reallocates JOIN::join_tab, JOIN::table in make_simple_join() and the local variable in 'sortorder' in create_sort_index(), which is allocated by make_unireg_sortorder(). Care must be taken not to allocate anything into the thread's memory pool while re-initializing query plan structures between subquery re-executions. All such items mush be cached and reused because the thread's memory pool is freed at the end of the whole query. Note that they must be cached and reused even for queries that are not otherwise cacheable because otherwise it will grow the thread's memory pool every time a cacheable query is re-executed. We provide additional members to the JOIN structure to store references to the items that need to be cached. mysql-test/r/subselect.result@stripped, 2006-10-17 16:20:16+03:00, gkodinov@stripped +26 -0 Bug#21798: memory leak during query execution with subquery in column list using a function - test case mysql-test/t/subselect.test@stripped, 2006-10-17 16:20:16+03:00, gkodinov@stripped +26 -0 Bug#21798: memory leak during query execution with subquery in column list using a function - test case sql/mysql_priv.h@stripped, 2006-10-17 16:20:17+03:00, gkodinov@stripped +2 -1 Bug#21798: memory leak during query execution with subquery in column list using a function - cache the entities allocated in the threads memory pool by JOIN::exec (). sql/sql_delete.cc@stripped, 2006-10-17 16:20:18+03:00, gkodinov@stripped +1 -1 Bug#21798: memory leak during query execution with subquery in column list using a function - cache the SORT_ORDER, TABLE * and JOIN_TAB allocated in the thread's memory pool by JOIN::exec (). sql/sql_select.cc@stripped, 2006-10-17 16:20:18+03:00, gkodinov@stripped +32 -8 Bug#21798: memory leak during query execution with subquery in column list using a function - cache the SORT_ORDER, TABLE * and JOIN_TAB allocated in the thread's memory pool by JOIN::exec (). sql/sql_select.h@stripped, 2006-10-17 16:20:19+03:00, gkodinov@stripped +15 -0 Bug#21798: memory leak during query execution with subquery in column list using a function - cache the SORT_ORDER, TABLE * and JOIN_TAB allocated in the thread's memory pool by JOIN::exec (). sql/sql_table.cc@stripped, 2006-10-17 16:20:20+03:00, gkodinov@stripped +1 -1 Bug#21798: memory leak during query execution with subquery in column list using a function - cache the SORT_ORDER, TABLE * and JOIN_TAB allocated in the thread's memory pool by JOIN::exec (). sql/sql_update.cc@stripped, 2006-10-17 16:20:20+03:00, gkodinov@stripped +1 -1 Bug#21798: memory leak during query execution with subquery in column list using a function - cache the SORT_ORDER, TABLE * and JOIN_TAB allocated in the thread's memory pool by JOIN::exec (). # 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: gkodinov # Host: macbook.gmz # Root: /Users/kgeorge/mysql/work/B21798-5.0-opt --- 1.407/sql/mysql_priv.h 2006-10-17 16:20:43 +03:00 +++ 1.408/sql/mysql_priv.h 2006-10-17 16:20:43 +03:00 @@ -708,7 +708,8 @@ bool mysql_xa_recover(THD *thd); bool check_simple_select(); -SORT_FIELD * make_unireg_sortorder(ORDER *order, uint *length); +SORT_FIELD * make_unireg_sortorder(ORDER *order, uint *length, + SORT_FIELD *sortorder); int setup_order(THD *thd, Item **ref_pointer_array, TABLE_LIST *tables, List &fields, List &all_fields, ORDER *order); int setup_group(THD *thd, Item **ref_pointer_array, TABLE_LIST *tables, --- 1.178/sql/sql_delete.cc 2006-10-17 16:20:43 +03:00 +++ 1.179/sql/sql_delete.cc 2006-10-17 16:20:43 +03:00 @@ -167,7 +167,7 @@ bool mysql_delete(THD *thd, TABLE_LIST * MYF(MY_FAE | MY_ZEROFILL)); if (!(sortorder= make_unireg_sortorder((ORDER*) order->first, - &length)) || + &length, NULL)) || (table->sort.found_records = filesort(thd, table, sortorder, length, select, HA_POS_ERROR, &examined_rows)) --- 1.450/sql/sql_select.cc 2006-10-17 16:20:44 +03:00 +++ 1.451/sql/sql_select.cc 2006-10-17 16:20:44 +03:00 @@ -1574,6 +1574,7 @@ JOIN::exec() { DBUG_VOID_RETURN; } + sortorder= curr_join->sortorder; } thd->proc_info="Copying to group table"; @@ -1783,6 +1784,7 @@ JOIN::exec() (select_options & OPTION_FOUND_ROWS ? HA_POS_ERROR : unit->select_limit_cnt))) DBUG_VOID_RETURN; + sortorder= curr_join->sortorder; } } /* XXX: When can we have here thd->net.report_error not zero? */ @@ -4949,9 +4951,28 @@ make_simple_join(JOIN *join,TABLE *tmp_t JOIN_TAB *join_tab; DBUG_ENTER("make_simple_join"); - if (!(tableptr=(TABLE**) join->thd->alloc(sizeof(TABLE*))) || - !(join_tab=(JOIN_TAB*) join->thd->alloc(sizeof(JOIN_TAB)))) - DBUG_RETURN(TRUE); + /* + Reuse TABLE * and JOIN_TAB if already allocated by a previous call + to this function through JOIN::exec (may happen for sub-queries). + */ + if (!join->table_cache) + { + if (!(join->table_cache= (TABLE**) join->thd->alloc(sizeof(TABLE*)))) + DBUG_RETURN(TRUE); /* purecov: inspected */ + if (join->tmp_join) + join->tmp_join->table_cache= join->table_cache; + } + if (!join->join_tab_cache) + { + if (!(join->join_tab_cache= + (JOIN_TAB*) join->thd->alloc(sizeof(JOIN_TAB)))) + DBUG_RETURN(TRUE); /* purecov: inspected */ + if (join->tmp_join) + join->tmp_join->join_tab_cache= join->join_tab_cache; + } + tableptr= join->table_cache; + join_tab= join->join_tab_cache; + join->join_tab=join_tab; join->table=tableptr; tableptr[0]=tmp_table; join->tables=1; @@ -11971,7 +11992,6 @@ static int create_sort_index(THD *thd, JOIN *join, ORDER *order, ha_rows filesort_limit, ha_rows select_limit) { - SORT_FIELD *sortorder; uint length; ha_rows examined_rows; TABLE *table; @@ -11987,7 +12007,8 @@ create_sort_index(THD *thd, JOIN *join, if (test_if_skip_sort_order(tab,order,select_limit,0)) DBUG_RETURN(0); - if (!(sortorder=make_unireg_sortorder(order,&length))) + if (!(join->sortorder= + make_unireg_sortorder(order,&length,join->sortorder))) goto err; /* purecov: inspected */ /* It's not fatal if the following alloc fails */ table->sort.io_cache=(IO_CACHE*) my_malloc(sizeof(IO_CACHE), @@ -12034,7 +12055,7 @@ create_sort_index(THD *thd, JOIN *join, if (table->s->tmp_table) table->file->info(HA_STATUS_VARIABLE); // Get record count - table->sort.found_records=filesort(thd, table,sortorder, length, + table->sort.found_records=filesort(thd, table,join->sortorder, length, select, filesort_limit, &examined_rows); tab->records= table->sort.found_records; // For SQL_CALC_ROWS if (select) @@ -12381,7 +12402,8 @@ err: } -SORT_FIELD *make_unireg_sortorder(ORDER *order, uint *length) +SORT_FIELD *make_unireg_sortorder(ORDER *order, uint *length, + SORT_FIELD *sortorder) { uint count; SORT_FIELD *sort,*pos; @@ -12390,7 +12412,9 @@ SORT_FIELD *make_unireg_sortorder(ORDER count=0; for (ORDER *tmp = order; tmp; tmp=tmp->next) count++; - pos=sort=(SORT_FIELD*) sql_alloc(sizeof(SORT_FIELD)*(count+1)); + if (!sortorder) + sortorder= (SORT_FIELD*) sql_alloc(sizeof(SORT_FIELD)*(count+1)); + pos=sort=sortorder; if (!pos) return 0; --- 1.109/sql/sql_select.h 2006-10-17 16:20:44 +03:00 +++ 1.110/sql/sql_select.h 2006-10-17 16:20:44 +03:00 @@ -282,6 +282,18 @@ public: bool union_part; // this subselect is part of union bool optimized; // flag to avoid double optimization in EXPLAIN + /* + storage for caching buffers allocated during query execution. + These buffers allocations need to be cached as the thread memory pool is + cleared only at the end of the execution of the whole query and not caching + allocations that occur in repetition at execution time will result in + excessive memory usage. + */ + SORT_FIELD *sortorder; // make_unireg_sortorder() + TABLE **table_cache; // make_simple_join() + JOIN_TAB *join_tab_cache; // make_simple_join() + /* end of allocation caching storage */ + JOIN(THD *thd_arg, List &fields_arg, ulonglong select_options_arg, select_result *result_arg) :fields_list(fields_arg) @@ -307,6 +319,9 @@ public: examined_rows= 0; exec_tmp_table1= 0; exec_tmp_table2= 0; + sortorder= 0; + table_cache= 0; + join_tab_cache= 0; thd= thd_arg; sum_funcs= sum_funcs2= 0; procedure= 0; --- 1.320/sql/sql_table.cc 2006-10-17 16:20:44 +03:00 +++ 1.321/sql/sql_table.cc 2006-10-17 16:20:44 +03:00 @@ -4044,7 +4044,7 @@ copy_data_between_tables(TABLE *from,TAB if (thd->lex->select_lex.setup_ref_array(thd, order_num) || setup_order(thd, thd->lex->select_lex.ref_pointer_array, &tables, fields, all_fields, order) || - !(sortorder=make_unireg_sortorder(order, &length)) || + !(sortorder=make_unireg_sortorder(order, &length, NULL)) || (from->sort.found_records = filesort(thd, from, sortorder, length, (SQL_SELECT *) 0, HA_POS_ERROR, &examined_rows)) == --- 1.196/sql/sql_update.cc 2006-10-17 16:20:45 +03:00 +++ 1.197/sql/sql_update.cc 2006-10-17 16:20:45 +03:00 @@ -311,7 +311,7 @@ int mysql_update(THD *thd, table->sort.io_cache = (IO_CACHE *) my_malloc(sizeof(IO_CACHE), MYF(MY_FAE | MY_ZEROFILL)); - if (!(sortorder=make_unireg_sortorder(order, &length)) || + if (!(sortorder=make_unireg_sortorder(order, &length, NULL)) || (table->sort.found_records = filesort(thd, table, sortorder, length, select, limit, &examined_rows)) --- 1.156/mysql-test/r/subselect.result 2006-10-17 16:20:45 +03:00 +++ 1.157/mysql-test/r/subselect.result 2006-10-17 16:20:45 +03:00 @@ -3393,3 +3393,29 @@ id select_type table type possible_keys 4 UNION t12 system NULL NULL NULL NULL 0 const row not found NULL UNION RESULT ALL NULL NULL NULL NULL NULL DROP TABLE t1; +CREATE TABLE t1 (a VARCHAR(250), b INT auto_increment, PRIMARY KEY (b)); +insert into t1 (a) values (FLOOR(rand() * 100)); +insert into t1 (a) select FLOOR(rand() * 100) from t1; +insert into t1 (a) select FLOOR(rand() * 100) from t1; +insert into t1 (a) select FLOOR(rand() * 100) from t1; +insert into t1 (a) select FLOOR(rand() * 100) from t1; +insert into t1 (a) select FLOOR(rand() * 100) from t1; +insert into t1 (a) select FLOOR(rand() * 100) from t1; +insert into t1 (a) select FLOOR(rand() * 100) from t1; +insert into t1 (a) select FLOOR(rand() * 100) from t1; +insert into t1 (a) select FLOOR(rand() * 100) from t1; +insert into t1 (a) select FLOOR(rand() * 100) from t1; +insert into t1 (a) select FLOOR(rand() * 100) from t1; +insert into t1 (a) select FLOOR(rand() * 100) from t1; +insert into t1 (a) select FLOOR(rand() * 100) from t1; +SELECT a, +(SELECT REPEAT(' ',250) FROM t1 i1 +WHERE i1.b=t1.a ORDER BY RAND() LIMIT 1) AS a +FROM t1 ORDER BY a LIMIT 5; +a a +0 NULL +0 NULL +0 NULL +0 NULL +0 NULL +DROP TABLE t1; --- 1.126/mysql-test/t/subselect.test 2006-10-17 16:20:45 +03:00 +++ 1.127/mysql-test/t/subselect.test 2006-10-17 16:20:45 +03:00 @@ -2306,3 +2306,29 @@ explain select * from t1 where not exist ((select t11.i from t1 t11) union (select t12.i from t1 t12)); DROP TABLE t1; + +# +# Bug#21798: memory leak during query execution with subquery in column +# list using a function +# +CREATE TABLE t1 (a VARCHAR(250), b INT auto_increment, PRIMARY KEY (b)); +insert into t1 (a) values (FLOOR(rand() * 100)); +insert into t1 (a) select FLOOR(rand() * 100) from t1; +insert into t1 (a) select FLOOR(rand() * 100) from t1; +insert into t1 (a) select FLOOR(rand() * 100) from t1; +insert into t1 (a) select FLOOR(rand() * 100) from t1; +insert into t1 (a) select FLOOR(rand() * 100) from t1; +insert into t1 (a) select FLOOR(rand() * 100) from t1; +insert into t1 (a) select FLOOR(rand() * 100) from t1; +insert into t1 (a) select FLOOR(rand() * 100) from t1; +insert into t1 (a) select FLOOR(rand() * 100) from t1; +insert into t1 (a) select FLOOR(rand() * 100) from t1; +insert into t1 (a) select FLOOR(rand() * 100) from t1; +insert into t1 (a) select FLOOR(rand() * 100) from t1; +insert into t1 (a) select FLOOR(rand() * 100) from t1; + +SELECT a, + (SELECT REPEAT(' ',250) FROM t1 i1 + WHERE i1.b=t1.a ORDER BY RAND() LIMIT 1) AS a +FROM t1 ORDER BY a LIMIT 5; +DROP TABLE t1;