At file:///home/psergey/dev/maria-5.1-table-elim-r11-gcov/
------------------------------------------------------------
revno: 2748
revision-id: [email protected]
parent: [email protected]
committer: Sergey Petrunya <[email protected]>
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 +
_______________________________________________
Mailing list: https://launchpad.net/~maria-developers
Post to : [email protected]
Unsubscribe : https://launchpad.net/~maria-developers
More help : https://help.launchpad.net/ListHelp
