maria-developers team mailing list archive

[Igor Babaev <igor@xxxxxxxxxxxx>]

#At lp:maria/5.2 based on revid:psergey@xxxxxxxxxxxx-20100225080910-38wapbgw5vpm6nax

 2771 Igor Babaev	2010-03-05
      Corrected Evgen's fix for bug #45191.
      Made sure that join buffers could be used for inner tables of
      any semi-join when the first match strategy is employed.
      modified:
        mysql-test/r/subselect_sj2_jcl6.result
        mysql-test/r/subselect_sj_jcl6.result
        sql/sql_join_cache.cc
        sql/sql_select.cc
        sql/sql_select.h

=== modified file 'mysql-test/r/subselect_sj2_jcl6.result'
--- a/mysql-test/r/subselect_sj2_jcl6.result	2010-02-17 10:47:55 +0000
+++ b/mysql-test/r/subselect_sj2_jcl6.result	2010-03-05 18:54:48 +0000
@@ -713,9 +713,9 @@ c2 in (select 1 from t3, t2) and
 c1 in (select convert(c6,char(1)) from t2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	1	Using where
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	1	
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	1	Using where
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	FirstMatch(t2)
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	1	Using join buffer
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	FirstMatch(t2); Using join buffer
 drop table t2, t3;
 set join_cache_level=default;
 show variables like 'join_cache_level';

=== modified file 'mysql-test/r/subselect_sj_jcl6.result'
--- a/mysql-test/r/subselect_sj_jcl6.result	2010-02-24 11:33:42 +0000
+++ b/mysql-test/r/subselect_sj_jcl6.result	2010-03-05 18:54:48 +0000
@@ -374,8 +374,8 @@ WHERE PNUM IN
 (SELECT PNUM  FROM PROJ));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	STAFF	ALL	NULL	NULL	NULL	NULL	5	
-1	PRIMARY	PROJ	ALL	NULL	NULL	NULL	NULL	6	
-1	PRIMARY	WORKS	ALL	NULL	NULL	NULL	NULL	12	Using where; FirstMatch(STAFF)
+1	PRIMARY	PROJ	ALL	NULL	NULL	NULL	NULL	6	Using join buffer
+1	PRIMARY	WORKS	ALL	NULL	NULL	NULL	NULL	12	Using where; FirstMatch(STAFF); Using join buffer
 SELECT EMPNUM, EMPNAME
 FROM STAFF
 WHERE EMPNUM IN

=== modified file 'sql/sql_join_cache.cc'
--- a/sql/sql_join_cache.cc	2010-02-15 21:53:06 +0000
+++ b/sql/sql_join_cache.cc	2010-03-05 18:54:48 +0000
@@ -407,8 +407,10 @@ void JOIN_CACHE::set_constants()
     However at this moment we don't know whether we have referenced fields for
     the cache or not. Later when a referenced field is registered for the cache
     we adjust the value of the flag 'with_length'.
-  */        
-  with_length= is_key_access() || with_match_flag;
+  */ 
+  with_length= is_key_access() || 
+               join_tab->is_inner_table_of_semi_join_with_first_match() ||
+               join_tab->is_inner_table_of_outer_join();
   /* 
      At this moment we don't know yet the value of 'referenced_fields',
      but in any case it can't be greater than the value of 'fields'.
@@ -1304,7 +1306,7 @@ bool JOIN_CACHE::get_match_flag_by_pos(u
     uchar *prev_rec_ptr= prev_cache->get_rec_ref(rec_ptr);
     return prev_cache->get_match_flag_by_pos(prev_rec_ptr);
   } 
-  DBUG_ASSERT(1);
+  DBUG_ASSERT(0);
   return FALSE;
 }
 
@@ -1538,12 +1540,12 @@ bool JOIN_CACHE::read_referenced_field(C
 
 bool JOIN_CACHE::skip_record_if_match()
 {
-  DBUG_ASSERT(with_match_flag && with_length);
+  DBUG_ASSERT(with_length);
   uint offset= size_of_rec_len;
   if (prev_cache)
     offset+= prev_cache->get_size_of_rec_offset();
   /* Check whether the match flag is on */
-  if (test(*(pos+offset)))
+  if (get_match_flag_by_pos(pos+offset))
   {
     pos+= size_of_rec_len + get_rec_length(pos);
     return TRUE;

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2010-02-17 21:59:41 +0000
+++ b/sql/sql_select.cc	2010-03-05 18:54:48 +0000
@@ -6352,10 +6352,17 @@ make_outerjoin_info(JOIN *join)
       }
       if (!tab->first_inner)  
         tab->first_inner= nested_join->first_nested;
+      if (tab->table->reginfo.not_exists_optimize)
+        tab->first_inner->table->reginfo.not_exists_optimize= 1;         
       if (++nested_join->counter < nested_join->n_tables)
         break;
       /* Table tab is the last inner table for nested join. */
       nested_join->first_nested->last_inner= tab;
+      if (tab->first_inner->table->reginfo.not_exists_optimize)
+      {
+        for (JOIN_TAB *join_tab= tab->first_inner; join_tab <= tab; join_tab++)
+          join_tab->table->reginfo.not_exists_optimize= 1;
+      } 
     }
   }
   DBUG_VOID_RETURN;
@@ -7109,18 +7116,14 @@ uint check_join_cache_usage(JOIN_TAB *ta
   if (tab->use_quick == 2)
     goto no_join_cache;
   /*
-    Use join cache with FirstMatch semi-join strategy only when semi-join
-    contains only one table.
-  */
-  if (tab->is_inner_table_of_semi_join_with_first_match() &&
-      !tab->is_single_inner_of_semi_join_with_first_match())
-    goto no_join_cache;
-  /*
     Non-linked join buffers can't guarantee one match
   */
-  if (force_unlinked_cache && 
-      (tab->is_inner_table_of_outer_join() &&
-       !tab->is_single_inner_of_outer_join()))
+   if (force_unlinked_cache && 
+       (!tab->type == JT_ALL || cache_level <= 4) && 
+       ((tab->is_inner_table_of_semi_join_with_first_match() &&
+         !tab->is_single_inner_of_semi_join_with_first_match()) ||
+        (tab->is_inner_table_of_outer_join() &&
+         !tab->is_single_inner_of_outer_join())))
     goto no_join_cache;
 
   /*

=== modified file 'sql/sql_select.h'
--- a/sql/sql_select.h	2010-02-15 21:53:06 +0000
+++ b/sql/sql_select.h	2010-03-05 18:54:48 +0000
@@ -321,8 +321,8 @@ typedef struct st_join_table {
   }
   bool check_only_first_match()
   {
-    return  last_sj_inner_tab == this ||
-           (first_inner && first_inner->last_inner == this &&
+    return is_inner_table_of_semi_join_with_first_match() ||
+           (is_inner_table_of_outer_join() &&
             table->reginfo.not_exists_optimize);
   }
   bool is_last_inner_table()