GROUP BY not properly observed with join cache

[Philip Stoev]

The following query:

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` ;

produces a result set with duplicates, even though the GROUP BY should remove them.

In 5.3-main, the query produces the duplicates when executed with join_cache_level=8

In 5.3-mwl128 the query produces duplicates under join_cache_level = 4, both with BNLH and BKA plans , even though the number of rows returned is different for BKA and BNLH.

[Philip Stoev (pstoev-askmonty)]

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_...

[Philip Stoev (pstoev-askmonty)]

Still reproducible with

revision-id: <email address hidden>
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 <email address hidden>
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.