Re: Wrong result with maria-5.3-mwl128, join_cache_level=4, BNLH and integers only
Test case. maria-5.3 returns consistent results on all join_cache_levels.

SET SESSION join_cache_level = 4;
SET SESSION join_buffer_size = 164;

--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,
PRIMARY KEY (pk),
KEY col_int_key (col_int_key)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;
INSERT INTO t1 VALUES (6,-1636630528),(2,-1097924608),(1,6),(3,6),(4,1148715008),(5,1541734400);
CREATE TABLE t2 (
col_int_key int(11) DEFAULT NULL,
pk int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (pk),
KEY col_int_key (col_int_key)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
INSERT INTO t2 VALUES (-1993998336,20),(-1036582912,1),(-733413376,5),(-538247168,16),(-514260992,4),(-249561088,9),(1,2),(1,6),(2,10),(2,19),(4,17),(5,14),(5,15),(6,8),(7,13),(8,18),(9,11),(9,12),(257425408,7),(576061440,3);

SELECT t1 .col_int_key AS field1
FROM t2 JOIN t1 ON t2.col_int_key = t1.pk
WHERE t1.pk BETWEEN 0 AND 224
HAVING field1 > 7
ORDER BY field1 ;

Re: Wrong result with maria-5.3-mwl128, join_cache_level=4, BNLH and integers only
Philip,

With the latest version of mwl128 tree I had:

MariaDB [test]> SET SESSION join_buffer_size = 164;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> SET SESSION join_cache_level = 1;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> SELECT t1 .col_int_key AS field1
-> FROM t2 JOIN t1 ON t2.col_int_key = t1.pk
-> WHERE t1.pk BETWEEN 0 AND 224
-> HAVING field1 > 7
-> ORDER BY field1 ;
-------------

-------------

-------------
8 rows in set (0.00 sec)

MariaDB [test]> set join_cache_level=4;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> SELECT t1 .col_int_key AS field1 FROM t2 JOIN t1 ON t2.col_int_key = t1.pk WHERE t1.pk BETWEEN 0 AND 224 HAVING field1 > 7 ORDER BY field1;
------------

------------

------------
3 rows in set (0.01 sec)

MariaDB [test]> set join_cache_level=6;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> SELECT t1 .col_int_key AS field1 FROM t2 JOIN t1 ON t2.col_int_key = t1.pk WHERE t1.pk BETWEEN 0 AND 224 HAVING field1 > 7 ORDER BY field1;
-------------

-------------

-------------
8 rows in set (0.00 sec)

Re: Wrong result with maria-5.3-mwl128, join_cache_level=4, BNLH and integers only
Yep, rows "-1636630528" do not match the HAVING condition and therefore should not be part of the result set.

Re: Wrong result with maria-5.3-mwl128, join_cache_level=4, BNLH and integers only
The same problem exists in the 5.3 ree

Re: Wrong result with maria-5.3-mwl128, join_cache_level=4, BNLH and integers only
A patch fixing the bug has been committed against the 5.3 tree.

Launchpad bug id: 668644