At file:///home/psergey/dev/maria-5.1-table-elim-r10/ ------------------------------------------------------------ revno: 2731 revision-id: psergey@askmonty.org-20090817150229-jy461nqbmk8nzhha parent: psergey@askmonty.org-20090816180159-z3lfkjpjfsm7zbp0 committer: Sergey Petrunya <psergey@askmonty.org> branch nick: maria-5.1-table-elim-r10 timestamp: Mon 2009-08-17 18:02:29 +0300 message: MWL#17: Table elimination - More dbug printouts - More testcases === modified file 'mysql-test/r/table_elim.result' --- a/mysql-test/r/table_elim.result 2009-06-29 13:51:15 +0000 +++ b/mysql-test/r/table_elim.result 2009-08-17 15:02:29 +0000 @@ -202,3 +202,20 @@ 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; === modified file 'mysql-test/t/table_elim.test' --- a/mysql-test/t/table_elim.test 2009-06-29 13:51:15 +0000 +++ b/mysql-test/t/table_elim.test 2009-08-17 15:02:29 +0000 @@ -157,4 +157,23 @@ 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; === modified file 'sql/opt_table_elimination.cc' --- a/sql/opt_table_elimination.cc 2009-08-16 18:01:59 +0000 +++ b/sql/opt_table_elimination.cc 2009-08-17 15:02:29 +0000 @@ -136,7 +136,8 @@ /* - 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 @@ /* - A 'module' + A 'module'. Module has dependencies */ class Module_dep : public Sql_alloc @@ -227,7 +228,6 @@ }; - /* A "tbl.column= expr" equality dependency. tbl.column depends on fields used in expr. @@ -333,6 +333,9 @@ 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 @@ - 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;