Re: Valgrind warnings around create_sort_index() in maria-5.3-mwl128
Test case. Unfortunately automatic simplification is not currently available for Valgrind errors.

CREATE TABLE `CC` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`col_int_nokey` int(11) NOT NULL,
`col_int_key` int(11) NOT NULL,
`col_date_key` date NOT NULL,
`col_time_key` time NOT NULL,
`col_datetime_key` datetime NOT NULL,
`col_varchar_key` varchar(1) NOT NULL,
`col_varchar_nokey` varchar(1) NOT NULL,
PRIMARY KEY (`pk`),
KEY `col_int_key` (`col_int_key`),
KEY `col_date_key` (`col_date_key`),
KEY `col_time_key` (`col_time_key`),
KEY `col_datetime_key` (`col_datetime_key`),
KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=MEMORY AUTO_INCREMENT=30 DEFAULT CHARSET=latin1;
INSERT INTO `CC` VALUES (10,3,8,'2008-12-04','00:00:00','2001-02-09 08:48:05','v','v');
INSERT INTO `CC` VALUES (11,3,8,'2009-03-28','00:00:00','1900-01-01 00:00:00','f','f');
INSERT INTO `CC` VALUES (12,3,5,'1900-01-01','00:55:47','2006-07-23 00:00:00','v','v');
INSERT INTO `CC` VALUES (13,2,8,'2009-10-02','00:00:00','1900-01-01 00:00:00','s','s');
INSERT INTO `CC` VALUES (14,1,8,'1900-01-01','20:51:59','1900-01-01 00:00:00','a','a');
INSERT INTO `CC` VALUES (15,0,6,'2008-06-04','09:47:27','2002-08-15 00:00:00','p','p');
INSERT INTO `CC` VALUES (16,8,7,'2009-01-13','21:58:29','2004-01-12 16:12:14','z','z');
INSERT INTO `CC` VALUES (17,5,2,'1900-01-01','22:45:53','2005-12-05 16:09:29','a','a');
INSERT INTO `CC` VALUES (18,9,5,'2008-01-28','14:06:48','2009-12-10 00:00:00','h','h');
INSERT INTO `CC` VALUES (19,5,7,'2004-09-18','22:17:16','2009-07-18 00:00:00','h','h');
INSERT INTO `CC` VALUES (20,4,2,'2006-10-14','14:59:37','2007-06-27 11:01:12','v','v');
INSERT INTO `CC` VALUES (21,2,9,'1900-01-01','23:37:40','2007-02-18 01:53:15','v','v');
INSERT INTO `CC` VALUES (22,33,142,'2000-11-28','14:14:01','2001-08-17 18:39:57','b','b');
INSERT INTO `CC` VALUES (23,5,3,'2008-04-04','02:54:19','2007-03-18 08:25:05','y','y');
INSERT INTO `CC` VALUES (24,1,0,'2002-07-13','06:34:26','2006-02-15 00:00:00','v','v');
INSERT INTO `CC` VALUES (25,9,3,'2003-01-03','18:07:38','2004-07-07 20:33:16','m','m');
INSERT INTO `CC` VALUES (26,1,5,'2006-04-02','13:55:23','2007-12-15 00:00:00','z','z');
INSERT INTO `CC` VALUES (27,3,9,'2006-10-19','20:32:28','2005-07-21 13:22:19','n','n');
INSERT INTO `CC` VALUES (28,8,1,'2005-06-08','11:57:44','2007-08-27 08:08:38','d','d');
INSERT INTO `CC` VALUES (29,231,107,'2006-12-26','03:10:35','2003-01-21 20:49:51','a','a');
CREATE TABLE `D` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`col_int_nokey` int(11) NOT NULL,
`col_int_key` int(11) NOT NULL,
`col_date_key` date NOT NULL,
`col_time_key` time NOT NULL,
`col_datetime_key` datetime NOT NULL,
`col_varchar_key` varchar(1) NOT NULL,
`col_varchar_nokey` varchar(1) NOT NULL,
PRIMARY KEY (`pk`),
KEY `col_int_key` (`col_int_key`),
KEY `col_date_key` (`col_date_key`),
KEY `col_time_key` (`col_time_key`),
KEY `col_datetime_key` (`col_datetime_key`),
KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=MEMORY AUTO_INCREMENT=101 DEFAULT CHARSET=latin1;
INSERT INTO `D` VALUES (1,249,217,'2005-01-18','00:31:39','2009-02-20 15:11:46','v','v');
INSERT INTO `D` VALUES (2,1,8,'2008-08-08','19:20:08','2006-03-06 09:11:20','k','k');
INSERT INTO `D` VALUES (3,2,5,'2004-08-10','00:00:00','2005-02-13 08:32:24','s','s');
INSERT INTO `D` VALUES (4,172,232,'2001-12-19','04:31:40','2008-08-15 12:34:55','y','y');
INSERT INTO `D` VALUES (5,2,0,'2005-04-19','04:59:50','2006-08-28 09:48:16','g','g');
INSERT INTO `D` VALUES (6,0,2,'2000-06-09','15:44:21','2004-02-08 01:31:00','j','j');
INSERT INTO `D` VALUES (7,9,8,'2006-08-15','07:02:47','2002-11-20 05:29:23','g','g');
INSERT INTO `D` VALUES (8,7,8,'2002-06-05','17:51:04','2008-02-05 23:27:15','e','e');
INSERT INTO `D` VALUES (9,7,6,'1900-01-01','07:12:37','2005-11-20 15:04:39','v','v');
INSERT INTO `D` VALUES (10,0,4,'0000-00-00','00:00:00','2006-11-05 00:00:00','p','p');
INSERT INTO `D` VALUES (11,6,2,'1900-01-01','14:07:06','2006-08-06 00:00:00','e','e');
INSERT INTO `D` VALUES (12,7,9,'0000-00-00','00:00:00','2000-07-15 16:43:08','z','z');
INSERT INTO `D` VALUES (13,8,8,'2008-06-24','09:58:06','2004-03-23 00:00:00','f','f');
INSERT INTO `D` VALUES (14,8,9,'2001-04-21','00:00:00','2009-04-15 00:08:29','y','y');
INSERT INTO `D` VALUES (15,1,8,'1900-01-01','00:42:24','2002-10-09 21:02:09','k','k');
INSERT INTO `D` VALUES (16,3,3,'2001-06-15','00:00:00','2007-07-13 01:32:19','h','h');
INSERT INTO `D` VALUES (17,5,1,'2003-08-10','05:47:42','2003-10-05 09:56:59','u','u');
INSERT INTO `D` VALUES (18,5,7,'2002-05-19','00:00:00','2009-02-05 00:20:34','o','o');
INSERT INTO `D` VALUES (19,3,1,'2008-01-12','00:00:00','2007-10-11 10:41:25','z','z');
INSERT INTO `D` VALUES (20,1,8,'2002-01-23','16:16:04','1900-01-01 00:00:00','d','d');
INSERT INTO `D` VALUES (21,4,2,'2009-06-10','18:44:41','2007-05-26 00:00:00','c','c');
INSERT INTO `D` VALUES (22,5,5,'2009-01-03','00:00:00','2004-07-07 00:00:00','f','f');
INSERT INTO `D` VALUES (23,2,7,'2007-09-22','06:07:08','2001-01-23 12:29:20','i','i');
INSERT INTO `D` VALUES (24,1,6,'2006-02-12','11:02:01','2003-06-17 00:00:00','r','r');
INSERT INTO `D` VALUES (25,7,5,'2008-03-03','21:32:00','2000-12-25 08:41:43','m','m');
INSERT INTO `D` VALUES (26,5,1,'2000-08-16','17:15:12','2000-05-28 17:37:37','h','h');
INSERT INTO `D` VALUES (27,1,3,'2002-02-07','15:44:16','2002-07-12 19:18:28','w','w');
INSERT INTO `D` VALUES (28,5,3,'2001-06-04','06:21:34','2002-11-22 21:21:45','k','k');
INSERT INTO `D` VALUES (29,7,7,'2005-06-05','18:24:44','2000-02-25 03:07:15','g','g');
INSERT INTO `D` VALUES (30,0,9,'2008-04-12','03:52:43','2001-02-20 07:06:40','f','f');
INSERT INTO `D` VALUES (31,66,49,'0000-00-00','17:37:09','2009-12-23 19:40:09','d','d');
INSERT INTO `D` VALUES (32,7,2,'2006-12-21','16:35:00','2004-06-05 20:14:19','i','i');
INSERT INTO `D` VALUES (33,5,4,'2001-10-10','00:00:00','2007-10-26 00:00:00','k','k');
INSERT INTO `D` VALUES (34,6,2,'2000-05-23','13:33:01','2002-04-19 18:28:34','m','m');
INSERT INTO `D` VALUES (35,2,1,'2002-08-10','07:29:31','2002-06-25 08:07:07','g','g');
INSERT INTO `D` VALUES (36,3,8,'2004-12-01','06:22:30','2008-08-27 23:13:53','p','p');
INSERT INTO `D` VALUES (37,6,64,'2004-06-08','07:14:10','2004-11-23 11:12:03','q','q');
INSERT INTO `D` VALUES (38,5,1,'2003-03-18','03:38:41','2002-02-01 20:26:26','m','m');
INSERT INTO `D` VALUES (39,6,5,'2000-05-06','00:00:00','2003-10-02 08:21:57','x','x');
INSERT INTO `D` VALUES (40,3,7,'2000-03-24','09:07:20','2002-10-04 23:30:25','c','c');
INSERT INTO `D` VALUES (41,6,3,'2000-05-25','06:06:41','2006-04-01 20:07:58','b','b');
INSERT INTO `D` VALUES (42,2,9,'2001-08-05','03:50:47','2001-10-18 14:26:08','t','t');
INSERT INTO `D` VALUES (43,4,6,'2009-01-04','10:17:40','2004-04-19 04:18:47','p','p');
INSERT INTO `D` VALUES (44,0,4,'2009-02-14','03:37:09','2000-01-06 20:32:48','r','r');
INSERT INTO `D` VALUES (45,7,3,'2006-04-17','18:58:26','2000-01-18 18:01:27','x','x');
INSERT INTO `D` VALUES (46,125,119,'2007-09-18','04:39:05','2007-04-16 00:00:00','u','u');
INSERT INTO `D` VALUES (47,0,1,'2000-08-10','05:09:46','2006-07-08 00:00:00','u','u');
INSERT INTO `D` VALUES (48,3,8,'0000-00-00','06:14:06','2007-09-02 05:48:23','c','c');
INSERT INTO `D` VALUES (49,2,0,'2005-08-06','00:00:00','2003-12-15 00:00:00','q','q');
INSERT INTO `D` VALUES (50,232,84,'0000-00-00','00:00:00','2001-09-18 16:11:13','r','r');
INSERT INTO `D` VALUES (51,108,42,'1900-01-01','00:00:00','2002-08-03 00:00:00','n','n');
INSERT INTO `D` VALUES (52,8,9,'2008-04-14','08:36:34','2000-02-13 19:15:38','l','l');
INSERT INTO `D` VALUES (53,0,9,'2005-04-19','04:38:05','2002-09-09 10:58:07','c','c');
INSERT INTO `D` VALUES (54,7,6,'0000-00-00','00:36:57','2005-10-03 01:18:06','t','t');
INSERT INTO `D` VALUES (55,161,189,'2004-03-12','03:22:32','2003-04-19 00:00:00','o','o');
INSERT INTO `D` VALUES (56,1,7,'2008-02-10','14:59:11','2006-08-28 04:56:58','i','i');
INSERT INTO `D` VALUES (57,5,6,'2002-06-24','00:00:00','2009-12-05 15:13:00','v','v');
INSERT INTO `D` VALUES (58,6,0,'0000-00-00','17:16:18','2002-12-19 00:00:00','r','r');
INSERT INTO `D` VALUES (59,1,8,'2000-03-28','16:15:49','1900-01-01 00:00:00','z','z');
INSERT INTO `D` VALUES (60,8,5,'2004-02-26','00:00:00','2006-03-23 13:32:58','d','d');
INSERT INTO `D` VALUES (61,9,2,'2004-06-04','00:00:00','2009-08-03 13:39:36','y','y');
INSERT INTO `D` VALUES (62,7,7,'2007-07-14','16:36:53','2009-06-15 21:53:52','s','s');
INSERT INTO `D` VALUES (63,8,5,'0000-00-00','22:01:39','2005-03-28 11:25:18','u','u');
INSERT INTO `D` VALUES (64,9,4,'2003-06-20','15:46:33','2005-12-17 06:44:58','a','a');
INSERT INTO `D` VALUES (65,9,4,'2007-05-01','22:06:41','2006-02-21 02:26:41','p','p');
INSERT INTO `D` VALUES (66,3,5,'0000-00-00','20:07:22','2002-09-21 00:00:00','u','u');
INSERT INTO `D` VALUES (67,9,6,'2008-04-23','01:39:11','2003-09-25 06:41:08','a','a');
INSERT INTO `D` VALUES (68,2,3,'2005-06-18','15:00:02','2006-01-19 00:00:00','q','q');
INSERT INTO `D` VALUES (69,7,8,'2001-08-01','02:52:26','2006-04-27 00:00:00','y','y');
INSERT INTO `D` VALUES (70,182,11,'0000-00-00','13:12:53','2009-11-21 00:00:00','x','x');
INSERT INTO `D` VALUES (71,0,1,'2002-04-26','12:16:07','2008-05-21 04:30:11','z','z');
INSERT INTO `D` VALUES (72,0,5,'2002-11-09','00:00:00','2006-08-16 17:17:32','j','j');
INSERT INTO `D` VALUES (73,243,142,'2004-06-23','22:28:21','2004-07-13 18:36:14','i','i');
INSERT INTO `D` VALUES (74,9,5,'2001-12-17','06:44:08','2009-06-11 05:30:37','j','j');
INSERT INTO `D` VALUES (75,4,2,'2007-04-05','13:21:28','2001-02-19 00:04:56','h','h');
INSERT INTO `D` VALUES (76,7,9,'2001-02-28','07:53:22','2007-08-17 00:00:00','w','w');
INSERT INTO `D` VALUES (77,5,4,'1900-01-01','00:00:00','2005-12-14 00:00:00','g','g');
INSERT INTO `D` VALUES (78,9,4,'2004-11-20','17:25:18','2004-12-08 06:25:10','b','b');
INSERT INTO `D` VALUES (79,6,2,'2008-12-12','12:31:05','2001-09-02 16:17:35','q','q');
INSERT INTO `D` VALUES (80,213,120,'2002-01-12','13:45:43','2009-09-20 08:05:43','i','i');
INSERT INTO `D` VALUES (81,2,8,'2007-03-21','14:18:01','2005-08-20 21:52:10','j','j');
INSERT INTO `D` VALUES (82,9,9,'2001-10-18','20:38:54','2004-08-06 00:00:00','c','c');
INSERT INTO `D` VALUES (83,0,7,'2009-05-21','23:36:30','2003-04-23 18:29:07','l','l');
INSERT INTO `D` VALUES (84,6,8,'1900-01-01','21:40:11','2006-07-08 17:59:51','b','b');
INSERT INTO `D` VALUES (85,7,0,'2003-07-21','13:46:00','2003-02-28 16:54:49','e','e');
INSERT INTO `D` VALUES (86,2,0,'1900-01-01','12:14:50','2004-11-17 00:00:00','u','u');
INSERT INTO `D` VALUES (87,1,8,'2000-03-23','20:56:56','2008-01-22 23:55:20','s','s');
INSERT INTO `D` VALUES (88,6,3,'0000-00-00','09:50:33','2009-12-23 00:00:00','s','s');
INSERT INTO `D` VALUES (89,6,4,'2007-08-20','18:21:00','2003-11-09 08:15:38','z','z');
INSERT INTO `D` VALUES (90,0,2,'0000-00-00','11:06:01','2001-08-07 02:31:56','k','k');
INSERT INTO `D` VALUES (91,5,3,'2009-12-05','01:08:32','2009-06-22 07:26:26','n','n');
INSERT INTO `D` VALUES (92,8,8,'0000-00-00','19:26:57','2003-08-03 17:57:07','w','w');
INSERT INTO `D` VALUES (93,6,0,'2005-02-27','09:01:58','1900-01-01 00:00:00','v','v');
INSERT INTO `D` VALUES (94,9,5,'2004-01-27','00:55:46','2007-12-27 00:00:00','d','d');
INSERT INTO `D` VALUES (95,4,8,'2005-05-17','11:53:29','2005-01-03 00:00:00','n','n');
INSERT INTO `D` VALUES (96,3,9,'2009-02-28','00:00:00','2007-12-06 06:15:45','t','t');
INSERT INTO `D` VALUES (97,9,6,'0000-00-00','15:50:03','2007-02-24 00:00:00','r','r');
INSERT INTO `D` VALUES (98,8,2,'2003-10-24','09:20:20','2006-09-26 20:58:34','k','k');
INSERT INTO `D` VALUES (99,2,3,'0000-00-00','21:24:33','2004-04-20 18:58:38','k','k');
INSERT INTO `D` VALUES (100,6,4,'2008-01-06','13:49:03','2007-07-05 06:05:56','q','q');

SELECT MAX( DISTINCT table2 . `col_varchar_key` ) AS field1 , table2 . `col_datetime_key` AS field2 , table2 . `col_datetime_key` AS field3 , table2 . `col_int_key` AS field4 , table2 . `col_date_key` AS field5 , table1 . `col_varchar_key` AS field6 , table1 . `col_time_key` AS field7 , table1 . `col_date_key` AS field8 , SUM( DISTINCT table2 . `col_varchar_key` ) AS field9 , table2 . `col_datetime_key` AS field10 , table1 . `col_datetime_key` AS field11 , MIN( DISTINCT table1 . `col_int_key` ) AS field12 , table1 . `col_date_key` AS field13 , ( ( table1 . `col_int_key` ) * ( table1 . `pk` ) ) AS field14 , table1 . `col_datetime_key` AS field15 , table2 . `col_varchar_key` AS field16 , table1 . `col_int_nokey` AS field17 , table2 . `col_date_key` AS field18 , table2 . `col_int_key` AS field19 , table2 . `col_date_key` AS field20 , table1 . `col_varchar_nokey` AS field21
FROM ( D AS table1 INNER JOIN ( ( CC AS table2 INNER JOIN CC AS table3 ON (( table3 . `col_varchar_key` <> table2 . `col_varchar_key` ) AND ( table3 . `col_varchar_key` > table2 . `col_varchar_nokey` ) ) ) ) ON (( table3 . `col_varchar_key` <> table2 . `col_varchar_key` ) OR ( table3 . `col_int_key` > table2 . `pk` ) ) )
WHERE table1 . `col_int_key` = 198 OR table2 . `col_varchar_nokey` < 'u'
GROUP BY field2, field3, field4, field5, field6, field7, field8, field10, field11, field13, field14, field15, field16, field17, field18, field19, field20, field21
HAVING field1 >= 'ou'
ORDER BY field1, field2, field3, field4, field5, field6, field7, field8, field9, field10, field11, field12, field13, field14, field15, field16, field17, field18, field19, field20, field21 , table1 . `col_datetime_key` DESC
;

Re: Valgrind warnings around create_sort_index() in maria-5.3-mwl128
I could not reproduce any valgrind failure with the reported test case neither on 32-bit (Ubuntu 9.0.4) nor on 64-bit (SuSE 11.3) platforms. I tried the latest maria-5.3-mwl128 tree.

Re: Valgrind warnings around create_sort_index() in maria-5.3-mwl128
New test case. Repeatable on maria-5.2, maria-5.3, mysql-5.5

CREATE TABLE t1 (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`col_int_nokey` int(11) DEFAULT NULL,
`col_int_key` int(11) DEFAULT NULL,
`col_varchar_key` varchar(3) DEFAULT NULL,
`col_varchar_nokey` varchar(35) DEFAULT NULL,
PRIMARY KEY (`pk`),
KEY `col_int_key` (`col_int_key`),
KEY `col_varchar_key` (`col_varchar_key`,`col_varchar_nokey`)
) ENGINE=MyISAM AUTO_INCREMENT=4067 DEFAULT CHARSET=latin1;

INSERT INTO t1 VALUES (3989,12,12,'USA','Abilene'),(3873,22,22,'USA','Akron'),(4048,9,9,'USA','Albany'),(3827,45,45,'USA','Albuquerque'),(3960,13,13,'USA','Alexandria'),(4012,11,11,'USA','Allentown'),(3911,17,17,'USA','Amarillo'),(3847,33,33,'USA','Anaheim'),(3857,26,26,'USA','Anchorage'),(3991,11,11,'USA','Ann Arbor'),(4055,9,9,'USA','Arden-Arcade'),(3845,33,33,'USA','Arlington'),(3909,17,17,'USA','Arlington'),(4028,10,10,'USA','Arvada'),(4031,10,10,'USA','Athens-Clarke County'),(3831,42,42,'USA','Atlanta'),(3881,20,20,'USA','Augusta-Richmond County'),(3853,28,28,'USA','Aurora'),(3941,14,14,'USA','Aurora'),(3808,66,66,'USA','Austin'),(3861,25,25,'USA','Bakersfield'),(3809,65,65,'USA','Baltimore'),(3866,23,23,'USA','Baton Rouge'),(3992,11,11,'USA','Beaumont'),(4003,11,11,'USA','Bellevue'),(4024,10,10,'USA','Berkeley'),(4051,9,9,'USA','Billings'),(3863,24,24,'USA','Birmingham'),(3898,19,19,'USA','Boise City'),(3812,59,59,'USA','Boston'),(4058,9,9,'USA','Boulder'),(3946,14,14,'USA','Bridgeport'),(4049,9,9,'USA','Brockton'),(3945,14,14,'USA','Brownsville'),(3850,29,29,'USA','Buffalo'),(4037,10,10,'USA','Burbank'),(4032,10,10,'USA','Cambridge'),(4027,10,10,'USA','Cape Coral'),(4002,11,11,'USA','Carrollton'),(4065,9,9,'USA','Carson'),(4059,9,9,'USA','Cary'),(3977,12,12,'USA','Cedar Rapids'),(3906,18,18,'USA','Chandler'),(4066,9,9,'USA','Charleston'),(3818,54,54,'USA','Charlotte'),(3922,16,16,'USA','Chattanooga'),(3883,20,20,'USA','Chesapeake'),(3795,290,290,'USA','Chicago'),(3913,17,17,'USA','Chula Vista'),(3846,33,33,'USA','Cincinnati'),(4021,10,10,'USA','Citrus Heights'),(4005,11,11,'USA','Clarksville'),(4038,10,10,'USA','Clearwater'),(3825,48,48,'USA','Cleveland'),(3840,36,36,'USA','Colorado Springs'),(3987,12,12,'USA','Columbia'),(3807,71,71,'USA','Columbus'),(3896,19,19,'USA','Columbus'),(4052,9,9,'USA','Compton'),(3973,12,12,'USA','Concord'),(3982,12,12,'USA','Coral Springs'),(3965,12,12,'USA','Corona'),(3852,28,28,'USA','Corpus Christi'),(4006,11,11,'USA','Costa Mesa'),(3800,119,119,'USA','Dallas'),(4020,10,10,'USA','Daly City'),(4040,10,10,'USA','Davenport'),(3916,17,17,'USA','Dayton'),(3817,55,55,'USA','Denver'),(3885,20,20,'USA','Des Moines'),(3802,95,95,'USA','Detroit'),(4009,11,11,'USA','Downey'),(3895,19,19,'USA','Durham'),(3963,13,13,'USA','East Los Angeles'),(4045,9,9,'USA','El Cajon'),(3988,12,12,'USA','El Monte'),(3815,56,56,'USA','El Paso'),(4063,9,9,'USA','Elgin'),(3978,12,12,'USA','Elizabeth'),(4019,10,10,'USA','Erie'),(3954,13,13,'USA','Escondido'),(3950,14,14,'USA','Eugene'),(3974,12,12,'USA','Evansville'),(4054,9,9,'USA','Fairfield'),(4061,9,9,'USA','Fall River'),(3976,12,12,'USA','Fayetteville'),(3966,12,12,'USA','Flint'),(3957,13,13,'USA','Fontana'),(3981,12,12,'USA','Fort Collins'),(3923,15,15,'USA','Fort Lauderdale'),(3876,21,21,'USA','Fort Wayne'),(3819,53,53,'USA','Fort Worth'),(3877,20,20,'USA','Fremont'),(3829,43,43,'USA','Fresno'),(3964,13,13,'USA','Fullerton'),(4053,9,9,'USA','Gainesville'),(3917,17,17,'USA','Garden Grove'),(3874,22,22,'USA','Garland'),(4023,10,10,'USA','Gary'),(4001,11,11,'USA','Gilbert'),(3872,22,22,'USA','Glendale'),(3890,19,19,'USA','Glendale'),(3962,13,13,'USA','Grand Prairie'),(3886,20,20,'USA','Grand Rapids'),(4026,10,10,'USA','Green Bay'),(3869,22,22,'USA','Greensboro'),(3937,15,15,'USA','Hampton'),(3975,12,12,'USA','Hartford'),(3944,14,14,'USA','Hayward'),(3908,18,18,'USA','Henderson'),(3867,23,23,'USA','Hialeah'),(3947,14,14,'USA','Hollywood'),(3838,37,37,'USA','Honolulu'),(3796,195,195,'USA','Houston'),(3893,19,19,'USA','Huntington Beach'),(3920,16,16,'USA','Huntsville'),(3995,11,11,'USA','Independence'),(3804,79,79,'USA','Indianapolis'),(3997,11,11,'USA','Inglewood'),(3940,14,14,'USA','Irvine'),(3892,19,19,'USA','Irving'),(3901,18,18,'USA','Jackson'),(3806,74,74,'USA','Jacksonville'),(3864,24,24,'USA','Jersey City'),(4014,11,11,'USA','Joliet'),(3828,44,44,'USA','Kansas City'),(3936,15,15,'USA','Kansas City'),(4062,9,9,'USA','Kenosha'),(3910,17,17,'USA','Knoxville'),(4000,11,11,'USA','Lafayette'),(3938,14,14,'USA','Lakewood'),(3980,12,12,'USA','Lancaster'),(3979,12,12,'USA','Lansing'),(3907,18,18,'USA','Laredo'),(3824,48,48,'USA','Las Vegas'),(3856,26,26,'USA','Lexington-Fayette'),(3868,23,23,'USA','Lincoln'),(3902,18,18,'USA','Little Rock'),(4036,10,10,'USA','Livonia'),(3826,46,46,'USA','Long Beach'),(3794,369,369,'USA','Los Angeles'),(3858,26,26,'USA','Louisville'),(4015,11,11,'USA','Lowell'),(3882,20,20,'USA','Lubbock'),(3994,11,11,'USA','Macon'),(3875,21,21,'USA','Madison'),(4011,11,11,'USA','Manchester'),(4013,11,11,'USA','McAllen'),(3810,65,65,'USA','Memphis'),(3834,40,40,'USA','Mesa'),(3968,12,12,'USA','Mesquite'),(3931,15,15,'USA','Metairie'),(3839,36,36,'USA','Miami'),(4042,10,10,'USA','Miami Beach'),(4039,10,10,'USA','Midland'),(3811,60,60,'USA','Milwaukee'),(3837,38,38,'USA','Minneapolis'),(4041,10,10,'USA','Mission Viejo'),(3884,20,20,'USA','Mobile'),(3894,19,19,'USA','Modesto'),(3879,20,20,'USA','Montgomery'),(3942,14,14,'USA','Moreno Valley'),(3959,13,13,'USA','Naperville'),(3814,57,57,'USA','Nashville-Davidson'),(4044,9,9,'USA','New Bedford'),(3971,12,12,'USA','New Haven'),(3823,48,48,'USA','New Orleans'),(3793,801,801,'USA','New York'),(3855,27,27,'USA','Newark'),(3905,18,18,'USA','Newport News'),(3865,23,23,'USA','Norfolk'),(4046,9,9,'USA','Norman'),(3990,12,12,'USA','North Las Vegas'),(4022,10,10,'USA','Norwalk'),(3833,40,40,'USA','Oakland'),(3918,16,16,'USA','Oceanside'),(4064,9,9,'USA','Odessa'),(3821,51,51,'USA','Oklahoma City'),(3836,39,39,'USA','Omaha'),(3921,16,16,'USA','Ontario'),(3958,13,13,'USA','Orange'),(3897,19,19,'USA','Orlando'),(3933,15,15,'USA','Overland Park'),(3915,17,17,'USA','Oxnard'),(3986,12,12,'USA','Palmdale'),(3967,12,12,'USA','Paradise'),(3943,14,14,'USA','Pasadena'),(3953,13,13,'USA','Pasadena'),(3932,15,15,'USA','Paterson'),(3951,14,14,'USA','Pembroke Pines'),(3996,11,11,'USA','Peoria'),(4007,11,11,'USA','Peoria'),(3797,152,152,'USA','Philadelphia'),(3798,132,132,'USA','Phoenix'),(3844,33,33,'USA','Pittsburgh'),(3870,22,22,'USA','Plano'),(3930,15,15,'USA','Pomona'),(3820,53,53,'USA','Portland'),(4035,10,10,'USA','Portsmouth'),(3912,17,17,'USA','Providence'),(4016,11,11,'USA','Provo'),(4029,10,10,'USA','Pueblo'),(3854,28,28,'USA','Raleigh'),(3961,13,13,'USA','Rancho Cucamonga'),(3904,18,18,'USA','Reno'),(3887,20,20,'USA','Richmond'),(4047,9,9,'USA','Richmond'),(3859,26,26,'USA','Riverside'),(4050,9,9,'USA','Roanoke'),(3871,22,22,'USA','Rochester'),(3929,15,15,'USA','Rockford'),(3832,41,41,'USA','Sacramento'),(3841,35,35,'USA','Saint Louis'),(3851,29,29,'USA','Saint Paul'),(3860,25,25,'USA','Saint Petersburg'),(3952,14,14,'USA','Salem'),(3927,15,15,'USA','Salinas'),(3903,18,18,'USA','Salt Lake City'),(3801,114,114,'USA','San Antonio'),(3900,19,19,'USA','San Bernardino'),(4034,10,10,'USA','San Buenaventura'),(3799,122,122,'USA','San Diego'),(3805,78,78,'USA','San Francisco'),(3803,89,89,'USA','San Jose'),(4056,9,9,'USA','San Mateo'),(4030,10,10,'USA','Sandy'),(3843,34,34,'USA','Santa Ana'),(4025,10,10,'USA','Santa Clara'),(3926,15,15,'USA','Santa Clarita'),(4060,9,9,'USA','Santa Monica'),(3934,15,15,'USA','Santa Rosa'),(3956,13,13,'USA','Savannah'),(3878,20,20,'USA','Scottsdale'),(3816,56,56,'USA','Seattle'),(3880,20,20,'USA','Shreveport'),(3999,11,11,'USA','Simi Valley'),(3970,12,12,'USA','Sioux Falls'),(4008,11,11,'USA','South Bend'),(3889,20,20,'USA','Spokane'),(3924,15,15,'USA','Springfield'),(3925,15,15,'USA','Springfield'),(3998,11,11,'USA','Springfield'),(3983,12,12,'USA','Stamford'),(3969,12,12,'USA','Sterling Heights'),(3862,24,24,'USA','Stockton'),(3955,13,13,'USA','Sunnyvale'),(4043,10,10,'USA','Sunrise Manor'),(3935,15,15,'USA','Syracuse'),(3891,19,19,'USA','Tacoma'),(3928,15,15,'USA','Tallahassee'),(3849,30,30,'USA','Tampa'),(3919,16,16,'USA','Tempe'),(3984,12,12,'USA','Thousand Oaks'),(3848,31,31,'USA','Toledo'),(3972,12,12,'USA','Topeka'),(3949,14,14,'USA','Torrance'),(3822,49,49,'USA','Tucson'),(3835,39,39,'USA','Tulsa'),(3985,12,12,'USA','Vallejo'),(3939,14,14,'USA','Vancouver'),(3830,43,43,'USA','Virginia Beach'),(4057,9,9,'USA','Visalia'),(3993,11,11,'USA','Waco'),(3948,14,14,'USA','Warren'),(3813,57,57,'USA','Washington'),(4010,11,11,'USA','Waterbury'),(4017,11,11,'USA','West Covina'),(4004,11,11,'USA','West Valley City'),(4033,10,10,'USA','Westminster'),(3842,34,34,'USA','Wichita'),(4018,10,10,'USA','Wichita Falls'),(3899,19,19,'USA','Winston-Salem'),(3914,17,17,'USA','Worcester'),(3888,20,20,'USA','Yonkers');

CREATE TABLE t2 SELECT alias2 . `col_varchar_key` AS field1 , alias1 . `col_varchar_nokey` AS field2 FROM ( t1 AS alias1 INNER JOIN t1 AS alias2 ON (alias2 . `col_int_key` != alias1 . `pk` ) ) WHERE alias1 . `col_varchar_key` IN ('m' , 'USA') ORDER BY field1 , field2 LIMIT 10 ;

SELECT * FROM t2 WHERE (`field1`, `field2`) IN ( SELECT alias2 . `col_varchar_key` AS field1 , alias1 . `col_varchar_nokey` AS field2 FROM ( t1 AS alias1 INNER JOIN t1 AS alias2 ON (alias2 . `col_int_key` != alias1 . `pk` ) ) WHERE alias1 . `col_varchar_key` IN ('m' , 'USA') ORDER BY field1 , field2 );

Launchpad bug id: 666347

Reproducible in MariaDB 5.1, 5.2, 5.3.
Not reproducible in MariaDB 5.5, 10.0.

Some history:

In MySQL the bugfix went under nickname "Bug#12856915 VALGRIND FAILURE IN FILESORT/CREATE_SORT_INDEX" and took several revisions. On 5.5, it disappeared after this one:

revno: 3519

revision-id: tor.didriksen@oracle.com-20110905140433-lip77sibwvj5jglh

parent: nirbhay.choubey@oracle.com-20110902112204-he72rib9rwdwgujf

committer: Tor Didriksen <tor.didriksen@oracle.com>

branch nick: 5.5

timestamp: Mon 2011-09-05 16:04:33 +0200

message:

  Bug#12856915 VALGRIND FAILURE IN FILESORT/CREATE_SORT_INDEX

  Post-push fix:

  Replace fun:inline_mysql_file_write with '...' since it may be optimized away.

But the first appearance of Bug#12856915 fix was in the 5.1 tree, with the following revision:

revno: 3592

revision-id: tor.didriksen@oracle.com-20110825083807-x8pe3e3in38j6nb4

parent: rohit.kalhans@oracle.com-20110826095729-t37brazu6d85s527

committer: Tor Didriksen <tor.didriksen@oracle.com>

branch nick: 5.1-bug12856915-valgrind

timestamp: Thu 2011-08-25 10:38:07 +0200

message:

  Bug#12856915 VALGRIND FAILURE IN FILESORT/CREATE_SORT_INDEX

  Suppress the known warnings generated by filesort().

  The real fix belongs to worklog 1509:

  Pack values of non-sorted fields in the sort buffer

  (which is basically the same issue, but in an optimization context:

   We are writing the entire sort buffer to disk, 

   including un-used space for varchar columns.)

The mentioned worklog appears in 5.7 tree, but not before.

However, back to MariaDB 5.5, the valgrind warnings don't appear even without MTR suppressions. Since the issue has never been observed in MariaDB 5.5 and does not appear there now, I think it does not make much sense to fix it in 5.1-5.3, thus closing.

igor or serg – if you disagree, please feel free to re-open.