Re: Wrong result with join buffer (flat, BNLH join) and GROUP BY/LIMIT in maria-5.3-mwl128
Test case:
--source include/have_innodb.inc
--disable_warnings
DROP TABLE /*! IF EXISTS */ t1;
DROP TABLE /*! IF EXISTS */ t2;
--enable_warnings
CREATE TABLE t1 (
pk int(11) NOT NULL AUTO_INCREMENT,
col_int_key int(11) DEFAULT NULL,
col_varchar_key varchar(1) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
PRIMARY KEY (pk),
KEY col_int_key (col_int_key),
KEY col_varchar_key (col_varchar_key,col_int_key)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
INSERT INTO t1 VALUES (11,0,NULL),(16,1,'c'),(20,2,'d'),(13,166,'e'),(19,6,'f'),(9,8,'h'),(7,3,'j'),(12,5,'k'),(4,9,'k'),(3,3,'m'),(2,9,'m'),(17,9,'m'),(14,3,'n'),(10,53,'o'),(5,NULL,'r'),(15,0,'t'),(6,9,'t'),(8,8,'u'),(1,2,'w'),(18,5,'y');
CREATE TABLE t2 (
pk int(11) NOT NULL AUTO_INCREMENT,
col_int_key int(11) DEFAULT NULL,
col_varchar_key varchar(1) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
PRIMARY KEY (pk),
KEY col_int_key (col_int_key),
KEY col_varchar_key (col_varchar_key,col_int_key)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
INSERT INTO t2 VALUES (1,7,'f');
SET SESSION join_cache_level = 4;
SET SESSION join_buffer_size = 136;
EXPLAIN SELECT t1.col_int_key AS field1 FROM t2 JOIN t1 ON t2.pk WHERE t2.col_int_key = 143 GROUP BY field1 LIMIT 10;
SELECT t1.col_int_key AS field1 FROM t2 JOIN t1 ON t2.pk WHERE t2.col_int_key = 143 GROUP BY field1 LIMIT 10;
DROP TABLE t1;
DROP TABLE t2;
Bug
Critical
Re: Wrong result with join buffer (flat, BNLH join) and GROUP BY/LIMIT in maria-5.3-mwl128
Test case:
--source include/have_innodb.inc
--disable_warnings
DROP TABLE /*! IF EXISTS */ t1;
DROP TABLE /*! IF EXISTS */ t2;
--enable_warnings
CREATE TABLE t1 (
pk int(11) NOT NULL AUTO_INCREMENT,
col_int_key int(11) DEFAULT NULL,
col_varchar_key varchar(1) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
PRIMARY KEY (pk),
KEY col_int_key (col_int_key),
KEY col_varchar_key (col_varchar_key,col_int_key)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
INSERT INTO t1 VALUES (11,0,NULL),(16,1,'c'),(20,2,'d'),(13,166,'e'),(19,6,'f'),(9,8,'h'),(7,3,'j'),(12,5,'k'),(4,9,'k'),(3,3,'m'),(2,9,'m'),(17,9,'m'),(14,3,'n'),(10,53,'o'),(5,NULL,'r'),(15,0,'t'),(6,9,'t'),(8,8,'u'),(1,2,'w'),(18,5,'y');
CREATE TABLE t2 (
pk int(11) NOT NULL AUTO_INCREMENT,
col_int_key int(11) DEFAULT NULL,
col_varchar_key varchar(1) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
PRIMARY KEY (pk),
KEY col_int_key (col_int_key),
KEY col_varchar_key (col_varchar_key,col_int_key)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
INSERT INTO t2 VALUES (1,7,'f');
SET SESSION join_cache_level = 4;
SET SESSION join_buffer_size = 136;
EXPLAIN SELECT t1.col_int_key AS field1 FROM t2 JOIN t1 ON t2.pk WHERE t2.col_int_key = 143 GROUP BY field1 LIMIT 10;
SELECT t1.col_int_key AS field1 FROM t2 JOIN t1 ON t2.pk WHERE t2.col_int_key = 143 GROUP BY field1 LIMIT 10;
DROP TABLE t1;
DROP TABLE t2;