MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:kgeorge Date:October 17 2006 1:20pm
Subject:bk commit into 5.0 tree (gkodinov:1.2261) BUG#21798
View as plain text  
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<Item> &fields, List <Item> &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<Item> &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	<union2,4>	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;
Thread
bk commit into 5.0 tree (gkodinov:1.2261) BUG#21798kgeorge17 Oct