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

[Philip Stoev]

The following query:

 SELECT table2 .`col_int_key` field1 FROM T table1 JOIN J table2 ON table1 .`col_int_key` = table2 .`pk` WHERE table2 .`pk` BETWEEN 0 AND 224 HAVING field1 > 7 ORDER BY field1

returns rows that do not match the HAVING condition when executed with join_cache_level=4 , join_buffer_size = 164 . The explain plan says "Using index; Using join buffer (flat, BNLH join)".

The query uses only integers, so this is not a charset-mismatch issue.

[Philip Stoev (pstoev-askmonty)]

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 ;

[Igor Babaev (igorb-seattle)]

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 ;
+-------------+
| field1 |
+-------------+
| -1636630528 |
| -1097924608 |
| -1097924608 |
| 6 |
| 6 |
| 1148715008 |
| 1541734400 |
| 1541734400 |
+-------------+
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;
+------------+
| field1 |
+------------+
| 1148715008 |
| 1541734400 |
| 1541734400 |
+------------+
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;
+-------------+
| field1 |
+-------------+
| -1636630528 |
| -1097924608 |
| -1097924608 |
| 6 |
| 6 |
| 1148715008 |
| 1541734400 |
| 1541734400 |
+-------------+
8 rows in set (0.00 sec)

[Philip Stoev (pstoev-askmonty)]

Yep, rows "-1636630528" do not match the HAVING condition and therefore should not be part of the result set.

[Igor Babaev (igorb-seattle)]

The same problem exists in the 5.3 ree

[Igor Babaev (igorb-seattle)]

A patch fixing the bug has been committed against the 5.3 tree.