diff options
| -rw-r--r-- | mysql-test/r/join_cache.result | 20 | ||||
| -rw-r--r-- | mysql-test/t/join_cache.test | 22 | ||||
| -rw-r--r-- | sql/key.cc | 22 | ||||
| -rw-r--r-- | sql/sql_select.cc | 6 |
4 files changed, 47 insertions, 23 deletions
diff --git a/mysql-test/r/join_cache.result b/mysql-test/r/join_cache.result index b5ed0b9d59a..23a3d2081e2 100644 --- a/mysql-test/r/join_cache.result +++ b/mysql-test/r/join_cache.result @@ -6952,4 +6952,24 @@ a a b 2 NULL 42 SET SESSION join_cache_level = DEFAULT; DROP TABLE t1,t2; +# +# Bug #697557: hash join on a varchar field +# +CREATE TABLE t1 ( f1 varchar(10) , f2 int(11) , KEY (f1)); +INSERT INTO t1 VALUES ('r',1), ('m',2); +CREATE TABLE t2 ( f1 varchar(10) , f2 int(11) , KEY (f1)); +INSERT INTO t2 VALUES +('hgtofubn',1), ('GDOXZ',91), ('n',2), ('fggxgalh',88), +('hgtofu',1), ('GDO',101), ('n',3), ('fggxga',55), +('hgtofu',3), ('GDO',33), ('nn',3), ('fggxgarrr',77); +SET SESSION join_cache_level=3; +EXPLAIN +SELECT * FROM t1,t2 WHERE t2.f1 = t1.f1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL f1 NULL NULL NULL 2 Using where +1 SIMPLE t2 hash f1 f1 13 test.t1.f1 12 Using join buffer (flat, BNLH join) +SELECT * FROM t1,t2 WHERE t2.f1 = t1.f1; +f1 f2 f1 f2 +SET SESSION join_cache_level = DEFAULT; +DROP TABLE t1,t2; set @@optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/t/join_cache.test b/mysql-test/t/join_cache.test index ab9e10536e1..9892d2cf5be 100644 --- a/mysql-test/t/join_cache.test +++ b/mysql-test/t/join_cache.test @@ -2931,6 +2931,28 @@ SET SESSION join_cache_level = DEFAULT; DROP TABLE t1,t2; +--echo # +--echo # Bug #697557: hash join on a varchar field +--echo # + +CREATE TABLE t1 ( f1 varchar(10) , f2 int(11) , KEY (f1)); +INSERT INTO t1 VALUES ('r',1), ('m',2); + +CREATE TABLE t2 ( f1 varchar(10) , f2 int(11) , KEY (f1)); +INSERT INTO t2 VALUES + ('hgtofubn',1), ('GDOXZ',91), ('n',2), ('fggxgalh',88), + ('hgtofu',1), ('GDO',101), ('n',3), ('fggxga',55), + ('hgtofu',3), ('GDO',33), ('nn',3), ('fggxgarrr',77); + +SET SESSION join_cache_level=3; +EXPLAIN +SELECT * FROM t1,t2 WHERE t2.f1 = t1.f1; +SELECT * FROM t1,t2 WHERE t2.f1 = t1.f1; + +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; diff --git a/sql/key.cc b/sql/key.cc index 780cc6733c1..fd5c129eee8 100644 --- a/sql/key.cc +++ b/sql/key.cc @@ -689,20 +689,12 @@ ulong key_hashnr(KEY *key_info, uint used_key_parts, const uchar *key) pack_length= 0; break; case HA_KEYTYPE_VARTEXT1: - cs= key_part->field->charset(); - length= (uint)(pos[0]); - pack_length= 1; - break; - case HA_KEYTYPE_VARBINARY1: - cs= &my_charset_bin; - length= (uint)(pos[0]); - pack_length= 1; - break; case HA_KEYTYPE_VARTEXT2: cs= key_part->field->charset(); length= uint2korr(pos); pack_length= 2; break; + case HA_KEYTYPE_VARBINARY1: case HA_KEYTYPE_VARBINARY2: cs= &my_charset_bin; length= uint2korr(pos); @@ -806,23 +798,13 @@ bool key_buf_cmp(KEY *key_info, uint used_key_parts, pack_length= 0; break; case HA_KEYTYPE_VARTEXT1: - cs= key_part->field->charset(); - length1= (uint)(pos1[0]); - length2= (uint)(pos2[0]); - pack_length= 1; - break; - case HA_KEYTYPE_VARBINARY1: - cs= &my_charset_bin; - length1= (uint)(pos1[0]); - length2= (uint)(pos2[0]); - pack_length= 1; - break; case HA_KEYTYPE_VARTEXT2: cs= key_part->field->charset(); length1= uint2korr(pos1); length2= uint2korr(pos2); pack_length= 2; break; + case HA_KEYTYPE_VARBINARY1: case HA_KEYTYPE_VARBINARY2: cs= &my_charset_bin; length1= uint2korr(pos1); diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 37930edb27f..de9c7f5c13a 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -7877,8 +7877,8 @@ uint check_join_cache_usage(JOIN_TAB *tab, bool *icp_other_tables_ok, bool *idx_cond_fact_out) { - uint flags; COST_VECT cost; + uint flags= 0; ha_rows rows= 0; uint bufsz= 4096; JOIN_CACHE *prev_cache=0; @@ -7996,8 +7996,8 @@ uint check_join_cache_usage(JOIN_TAB *tab, } if ((cache_level <=4 && !no_hashed_cache) || no_bka_cache || - ((flags & HA_MRR_NO_ASSOCIATION) && cache_level <=6) || - tab->is_ref_for_hash_join()) + tab->is_ref_for_hash_join() || + ((flags & HA_MRR_NO_ASSOCIATION) && cache_level <=6)) { if (!tab->hash_join_is_possible() || tab->make_scan_filter()) |