Wrong result with maria-5.3-mwl128, join_cache_level=4, BNLH and integers only
Bug #668644 reported by
Philip Stoev
This bug affects 1 person
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
MariaDB |
Fix Released
|
Medium
|
Igor Babaev |
Bug Description
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.
Related branches
Changed in maria: | |
status: | Fix Committed → Fix Released |
To post a comment you must log in.
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 ( ,(2,-1097924608 ),(1,6) ,(3,6), (4,1148715008) ,(5,1541734400) ; 20),(-103658291 2,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);
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)
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,
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 ;