Wrong result in maria-5.3-mwl128 with join_cache_level = 4 and small join_buffer_size and join buffer (flat, BNLH join)
Bug #668290 reported by
Philip Stoev
This bug affects 1 person
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
MariaDB |
Fix Released
|
High
|
Igor Babaev |
Bug Description
The following query:
SELECT t2 .col_int_key FROM t2 JOIN t1 ON t2 .col_varchar_
returns no rows when executed with join_cache_level = 4, join_buffer_size = 164 , even though there are rows for which the condition t2 .col_varchar_
The execution strategy is reported as "Using join buffer (flat, BNLH join)". maria-5.3 is not affected.
Related branches
Changed in maria: | |
status: | New → Confirmed |
importance: | Undecided → High |
Changed in maria: | |
status: | Confirmed → Fix Committed |
Changed in maria: | |
status: | Fix Committed → Fix Released |
To post a comment you must log in.
Test case:
--disable_warnings
DROP TABLE /*! IF EXISTS */ t1;
DROP TABLE /*! IF EXISTS */ t2;
--enable_warnings
CREATE TABLE t1 ( 10_latin1_ key varchar(10) DEFAULT NULL, 10_utf8_ key varchar(10) CHARACTER SET utf8 DEFAULT NULL, 10_latin1_ key (col_varchar_ 10_latin1_ key), 10_utf8_ key (col_varchar_ 10_utf8_ key) 'cmxffkpsel' ,'z'),( 535298048, 'tvtjrcmxff' ,'y'),( 1626865664, 'when', 'for'), (39649280, 'rcvljitvtj' ,'ercvljitvt' ),(792068096, 'ttercvljit' ,'jttercvlji' ); 10_utf8_ key varchar(10) CHARACTER SET utf8 DEFAULT NULL, 10_latin1_ key varchar(10) DEFAULT NULL, 10_utf8_ key (col_varchar_ 10_utf8_ key), 10_latin1_ key (col_varchar_ 10_latin1_ key) 'like') ,('fujttercvl' ,6,'y') ,('s',2, 'e'),(' didn\'t' ,0,'v') ,('gvdrodpedk' ,8,'chogvdrodp' ),('jichogvdro' ,7,'will' );
col_int_key int(11) DEFAULT NULL,
col_varchar_
col_varchar_
KEY col_int_key (col_int_key),
KEY col_varchar_
KEY col_varchar_
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO t1 VALUES (650903552,
CREATE TABLE t2 (
col_varchar_
col_int_key int(11) DEFAULT NULL,
col_varchar_
KEY col_varchar_
KEY col_int_key (col_int_key),
KEY col_varchar_
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO t2 VALUES ('g',7,
SELECT t2 .col_int_key FROM t2 JOIN t1 ON t2 .col_varchar_ 10_latin1_ key = t1 .col_varchar_ 10_utf8_ key ; 10_latin1_ key = t1 .col_varchar_ 10_utf8_ key ;
EXPLAIN SELECT t2 .col_int_key FROM t2 JOIN t1 ON t2 .col_varchar_
SET SESSION join_cache_level = 4;
SET SESSION join_buffer_size = 164;
SELECT t2 .col_int_key FROM t2 JOIN t1 ON t2 .col_varchar_ 10_latin1_ key = t1 .col_varchar_ 10_utf8_ key ; 10_latin1_ key = t1 .col_varchar_ 10_utf8_ key ;
EXPLAIN SELECT t2 .col_int_key FROM t2 JOIN t1 ON t2 .col_varchar_