Re: GROUP BY not properly observed with join cache
Test case:

CREATE TABLE `CC` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`col_int_key` int(11) NOT NULL,
`col_varchar_key` varchar(1) NOT NULL,
PRIMARY KEY (`pk`),
KEY `col_int_key` (`col_int_key`),
KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=MyISAM AUTO_INCREMENT=30 DEFAULT CHARSET=latin1;
INSERT INTO `CC` VALUES (10,8,'v');
INSERT INTO `CC` VALUES (11,8,'f');
INSERT INTO `CC` VALUES (12,5,'v');
INSERT INTO `CC` VALUES (13,8,'s');
INSERT INTO `CC` VALUES (14,8,'a');
INSERT INTO `CC` VALUES (15,6,'p');
INSERT INTO `CC` VALUES (16,7,'z');
INSERT INTO `CC` VALUES (17,2,'a');
INSERT INTO `CC` VALUES (18,5,'h');
INSERT INTO `CC` VALUES (19,7,'h');
INSERT INTO `CC` VALUES (20,2,'v');
INSERT INTO `CC` VALUES (21,9,'v');
INSERT INTO `CC` VALUES (22,142,'b');
INSERT INTO `CC` VALUES (23,3,'y');
INSERT INTO `CC` VALUES (24,0,'v');
INSERT INTO `CC` VALUES (25,3,'m');
INSERT INTO `CC` VALUES (26,5,'z');
INSERT INTO `CC` VALUES (27,9,'n');
INSERT INTO `CC` VALUES (28,1,'d');
INSERT INTO `CC` VALUES (29,107,'a');
CREATE TABLE `C` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`col_int_key` int(11) NOT NULL,
`col_varchar_key` varchar(1) NOT NULL,
PRIMARY KEY (`pk`),
KEY `col_int_key` (`col_int_key`),
KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
INSERT INTO `C` VALUES (1,9,'x');
INSERT INTO `C` VALUES (2,5,'g');
INSERT INTO `C` VALUES (3,1,'o');
INSERT INTO `C` VALUES (4,0,'g');
INSERT INTO `C` VALUES (5,1,'v');
INSERT INTO `C` VALUES (6,190,'m');
INSERT INTO `C` VALUES (7,6,'x');
INSERT INTO `C` VALUES (8,3,'c');
INSERT INTO `C` VALUES (9,4,'z');
INSERT INTO `C` VALUES (10,3,'i');
INSERT INTO `C` VALUES (11,186,'x');
INSERT INTO `C` VALUES (12,1,'g');
INSERT INTO `C` VALUES (13,8,'q');
INSERT INTO `C` VALUES (14,226,'m');
INSERT INTO `C` VALUES (15,133,'p');
INSERT INTO `C` VALUES (16,6,'e');
INSERT INTO `C` VALUES (17,3,'t');
INSERT INTO `C` VALUES (18,8,'j');
INSERT INTO `C` VALUES (19,5,'h');
INSERT INTO `C` VALUES (20,7,'w');

SELECT table2 .`col_varchar_key` field1 FROM CC table1 LEFT JOIN CC table2 STRAIGHT_JOIN C table3 ON table3 .`col_varchar_key` <> table2 .`col_varchar_key` ON table3 .`pk` = table2 .`col_int_key` WHERE table1 .`col_varchar_key` = table3 .`col_varchar_key` GROUP BY field1 ORDER BY table1 .`pk` ;
EXPLAIN SELECT table2 .`col_varchar_key` field1 FROM CC table1 LEFT JOIN CC table2 STRAIGHT_JOIN C table3 ON table3 .`col_varchar_key` <> table2 .`col_varchar_key` ON table3 .`pk` = table2 .`col_int_key` WHERE table1 .`col_varchar_key` = table3 .`col_varchar_key` GROUP BY field1 ORDER BY table1 .`pk` ;

SET SESSION join_cache_level = 4;
SELECT table2 .`col_varchar_key` field1 FROM CC table1 LEFT JOIN CC table2 STRAIGHT_JOIN C table3 ON table3 .`col_varchar_key` <> table2 .`col_varchar_key` ON table3 .`pk` = table2 .`col_int_key` WHERE table1 .`col_varchar_key` = table3 .`col_varchar_key` GROUP BY field1 ORDER BY table1 .`pk` ;
EXPLAIN SELECT table2 .`col_varchar_key` field1 FROM CC table1 LEFT JOIN CC table2 STRAIGHT_JOIN C table3 ON table3 .`col_varchar_key` <> table2 .`col_varchar_key` ON table3 .`pk` = table2 .`col_int_key` WHERE table1 .`col_varchar_key` = table3 .`col_varchar_key` GROUP BY field1 ORDER BY table1 .`pk` ;

SET SESSION optimizer_switch='join_cache_hashed=off';
SELECT table2 .`col_varchar_key` field1 FROM CC table1 LEFT JOIN CC table2 STRAIGHT_JOIN C table3 ON table3 .`col_varchar_key` <> table2 .`col_varchar_key` ON table3 .`pk` = table2 .`col_int_key` WHERE table1 .`col_varchar_key` = table3 .`col_varchar_key` GROUP BY field1 ORDER BY table1 .`pk` ;
EXPLAIN SELECT table2 .`col_varchar_key` field1 FROM CC table1 LEFT JOIN CC table2 STRAIGHT_JOIN C table3 ON table3 .`col_varchar_key` <> table2 .`col_varchar_key` ON table3 .`pk` = table2 .`col_int_key` WHERE table1 .`col_varchar_key` = table3 .`col_varchar_key` GROUP BY field1 ORDER BY table1 .`pk` ;

SET SESSION optimizer_switch='join_cache_bka=off';
SELECT table2 .`col_varchar_key` field1 FROM CC table1 LEFT JOIN CC table2 STRAIGHT_JOIN C table3 ON table3 .`col_varchar_key` <> table2 .`col_varchar_key` ON table3 .`pk` = table2 .`col_int_key` WHERE table1 .`col_varchar_key` = table3 .`col_varchar_key` GROUP BY field1 ORDER BY table1 .`pk` ;
EXPLAIN SELECT table2 .`col_varchar_key` field1 FROM CC table1 LEFT JOIN CC table2 STRAIGHT_JOIN C table3 ON table3 .`col_varchar_key` <> table2 .`col_varchar_key` ON table3 .`pk` = table2 .`col_int_key` WHERE table1 .`col_varchar_key` = table3 .`col_varchar_key` GROUP BY field1 ORDER BY table1 .`pk` ;

Re: GROUP BY not properly observed with join cache
Still reproducible with

revision-id: igor@askmonty.org-20101022223047-phunrh5g3xzrx2d9
date: 2010-10-22 15:30:47 -0700
build-date: 2010-10-23 14:57:52 +0300
revno: 2832
branch-nick: maria-5.3-mwl128

revno: 2832
committer: Igor Babaev <igor@askmonty.org>
branch nick: maria-5.3-mwl128-bug663818
timestamp: Fri 2010-10-22 15:30:47 -0700
message:
Fixed LP bug #663818.
After the patch for bug 663840 had been applied the test case for
bug 663818 triggered the assert introduced by this patch.
It happened because the the patch turned out to be incomplete:
the space needed for a key entry must be taken into account
for the record written into the buffer, and, for the next record
as well, when figuring out whether the record being written is
the last for the buffer or not.

Launchpad bug id: 664508