Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Critical
-
Resolution: Fixed
-
Affects Version/s: None
-
Fix Version/s: None
-
Component/s: None
-
Labels:
Description
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.
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Re: Wrong result with join_cache_level=3, BNLH join in maria-5.3-mwl128
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)
Launchpad bug id: 697557
Re: Wrong result with join_cache_level=3, BNLH join in maria-5.3-mwl128
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;
------
------
------
1 row in set (0.00 sec)