At file:///home/psergey/dev/maria-5.1-table-elim-r11-gcov/ ------------------------------------------------------------ revno: 2748 revision-id: psergey@askmonty.org-20090826210140-y2pba3ucdwnvr956 parent: psergey@askmonty.org-20090825150255-46i5gvl2ctd0jcm2 committer: Sergey Petrunya <psergey@askmonty.org> branch nick: maria-5.1-table-elim-r11-gcov timestamp: Thu 2009-08-27 01:01:40 +0400 message: MWL#17: Table elimination: - Fix a trivial problem when OR-merging two multi-equalities - Amend testsuite to provide full gcov coverage === modified file 'mysql-test/r/table_elim.result' --- a/mysql-test/r/table_elim.result 2009-08-25 10:38:22 +0000 +++ b/mysql-test/r/table_elim.result 2009-08-26 21:01:40 +0000 @@ -352,3 +352,69 @@ 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,t3; +# +# Multi-equality tests +# +create table t1 (a int, b int, c int, d int); +insert into t1 values (0,0,0,0),(1,1,1,1),(2,2,2,2),(3,3,3,3); +create table t2 (pk int primary key, b int, c int); +insert into t2 select a,a,a from t1 where a in (1,2); +explain +select t1.* +from t1 left join t2 on t2.pk=t2.c and t2.b=t1.a and t1.a=t1.b and t2.c=t2.b +where t1.d=1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using where +explain +select t1.* +from +t1 +left join +t2 +on (t2.pk=t2.c and t2.b=t1.a and t1.a=t1.b and t2.c=t2.b) or +(t2.pk=t2.c and t2.b=t1.a and t1.a=t1.b and t2.c=t2.b) +where t1.d=1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using where +#This can't be eliminated: +explain +select t1.* +from +t1 +left join +t2 +on (t2.pk=t2.c and t2.b=t1.a and t2.c=t1.b) or +(t2.pk=t2.c and t1.a=t1.b and t2.c=t1.b) +where t1.d=1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using where +1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 +explain +select t1.* +from +t1 +left join +t2 +on (t2.pk=t2.c and t2.b=t1.a and t2.c=t1.b) or +(t2.pk=t2.c and t2.c=t1.b) +; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 +explain +select t1.* +from t1 left join t2 on t2.pk=3 or t2.pk= 4; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 +1 SIMPLE t2 index PRIMARY PRIMARY 4 NULL 2 Using index +explain +select t1.* +from t1 left join t2 on t2.pk=3 or t2.pk= 3; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 +explain +select t1.* +from t1 left join t2 on (t2.pk=3 and t2.b=3) or (t2.pk= 4 and t2.b=3); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 +1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 2 Using where +drop table t1, t2; === modified file 'mysql-test/t/table_elim.test' --- a/mysql-test/t/table_elim.test 2009-08-25 10:38:22 +0000 +++ b/mysql-test/t/table_elim.test 2009-08-26 21:01:40 +0000 @@ -278,3 +278,61 @@ drop table t1,t2,t3; +--echo # +--echo # Multi-equality tests +--echo # +create table t1 (a int, b int, c int, d int); +insert into t1 values (0,0,0,0),(1,1,1,1),(2,2,2,2),(3,3,3,3); + +create table t2 (pk int primary key, b int, c int); +insert into t2 select a,a,a from t1 where a in (1,2); + +explain +select t1.* +from t1 left join t2 on t2.pk=t2.c and t2.b=t1.a and t1.a=t1.b and t2.c=t2.b +where t1.d=1; + +explain +select t1.* +from + t1 + left join + t2 + on (t2.pk=t2.c and t2.b=t1.a and t1.a=t1.b and t2.c=t2.b) or + (t2.pk=t2.c and t2.b=t1.a and t1.a=t1.b and t2.c=t2.b) +where t1.d=1; + +--echo #This can't be eliminated: +explain +select t1.* +from + t1 + left join + t2 + on (t2.pk=t2.c and t2.b=t1.a and t2.c=t1.b) or + (t2.pk=t2.c and t1.a=t1.b and t2.c=t1.b) +where t1.d=1; + +explain +select t1.* +from + t1 + left join + t2 + on (t2.pk=t2.c and t2.b=t1.a and t2.c=t1.b) or + (t2.pk=t2.c and t2.c=t1.b) +; + +explain +select t1.* +from t1 left join t2 on t2.pk=3 or t2.pk= 4; + +explain +select t1.* +from t1 left join t2 on t2.pk=3 or t2.pk= 3; + +explain +select t1.* +from t1 left join t2 on (t2.pk=3 and t2.b=3) or (t2.pk= 4 and t2.b=3); + +drop table t1, t2; === modified file 'sql/opt_table_elimination.cc' --- a/sql/opt_table_elimination.cc 2009-08-25 14:59:15 +0000 +++ b/sql/opt_table_elimination.cc 2009-08-26 21:01:40 +0000 @@ -654,7 +654,7 @@ List <Field_value> *fv; if (!(fv= new List<Field_value>)) - break; + break; /* purecov: inspected */ List_iterator<Field_value> it1(*old->mult_equal_fields); List_iterator<Field_value> it2(*new_fields->mult_equal_fields); @@ -664,7 +664,11 @@ while (lfield && rfield) { if (lfield == rfield) + { fv->push_back(lfield); + lfield=it1++; + rfield=it2++; + } else { uint left_ratio= lfield->field->table->tablenr*MAX_FIELDS +