[MDEV-3077] LP:697557 - Wrong result with join_cache_level=3, BNLH join in maria-5.3-mwl128 Created: 2011-01-05  Updated: 2015-02-02  Resolved: 2012-10-04

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Critical
Reporter: Philip Stoev (Inactive) Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug697557.xml    

 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.

 Comments   
Comment by Igor Babaev [ 2011-01-05 ]

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;
------

f2

------

2

------
1 row in set (0.00 sec)

Comment by Igor Babaev [ 2011-01-05 ]

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)

Comment by Rasmus Johansson (Inactive) [ 2011-12-13 ]

Launchpad bug id: 697557

Generated at Thu Feb 08 06:46:10 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.