Re: Wrong result in maria-5.3-mwl128 with join_cache_level = 4 and small join_buffer_size
Test case:
--disable_warnings
DROP TABLE /*! IF EXISTS */ t1;
DROP TABLE /*! IF EXISTS */ t2;
--enable_warnings
CREATE TABLE t1 (
col_int_key int(11) DEFAULT NULL,
col_varchar_10_latin1_key varchar(10) DEFAULT NULL,
col_varchar_10_utf8_key varchar(10) CHARACTER SET utf8 DEFAULT NULL,
KEY col_int_key (col_int_key),
KEY col_varchar_10_latin1_key (col_varchar_10_latin1_key),
KEY col_varchar_10_utf8_key (col_varchar_10_utf8_key)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO t1 VALUES (650903552,'cmxffkpsel','z'),(535298048,'tvtjrcmxff','y'),(1626865664,'when','for'),(39649280,'rcvljitvtj','ercvljitvt'),(792068096,'ttercvljit','jttercvlji');
CREATE TABLE t2 (
col_varchar_10_utf8_key varchar(10) CHARACTER SET utf8 DEFAULT NULL,
col_int_key int(11) DEFAULT NULL,
col_varchar_10_latin1_key varchar(10) DEFAULT NULL,
KEY col_varchar_10_utf8_key (col_varchar_10_utf8_key),
KEY col_int_key (col_int_key),
KEY col_varchar_10_latin1_key (col_varchar_10_latin1_key)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO t2 VALUES ('g',7,'like'),('fujttercvl',6,'y'),('s',2,'e'),('didn\'t',0,'v'),('gvdrodpedk',8,'chogvdrodp'),('jichogvdro',7,'will');
SELECT t2 .col_int_key FROM t2 JOIN t1 ON t2 .col_varchar_10_latin1_key = t1 .col_varchar_10_utf8_key ;
EXPLAIN SELECT t2 .col_int_key FROM t2 JOIN t1 ON t2 .col_varchar_10_latin1_key = t1 .col_varchar_10_utf8_key ;
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 ;
EXPLAIN SELECT t2 .col_int_key FROM t2 JOIN t1 ON t2 .col_varchar_10_latin1_key = t1 .col_varchar_10_utf8_key ;
Bug
Major
Re: Wrong result in maria-5.3-mwl128 with join_cache_level = 4 and small join_buffer_size
Test case:
--disable_warnings
DROP TABLE /*! IF EXISTS */ t1;
DROP TABLE /*! IF EXISTS */ t2;
--enable_warnings
CREATE TABLE t1 (
col_int_key int(11) DEFAULT NULL,
col_varchar_10_latin1_key varchar(10) DEFAULT NULL,
col_varchar_10_utf8_key varchar(10) CHARACTER SET utf8 DEFAULT NULL,
KEY col_int_key (col_int_key),
KEY col_varchar_10_latin1_key (col_varchar_10_latin1_key),
KEY col_varchar_10_utf8_key (col_varchar_10_utf8_key)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO t1 VALUES (650903552,'cmxffkpsel','z'),(535298048,'tvtjrcmxff','y'),(1626865664,'when','for'),(39649280,'rcvljitvtj','ercvljitvt'),(792068096,'ttercvljit','jttercvlji');
CREATE TABLE t2 (
col_varchar_10_utf8_key varchar(10) CHARACTER SET utf8 DEFAULT NULL,
col_int_key int(11) DEFAULT NULL,
col_varchar_10_latin1_key varchar(10) DEFAULT NULL,
KEY col_varchar_10_utf8_key (col_varchar_10_utf8_key),
KEY col_int_key (col_int_key),
KEY col_varchar_10_latin1_key (col_varchar_10_latin1_key)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO t2 VALUES ('g',7,'like'),('fujttercvl',6,'y'),('s',2,'e'),('didn\'t',0,'v'),('gvdrodpedk',8,'chogvdrodp'),('jichogvdro',7,'will');
SELECT t2 .col_int_key FROM t2 JOIN t1 ON t2 .col_varchar_10_latin1_key = t1 .col_varchar_10_utf8_key ;
EXPLAIN SELECT t2 .col_int_key FROM t2 JOIN t1 ON t2 .col_varchar_10_latin1_key = t1 .col_varchar_10_utf8_key ;
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 ;
EXPLAIN SELECT t2 .col_int_key FROM t2 JOIN t1 ON t2 .col_varchar_10_latin1_key = t1 .col_varchar_10_utf8_key ;