revision-id: e3e015b56b23de48e5a0f7d5e0a27fb9f576f7aa (mariadb-5.5.56-247-ge3e015b56b2) parent(s): 90cb7212742e9ae3a63bd183e171c95bd12d559f author: Varun Gupta committer: Varun Gupta timestamp: 2018-07-04 18:05:58 +0530 message: MDEV-16307: Incorrect results when using BNLH join instead of BNL join with views In this issue we are using derived_with_keys optimization and we are using these keys to do a hash join which is incorrect. We cannot create keys for BLOBS or TEXT columns as only specified length indexes are allowed, so we cannot allow creation of keys on derived tables if the keyparts have BLOBS or TEXT columns. --- mysql-test/r/derived.result | 31 +++++++++++++++++++++++++++++++ mysql-test/t/derived.test | 19 +++++++++++++++++++ sql/table.cc | 5 +++-- 3 files changed, 53 insertions(+), 2 deletions(-) diff --git a/mysql-test/r/derived.result b/mysql-test/r/derived.result index 54c78dc9f6f..96f9f49d53e 100644 --- a/mysql-test/r/derived.result +++ b/mysql-test/r/derived.result @@ -1052,4 +1052,35 @@ i drop procedure pr; drop view v1; drop table t1; +# +# Incorrect results when using BNLH join instead of BNL join with views +# +CREATE TABLE t1 (c1 text, c2 int); +INSERT INTO t1 VALUES ('a',1), ('c',3), ('g',7), ('d',4), ('c',3); +CREATE TABLE t2 (c1 text, c2 int); +INSERT INTO t2 VALUES ('b',2), ('c',3); +CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t1; +explain SELECT v1.c1, v1.c2, t2.c1, t2.c2 FROM v1, t2 WHERE v1.c1=t2.c1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where +1 PRIMARY <derived2> hash_ALL NULL #hash#$hj 3 test.t2.c1 5 Using where; Using join buffer (flat, BNLH join) +2 DERIVED t1 ALL NULL NULL NULL NULL 5 +SELECT v1.c1, v1.c2, t2.c1, t2.c2 FROM v1, t2 WHERE v1.c1=t2.c1; +c1 c2 c1 c2 +c 3 c 3 +c 3 c 3 +set @save_join_cache_level= @@join_cache_level; +set @@join_cache_level=4; +explain SELECT v1.c1, v1.c2, t2.c1, t2.c2 FROM v1, t2 WHERE v1.c1=t2.c1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where +1 PRIMARY <derived2> hash_ALL NULL #hash#$hj 3 test.t2.c1 5 Using where; Using join buffer (flat, BNLH join) +2 DERIVED t1 ALL NULL NULL NULL NULL 5 +SELECT v1.c1, v1.c2, t2.c1, t2.c2 FROM v1, t2 WHERE v1.c1=t2.c1; +c1 c2 c1 c2 +c 3 c 3 +c 3 c 3 +drop table t1,t2; +drop view v1; +set @@join_cache_level= @save_join_cache_level; # end of 5.5 diff --git a/mysql-test/t/derived.test b/mysql-test/t/derived.test index c5b792c8d4d..682fe5cb795 100644 --- a/mysql-test/t/derived.test +++ b/mysql-test/t/derived.test @@ -903,4 +903,23 @@ drop procedure pr; drop view v1; drop table t1; +--echo # +--echo # Incorrect results when using BNLH join instead of BNL join with views +--echo # + +CREATE TABLE t1 (c1 text, c2 int); +INSERT INTO t1 VALUES ('a',1), ('c',3), ('g',7), ('d',4), ('c',3); +CREATE TABLE t2 (c1 text, c2 int); +INSERT INTO t2 VALUES ('b',2), ('c',3); +CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t1; + +explain SELECT v1.c1, v1.c2, t2.c1, t2.c2 FROM v1, t2 WHERE v1.c1=t2.c1; +SELECT v1.c1, v1.c2, t2.c1, t2.c2 FROM v1, t2 WHERE v1.c1=t2.c1; +set @save_join_cache_level= @@join_cache_level; +set @@join_cache_level=4; +explain SELECT v1.c1, v1.c2, t2.c1, t2.c2 FROM v1, t2 WHERE v1.c1=t2.c1; +SELECT v1.c1, v1.c2, t2.c1, t2.c2 FROM v1, t2 WHERE v1.c1=t2.c1; +drop table t1,t2; +drop view v1; +set @@join_cache_level= @save_join_cache_level; --echo # end of 5.5 diff --git a/sql/table.cc b/sql/table.cc index 48aa445e1aa..cf3b78c8432 100644 --- a/sql/table.cc +++ b/sql/table.cc @@ -6075,11 +6075,12 @@ bool TABLE::check_tmp_key(uint key, uint key_parts, { uint fld_idx= next_field_no(arg); reg_field= field + fld_idx; + if ((*reg_field)->type() == MYSQL_TYPE_BLOB) + return FALSE; uint fld_store_len= (uint16) (*reg_field)->key_length(); if ((*reg_field)->real_maybe_null()) fld_store_len+= HA_KEY_NULL_LENGTH; - if ((*reg_field)->type() == MYSQL_TYPE_BLOB || - (*reg_field)->real_type() == MYSQL_TYPE_VARCHAR || + if ((*reg_field)->real_type() == MYSQL_TYPE_VARCHAR || (*reg_field)->type() == MYSQL_TYPE_GEOMETRY) fld_store_len+= HA_KEY_BLOB_LENGTH; key_len+= fld_store_len;