maria-developers team mailing list archive

[Sergey Petrunya <psergey@xxxxxxxxxxxx>]

At file:///home/psergey/dev/maria-5.1-table-elim-r11-gcov/

------------------------------------------------------------
revno: 2748
revision-id: psergey@xxxxxxxxxxxx-20090826210140-y2pba3ucdwnvr956
parent: psergey@xxxxxxxxxxxx-20090825150255-46i5gvl2ctd0jcm2
committer: Sergey Petrunya <psergey@xxxxxxxxxxxx>
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 +