Wrong result with join buffer (flat, BNLH join) and GROUP BY/LIMIT in maria-5.3-mwl128

[Philip Stoev]

The following query:

SELECT t1.col_int_key AS field1 FROM t2 JOIN t1 ON t2.pk WHERE t2.col_int_key = 143 GROUP BY field1 LIMIT 10;

When executed with join_cache_level = 4, returns 10 rows which are totally bogus since there is no row for which t2.col_int_key = 143. The explain reports "Using where; Using index; Using join buffer (flat, BNLH join)".

[Philip Stoev (pstoev-askmonty)]

Test case:

--source include/have_innodb.inc

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

CREATE TABLE t1 (
  pk int(11) NOT NULL AUTO_INCREMENT,
  col_int_key int(11) DEFAULT NULL,
  col_varchar_key varchar(1) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  PRIMARY KEY (pk),
  KEY col_int_key (col_int_key),
  KEY col_varchar_key (col_varchar_key,col_int_key)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
INSERT INTO t1 VALUES (11,0,NULL),(16,1,'c'),(20,2,'d'),(13,166,'e'),(19,6,'f'),(9,8,'h'),(7,3,'j'),(12,5,'k'),(4,9,'k'),(3,3,'m'),(2,9,'m'),(17,9,'m'),(14,3,'n'),(10,53,'o'),(5,NULL,'r'),(15,0,'t'),(6,9,'t'),(8,8,'u'),(1,2,'w'),(18,5,'y');
CREATE TABLE t2 (
  pk int(11) NOT NULL AUTO_INCREMENT,
  col_int_key int(11) DEFAULT NULL,
  col_varchar_key varchar(1) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  PRIMARY KEY (pk),
  KEY col_int_key (col_int_key),
  KEY col_varchar_key (col_varchar_key,col_int_key)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
INSERT INTO t2 VALUES (1,7,'f');

SET SESSION join_cache_level = 4;
SET SESSION join_buffer_size = 136;

EXPLAIN SELECT t1.col_int_key AS field1 FROM t2 JOIN t1 ON t2.pk WHERE t2.col_int_key = 143 GROUP BY field1 LIMIT 10;
SELECT t1.col_int_key AS field1 FROM t2 JOIN t1 ON t2.pk WHERE t2.col_int_key = 143 GROUP BY field1 LIMIT 10;

DROP TABLE t1;
DROP TABLE t2;