[Commits] Rev 2944: Merge in file:///home/igor/maria/maria-5.1/

igor at askmonty.org igor at askmonty.org
Mon Sep 27 01:22:53 EEST 2010


At file:///home/igor/maria/maria-5.1/

------------------------------------------------------------
revno: 2944 [merge]
revision-id: igor at askmonty.org-20100926222257-qtynyyogm3esvrlv
parent: igor at askmonty.org-20100925161838-wdqrw7w4qe7rktj9
parent: igor at askmonty.org-20100926161234-2187st1jem85nl3e
committer: Igor Babaev <igor at askmonty.org>
branch nick: maria-5.1
timestamp: Sun 2010-09-26 15:22:57 -0700
message:
  Merge
modified:
  mysql-test/r/join_outer.result sp1f-join_outer.result-20001228015633-vk2jshiracfus3ze2d2bim2csnnrc5us
  mysql-test/t/join_outer.test   sp1f-join_outer.test-20001228015636-himrcptylaquy6l5d7pl7pawom3ytmtw
  sql/sql_select.cc              sp1f-sql_select.cc-19700101030959-egb7whpkh76zzvikycs5nsnuviu4fdlb
  sql/sql_select.h               sp1f-sql_select.h-19700101030959-oqegfxr76xlgmrzd6qlevonoibfnwzoz
-------------- next part --------------
=== modified file 'mysql-test/r/join_outer.result'
--- a/mysql-test/r/join_outer.result	2010-09-25 16:00:01 +0000
+++ b/mysql-test/r/join_outer.result	2010-09-26 16:12:34 +0000
@@ -1449,4 +1449,63 @@
 pk	t
 2001	3001
 drop table t1,t2,t3,t4;
+#
+# Bug#57024: Poor performance when conjunctive condition over the outer 
+#            table is used in the on condition of an outer join
+# 
+create table t1 (a int);
+insert into t1 values (NULL), (NULL), (NULL), (NULL);
+insert into t1 select * from t1;
+insert into t1 select * from t1;
+insert into t1 select * from t1;
+insert into t1 select * from t1;
+insert into t1 select * from t1;
+insert into t1 select * from t1;
+insert into t1 select * from t1;
+insert into t1 select * from t1;
+insert into t1 select * from t1;
+insert into t1 select * from t1;
+insert into t1 select * from t1;
+insert into t1 select * from t1;
+insert into t1 select * from t1;
+insert into t1 select * from t1;
+insert into t1 select * from t1;
+insert into t1 select * from t1;
+insert into t1 select * from t1;
+insert into t1 select * from t1;
+insert into t1 values (4), (2), (1), (3);
+create table t2 like t1;
+insert into t2 select if(t1.a is null, 10, t1.a) from t1;
+create table t3 (a int, b int, index idx(a));
+insert into t3 values (1, 100), (3, 301), (4, 402), (1, 102), (1, 101);
+analyze table t1,t2,t3;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	OK
+test.t2	analyze	status	OK
+test.t3	analyze	status	OK
+flush status;
+select sum(t3.b) from t1 left join t3 on t3.a=t1.a and t1.a is not null;
+sum(t3.b)
+1006
+show status like "handler_read%";
+Variable_name	Value
+Handler_read_first	0
+Handler_read_key	4
+Handler_read_next	5
+Handler_read_prev	0
+Handler_read_rnd	0
+Handler_read_rnd_next	1048581
+flush status;
+select sum(t3.b) from t2 left join t3 on t3.a=t2.a and t2.a <> 10;
+sum(t3.b)
+1006
+show status like "handler_read%";
+Variable_name	Value
+Handler_read_first	0
+Handler_read_key	4
+Handler_read_next	5
+Handler_read_prev	0
+Handler_read_rnd	0
+Handler_read_rnd_next	1048581
+drop table t1,t2,t3;
 End of 5.1 tests

=== modified file 'mysql-test/t/join_outer.test'
--- a/mysql-test/t/join_outer.test	2010-09-25 16:00:01 +0000
+++ b/mysql-test/t/join_outer.test	2010-09-26 16:12:34 +0000
@@ -1029,4 +1029,48 @@
 
 drop table t1,t2,t3,t4;
 
+--echo #
+--echo # Bug#57024: Poor performance when conjunctive condition over the outer 
+--echo #            table is used in the on condition of an outer join
+--echo # 
+
+create table t1 (a int);
+insert into t1 values (NULL), (NULL), (NULL), (NULL);
+insert into t1 select * from t1; 
+insert into t1 select * from t1; 
+insert into t1 select * from t1; 
+insert into t1 select * from t1; 
+insert into t1 select * from t1; 
+insert into t1 select * from t1; 
+insert into t1 select * from t1; 
+insert into t1 select * from t1; 
+insert into t1 select * from t1; 
+insert into t1 select * from t1; 
+insert into t1 select * from t1; 
+insert into t1 select * from t1; 
+insert into t1 select * from t1; 
+insert into t1 select * from t1; 
+insert into t1 select * from t1; 
+insert into t1 select * from t1; 
+insert into t1 select * from t1; 
+insert into t1 select * from t1; 
+insert into t1 values (4), (2), (1), (3);
+
+create table t2 like t1;
+insert into t2 select if(t1.a is null, 10, t1.a) from t1;
+
+create table t3 (a int, b int, index idx(a));  
+insert into t3 values (1, 100), (3, 301), (4, 402), (1, 102), (1, 101);
+
+analyze table t1,t2,t3;
+
+flush status;
+select sum(t3.b) from t1 left join t3 on t3.a=t1.a and t1.a is not null;
+show status like "handler_read%";
+flush status;
+select sum(t3.b) from t2 left join t3 on t3.a=t2.a and t2.a <> 10;
+show status like "handler_read%";
+
+drop table t1,t2,t3;
+
 --echo End of 5.1 tests

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2010-09-13 04:25:57 +0000
+++ b/sql/sql_select.cc	2010-09-26 16:12:34 +0000
@@ -6632,6 +6632,9 @@
           if (tmp_cond)
           {
             JOIN_TAB *cond_tab= tab < first_inner_tab ? first_inner_tab : tab;
+            Item **sel_cond_ref= tab < first_inner_tab ?
+                                   &first_inner_tab->on_precond :
+                                   &tab->select_cond;
             /*
               First add the guards for match variables of
               all embedding outer join operations.
@@ -6654,14 +6657,14 @@
               tmp_cond->quick_fix_field();
 	    /* Add the predicate to other pushed down predicates */
             DBUG_PRINT("info", ("Item_cond_and"));
-            cond_tab->select_cond= !cond_tab->select_cond ? tmp_cond :
-	                          new Item_cond_and(cond_tab->select_cond,
-                                                    tmp_cond);
+            *sel_cond_ref= !(*sel_cond_ref) ? 
+                             tmp_cond :
+                             new Item_cond_and(*sel_cond_ref, tmp_cond);
             DBUG_PRINT("info", ("Item_cond_and 0x%lx",
-                                (ulong)cond_tab->select_cond));
-            if (!cond_tab->select_cond)
-	      DBUG_RETURN(1);
-            cond_tab->select_cond->quick_fix_field();
+                                (ulong)(*sel_cond_ref)));
+            if (!(*sel_cond_ref))
+              DBUG_RETURN(1);
+            (*sel_cond_ref)->quick_fix_field();
           }              
         }
         first_inner_tab= first_inner_tab->first_upper;       
@@ -11646,7 +11649,7 @@
     return (*join_tab->next_select)(join,join_tab+1,end_of_records);
 
   int error;
-  enum_nested_loop_state rc;
+  enum_nested_loop_state rc= NESTED_LOOP_OK;
   READ_RECORD *info= &join_tab->read_record;
 
   if (join->resume_nested_loop)
@@ -11674,11 +11677,16 @@
 
       /* Set first_unmatched for the last inner table of this group */
       join_tab->last_inner->first_unmatched= join_tab;
+      if (join_tab->on_precond && !join_tab->on_precond->val_int())
+        rc= NESTED_LOOP_NO_MORE_ROWS;
     }
     join->thd->row_count= 0;
 
-    error= (*join_tab->read_first_record)(join_tab);
-    rc= evaluate_join_record(join, join_tab, error);
+    if (rc != NESTED_LOOP_NO_MORE_ROWS)
+    {
+      error= (*join_tab->read_first_record)(join_tab);
+      rc= evaluate_join_record(join, join_tab, error);
+    }
   }
 
   while (rc == NESTED_LOOP_OK)

=== modified file 'sql/sql_select.h'
--- a/sql/sql_select.h	2010-08-24 17:17:17 +0000
+++ b/sql/sql_select.h	2010-09-26 16:12:34 +0000
@@ -154,7 +154,9 @@
   TABLE		*table;
   KEYUSE	*keyuse;			/**< pointer to first used key */
   SQL_SELECT	*select;
-  COND		*select_cond;
+  COND          *select_cond;
+  COND          *on_precond;    /**< part of on condition to check before
+				     accessing the first inner table           */  
   QUICK_SELECT_I *quick;
   Item	       **on_expr_ref;   /**< pointer to the associated on expression   */
   COND_EQUAL    *cond_equal;    /**< multiple equalities for the on expression */



More information about the commits mailing list