Bug #59110 Memory leak of QUICK_SELECT_I allocated memory
Submitted: 22 Dec 2010 9:49 Modified: 2 Mar 2011 3:21
Reporter: Ole John Aske Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:5.6.99, 5.5, 5.1 -> OS:Any
Assigned to: Ole John Aske CPU Architecture:Any
Tags: Leak, memory leak, memory leak

[22 Dec 2010 9:49] Ole John Aske
Description:
Valgrind detects these memory leaks after execution of the SELECT query in the testcase below

==15943== 584 (304 direct, 280 indirect) bytes in 1 blocks are definitely lost in loss record 14 of 18
==15943==    at 0x4C2166E: operator new(unsigned long) (vg_replace_malloc.c:220)
==15943==    by 0x7ED807: TRP_GROUP_MIN_MAX::make_quick(PARAM*, bool, st_mem_root*) (opt_range.cc:10205)
==15943==    by 0x7F8189: SQL_SELECT::test_quick_select(THD*, Bitmap<64u>, unsigned long long, unsigned long long, bool) (opt_range.cc:2443)
==15943==    by 0x7379C2: test_if_skip_sort_order(st_join_table*, st_order*, unsigned long long, bool, Bitmap<64u>*) (sql _select.cc:13941)
==15943==    by 0x756B95: JOIN::optimize() (sql_select.cc:1477)
==15943==    by 0x759186: mysql_select(THD*, Item***, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_ord er*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*) (sql_select.cc :2604)
==15943==    by 0x75EDD6: handle_select(THD*, st_lex*, select_result*, unsigned long) (sql_select.cc:271)
==15943==    by 0x6C2684: execute_sqlcom_select(THD*, TABLE_LIST*) (sql_parse.cc:5133)
==15943==    by 0x6C32D8: mysql_execute_command(THD*) (sql_parse.cc:2295)
==15943==    by 0x6CBF60: mysql_parse(THD*, char*, unsigned int, char const**) (sql_parse.cc:6057)
==15943==    by 0x6CD911: dispatch_command(enum_server_command, THD*, char*, unsigned int) (sql_parse.cc:1263)
==15943==    by 0x6CEE04: do_command(THD*) (sql_parse.cc:891)
==15943==
==15943== 584 (304 direct, 280 indirect) bytes in 1 blocks are definitely lost in loss record 15 of 18
==15943==    at 0x4C2166E: operator new(unsigned long) (vg_replace_malloc.c:220)
==15943==    by 0x7ED807: TRP_GROUP_MIN_MAX::make_quick(PARAM*, bool, st_mem_root*) (opt_range.cc:10205)
==15943==    by 0x7F8189: SQL_SELECT::test_quick_select(THD*, Bitmap<64u>, unsigned long long, unsigned long long, bool) (opt_range.cc:2443)
==15943==    by 0x7379C2: test_if_skip_sort_order(st_join_table*, st_order*, unsigned long long, bool, Bitmap<64u>*) (sql _select.cc:13941)
==15943==    by 0x744B70: create_sort_index(THD*, JOIN*, st_order*, unsigned long long, unsigned long long, bool) (sql_se lect.cc:14125)
==15943==    by 0x75E72A: JOIN::exec() (sql_select.cc:2358)
==15943==    by 0x75922B: mysql_select(THD*, Item***, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_ord er*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*) (sql_select.cc :2618)
==15943==    by 0x75EDD6: handle_select(THD*, st_lex*, select_result*, unsigned long) (sql_select.cc:271)
==15943==    by 0x6C2684: execute_sqlcom_select(THD*, TABLE_LIST*) (sql_parse.cc:5133)
==15943==    by 0x6C32D8: mysql_execute_command(THD*) (sql_parse.cc:2295)
==15943==    by 0x6CBF60: mysql_parse(THD*, char*, unsigned int, char const**) (sql_parse.cc:6057)
==15943==    by 0x6CD911: dispatch_command(enum_server_command, THD*, char*, unsigned int) (sql_parse.cc:1263)
==15943==

How to repeat:
Start mysqld in Valgrind, then:

CREATE TABLE `Z` (
  `col_datetime_unique` datetime DEFAULT NULL,
  `col_char_16_unique` char(16) COLLATE latin1_bin DEFAULT NULL,
  `col_varchar_10` varchar(10) COLLATE latin1_bin DEFAULT NULL,
  `col_varchar_256_key` varchar(256) COLLATE latin1_bin DEFAULT NULL,
  `col_char_16` char(16) COLLATE latin1_bin DEFAULT NULL,
  `col_int_unique` int(11) DEFAULT NULL,
  `col_datetime_key` datetime DEFAULT NULL,
  `col_varchar_256` varchar(256) COLLATE latin1_bin DEFAULT NULL,
  `col_int` int(11) DEFAULT NULL,
  `col_varchar_256_unique` varchar(256) COLLATE latin1_bin DEFAULT NULL,
  `col_int_key` int(11) DEFAULT NULL,
  `col_char_16_key` char(16) COLLATE latin1_bin DEFAULT NULL,
  `col_datetime` datetime DEFAULT NULL,
  `col_varchar_10_unique` varchar(10) COLLATE latin1_bin DEFAULT NULL,
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `col_varchar_10_key` varchar(10) COLLATE latin1_bin DEFAULT NULL,
  PRIMARY KEY (`pk`),
  UNIQUE KEY `col_datetime_unique` (`col_datetime_unique`),
  UNIQUE KEY `col_char_16_unique` (`col_char_16_unique`),
  UNIQUE KEY `col_varchar_256_unique` (`col_varchar_256_unique`),
  UNIQUE KEY `ix1` (`col_char_16`,`col_char_16_unique`) USING HASH,
  UNIQUE KEY `ix2` (`col_varchar_256`,`col_varchar_10_unique`) USING HASH,
  KEY `col_varchar_256_key` (`col_varchar_256_key`),
  KEY `col_datetime_key` (`col_datetime_key`),
  KEY `col_int_key` (`col_int_key`),
  KEY `col_char_16_key` (`col_char_16_key`),
  KEY `col_varchar_10_key` (`col_varchar_10_key`)
) ENGINE=myisam AUTO_INCREMENT=39 DEFAULT CHARSET=latin1 COLLATE=latin1_bin;

INSERT INTO `Z` VALUES (NULL,'yvudterpnrypwluf','look','nyvudterpnrypwlu','tnyvudterpnryp',33,NULL,'back',8,'g',48,'qg',NULL,'iqgtnyvudt',35,'piqgtnyvud'),(NULL,'kjhnmbilbqswv','get','gkjhnmbilb','go',23,'2008-02-12 08:30:32','I',29,'wgkjhnmbilbqswvxcrkhekpz',41,'wwgkjhnmbilbqswv',NULL,'swwgkjhnmb',4,'yswwgkjhnm'),(NULL,'okay','he','her','iyswwgkjhnmbilbq',20,'2004-06-21 08:05:48','of',29,'ziyswwg',17,'he','2003-09-01 05:31:33','all',5,'bziyswwg'),('2005-12-10 17:32:17','xfzpsfjnmbuxvgcx','cxfzpsfjnm','gcxfz','mgcxfzpsfjnmbuxv',32,'2005-06-09 23:34:31','some',14,'smgcxfzpsfjnmbuxvg',24,'osmgcxfzpsfjnmbu','2002-10-14 07:17:40','yosmgcxfzp',27,'no'),(NULL,'fnxdhvlyhwlbikom','rfnxdhvlyh','yes','there',31,NULL,'were',6,'in',20,'irfnxdhvlyhwlbik','2001-05-24 04:48:26','nirfnxdhvl',8,'hnirfnxdhv'),(NULL,'out','what','wrhwd','nwrhwdpojkrpktlh',34,NULL,'time',35,'inwrhwdpojk',15,'uinwrhwdpojkrpkt',NULL,'well',13,'wuinwrhwdp'),('2009-04-14 15:55:58','swvxcrkhekp','qswvxcrkhe','bqswvxcrkhekpzowzfontwjyfzdrw','lbqswvxcrkhekpzo',8,'2006-11-25 17:44:57','ilbqswvxcr',3,'bilbqswvxcrkhekpzowzfon',14,'right','2006-11-01 22:57:04','mbilbqswvx',2,'this'),(NULL,'nbxvck','gnbxvckyic','fgnbxv','ufgnbxvckyicivkv',15,NULL,'so',16,'mufgnbxvckyicivkvrsgatnvh',10,'well','2007-06-04 15:51:25','jmufgnbxvc',38,'did'),(NULL,'mxnvcshpkxmsktas','it','hm','x',13,NULL,'can\'t',40,'there',42,'qxhmxnvcshpkxmsk',NULL,'really',30,'be'),('2008-04-03 10:06:40','been','going','will','be',46,NULL,'kegiui',17,'tell',41,'fkegiuijvhyckkv','2009-03-08 21:20:43','some',33,'ufkegiu'),('2007-11-20 13:30:33','was','get','viohnirfnxdhvlyh','qviohnirfnxdh',6,'2004-03-24 05:28:37','bqviohnirfn',4,'zbqviohnirfnxdhvlyhwl',22,'ozbqviohnirfnxdh','2008-08-11 10:55:12','fozbqviohn',9,'hfozbqvioh'),(NULL,'I','be','I\'ll','don\'t',9,NULL,'lhfozbqviohnirfnxdhvlyhwlbiko',12,'tlhfoz',3,'all',NULL,'ktlh',10,'pktlhfozbq'),(NULL,'uc','but','have','ouczgydlsz',25,NULL,'mouczgydlsz',19,'lmouczgydlszcbfynymiufe',17,'ylmouczgydlszcbf',NULL,'vylmouczgy',18,'qvylmouczg'),(NULL,'yd','gydlszcbfy','got','do',2,'2009-11-15 11:55:53','zgydlszcbfynymiufezzfemdakkzxrw',NULL,'they',20,'czgydlszcbfynymi','2007-02-02 08:58:05','on',17,'didn\'t'),('2001-07-01 05:50:54','jibziyswwgkjhnmb','up','mjibziyswwg','o',10,'2003-04-19 05:27:04','that\'s',18,'komjibziyswwgkjhnmbilbqswvxcrkhe',28,'ikomjibziyswwgkj','2005-06-14 08:32:49','bikomjibzi',7,'mean'),(NULL,'msktaszyosm','how','xmsktas','your',36,NULL,'kxmsktaszyosmgcxfzpsfjnmb',40,'for',11,'know','2002-10-22 16:30:18','pkxmsktasz',28,'why'),(NULL,'qlzlqvylmouczg','got','I\'m','xqlzlqvylmouczgy',39,'2009-04-16 14:11:43','out',32,'yeah',27,'you','2009-04-25 12:37:37','exqlzlqvyl',20,'wexqlz'),('2003-10-16 03:11:46','on','nmbilbqswv','up','go',30,'2005-02-02 00:05:31','have',39,'hnmbilbqswvxcrkhek',9,'here',NULL,'who',3,'jhnmbilbqs'),('2008-12-18 00:53:06','no','want','up','I\'ll',18,NULL,'ibziyswwgkjhnmbilbqswvx',6,'yes',14,'why','2000-04-22 23:21:00','look',6,'we'),('2004-08-17 10:38:33','or','o','tell','ok',45,NULL,'sod',10,'ksodjtwupnurcfcqvg',2,'now',NULL,'is',23,'he'),(NULL,'uxvgcxtgontrvw','buxvgcxtgo','mbuxvgcxt','nmbuxvgcxtgontrv',48,'2000-08-27 03:12:35','yeah',3,'if',8,'jnmbuxvgc','2001-11-17 06:48:35','fjnmbuxvgc',26,'come'),(NULL,'hpkxmsktas','s','it','oh',22,'2002-03-27 22:41:36','cshpkxmsktaszyosmgcxfzpsfjn',46,'vcshpkxmsktaszyosmgcxfzpsfj',12,'nvcshpkxmsk',NULL,'xnvcshpkxm',29,'to'),('2008-03-27 04:32:03','iufezzfemdakkzxr','miufe','not','ymiufezzfe',16,NULL,'nymiufezzfemdakkzxrwbixqay',2,'yn',2,'don\'t',NULL,'did',16,'fynymiufez'),('2002-01-19 15:50:18','flymskiwuinwrhwd','aflymskiwu','I','yaflymskiwuinwrh',7,'2008-12-04 17:53:42','is',47,'ayaflyms',8,'will','2006-01-10 03:45:55','qayaflymsk',15,'xqayaflyms'),('2004-07-13 11:26:18','him','with','kv','didn\'t',41,'2006-07-08 18:03:06','no',18,'kkvdxaxskqbudpmogzylcsdqxhm',50,'ckkv','2006-08-09 14:20:56','be',31,'yckkvdx'),(NULL,'nrypwlufkegiuijv','been','in','you',1,NULL,'pnrypwlufkegiuijvhyc',1,'rpnrypw',13,'up','2009-08-23 01:22:31','you',34,'got'),(NULL,'wczpiqgtnyvu','owczpi','qowczpiqgtnyvudterpnrypw','ok',47,NULL,'had',8,'aqowczp',0,'faqowczpiqgtn','2007-11-21 07:01:10','don\'t',36,'xfaqowczpi'),('2001-01-11 12:23:15','eryxfaqowc','me','now','teryxfaqowczpiqg',12,'2002-06-24 22:53:17','of',20,'pter',19,'can',NULL,'would',37,'lpteryxfaq'),('2008-06-18 15:02:52','up','cksodjtwup','my','acksodjtwupnurcf',19,NULL,'back',48,'like',50,'gacksodjtwupnurc','2001-01-12 23:17:29','egacksodjt',24,'if'),('2005-10-16 07:51:29','I\'m','see','and','or',0,NULL,'lqvylmouczgydlszcbf',8,'zlqvylmou',47,'so','2008-11-27 22:39:44','get',19,'lzlqvyl'),('2009-03-28 01:43:23','is','I\'m','didn\'t','wdpojkrpktlhfozb',3,NULL,'didn\'t',49,'to',39,'hwdpojkr',NULL,'rhwdpojkrp',12,'really'),(NULL,'at','up','iuijvhyckkvdxaxskqbudpmogzylcsdq','her',27,'2009-11-04 00:38:34','on',37,'giuijvhyckkvdxaxskqbudpmogzy',13,'could',NULL,'egiu',32,'be'),(NULL,'fyhmcggzwexql','but','see','can',4,NULL,'qfyhmcggzwexqlzlq',12,'rqfyhmcggzwexqlzlq',4,'orqfyhmcggzwexql',NULL,'oh',21,'mean'),(NULL,'some','time','porqfyhmcggzwexqlzlqvylmouc','upo',40,'2008-09-09 22:23:29','something',14,'guporqfyhmcggz',6,'will',NULL,'jguporqfyh',22,'bjguporq'),(NULL,'iwuinwrhwdpojk','him','kiwuinwrhwdpojkrpktlhfozbqvioh','skiwuinwrhwdpojk',26,'2001-08-11 11:10:08','that',8,'from',33,'why',NULL,'say',14,'you'),(NULL,'there','krpktlhfoz','see','are',5,NULL,'jkrpktlhfozbqviohnirfn',9,'but',6,'ojkrpktlhfozbqvi',NULL,'pojkrpktlh',11,'dpojkr'),('2008-03-01 03:24:20','think','rkhekpzowz','crkhekpz','yeah',14,NULL,'are',0,'xcrkhekpzowzf',16,'she',NULL,'v',1,'wvxcrkhekp'),(NULL,'in','hynsanddav','ohynsanddavregacksodjtw','oohynsanddavrega',28,NULL,'hey',17,'uoohynsanddavregacksodjtw',38,'au',NULL,'nauoohynsa',25,'jnauoohyns');

SELECT DISTINCT table1.col_int_key AS field1, 1 AS field2
 FROM Z AS table1
 WHERE table1.col_int_key <> 1
 ORDER BY field1 DESC;

Suggested fix:
Root cause is the resetting of 'select->quick= save_quick' at the end of test_if_skip_sort_order() if we detect that the created quick select is unusable due to incorrect 'order_direction'.

This code should be replaced by something like:

          if (select->quick != save_quick)
          {
            delete select->quick;
            select->quick= save_quick;
          }
[22 Dec 2010 12:25] Valeriy Kravchuk
Verified with current mysql-5.1-security tree on 32-bit Ubuntu.
[23 Dec 2010 9:12] MySQL Verification Team
I was curious why my own tests never hit this bug.  So I made a simple testcase, and we see not only a memory leak, but also a totally incorrect result!!!
Doubly bad!

drop table if exists `t1`;
create table `t1` (`a` int,key (`a`)) engine=myisam;
insert into `t1` values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
select distinct `a`,1 from `t1` where `a` <> 1 order by `a` desc;

+------+---+
| a    | 1 |
+------+---+
| NULL | 1 |
| NULL | 1 |
| NULL | 1 |
| NULL | 1 |
| NULL | 1 |
| NULL | 1 |
| NULL | 1 |
| NULL | 1 |
| NULL | 1 |
+------+---+
9 rows in set (0.95 sec)
[29 Dec 2010 0:56] Omer Barnir
triage: setting as a valgrind bug - SR51MRU
[5 Jan 2011 11:48] Ole John Aske
This testcase also gives an incorrect result which has been reported as bug#59308
[5 Jan 2011 13:52] Ole John Aske
This bug has been fixed by the patch which is available for bug#59308
[7 Feb 2011 9:48] Bugs System
Pushed into mysql-5.1 5.1.56 (revid:ole.john.aske@oracle.com-20110207093621-hyaa46qkdbrwbiom) (version source revid:ole.john.aske@oracle.com-20110207093621-hyaa46qkdbrwbiom) (merge vers: 5.1.56) (pib:24)
[7 Feb 2011 9:48] Bugs System
Pushed into mysql-trunk 5.6.2 (revid:ole.john.aske@oracle.com-20110207094653-lcejq75drwade4dd) (version source revid:ole.john.aske@oracle.com-20110207094653-lcejq75drwade4dd) (merge vers: 5.6.2) (pib:24)
[7 Feb 2011 9:49] Bugs System
Pushed into mysql-5.5 5.5.10 (revid:ole.john.aske@oracle.com-20110207094042-ix1ch1gq52ti51i9) (version source revid:ole.john.aske@oracle.com-20110207094042-ix1ch1gq52ti51i9) (merge vers: 5.5.10) (pib:24)
[2 Mar 2011 3:21] Paul DuBois
Noted in 5.1.56, 5.5.10, 5.6.2 changelogs.

Memory leaks detected by Valgrind, some of which could cause
incorrect query results, were corrected.