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-19 20:00:25+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 calls the check_stack_overrun
  function for stack checking purposes to prevent server crash.

  mysql-test/r/subselect.result@stripped, 2007-11-19 19:55:51+00:00, evgen@stripped
+104 -0
    Added a test case for the bug#31048: Many nested subqueries may cause server crash.

  mysql-test/t/subselect.test@stripped, 2007-11-19 19:55:43+00:00, evgen@stripped +95
-0
    Added a test case for the bug#31048: Many nested subqueries may cause server crash.

  sql/item_subselect.cc@stripped, 2007-11-19 19:59:30+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-19 19:59:00+00:00, evgen@stripped +6 -0
    Bug#31048: Many nested subqueries may cause server crash.
    Now the SQL_SELECT::test_quick_select function calls the check_stack_overrun
    function for stack checking purposes to preven server crash.

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-19 19:55:51 +00:00
@@ -4139,4 +4139,108 @@ 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
+)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
+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;
+ERROR HY000: Thread stack overrun detected
+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
+)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	1	Using where; Using temporary; Using filesort
+14	SUBQUERY	t1	index	NULL	a	5	NULL	9	Using index
+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;
+ERROR HY000: Thread stack overrun detected
+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-19 19:55:43 +00:00
@@ -2987,4 +2987,99 @@ 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
+  )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;
+--replace_regex /overrun.*$/overrun detected/
+--error 1436
+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
+  )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;
+--replace_regex /overrun.*$/overrun detected/
+--error 1436
+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-19 19:59:30 +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-19 19:59:00 +00:00
@@ -1978,11 +1978,17 @@ 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;
+
+    if (check_stack_overrun(thd, STACK_MIN_SIZE, buff))
+      DBUG_RETURN(0);                           // Fatal error flag is set
 
     /* set up parameter that is passed to all functions */
     param.thd= thd;