[Commits] Rev 2847: Fixed LP bug #671901. in file:///home/igor/maria/maria-5.3-mwl128-bug671901/

Igor Babaev igor at askmonty.org
Sun Nov 7 18:32:18 EET 2010


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

------------------------------------------------------------
revno: 2847
revision-id: igor at askmonty.org-20101107163217-naqlxcf65guty3lb
parent: igor at askmonty.org-20101106022054-fxkng8ovao0ru7sj
committer: Igor Babaev <igor at askmonty.org>
branch nick: maria-5.3-mwl128-bug671901
timestamp: Sun 2010-11-07 08:32:17 -0800
message:
  Fixed LP bug #671901.
  Currently BNLH join uses a simplified implementation of hash function
  when hash function is calculated over the whole key buffer, not only
  the significant bytes of it. It means that both building keys and
  probing keys both must fill insignificant bytes with the same filler.
  Usually 0 is used as such a filler.
  Yet the code before patch filled insignificant bytes only for probing
  keys. 
-------------- next part --------------
=== modified file 'mysql-test/r/join_cache.result'
--- a/mysql-test/r/join_cache.result	2010-11-05 04:00:33 +0000
+++ b/mysql-test/r/join_cache.result	2010-11-07 16:32:17 +0000
@@ -5615,4 +5615,43 @@
 SET SESSION join_cache_level = DEFAULT;
 SET SESSION join_buffer_size = DEFAULT;
 DROP TABLE t1,t2;
+#
+# Bug #671901: hash join using a ref to a varchar field
+#
+CREATE TABLE t1 (
+v varchar(10) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
+i int  DEFAULT NULL
+);
+INSERT INTO t1 VALUES
+('k',8), ('abcdefjh',-575340544), ('because', 2), ('f',-517472256), ('abcdefjhj',5), ('z',7);
+CREATE TABLE t2 (
+v varchar(10) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
+i int DEFAULT NULL,
+INDEX idx (v)
+);
+INSERT INTO t2 VALUES
+('did',5), ('was',-1631322112), ('are',3), ('abcdefjhjk',3), 
+('abcdefjhjk',4), ('tell',-824573952), ('t',0),('v',-1711013888),
+('abcdefjhjk',1015414784), ('or',4), ('now',0), ('abcdefjhjk',-32702464),
+('abcdefjhjk',4), ('time',1078394880), ('f',4), ('m',-1845559296), 
+('abcdefjhjk',-1074397184);
+EXPLAIN 
+SELECT t1.v,t2.i FROM t1,t2 WHERE t1.v = t2.v;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	6	Using where
+1	SIMPLE	t2	ref	idx	idx	13	test.t1.v	2	
+SELECT t1.v,t2.i FROM t1,t2 WHERE t1.v = t2.v;
+v	i
+f	4
+SET SESSION join_cache_level = 4;
+EXPLAIN 
+SELECT t1.v,t2.i FROM t1,t2 WHERE t1.v = t2.v;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	6	Using where
+1	SIMPLE	t2	ref	idx	idx	13	test.t1.v	2	Using join buffer (flat, BNLH join)
+SELECT t1.v,t2.i FROM t1,t2 WHERE t1.v = t2.v;
+v	i
+f	4
+SET SESSION 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-11-05 04:00:33 +0000
+++ b/mysql-test/t/join_cache.test	2010-11-07 16:32:17 +0000
@@ -2315,5 +2315,41 @@
 
 DROP TABLE t1,t2;
 
+--echo #
+--echo # Bug #671901: hash join using a ref to a varchar field
+--echo #
+
+CREATE TABLE t1 (
+  v varchar(10) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
+  i int  DEFAULT NULL
+);
+INSERT INTO t1 VALUES
+ ('k',8), ('abcdefjh',-575340544), ('because', 2), ('f',-517472256), ('abcdefjhj',5), ('z',7);
+
+CREATE TABLE t2 (
+  v varchar(10) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
+  i int DEFAULT NULL,
+  INDEX idx (v)
+);
+INSERT INTO t2 VALUES
+  ('did',5), ('was',-1631322112), ('are',3), ('abcdefjhjk',3), 
+  ('abcdefjhjk',4), ('tell',-824573952), ('t',0),('v',-1711013888),
+  ('abcdefjhjk',1015414784), ('or',4), ('now',0), ('abcdefjhjk',-32702464),
+  ('abcdefjhjk',4), ('time',1078394880), ('f',4), ('m',-1845559296), 
+  ('abcdefjhjk',-1074397184);
+
+EXPLAIN 
+SELECT t1.v,t2.i FROM t1,t2 WHERE t1.v = t2.v;
+SELECT t1.v,t2.i FROM t1,t2 WHERE t1.v = t2.v;
+
+SET SESSION join_cache_level = 4;
+EXPLAIN 
+SELECT t1.v,t2.i FROM t1,t2 WHERE t1.v = t2.v;
+SELECT t1.v,t2.i FROM t1,t2 WHERE t1.v = t2.v;
+
+SET SESSION 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/sql_select.h'
--- a/sql/sql_select.h	2010-11-06 02:20:54 +0000
+++ b/sql/sql_select.h	2010-11-07 16:32:17 +0000
@@ -2431,6 +2431,15 @@
     TABLE *table= copy_field.to_field->table;
     my_bitmap_map *old_map= dbug_tmp_use_all_columns(table,
                                                      table->write_set);
+
+    /* 
+      It looks like the next statement is needed only for a simplified
+      hash function over key values used now in BNLH join.
+      When the implementation of this function will be replaced for a proper
+      full version this statement probably should be removed.
+    */  
+    bzero(copy_field.to_ptr,copy_field.to_length);
+
     copy_field.do_copy(&copy_field);
     dbug_tmp_restore_column_map(table->write_set, old_map);
     null_key= to_field->is_null();



More information about the commits mailing list