|
CREATE TABLE t1 (f1 VARCHAR(8) /*!100302 COMPRESSED */);
|
INSERT INTO t1 VALUES ('a'),('b'),('c'),('d');
|
|
CREATE TABLE t2 (f2 VARCHAR(8));
|
INSERT INTO t2 VALUES ('b'),('d');
|
|
set join_cache_level= 3;
|
SELECT * FROM t1 JOIN t2 ON ( f1 = f2 );
|
|
Actual result (empty)
|
set join_cache_level= 3;
|
SELECT * FROM t1 JOIN t2 ON ( f1 = f2 );
|
f1 f2
|
|
EXPLAIN EXTENDED
|
EXPLAIN EXTENDED SELECT * FROM t1 JOIN t2 ON ( f1 = f2 );
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where
|
1 SIMPLE t1 hash_ALL NULL #hash#$hj 12 test.t2.f2 4 100.00 Using where; Using join buffer (flat, BNLH join)
|
Warnings:
|
Note 1003 select `test`.`t1`.`f1` AS `f1`,`test`.`t2`.`f2` AS `f2` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`f1` = `test`.`t2`.`f2`
|
Reproducible with at least InnoDB, MyISAM, Aria.
Reproducible on 10.3-10.5.
Reproducible with all join_cache_level values greater than 2.
Not applicable to 10.2, as compressed columns were introduced in 10.3.
Not reproducible when t1.f1 is not compressed, even although the plan looks the same.
Not reproducible with join_cache_level=2, the plan shows BNL join instead of BNLH.
In both cases the query returns 2 rows as expected:
|
Expected result
|
SELECT * FROM t1 JOIN t2 ON ( f1 = f2 );
|
f1 f2
|
b b
|
d d
|
|