From: Date: July 30 2007 3:12pm Subject: bk commit into 5.0 tree (sergefp:1.2532) BUG#29582 List-Archive: http://lists.mysql.com/commits/31817 X-Bug: 29582 Message-Id: <20070730131241.1B2B6FBC15@pylon.mylan> Below is the list of changes that have just been committed into a local 5.0 repository of psergey. When psergey 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, 2007-07-30 17:12:34+04:00, sergefp@stripped +3 -0 BUG#29582: huge memory consumption with union, subselect, joins: - Don't call mysql_select() several times for the select that enumerates the temporary table with the results of the UNION. Making this call for every subquery execution caused O(#enumerated-rows-in-the-outer-query) memory allocations. - Instead, call join->reinit() and join->exec(), and = disable constant table detection for such joins, = provide special handling for table-less constant subqueries. sql/sql_select.cc@stripped, 2007-07-30 17:12:29+04:00, sergefp@stripped +1 -1 BUG#29582: huge memory consumption with union, subselect, joins: - Don't mark tables as constant if JOIN::no_const_tables flag is set sql/sql_select.h@stripped, 2007-07-30 17:12:29+04:00, sergefp@stripped +11 -0 BUG#29582: huge memory consumption with union, subselect, joins: - Don't mark tables as constant if JOIN::no_const_tables flag is set sql/sql_union.cc@stripped, 2007-07-30 17:12:29+04:00, sergefp@stripped +35 -17 BUG#29582: huge memory consumption with union, subselect, joins: - Don't call mysql_select() several times for the select that enumerates the temporary table with UNION results. - Instead, call join->reinit() and join->exec(). - Provide special handling for table-less constant subqueries. diff -Nrup a/sql/sql_select.cc b/sql/sql_select.cc --- a/sql/sql_select.cc 2007-07-12 05:45:22 +04:00 +++ b/sql/sql_select.cc 2007-07-30 17:12:29 +04:00 @@ -2416,7 +2416,7 @@ make_join_statistics(JOIN *join, TABLE_L if ((table->s->system || table->file->records <= 1) && ! s->dependent && !(table->file->table_flags() & HA_NOT_EXACT_COUNT) && - !table->fulltext_searched) + !table->fulltext_searched && !join->no_const_tables) { set_position(join,const_count++,s,(KEYUSE*) 0); } diff -Nrup a/sql/sql_select.h b/sql/sql_select.h --- a/sql/sql_select.h 2007-06-29 11:39:15 +04:00 +++ b/sql/sql_select.h 2007-07-30 17:12:29 +04:00 @@ -277,6 +277,15 @@ public: SELECT_LEX_UNIT *unit; // select that processed SELECT_LEX *select_lex; + /* + TRUE <=> optimizer must not mark any tables as constant tables. + This is needed for subqueries in form "a IN (SELECT .. UNION SELECT ..): + when we optimize the select that reads the results of the union from the + temporary table, we must not mark the temp. table as constant because + the number of rows in it may change from one subquery execution to + another. + */ + bool no_const_tables; JOIN *tmp_join; // copy of this JOIN to be used with temporary tables ROLLUP rollup; // Used with rollup @@ -397,6 +406,8 @@ public: tmp_table_param.init(); tmp_table_param.end_write_records= HA_POS_ERROR; rollup.state= ROLLUP::STATE_NONE; + + no_const_tables= FALSE; } int prepare(Item ***rref_pointer_array, TABLE_LIST *tables, uint wind_num, diff -Nrup a/sql/sql_union.cc b/sql/sql_union.cc --- a/sql/sql_union.cc 2007-05-15 04:06:03 +04:00 +++ b/sql/sql_union.cc 2007-07-30 17:12:29 +04:00 @@ -545,6 +545,10 @@ bool st_select_lex_unit::exec() /* allocate JOIN for fake select only once (prevent mysql_select automatic allocation) + TODO: The above is nonsense. mysql_select() will not allocate the + join if one already exists. There must be some other reason why we + don't let it allocate the join. Perhaps this is because we need + some special parameter values passed to join constructor? */ if (!(fake_select_lex->join= new JOIN(thd, item_list, fake_select_lex->options, result))) @@ -552,33 +556,47 @@ bool st_select_lex_unit::exec() fake_select_lex->table_list.empty(); DBUG_RETURN(TRUE); } + fake_select_lex->join->no_const_tables= TRUE; /* Fake st_select_lex should have item list for correctref_array allocation. */ fake_select_lex->item_list= item_list; + saved_error= mysql_select(thd, &fake_select_lex->ref_pointer_array, + &result_table_list, + 0, item_list, NULL, + global_parameters->order_list.elements, + (ORDER*)global_parameters->order_list.first, + (ORDER*) NULL, NULL, (ORDER*) NULL, + fake_select_lex->options | SELECT_NO_UNLOCK, + result, this, fake_select_lex); } else { - JOIN_TAB *tab,*end; - for (tab=join->join_tab, end=tab+join->tables ; - tab && tab != end ; - tab++) - { - delete tab->select; - delete tab->quick; - } - join->init(thd, item_list, fake_select_lex->options, result); + if (describe) + { + /* + A special branch that is needed for + */ + join->init(thd, item_list, fake_select_lex->options, result); + saved_error= mysql_select(thd, &fake_select_lex->ref_pointer_array, + &result_table_list, + 0, item_list, NULL, + global_parameters->order_list.elements, + (ORDER*)global_parameters->order_list.first, + (ORDER*) NULL, NULL, (ORDER*) NULL, + fake_select_lex->options | SELECT_NO_UNLOCK, + result, this, fake_select_lex); + } + else + { + join->examined_rows= 0; + join->reinit(); + join->exec(); + saved_error= 0; + } } - saved_error= mysql_select(thd, &fake_select_lex->ref_pointer_array, - &result_table_list, - 0, item_list, NULL, - global_parameters->order_list.elements, - (ORDER*)global_parameters->order_list.first, - (ORDER*) NULL, NULL, (ORDER*) NULL, - fake_select_lex->options | SELECT_NO_UNLOCK, - result, this, fake_select_lex); fake_select_lex->table_list.empty(); if (!saved_error)