Wrong result with join_cache_level=3, BNLH join in maria-5.3-mwl128

[Philip Stoev]

After the most recent push to maria-5.3-mwl128, the following query:

SELECT t1.f2 FROM t2 JOIN t1 ON t2.f1 = t1.f1;

returns rows that do not match the ON condition.

Explain:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 index f1 f1 13 NULL 1 Using where; Using index
1 SIMPLE t1 hash f1 f1 13 test.t2.f1 4 Using join buffer (flat, BNLH join)

test case:

SET SESSION SQL_MODE='NO_ENGINE_SUBSTITUTION';

CREATE TABLE t1 ( f1 varchar(10) , f2 int(11) , KEY (f1)) ENGINE=PBXT;
INSERT INTO t1 VALUES ('hgtofubnib',1),('GDOXZ',1492123648),('n',2),('fggxgalhgt',-2024407040);
CREATE TABLE t2 ( f1 varchar(10) , f2 int(11) , KEY (f1)) ENGINE=PBXT;
INSERT INTO t2 VALUES ('r',1);

SET SESSION join_cache_level=3;

SELECT t1.f2 FROM t2 JOIN t1 ON t2.f1 = t1.f1;

This particular test case is for PBXT, but the issue has been observed with other storage engines as well.

[Igor Babaev (igorb-seattle)]

The bug is reproducible without the latest push into maria-5.3-mwl128 as well.
With the current 5.3 tree we have:

MariaDB [test]> SET SESSION SQL_MODE='NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> CREATE TABLE t1 ( f1 varchar(10) , f2 int(11) , KEY (f1)) ENGINE=PBXT;
Query OK, 0 rows affected (0.28 sec)

MariaDB [test]> INSERT INTO t1 VALUES ('hgtofubnib',1),('GDOXZ',1492123648),('n',2),('fggxgalhgt',-2024407040);
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0

MariaDB [test]> CREATE TABLE t2 ( f1 varchar(10) , f2 int(11) , KEY (f1)) ENGINE=PBXT;
Query OK, 0 rows affected (0.02 sec)

MariaDB [test]> INSERT INTO t2 VALUES ('r',1);
Query OK, 1 row affected (0.01 sec)

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

MariaDB [test]>
MariaDB [test]> SELECT t1.f2 FROM t2 JOIN t1 ON t2.f1 = t1.f1;
+------+
| f2 |
+------+
| 2 |
+------+
1 row in set (0.00 sec)

[Igor Babaev (igorb-seattle)]

Here's the test case where this problem can be seen with MyISAM in 5.3:

 MariaDB [test]> CREATE TABLE t1 ( f1 varchar(10) , f2 int(11) , KEY (f1));
Query OK, 0 rows affected (0.01 sec)

MariaDB [test]> INSERT INTO t1 VALUES ('r',1), ('m',2);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0

MariaDB [test]> CREATE TABLE t2 ( f1 varchar(10) , f2 int(11) , KEY (f1));
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> INSERT INTO t2 VALUES
    -> ('hgtofubn',1), ('GDOXZ',91), ('n',2), ('fggxgalh',88),
    -> ('hgtofu',1), ('GDO',101), ('n',3), ('fggxga',55),
    -> ('hgtofu',3), ('GDO',33), ('nn',3), ('fggxgarrr',77);
Query OK, 12 rows affected (0.00 sec)
Records: 12 Duplicates: 0 Warnings: 0

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

MariaDB [test]> EXPLAIN
    -> SELECT * FROM t1,t2 WHERE t2.f1 = t1.f1;
+----+-------------+-------+------+---------------+------+---------+------------+------+-------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------------+------+-------------------------------------+
| 1 | SIMPLE | t1 | ALL | f1 | NULL | NULL | NULL | 2 | Using where |
| 1 | SIMPLE | t2 | ref | f1 | f1 | 13 | test.t1.f1 | 2 | Using join buffer (flat, BNLH join) |
+----+-------------+-------+------+---------------+------+---------+------------+------+-------------------------------------+
2 rows in set (0.00 sec)

MariaDB [test]> SELECT * FROM t1,t2 WHERE t2.f1 = t1.f1;
+------+------+------+------+
| f1 | f2 | f1 | f2 |
+------+------+------+------+
| r | 1 | n | 2 |
| m | 2 | n | 2 |
| r | 1 | n | 3 |
| m | 2 | n | 3 |
+------+------+------+------+
4 rows in set (0.00 sec)