#At file:///home/kgeorge/mysql/work/B38795-5.0-bugteam/ based on revid:kgeorge@stripped
2708 Georgi Kodinov 2009-01-13
Bug #38795: Automatic search depth and nested join's results in server crash
The greedy optimizer tracks the current level of nested joins and the position
inside these by setting and maintaining a state that's global for the whole FROM
clause.
This state was correctly maintained inside the selection of the next partial plan
table (in best_extension_by_limited_search()).
greedy_search() also moves the current position by adding the last partial match
table when there's not enough tables in the partial plan found by
best_extension_by_limited_search().
This may require update of the global state variables that describe the current
position in the plan if the last table placed by greedy_search is not a top-level
join table.
Fixed by updating the state after placing the partial plan table in greedy_search()
in the same way this is done on entering the best_extension_by_limited_search().
Fixed the signature of the function called to update the state :
check_interleaving_with_nj
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: correctly update current position when placing
the next partial plan table in greedy_search().
=== 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 2009-01-13 11:09:12 +0000
@@ -655,3 +655,80 @@ 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, j int);
+INSERT INTO t2 VALUES (1,1,1);
+CREATE TABLE t2_1 (j int);
+INSERT INTO t2_1 VALUES (1);
+CREATE TABLE t3 (c int, f int);
+INSERT INTO t3 VALUES (1,1);
+CREATE TABLE t3_1 (f int);
+INSERT INTO t3_1 VALUES (1);
+CREATE TABLE t4 (d int, e int, k int);
+INSERT INTO t4 VALUES (1,1,1);
+CREATE TABLE t4_1 (k int);
+INSERT INTO t4_1 VALUES (1);
+CREATE TABLE t5 (g int, d int, h int, l int);
+INSERT INTO t5 VALUES (1,1,1,1);
+CREATE TABLE t5_1 (l int);
+INSERT INTO t5_1 VALUES (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
+SELECT 1
+FROM t1
+LEFT JOIN (
+t2 LEFT JOIN (t3 JOIN t3_1 ON t3.f = t3_1.f) 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
+SELECT 1
+FROM t1
+LEFT JOIN (
+(t2 JOIN t2_1 ON t2.j = t2_1.j) 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
+SELECT 1
+FROM t1
+LEFT JOIN (
+t2 JOIN t3 ON t3.c = t2.c
+) ON t2.b = t1.b
+LEFT JOIN (
+(t4 JOIN t4_1 ON t4.k = t4_1.k) LEFT JOIN t5 ON t5.d = t4.d
+) ON t4.d = t1.d
+;
+1
+1
+SELECT 1
+FROM t1
+LEFT JOIN (
+t2 JOIN t3 ON t3.c = t2.c
+) ON t2.b = t1.b
+LEFT JOIN (
+t4 LEFT JOIN (t5 JOIN t5_1 ON t5.l = t5_1.l) ON t5.d = t4.d
+) ON t4.d = t1.d
+;
+1
+1
+SET optimizer_search_depth = DEFAULT;
+DROP TABLE t1,t2,t2_1,t3,t3_1,t4,t4_1,t5,t5_1;
+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 2009-01-13 11:09:12 +0000
@@ -311,3 +311,76 @@ 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, j int); INSERT INTO t2 VALUES (1,1,1);
+CREATE TABLE t2_1 (j int); INSERT INTO t2_1 VALUES (1);
+CREATE TABLE t3 (c int, f int); INSERT INTO t3 VALUES (1,1);
+CREATE TABLE t3_1 (f int); INSERT INTO t3_1 VALUES (1);
+CREATE TABLE t4 (d int, e int, k int); INSERT INTO t4 VALUES (1,1,1);
+CREATE TABLE t4_1 (k int); INSERT INTO t4_1 VALUES (1);
+CREATE TABLE t5 (g int, d int, h int, l int); INSERT INTO t5 VALUES (1,1,1,1);
+CREATE TABLE t5_1 (l int); INSERT INTO t5_1 VALUES (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
+;
+
+SELECT 1
+FROM t1
+LEFT JOIN (
+ t2 LEFT JOIN (t3 JOIN t3_1 ON t3.f = t3_1.f) 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
+;
+
+SELECT 1
+FROM t1
+LEFT JOIN (
+ (t2 JOIN t2_1 ON t2.j = t2_1.j) 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
+;
+
+SELECT 1
+FROM t1
+LEFT JOIN (
+ t2 JOIN t3 ON t3.c = t2.c
+) ON t2.b = t1.b
+LEFT JOIN (
+ (t4 JOIN t4_1 ON t4.k = t4_1.k) LEFT JOIN t5 ON t5.d = t4.d
+) ON t4.d = t1.d
+;
+
+SELECT 1
+FROM t1
+LEFT JOIN (
+ t2 JOIN t3 ON t3.c = t2.c
+) ON t2.b = t1.b
+LEFT JOIN (
+ t4 LEFT JOIN (t5 JOIN t5_1 ON t5.l = t5_1.l) ON t5.d = t4.d
+) ON t4.d = t1.d
+;
+
+SET optimizer_search_depth = DEFAULT;
+DROP TABLE t1,t2,t2_1,t3,t3_1,t4,t4_1,t5,t5_1;
+
+--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 2009-01-13 11:09:12 +0000
@@ -100,7 +100,7 @@ static COND* substitute_for_best_equal_f
void *table_join_idx);
static COND *simplify_joins(JOIN *join, List<TABLE_LIST> *join_list,
COND *conds, bool top);
-static bool check_interleaving_with_nj(JOIN_TAB *last, JOIN_TAB *next);
+static bool check_interleaving_with_nj(JOIN_TAB *next);
static void restore_prev_nj_state(JOIN_TAB *last);
static void reset_nj_counters(List<TABLE_LIST> *join_list);
static uint build_bitmap_for_nested_joins(List<TABLE_LIST> *join_list,
@@ -4705,6 +4705,18 @@ greedy_search(JOIN *join,
*/
join->positions[idx]= best_pos;
+ /*
+ Update the interleaving state after extending the current partial plan
+ with a new table.
+ We are doing this here because best_extension_by_limited_search reverts
+ the interleaving state to the one of the non-extended partial plan
+ on exit.
+ */
+ IF_DBUG(bool is_interleave_error= )
+ check_interleaving_with_nj (best_table);
+ /* This has been already checked by best_extension_by_limited_search */
+ DBUG_ASSERT(!is_interleave_error);
+
/* find the position of 'best_table' in 'join->best_ref' */
best_idx= idx;
JOIN_TAB *pos= join->best_ref[best_idx];
@@ -4722,7 +4734,7 @@ greedy_search(JOIN *join,
--size_remain;
++idx;
- DBUG_EXECUTE("opt", print_plan(join, join->tables,
+ DBUG_EXECUTE("opt", print_plan(join, idx,
record_count, read_time, read_time,
"extended"););
} while (TRUE);
@@ -4873,7 +4885,7 @@ best_extension_by_limited_search(JOIN
table_map real_table_bit= s->table->map;
if ((remaining_tables & real_table_bit) &&
!(remaining_tables & s->dependent) &&
- (!idx || !check_interleaving_with_nj(join->positions[idx-1].table, s)))
+ (!idx || !check_interleaving_with_nj(s)))
{
double current_record_count, current_read_time;
@@ -5018,7 +5030,7 @@ find_best(JOIN *join,table_map rest_tabl
{
table_map real_table_bit=s->table->map;
if ((rest_tables & real_table_bit) && !(rest_tables & s->dependent) &&
- (!idx|| !check_interleaving_with_nj(join->positions[idx-1].table, s)))
+ (!idx|| !check_interleaving_with_nj(s)))
{
double records, best;
best_access_path(join, s, thd, rest_tables, idx, record_count,
@@ -8380,9 +8392,6 @@ static void reset_nj_counters(List<TABLE
SYNOPSIS
check_interleaving_with_nj()
- join Join being processed
- last_tab Last table in current partial join order (this function is
- not called for empty partial join orders)
next_tab Table we're going to extend the current partial join with
DESCRIPTION
@@ -8467,10 +8476,10 @@ static void reset_nj_counters(List<TABLE
TRUE Requested join order extension not allowed.
*/
-static bool check_interleaving_with_nj(JOIN_TAB *last_tab, JOIN_TAB *next_tab)
+static bool check_interleaving_with_nj(JOIN_TAB *next_tab)
{
TABLE_LIST *next_emb= next_tab->table->pos_in_table_list->embedding;
- JOIN *join= last_tab->join;
+ JOIN *join= next_tab->join;
if (join->cur_embedding_map & ~next_tab->embedding_map)
{
| Thread |
|---|
| • bzr commit into mysql-5.0-bugteam branch (joro:2708) Bug#38795 | Georgi Kodinov | 13 Jan |
