[Commits] Rev 2822: Fixed bug #52636. in file:///home/igor/maria/maria-5.3-mwl128-bugs/

igor at askmonty.org igor at askmonty.org
Fri Sep 24 05:22:48 EEST 2010


At file:///home/igor/maria/maria-5.3-mwl128-bugs/

------------------------------------------------------------
revno: 2822
revision-id: igor at askmonty.org-20100924022236-dxoaxyfd142jysmy
parent: igor at askmonty.org-20100923182545-fjwtj40q55lanx89
committer: Igor Babaev <igor at askmonty.org>
branch nick: maria-5.3-mwl128-bugs
timestamp: Thu 2010-09-23 19:22:36 -0700
message:
  Fixed bug #52636.
  Made the BKA and BKAH algorithms aware of join keys with null
  rejecting components that happened to take null values. For such
  keys index lookups must be skipped.
-------------- next part --------------
=== modified file 'mysql-test/r/join_cache.result'
--- a/mysql-test/r/join_cache.result	2010-09-23 18:25:45 +0000
+++ b/mysql-test/r/join_cache.result	2010-09-24 02:22:36 +0000
@@ -5242,4 +5242,65 @@
 1	SIMPLE	t4	ref	carrier_id	carrier_id	5	test.t3.id	2	Using index
 SET join_cache_level=default;
 DROP TABLE t1,t2,t3,t4;
+#
+# BUG#52636: allowing JOINs on NULL values w/ join_cache_level = 5-8
+#
+CREATE TABLE t1 (b int);
+INSERT INTO t1 VALUES (NULL),(3);
+CREATE TABLE t2 (a int, b int, KEY (b));
+INSERT INTO t2 VALUES (100,NULL),(150,200);
+set join_cache_level = 5;
+explain SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
+1	SIMPLE	t2	ref	b	b	5	test.t1.b	2	Using join buffer
+SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
+a
+NULL
+NULL
+set join_cache_level = 8;
+explain SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
+1	SIMPLE	t2	ref	b	b	5	test.t1.b	2	Using join buffer
+SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
+a
+NULL
+NULL
+delete from t1;
+INSERT INTO t1 VALUES (NULL),(NULL);
+set join_cache_level = 5;
+explain SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
+1	SIMPLE	t2	ref	b	b	5	test.t1.b	2	Using join buffer
+SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
+a
+NULL
+NULL
+DROP TABLE t1,t2;
+CREATE TABLE t1 (b varchar(100));
+INSERT INTO t1 VALUES (NULL),("some varchar");
+CREATE TABLE t2 (a int, b varchar(100), KEY (b));
+INSERT INTO t2 VALUES (100,NULL),(150,"varchar"),(200,NULL),(250,"long long varchar");
+set join_cache_level = 5;
+explain SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
+1	SIMPLE	t2	ref	b	b	103	test.t1.b	2	Using join buffer
+SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
+a
+NULL
+NULL
+set join_cache_level = 8;
+explain SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
+1	SIMPLE	t2	ref	b	b	103	test.t1.b	2	Using join buffer
+SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
+a
+NULL
+NULL
+set join_cache_level = default;
+DROP TABLE t1,t2;
 set @@optimizer_switch=@save_optimizer_switch;

=== modified file 'mysql-test/t/join_cache.test'
--- a/mysql-test/t/join_cache.test	2010-09-23 18:25:45 +0000
+++ b/mysql-test/t/join_cache.test	2010-09-24 02:22:36 +0000
@@ -2026,5 +2026,55 @@
 
 DROP TABLE t1,t2,t3,t4;
 
+--echo #
+--echo # BUG#52636: allowing JOINs on NULL values w/ join_cache_level = 5-8
+--echo #
+
+CREATE TABLE t1 (b int);
+INSERT INTO t1 VALUES (NULL),(3);
+
+CREATE TABLE t2 (a int, b int, KEY (b));
+INSERT INTO t2 VALUES (100,NULL),(150,200);
+
+set join_cache_level = 5;
+explain SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
+--sorted_result
+SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
+
+set join_cache_level = 8;
+explain SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
+--sorted_result
+SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
+
+# test crash when no key is worth collecting by BKA for t2's ref
+delete from t1;
+INSERT INTO t1 VALUES (NULL),(NULL);
+set join_cache_level = 5;
+explain SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
+--sorted_result
+SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
+
+DROP TABLE t1,t2;
+
+# test varchar keys
+CREATE TABLE t1 (b varchar(100));
+INSERT INTO t1 VALUES (NULL),("some varchar");
+
+CREATE TABLE t2 (a int, b varchar(100), KEY (b));
+INSERT INTO t2 VALUES (100,NULL),(150,"varchar"),(200,NULL),(250,"long long varchar");
+
+set join_cache_level = 5;
+explain SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
+--sorted_result
+SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
+
+set join_cache_level = 8;
+explain SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
+--sorted_result
+SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
+
+set join_cache_level = default;
+DROP TABLE t1,t2;
+
 # this must be the last command in the file
 set @@optimizer_switch=@save_optimizer_switch;

=== modified file 'sql/multi_range_read.cc'
--- a/sql/multi_range_read.cc	2010-06-26 10:05:41 +0000
+++ b/sql/multi_range_read.cc	2010-09-24 02:22:36 +0000
@@ -223,7 +223,7 @@
 
 int handler::multi_range_read_next(char **range_info)
 {
-  int UNINIT_VAR(result);
+  int result= HA_ERR_END_OF_FILE;
   int range_res;
   DBUG_ENTER("handler::multi_range_read_next");
 

=== modified file 'sql/sql_join_cache.cc'
--- a/sql/sql_join_cache.cc	2010-09-21 23:41:53 +0000
+++ b/sql/sql_join_cache.cc	2010-09-24 02:22:36 +0000
@@ -2566,6 +2566,7 @@
   uchar *key_ref_ptr;
   uchar *link= 0;
   TABLE_REF *ref= &join_tab->ref;
+
   uchar *next_ref_ptr= pos;
 
   pos+= get_size_of_rec_offset();
@@ -2578,6 +2579,9 @@
     key= get_curr_emb_key();
   else
   {
+    if (ref->rejected_for_join_as_unknown())
+      return FALSE;
+ 
     /* Build the key over the fields read into the record buffers */ 
     cp_buffer_from_ref(join->thd, join_tab->table, ref);
     key= ref->key_buff;
@@ -3731,6 +3735,7 @@
   uint32 rec_len;
   uchar *init_pos;
   JOIN_CACHE *cache;
+  bool rejected= FALSE;
   
   /* Any record in a BKA cache is prepended with its length */
   DBUG_ASSERT(with_length);
@@ -3798,13 +3803,19 @@
     
     /* Build the key over the fields read into the record buffers */ 
     TABLE_REF *ref= &join_tab->ref;
-    cp_buffer_from_ref(join->thd, join_tab->table, ref);
-    *key= ref->key_buff;
-    len= ref->key_length;
+    if (!(rejected= ref->rejected_for_join_as_unknown()))
+    {
+      cp_buffer_from_ref(join->thd, join_tab->table, ref);
+      *key= ref->key_buff;
+      len= ref->key_length;
+    }
   }
 
   pos= init_pos+rec_len;
 
+  if (rejected)
+    return get_next_key(key);
+
   return len;
 } 
 

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2010-09-23 18:25:45 +0000
+++ b/sql/sql_select.cc	2010-09-24 02:22:36 +0000
@@ -13899,11 +13899,8 @@
   }
 
   /* Perform "Late NULLs Filtering" (see internals manual for explanations) */
-  for (uint i= 0 ; i < tab->ref.key_parts ; i++)
-  {
-    if ((tab->ref.null_rejecting & 1 << i) && tab->ref.items[i]->is_null())
-        return -1;
-  }
+  if (tab->ref.rejected_for_join_as_unknown())
+    return -1;
 
   if (cp_buffer_from_ref(tab->join->thd, table, &tab->ref))
     return -1;

=== modified file 'sql/sql_select.h'
--- a/sql/sql_select.h	2010-09-02 06:11:14 +0000
+++ b/sql/sql_select.h	2010-09-24 02:22:36 +0000
@@ -119,6 +119,23 @@
 
   bool tmp_table_index_lookup_init(THD *thd, KEY *tmp_key, Item_iterator &it,
                                    bool value);
+
+  /**
+    @returns TRUE if the reference makes joining condition to be evaluated
+             to unknown as at least one of the reference's component is null
+  */
+  bool rejected_for_join_as_unknown()
+  {
+    if (!null_rejecting)
+      return FALSE;
+    for (uint i= 0 ; i < key_parts ; i++)
+    {
+      if ((null_rejecting & 1 << i) && items[i]->is_null())
+        return TRUE;
+    }
+    return FALSE;
+  }    
+
 } TABLE_REF;
 
 



More information about the commits mailing list