No rows returned with (flat, BNLH join) in maria-5.3-mwl128

[Philip Stoev]

The following query

SELECT table1.col_int_key FROM t1 AS table1 JOIN t2 AS table2 ON table1.col_varchar_10_utf8_key = table2.col_varchar_1024_utf8_key;

returns no rows when executed with (flat, BNLH join) , but returns rows when executed with full table scan and classical nested loop join.

[Philip Stoev (pstoev-askmonty)]

Test case:

--disable_warnings
DROP TABLE /*! IF EXISTS */ t1;
DROP TABLE /*! IF EXISTS */ t2;
--enable_warnings

CREATE TABLE t1 (
  col_varchar_10_utf8_key varchar(10) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  col_int_key int(11) DEFAULT NULL,
  col_varchar_1024_utf8_key varchar(1024) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  KEY col_varchar_10_utf8_key (col_varchar_10_utf8_key),
  KEY col_int_key (col_int_key),
  KEY col_varchar_1024_utf8_key (col_varchar_1024_utf8_key(255))
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO t1 VALUES ('did',5,'abcdefjhjkl'),('was',-1631322112,'no'),('are',3,'abcdefjhjkl'),('abcdefjhjkl',3,'w'),('abcdefjhjkl',4,'x'),('tell',-824573952,'abcdefjhjkl'),('t',0,'r'),('v',-1711013888,'the'),('abcdefjhjkl',1015414784,'x'),('or',4,'o'),('now',0,'have'),('abcdefjhjkl',-32702464,'h'),('abcdefjhjkl',4,'abcdefjhjkl'),('time',1078394880,'abcdefjhjkl'),('f',4,'j'),('m',-1845559296,'abcdefjhjkl'),('abcdefjhjkl',-1074397184,'something');

CREATE TABLE t2 (
  col_varchar_10_utf8_key varchar(10) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  col_int_key int(11) DEFAULT NULL,
  col_varchar_1024_utf8_key varchar(1024) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  KEY col_varchar_10_utf8_key (col_varchar_10_utf8_key),
  KEY col_int_key (col_int_key),
  KEY col_varcohar_1024_utf8_key (col_varchar_1024_utf8_key(333))
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO t2 VALUES ('abcdefjhjkl',8,'k'),('abcdefjhjkl',-575340544,'abcdefjhjkl'),('some',2,'because'),('of',-517472256,'f'),('h',5,'abcdefjhjkl'),('mean',7,'z');

SET SESSION join_cache_level = 6;
SET SESSION join_buffer_size = 1024;
SET SESSION optimizer_switch='join_cache_bka=off';

SELECT table1.col_int_key FROM t1 AS table1 JOIN t2 AS table2 ON table1.col_varchar_10_utf8_key = table2.col_varchar_1024_utf8_key ;
EXPLAIN SELECT table1.col_int_key FROM t1 AS table1 JOIN t2 AS table2 ON table1.col_varchar_10_utf8_key = table2.col_varchar_1024_utf8_key ;

SET SESSION join_cache_level=0;
ALTER TABLE t1 DISABLE KEYS;
ALTER TABLE t2 DISABLE KEYS;
SELECT table1.col_int_key FROM t1 AS table1 JOIN t2 AS table2 ON table1.col_varchar_10_utf8_key = table2.col_varchar_1024_utf8_key;
EXPLAIN SELECT table1.col_int_key FROM t1 AS table1 JOIN t2 AS table2 ON table1.col_varchar_10_utf8_key = table2.col_varchar_1024_utf8_key;

DROP TABLE t1;
DROP TABLE t2;