bzr commit into mysql-5.0-bugteam branch (kgeorge:2708) Bug#38795

[Georgi Kodinov]

#At file:///home/kgeorge/mysql/bzr/B38795-5.0-bugteam/

 2708 Georgi Kodinov	2008-10-28
      Bug #38795: Automatic search depth and nested join's results in server crash
      
      The greedy optimizer was checking if it has a complete plan by comparing the remaining tables with the search depth.
      However the search depth includes the const tables (that are already placed) before the call to greedy_search()).
      And tables in the remaining_tables bitmask are only the non-constant ones.
      Fixed by adding the number of const tables to the number of remaining tables when comparing with search_depth (that has them).
modified:
  mysql-test/r/greedy_optimizer.result
  mysql-test/t/greedy_optimizer.test
  sql/sql_select.cc

per-file messages:
  mysql-test/r/greedy_optimizer.result
    Bug #38795: test case
  mysql-test/t/greedy_optimizer.test
    Bug #38795: test case
  sql/sql_select.cc
    Bug #38795: consider the const tables as an element of search_depth
=== modified file 'mysql-test/r/greedy_optimizer.result'
--- a/mysql-test/r/greedy_optimizer.result	2004-12-16 14:44:40 +0000
+++ b/mysql-test/r/greedy_optimizer.result	2008-10-28 16:57:50 +0000
@@ -655,3 +655,28 @@ show status like 'Last_query_cost';
 Variable_name	Value
 Last_query_cost	794.837037
 drop table t1,t2,t3,t4,t5,t6,t7;
+CREATE TABLE t1 (a int, b int, d int, i int);
+INSERT INTO t1 VALUES (1,1,1,1);
+CREATE TABLE t2 (b int, c int);
+INSERT INTO t2 VALUES (1,1);
+CREATE TABLE t3 (c int);
+INSERT INTO t3 VALUES (1);
+CREATE TABLE t4 (d int, e int);
+INSERT INTO t4 VALUES (1,1);
+CREATE TABLE t5 (g int, d int, h int);
+INSERT INTO t5 VALUES (1,1,1);
+SET optimizer_search_depth = 3;
+SELECT 1
+FROM t1
+LEFT JOIN (
+t2 JOIN t3 ON t3.c = t2.c
+) ON t2.b = t1.b
+LEFT JOIN (
+t4 JOIN t5 ON t5.d = t4.d
+) ON t4.d = t1.d
+;
+1
+1
+SET optimizer_search_depth = DEFAULT;
+DROP TABLE t1,t2,t3,t4,t5;
+End of 5.0 tests

=== modified file 'mysql-test/t/greedy_optimizer.test'
--- a/mysql-test/t/greedy_optimizer.test	2007-06-15 17:15:22 +0000
+++ b/mysql-test/t/greedy_optimizer.test	2008-10-28 16:57:50 +0000
@@ -311,3 +311,32 @@ explain select t1.c11 from t7, t6, t5, t
 show status like 'Last_query_cost';
 
 drop table t1,t2,t3,t4,t5,t6,t7;
+
+
+#
+# Bug # 38795: Automatic search depth and nested join's results in server
+# crash
+#
+
+CREATE TABLE t1 (a int, b int, d int, i int); INSERT INTO t1 VALUES (1,1,1,1);
+CREATE TABLE t2 (b int, c int); INSERT INTO t2 VALUES (1,1);
+CREATE TABLE t3 (c int); INSERT INTO t3 VALUES (1);
+CREATE TABLE t4 (d int, e int); INSERT INTO t4 VALUES (1,1);
+CREATE TABLE t5 (g int, d int, h int); INSERT INTO t5 VALUES (1,1,1);
+
+SET optimizer_search_depth = 3;
+
+SELECT 1
+FROM t1
+LEFT JOIN (
+  t2 JOIN t3 ON t3.c = t2.c
+) ON t2.b = t1.b
+LEFT JOIN (
+  t4 JOIN t5 ON t5.d = t4.d
+) ON t4.d = t1.d
+;
+
+SET optimizer_search_depth = DEFAULT;
+DROP TABLE t1,t2,t3,t4,t5;
+
+--echo End of 5.0 tests

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2008-10-16 16:37:17 +0000
+++ b/sql/sql_select.cc	2008-10-28 16:57:50 +0000
@@ -4683,7 +4683,7 @@ greedy_search(JOIN      *join,
                                          read_time, search_depth, prune_level))
       DBUG_RETURN(TRUE);
 
-    if (size_remain <= search_depth)
+    if (size_remain + join->const_tables <= search_depth)
     {
       /*
         'join->best_positions' contains a complete optimal extension of the