bk commit into 5.0 tree (evgen:1.2549) BUG#31048

[eugene]

Below is the list of changes that have just been committed into a local
5.0 repository of evgen. When evgen 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-11-13 18:03:08+00:00, evgen@stripped +4 -0
  Bug#31048: Many nested subqueries may cause server crash.
  
  This bug is actually two. The first one manifests itself on an EXPLAIN
  SELECT query with nested subqueries that employs the filesort algorithm.
  The whole SELECT under explain is marked as UNCACHEABLE_EXPLAIN to preserve
  some temporary structures for explain. As a side-effect of this values of
  nested subqueries weren't cached and subqueries were re-evaluated many
  times. Each time buffer for filesort was allocated but wasn't freed because
  freeing occurs at the end of topmost SELECT. Thus all available memory was
  eaten up step by step and OOM event occur.
  The second bug manifests itself on SELECT queries with conditions where
  a subquery result is compared with a key field and the subquery itself also
  has such condition. When a long chain of such nested subqueries is present
  the stack overrun occur. This happens because at some point the range optimizer
  temporary puts the PARAM structure on the stack. Its size if about 8K and
  the stack is exhausted very fast.
  
  Now the subselect_single_select_engine::exec function allows subquery result
  caching when the UNCACHEABLE_EXPLAIN flag is set.
  Now the SQL_SELECT::test_quick_select function allocates the PARAM structure
  on the heap and frees it on exit. Also now it calls the check_stack_overrun
  function for stack checking purposes.

  mysql-test/r/subselect.result@stripped, 2007-11-13 18:02:53+00:00, evgen@stripped +94
-0
    Added a test case for the bug#31048: Many nested subqueries may cause server crash.

  mysql-test/t/subselect.test@stripped, 2007-11-13 18:02:54+00:00, evgen@stripped +65
-0
    Added a test case for the bug#31048: Many nested subqueries may cause server crash.

  sql/item_subselect.cc@stripped, 2007-11-13 18:02:31+00:00, evgen@stripped +3 -1
    Bug31048: Many nested subqueries may cause server crash.
    Now the subselect_single_select_engine::exec function allows subquery result
    caching when the UNCACHEABLE_EXPLAIN flag is set.

  sql/opt_range.cc@stripped, 2007-11-13 18:02:36+00:00, evgen@stripped +43 -30
    Bug#31048: Many nested subqueries may cause server crash.
    Now the SQL_SELECT::test_quick_select function allocates the PARAM structure
    on the heap and frees it on exit. Also now it calls the check_stack_overrun
    function for stack checking purposes.

diff -Nrup a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
--- a/mysql-test/r/subselect.result	2007-06-29 07:39:15 +00:00
+++ b/mysql-test/r/subselect.result	2007-11-13 18:02:53 +00:00
@@ -4139,4 +4139,98 @@ SELECT (SELECT SUM(t1.a) FROM t2 WHERE a
 (SELECT SUM(t1.a) FROM t2 WHERE a=1)
 3
 DROP TABLE t1,t2;
+create table t1(a int,b int,key(a),key(b));
+insert into t1(a,b) values (1,2),(2,1),(2,3),(3,4),(5,4),(5,5),
+(6,7),(7,4),(5,3);
+select sum(a),a from t1 where a> (
+select sum(a) from t1 where a> ( select sum(a) from t1 where a> (
+select sum(a) from t1 where a> ( select sum(a) from t1 where a> (
+select sum(a) from t1 where a> ( select sum(a) from t1 where a> (
+select sum(a) from t1 where a> ( select sum(a) from t1 where a> (
+select sum(a) from t1 where a> ( select sum(a) from t1 where a> (
+select sum(a) from t1 where a> ( select sum(a) from t1 where a> (
+select sum(a) from t1 where a> ( select sum(a) from t1 where a> (
+select sum(a) from t1 where a> ( select sum(a) from t1 where a> (
+select sum(a) from t1 where a> ( select sum(a) from t1 where a> (
+select sum(a) from t1 where a> ( select sum(a) from t1 where a> (
+select sum(a) from t1 where a> ( select sum(a) from t1 where a> (
+select sum(a) from t1 where a> ( select sum(a) from t1 where a> (
+select sum(a) from t1 where a> ( select sum(a) from t1 where a> (
+select sum(a) from t1 where a> ( select sum(a) from t1 where a> (
+select sum(a) from t1 where a> ( select sum(a) from t1 where a> (
+select sum(a) from t1 
+)group by b limit 1)group by b limit 1)group by b limit 1
+)group by b limit 1)group by b limit 1)group by b limit 1
+)group by b limit 1)group by b limit 1)group by b limit 1
+)group by b limit 1)group by b limit 1)group by b limit 1
+)group by b limit 1)group by b limit 1)group by b limit 1
+)group by b limit 1)group by b limit 1)group by b limit 1
+)group by b limit 1)group by b limit 1)group by b limit 1
+)group by b limit 1)group by b limit 1)group by b limit 1
+)group by b limit 1)group by b limit 1)group by b limit 1
+)group by b limit 1)group by b limit 1)group by b limit 1) 
+group by a;
+sum(a)	a
+explain select sum(a),a from t1 where a> (
+select sum(a) from t1 where a> ( select sum(a) from t1 where a> (
+select sum(a) from t1 where a> ( select sum(a) from t1 where a> (
+select sum(a) from t1 where a> ( select sum(a) from t1 where a> (
+select sum(a) from t1 where a> ( select sum(a) from t1 where a> (
+select sum(a) from t1 where a> ( select sum(a) from t1 where a> (
+select sum(a) from t1 where a> ( select sum(a) from t1 where a> (
+select sum(a) from t1 where a> ( select sum(a) from t1 where a> (
+select sum(a) from t1 where a> ( select sum(a) from t1 where a> (
+select sum(a) from t1 where a> ( select sum(a) from t1 where a> (
+select sum(a) from t1 where a> ( select sum(a) from t1 where a> (
+select sum(a) from t1 where a> ( select sum(a) from t1 where a> (
+select sum(a) from t1 where a> ( select sum(a) from t1 where a> (
+select sum(a) from t1 where a> ( select sum(a) from t1 where a> (
+select sum(a) from t1 where a> ( select sum(a) from t1 where a> (
+select sum(a) from t1 where a> ( select sum(a) from t1 where a> (
+select sum(a) from t1 
+)group by b limit 1)group by b limit 1)group by b limit 1
+)group by b limit 1)group by b limit 1)group by b limit 1
+)group by b limit 1)group by b limit 1)group by b limit 1
+)group by b limit 1)group by b limit 1)group by b limit 1
+)group by b limit 1)group by b limit 1)group by b limit 1
+)group by b limit 1)group by b limit 1)group by b limit 1
+)group by b limit 1)group by b limit 1)group by b limit 1
+)group by b limit 1)group by b limit 1)group by b limit 1
+)group by b limit 1)group by b limit 1)group by b limit 1
+)group by b limit 1)group by b limit 1)group by b limit 1) 
+group by a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	index	a	a	5	NULL	9	Using where; Using index
+2	SUBQUERY	t1	range	a	a	5	NULL	9	Using where; Using temporary; Using filesort
+3	SUBQUERY	t1	range	a	a	5	NULL	9	Using where; Using temporary; Using filesort
+4	SUBQUERY	t1	range	a	a	5	NULL	9	Using where; Using temporary; Using filesort
+5	SUBQUERY	t1	range	a	a	5	NULL	9	Using where; Using temporary; Using filesort
+6	SUBQUERY	t1	range	a	a	5	NULL	9	Using where; Using temporary; Using filesort
+7	SUBQUERY	t1	range	a	a	5	NULL	9	Using where; Using temporary; Using filesort
+8	SUBQUERY	t1	range	a	a	5	NULL	9	Using where; Using temporary; Using filesort
+9	SUBQUERY	t1	range	a	a	5	NULL	9	Using where; Using temporary; Using filesort
+10	SUBQUERY	t1	range	a	a	5	NULL	9	Using where; Using temporary; Using filesort
+11	SUBQUERY	t1	range	a	a	5	NULL	9	Using where; Using temporary; Using filesort
+12	SUBQUERY	t1	range	a	a	5	NULL	9	Using where; Using temporary; Using filesort
+13	SUBQUERY	t1	range	a	a	5	NULL	9	Using where; Using temporary; Using filesort
+14	SUBQUERY	t1	range	a	a	5	NULL	9	Using where; Using temporary; Using filesort
+15	SUBQUERY	t1	range	a	a	5	NULL	9	Using where; Using temporary; Using filesort
+16	SUBQUERY	t1	range	a	a	5	NULL	9	Using where; Using temporary; Using filesort
+17	SUBQUERY	t1	range	a	a	5	NULL	9	Using where; Using temporary; Using filesort
+18	SUBQUERY	t1	range	a	a	5	NULL	9	Using where; Using temporary; Using filesort
+19	SUBQUERY	t1	range	a	a	5	NULL	9	Using where; Using temporary; Using filesort
+20	SUBQUERY	t1	range	a	a	5	NULL	9	Using where; Using temporary; Using filesort
+21	SUBQUERY	t1	range	a	a	5	NULL	9	Using where; Using temporary; Using filesort
+22	SUBQUERY	t1	range	a	a	5	NULL	9	Using where; Using temporary; Using filesort
+23	SUBQUERY	t1	range	a	a	5	NULL	9	Using where; Using temporary; Using filesort
+24	SUBQUERY	t1	range	a	a	5	NULL	9	Using where; Using temporary; Using filesort
+25	SUBQUERY	t1	range	a	a	5	NULL	9	Using where; Using temporary; Using filesort
+26	SUBQUERY	t1	range	a	a	5	NULL	9	Using where; Using temporary; Using filesort
+27	SUBQUERY	t1	range	a	a	5	NULL	9	Using where; Using temporary; Using filesort
+28	SUBQUERY	t1	range	a	a	5	NULL	9	Using where; Using temporary; Using filesort
+29	SUBQUERY	t1	range	a	a	5	NULL	9	Using where; Using temporary; Using filesort
+30	SUBQUERY	t1	range	a	a	5	NULL	9	Using where; Using temporary; Using filesort
+31	SUBQUERY	t1	range	a	a	5	NULL	1	Using where; Using temporary; Using filesort
+32	SUBQUERY	t1	index	NULL	a	5	NULL	9	Using index
+drop table t1;
 End of 5.0 tests.
diff -Nrup a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
--- a/mysql-test/t/subselect.test	2007-06-29 07:39:15 +00:00
+++ b/mysql-test/t/subselect.test	2007-11-13 18:02:54 +00:00
@@ -2987,4 +2987,69 @@ SELECT (SELECT SUM(t1.a) FROM t2 WHERE a
 SELECT (SELECT SUM(t1.a) FROM t2 WHERE a=1) FROM t1;
 DROP TABLE t1,t2;
 
+#
+# Bug31048: Many nested subqueries may cause server crash.
+#
+create table t1(a int,b int,key(a),key(b));
+insert into t1(a,b) values (1,2),(2,1),(2,3),(3,4),(5,4),(5,5),
+  (6,7),(7,4),(5,3);
+# test for the stack overflow bug
+select sum(a),a from t1 where a> (
+  select sum(a) from t1 where a> ( select sum(a) from t1 where a> (
+  select sum(a) from t1 where a> ( select sum(a) from t1 where a> (
+  select sum(a) from t1 where a> ( select sum(a) from t1 where a> (
+  select sum(a) from t1 where a> ( select sum(a) from t1 where a> (
+  select sum(a) from t1 where a> ( select sum(a) from t1 where a> (
+  select sum(a) from t1 where a> ( select sum(a) from t1 where a> (
+  select sum(a) from t1 where a> ( select sum(a) from t1 where a> (
+  select sum(a) from t1 where a> ( select sum(a) from t1 where a> (
+  select sum(a) from t1 where a> ( select sum(a) from t1 where a> (
+  select sum(a) from t1 where a> ( select sum(a) from t1 where a> (
+  select sum(a) from t1 where a> ( select sum(a) from t1 where a> (
+  select sum(a) from t1 where a> ( select sum(a) from t1 where a> (
+  select sum(a) from t1 where a> ( select sum(a) from t1 where a> (
+  select sum(a) from t1 where a> ( select sum(a) from t1 where a> (
+  select sum(a) from t1 where a> ( select sum(a) from t1 where a> (
+  select sum(a) from t1 
+  )group by b limit 1)group by b limit 1)group by b limit 1
+  )group by b limit 1)group by b limit 1)group by b limit 1
+  )group by b limit 1)group by b limit 1)group by b limit 1
+  )group by b limit 1)group by b limit 1)group by b limit 1
+  )group by b limit 1)group by b limit 1)group by b limit 1
+  )group by b limit 1)group by b limit 1)group by b limit 1
+  )group by b limit 1)group by b limit 1)group by b limit 1
+  )group by b limit 1)group by b limit 1)group by b limit 1
+  )group by b limit 1)group by b limit 1)group by b limit 1
+  )group by b limit 1)group by b limit 1)group by b limit 1) 
+group by a;
+# test for the memory consumption & subquery slowness bug
+explain select sum(a),a from t1 where a> (
+  select sum(a) from t1 where a> ( select sum(a) from t1 where a> (
+  select sum(a) from t1 where a> ( select sum(a) from t1 where a> (
+  select sum(a) from t1 where a> ( select sum(a) from t1 where a> (
+  select sum(a) from t1 where a> ( select sum(a) from t1 where a> (
+  select sum(a) from t1 where a> ( select sum(a) from t1 where a> (
+  select sum(a) from t1 where a> ( select sum(a) from t1 where a> (
+  select sum(a) from t1 where a> ( select sum(a) from t1 where a> (
+  select sum(a) from t1 where a> ( select sum(a) from t1 where a> (
+  select sum(a) from t1 where a> ( select sum(a) from t1 where a> (
+  select sum(a) from t1 where a> ( select sum(a) from t1 where a> (
+  select sum(a) from t1 where a> ( select sum(a) from t1 where a> (
+  select sum(a) from t1 where a> ( select sum(a) from t1 where a> (
+  select sum(a) from t1 where a> ( select sum(a) from t1 where a> (
+  select sum(a) from t1 where a> ( select sum(a) from t1 where a> (
+  select sum(a) from t1 where a> ( select sum(a) from t1 where a> (
+  select sum(a) from t1 
+  )group by b limit 1)group by b limit 1)group by b limit 1
+  )group by b limit 1)group by b limit 1)group by b limit 1
+  )group by b limit 1)group by b limit 1)group by b limit 1
+  )group by b limit 1)group by b limit 1)group by b limit 1
+  )group by b limit 1)group by b limit 1)group by b limit 1
+  )group by b limit 1)group by b limit 1)group by b limit 1
+  )group by b limit 1)group by b limit 1)group by b limit 1
+  )group by b limit 1)group by b limit 1)group by b limit 1
+  )group by b limit 1)group by b limit 1)group by b limit 1
+  )group by b limit 1)group by b limit 1)group by b limit 1) 
+group by a;
+drop table t1;
 --echo End of 5.0 tests.
diff -Nrup a/sql/item_subselect.cc b/sql/item_subselect.cc
--- a/sql/item_subselect.cc	2007-06-29 07:39:15 +00:00
+++ b/sql/item_subselect.cc	2007-11-13 18:02:31 +00:00
@@ -1800,7 +1800,9 @@ int subselect_single_select_engine::exec
       DBUG_RETURN(1);
     }
   }
-  if (select_lex->uncacheable && executed)
+  if (select_lex->uncacheable &&
+      select_lex->uncacheable != UNCACHEABLE_EXPLAIN
+      && executed)
   {
     if (join->reinit())
     {
diff -Nrup a/sql/opt_range.cc b/sql/opt_range.cc
--- a/sql/opt_range.cc	2007-10-23 11:32:03 +00:00
+++ b/sql/opt_range.cc	2007-11-13 18:02:36 +00:00
@@ -1978,37 +1978,49 @@ int SQL_SELECT::test_quick_select(THD *t
   keys_to_use.intersect(head->keys_in_use_for_query);
   if (!keys_to_use.is_clear_all())
   {
+#ifndef EMBEDDED_LIBRARY			// Avoid compiler warning
+    char buff[STACK_BUFF_ALLOC];
+#endif
     MEM_ROOT alloc;
     SEL_TREE *tree= NULL;
     KEY_PART *key_parts;
     KEY *key_info;
-    PARAM param;
+    PARAM *param;
+
+    if (check_stack_overrun(thd, STACK_MIN_SIZE, buff))
+      DBUG_RETURN(0);                           // Fatal error flag is set
+
+    param= (PARAM*)my_malloc(sizeof(PARAM),MYF(0));
+    if (!param)
+      DBUG_RETURN(0);				// Can't use range
 
     /* set up parameter that is passed to all functions */
-    param.thd= thd;
-    param.baseflag=head->file->table_flags();
-    param.prev_tables=prev_tables | const_tables;
-    param.read_tables=read_tables;
-    param.current_table= head->map;
-    param.table=head;
-    param.keys=0;
-    param.mem_root= &alloc;
-    param.old_root= thd->mem_root;
-    param.needed_reg= &needed_reg;
-    param.imerge_cost_buff_size= 0;
+    param->thd= thd;
+    param->baseflag=head->file->table_flags();
+    param->prev_tables=prev_tables | const_tables;
+    param->read_tables=read_tables;
+    param->current_table= head->map;
+    param->table=head;
+    param->keys=0;
+    param->mem_root= &alloc;
+    param->old_root= thd->mem_root;
+    param->needed_reg= &needed_reg;
+    param->imerge_cost_buff_size= 0;
 
     thd->no_errors=1;				// Don't warn about NULL
     init_sql_alloc(&alloc, thd->variables.range_alloc_block_size, 0);
-    if (!(param.key_parts= (KEY_PART*) alloc_root(&alloc,
+
+    if (!(param->key_parts= (KEY_PART*) alloc_root(&alloc,
                                                   sizeof(KEY_PART)*
                                                   head->s->key_parts)) ||
-        fill_used_fields_bitmap(&param))
+        fill_used_fields_bitmap(param))
     {
       thd->no_errors=0;
+      my_free((char*)param, MYF(0));
       free_root(&alloc,MYF(0));			// Return memory & allocator
       DBUG_RETURN(0);				// Can't use range
     }
-    key_parts= param.key_parts;
+    key_parts= param->key_parts;
     thd->mem_root= &alloc;
 
     /*
@@ -2024,12 +2036,12 @@ int SQL_SELECT::test_quick_select(THD *t
       if (key_info->flags & HA_FULLTEXT)
 	continue;    // ToDo: ft-keys in non-ft ranges, if possible   SerG
 
-      param.key[param.keys]=key_parts;
+      param->key[param->keys]=key_parts;
       key_part_info= key_info->key_part;
       for (uint part=0 ; part < key_info->key_parts ;
 	   part++, key_parts++, key_part_info++)
       {
-	key_parts->key=		 param.keys;
+	key_parts->key=		 param->keys;
 	key_parts->part=	 part;
 	key_parts->length=       key_part_info->length;
 	key_parts->store_length= key_part_info->store_length;
@@ -2039,16 +2051,16 @@ int SQL_SELECT::test_quick_select(THD *t
           (key_info->flags & HA_SPATIAL) ? Field::itMBR : Field::itRAW;
         key_parts->flag=         (uint8) key_part_info->key_part_flag;
       }
-      param.real_keynr[param.keys++]=idx;
+      param->real_keynr[param->keys++]=idx;
     }
-    param.key_parts_end=key_parts;
-    param.alloced_sel_args= 0;
+    param->key_parts_end=key_parts;
+    param->alloced_sel_args= 0;
 
     /* Calculate cost of full index read for the shortest covering index */
     if (!head->used_keys.is_clear_all())
     {
       int key_for_use= find_shortest_key(head, &head->used_keys);
-      double key_read_time= (get_index_only_read_time(&param, records,
+      double key_read_time= (get_index_only_read_time(param, records,
                                                      key_for_use) +
                              (double) records / TIME_FOR_COMPARE);
       DBUG_PRINT("info",  ("'all'+'using index' scan will be using key %d, "
@@ -2063,7 +2075,7 @@ int SQL_SELECT::test_quick_select(THD *t
 
     if (cond)
     {
-      if ((tree= get_mm_tree(&param,cond)))
+      if ((tree= get_mm_tree(param,cond)))
       {
         if (tree->type == SEL_TREE::IMPOSSIBLE)
         {
@@ -2081,7 +2093,7 @@ int SQL_SELECT::test_quick_select(THD *t
       Try to construct a QUICK_GROUP_MIN_MAX_SELECT.
       Notice that it can be constructed no matter if there is a range tree.
     */
-    group_trp= get_best_group_min_max(&param, tree);
+    group_trp= get_best_group_min_max(param, tree);
     if (group_trp && group_trp->read_cost < best_read_time)
     {
       best_trp= group_trp;
@@ -2101,7 +2113,7 @@ int SQL_SELECT::test_quick_select(THD *t
         bool can_build_covering= FALSE;
 
         /* Get best 'range' plan and prepare data for making other plans */
-        if ((range_trp= get_key_scans_params(&param, tree, FALSE,
+        if ((range_trp= get_key_scans_params(param, tree, FALSE,
                                              best_read_time)))
         {
           best_trp= range_trp;
@@ -2122,7 +2134,7 @@ int SQL_SELECT::test_quick_select(THD *t
             Get best non-covering ROR-intersection plan and prepare data for
             building covering ROR-intersection.
           */
-          if ((rori_trp= get_best_ror_intersect(&param, tree, best_read_time,
+          if ((rori_trp= get_best_ror_intersect(param, tree, best_read_time,
                                                 &can_build_covering)))
           {
             best_trp= rori_trp;
@@ -2132,7 +2144,7 @@ int SQL_SELECT::test_quick_select(THD *t
               and worth doing.
             */
             if (!rori_trp->is_covering && can_build_covering &&
-                (rori_trp= get_best_covering_ror_intersect(&param, tree,
+                (rori_trp= get_best_covering_ror_intersect(param, tree,
                                                            best_read_time)))
               best_trp= rori_trp;
           }
@@ -2150,7 +2162,7 @@ int SQL_SELECT::test_quick_select(THD *t
         List_iterator_fast<SEL_IMERGE> it(tree->merges);
         while ((imerge= it++))
         {
-          new_conj_trp= get_best_disjunct_quick(&param, imerge, best_read_time);
+          new_conj_trp= get_best_disjunct_quick(param, imerge, best_read_time);
           if (!best_conj_trp || (new_conj_trp && new_conj_trp->read_cost <
                                  best_conj_trp->read_cost))
             best_conj_trp= new_conj_trp;
@@ -2160,13 +2172,13 @@ int SQL_SELECT::test_quick_select(THD *t
       }
     }
 
-    thd->mem_root= param.old_root;
+    thd->mem_root= param->old_root;
 
     /* If we got a read plan, create a quick select from it. */
     if (best_trp)
     {
       records= best_trp->records;
-      if (!(quick= best_trp->make_quick(&param, TRUE)) || quick->init())
+      if (!(quick= best_trp->make_quick(param, TRUE)) || quick->init())
       {
         delete quick;
         quick= NULL;
@@ -2175,8 +2187,9 @@ int SQL_SELECT::test_quick_select(THD *t
 
   free_mem:
     free_root(&alloc,MYF(0));			// Return memory & allocator
-    thd->mem_root= param.old_root;
+    thd->mem_root= param->old_root;
     thd->no_errors=0;
+    my_free((char*)param, MYF(0));
   }
 
   DBUG_EXECUTE("info", print_quick(quick, &needed_reg););