diff options
| -rw-r--r-- | mysql-test/r/join_cache.result | 43 | ||||
| -rw-r--r-- | mysql-test/t/join_cache.test | 33 | ||||
| -rw-r--r-- | sql/key.cc | 8 |
3 files changed, 48 insertions, 36 deletions
diff --git a/mysql-test/r/join_cache.result b/mysql-test/r/join_cache.result index 37776a7bd87..3e6a27357a9 100644 --- a/mysql-test/r/join_cache.result +++ b/mysql-test/r/join_cache.result @@ -6221,21 +6221,34 @@ f1 f2 f1 f2 SET SESSION join_cache_level = DEFAULT; DROP TABLE t1,t2; # -# Backported testcase for: Bug #45092: join buffer contains two blob columns one of which is -# used in the key employed to access the joined table +# Bug #707827: hash join on varchar column with NULLs # -CREATE TABLE t1 (c1 int, c2 int, key (c2)); -INSERT INTO t1 VALUES (1,1); -INSERT INTO t1 VALUES (2,2); -CREATE TABLE t2 (c1 text, c2 text); -INSERT INTO t2 VALUES('tt', 'uu'); -INSERT INTO t2 VALUES('zzzz', 'xxxxxxxxx'); -ANALYZE TABLE t1,t2; -set join_cache_level=6; -SELECT t1.*, t2.*, LENGTH(t2.c1), LENGTH(t2.c2) FROM t1,t2 -WHERE t1.c2=LENGTH(t2.c2) and t1.c1=LENGTH(t2.c1); -c1 c2 c1 c2 LENGTH(t2.c1) LENGTH(t2.c2) -2 2 tt uu 2 2 -set join_cache_level=default; +CREATE TABLE t1 (v varchar(1)); +INSERT INTO t1 VALUES ('o'), ('u'); +CREATE TABLE t2 (a int, v varchar(1), INDEX idx (v)) ; +INSERT INTO t2 VALUES +(8,NULL), (10,'b'), (5,'k'), (4,NULL), +(1,NULL), (11,'u'), (7,NULL), (2,'d'); +SET SESSION join_buffer_size = 255; +SET SESSION join_cache_level = 4; +EXPLAIN +SELECT a FROM t1,t2 WHERE t2.v = t1.v ; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where +1 SIMPLE t2 ref idx idx 4 test.t1.v 2 Using join buffer (flat, BNLH join) +SELECT a FROM t1,t2 WHERE t2.v = t1.v ; +a +11 +SET SESSION join_cache_level = 1; +EXPLAIN +SELECT a FROM t1,t2 WHERE t2.v = t1.v ; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where +1 SIMPLE t2 ref idx idx 4 test.t1.v 2 +SELECT a FROM t1,t2 WHERE t2.v = t1.v ; +a +11 +SET SESSION join_cache_level = DEFAULT; +SET SESSION join_buffer_size = 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 692057a371b..e6669833824 100644 --- a/mysql-test/t/join_cache.test +++ b/mysql-test/t/join_cache.test @@ -2847,28 +2847,31 @@ SET SESSION join_cache_level = DEFAULT; DROP TABLE t1,t2; --echo # ---echo # Backported testcase for: Bug #45092: join buffer contains two blob columns one of which is ---echo # used in the key employed to access the joined table +--echo # Bug #707827: hash join on varchar column with NULLs --echo # -CREATE TABLE t1 (c1 int, c2 int, key (c2)); -INSERT INTO t1 VALUES (1,1); -INSERT INTO t1 VALUES (2,2); +CREATE TABLE t1 (v varchar(1)); +INSERT INTO t1 VALUES ('o'), ('u'); -CREATE TABLE t2 (c1 text, c2 text); -INSERT INTO t2 VALUES('tt', 'uu'); -INSERT INTO t2 VALUES('zzzz', 'xxxxxxxxx'); +CREATE TABLE t2 (a int, v varchar(1), INDEX idx (v)) ; +INSERT INTO t2 VALUES + (8,NULL), (10,'b'), (5,'k'), (4,NULL), + (1,NULL), (11,'u'), (7,NULL), (2,'d'); ---disable_result_log -ANALYZE TABLE t1,t2; ---enable_result_log +SET SESSION join_buffer_size = 255; -set join_cache_level=6; +SET SESSION join_cache_level = 4; +EXPLAIN +SELECT a FROM t1,t2 WHERE t2.v = t1.v ; +SELECT a FROM t1,t2 WHERE t2.v = t1.v ; -SELECT t1.*, t2.*, LENGTH(t2.c1), LENGTH(t2.c2) FROM t1,t2 - WHERE t1.c2=LENGTH(t2.c2) and t1.c1=LENGTH(t2.c1); +SET SESSION join_cache_level = 1; +EXPLAIN +SELECT a FROM t1,t2 WHERE t2.v = t1.v ; +SELECT a FROM t1,t2 WHERE t2.v = t1.v ; -set join_cache_level=default; +SET SESSION join_cache_level = DEFAULT; +SET SESSION join_buffer_size = DEFAULT; DROP TABLE t1,t2; diff --git a/sql/key.cc b/sql/key.cc index fd5c129eee8..19db7e9ec1f 100644 --- a/sql/key.cc +++ b/sql/key.cc @@ -663,8 +663,6 @@ ulong key_hashnr(KEY *key_info, uint used_key_parts, const uchar *key) switch (key_part->type) { case HA_KEYTYPE_VARTEXT1: case HA_KEYTYPE_VARBINARY1: - key++; - break; case HA_KEYTYPE_VARTEXT2: case HA_KEYTYPE_VARBINARY2: key+= 2; @@ -769,8 +767,6 @@ bool key_buf_cmp(KEY *key_info, uint used_key_parts, switch (key_part->type) { case HA_KEYTYPE_VARTEXT1: case HA_KEYTYPE_VARBINARY1: - key1++; key2++; - break; case HA_KEYTYPE_VARTEXT2: case HA_KEYTYPE_VARBINARY2: key1+= 2; key2+= 2; @@ -778,10 +774,10 @@ bool key_buf_cmp(KEY *key_info, uint used_key_parts, default: ; } - continue; + continue; } if (*pos1 != *pos2) - return FALSE; + return TRUE; pos1++; pos2++; } |