diff options
| -rw-r--r-- | mysql-test/r/table_elim.result | 17 | ||||
| -rw-r--r-- | mysql-test/t/table_elim.test | 19 | ||||
| -rw-r--r-- | sql/opt_table_elimination.cc | 19 |
3 files changed, 49 insertions, 6 deletions
diff --git a/mysql-test/r/table_elim.result b/mysql-test/r/table_elim.result index f635429ba8e..6a6e81f5bcf 100644 --- a/mysql-test/r/table_elim.result +++ b/mysql-test/r/table_elim.result @@ -202,3 +202,20 @@ t2.pk3=t2.pk1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 4 drop table t1, t2; +create table t1 (pk int primary key, col int); +insert into t1 values (1,1),(2,2); +create table t2 like t1; +insert into t2 select * from t1; +create table t3 like t1; +insert into t3 select * from t1; +explain +select t1.* from t1 left join ( t2 left join t3 on t3.pk=t2.col) on t2.col=t1.col; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 +explain +select t1.*, t2.* from t1 left join (t2 left join t3 on t3.pk=t2.col) on t2.pk=t1.col; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 +1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.col 1 +drop table t1, t2; diff --git a/mysql-test/t/table_elim.test b/mysql-test/t/table_elim.test index 5e6126b9ede..8a240e42e9f 100644 --- a/mysql-test/t/table_elim.test +++ b/mysql-test/t/table_elim.test @@ -157,4 +157,23 @@ explain select t1.* from t1 left join t2 on t2.pk2=t1.a and t2.pk3=t2.pk1; drop table t1, t2; +# +# Check that equality propagation is taken into account +# +create table t1 (pk int primary key, col int); +insert into t1 values (1,1),(2,2); + +create table t2 like t1; +insert into t2 select * from t1; + +create table t3 like t1; +insert into t3 select * from t1; + +explain +select t1.* from t1 left join ( t2 left join t3 on t3.pk=t2.col) on t2.col=t1.col; + +explain +select t1.*, t2.* from t1 left join (t2 left join t3 on t3.pk=t2.col) on t2.pk=t1.col; + +drop table t1, t2; diff --git a/sql/opt_table_elimination.cc b/sql/opt_table_elimination.cc index a59151fc535..5d8b68f48b8 100644 --- a/sql/opt_table_elimination.cc +++ b/sql/opt_table_elimination.cc @@ -136,7 +136,8 @@ class Table_elimination; /* - A value. + A value, something that can be bound or not bound. Also, values can be linked + in a list. */ class Value_dep : public Sql_alloc @@ -203,7 +204,7 @@ public: /* - A 'module' + A 'module'. Module has dependencies */ class Module_dep : public Sql_alloc @@ -227,7 +228,6 @@ public: }; - /* A "tbl.column= expr" equality dependency. tbl.column depends on fields used in expr. @@ -333,6 +333,9 @@ Equality_module *merge_func_deps(Equality_module *start, Equality_module *new_fi static Table_value *get_table_value(Table_elimination *te, TABLE *table); static Field_value *get_field_value(Table_elimination *te, Field *field); +static Outer_join_module *get_outer_join_dep(Table_elimination *te, + TABLE_LIST *outer_join, + table_map deps_map); static void run_elimination_wave(Table_elimination *te, Module_dep *bound_modules); void eliminate_tables(JOIN *join); @@ -1212,15 +1215,19 @@ void run_elimination_wave(Table_elimination *te, Module_dep *bound_modules) - expressions that depend on us. */ Field_value *field_dep= (Field_value*)bound_values; + DBUG_PRINT("info", ("field %s.%s is now bound", + field_dep->field->table->alias, + field_dep->field->field_name)); + for (Key_module *key_dep= field_dep->table->keys; key_dep; key_dep= key_dep->next_table_key) { - DBUG_PRINT("info", ("key %s.%s is now bound", - key_dep->table->table->alias, - key_dep->table->table->key_info[key_dep->keyno].name)); if (field_dep->field->part_of_key.is_set(key_dep->keyno) && key_dep->unknown_args && !--key_dep->unknown_args) { + DBUG_PRINT("info", ("key %s.%s is now bound", + key_dep->table->table->alias, + key_dep->table->table->key_info[key_dep->keyno].name)); /* Mark as bound and add to the list */ key_dep->next= bound_modules; bound_modules= key_dep; |