MySQL Bugs: #35945: segfault on select with group_concat(distinct ..) and left join

Bug #35945	segfault on select with group_concat(distinct ..) and left join
Submitted:	9 Apr 2008 17:31	Modified:	16 Apr 2008 16:22
Reporter:	Arkadiusz Miskiewicz (Basic Quality Contributor)	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.1.23/5.0BK/5.1BK/6.0BK	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	group_concat, qc

Description:
SELECT causes server segfault.

How to repeat:
DROP TABLE IF EXISTS `glpi_dropdown_manufacturer`;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `glpi_dropdown_manufacturer` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `comments` text COLLATE utf8_unicode_ci,
  PRIMARY KEY (`ID`),
  KEY `name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
SET character_set_client = @saved_cs_client;

DROP TABLE IF EXISTS `glpi_dropdown_os`;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `glpi_dropdown_os` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `comments` text COLLATE utf8_unicode_ci,
  PRIMARY KEY (`ID`),
  KEY `name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
SET character_set_client = @saved_cs_client;

DROP TABLE IF EXISTS `glpi_licenses`;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `glpi_licenses` (
  `ID` int(15) NOT NULL AUTO_INCREMENT,
  `sID` int(15) NOT NULL DEFAULT '0',
  `version` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `serial` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `expire` date DEFAULT NULL,
  `oem` smallint(6) NOT NULL DEFAULT '0',
  `oem_computer` int(11) NOT NULL DEFAULT '0',
  `buy` smallint(6) NOT NULL DEFAULT '1',
  `comments` text COLLATE utf8_unicode_ci,
  PRIMARY KEY (`ID`),
  KEY `sID` (`sID`),
  KEY `oem_computer` (`oem_computer`),
  KEY `serial` (`serial`),
  KEY `expire` (`expire`),
  KEY `oem` (`oem`),
  KEY `buy` (`buy`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
SET character_set_client = @saved_cs_client;

DROP TABLE IF EXISTS `glpi_software`;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `glpi_software` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `FK_entities` int(11) NOT NULL DEFAULT '0',
  `name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `comments` text COLLATE utf8_unicode_ci,
  `location` int(11) NOT NULL DEFAULT '0',
  `tech_num` int(11) NOT NULL DEFAULT '0',
  `platform` int(11) NOT NULL DEFAULT '0',
  `is_update` smallint(6) NOT NULL DEFAULT '0',
  `update_software` int(11) NOT NULL DEFAULT '-1',
  `FK_glpi_enterprise` int(11) NOT NULL DEFAULT '0',
  `deleted` smallint(6) NOT NULL DEFAULT '0',
  `is_template` smallint(6) NOT NULL DEFAULT '0',
  `tplname` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `date_mod` datetime DEFAULT NULL,
  `notes` longtext COLLATE utf8_unicode_ci,
  `FK_users` int(11) NOT NULL DEFAULT '0',
  `FK_groups` int(11) NOT NULL DEFAULT '0',
  `state` int(11) NOT NULL DEFAULT '0',
  `ticket_tco` decimal(20,4) DEFAULT '0.0000',
  `helpdesk_visible` int(11) NOT NULL DEFAULT '1',
  `category` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  KEY `platform` (`platform`),
  KEY `location` (`location`),
  KEY `FK_glpi_enterprise` (`FK_glpi_enterprise`),
  KEY `date_mod` (`date_mod`),
  KEY `tech_num` (`tech_num`),
  KEY `name` (`name`),
  KEY `FK_groups` (`FK_groups`),
  KEY `FK_users` (`FK_users`),
  KEY `update_software` (`update_software`),
  KEY `FK_entities` (`FK_entities`),
  KEY `is_template` (`is_template`),
  KEY `is_update` (`is_update`),
  KEY `deleted` (`deleted`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
SET character_set_client = @saved_cs_client;

LOCK TABLES `glpi_software` WRITE;
INSERT INTO `glpi_software` VALUES (1,0,'nazwa','',0,0,0,0,-1,0,0,0,'','2008-03-28 16:14:25',NULL,0,0,0,'0.0000',0,0);
UNLOCK TABLES;

SELECT 
	glpi_software.name AS ITEM_0, 
	glpi_software.ID AS ITEM_0_2, 
	glpi_dropdown_manufacturer.name AS ITEM_1,  
	GROUP_CONCAT( DISTINCT glpi_licenses.version SEPARATOR '$$$$') AS ITEM_2, 
	glpi_dropdown_os.name AS ITEM_3, glpi_software.ID AS ID  FROM glpi_software 
	LEFT JOIN glpi_dropdown_manufacturer  ON (glpi_software.FK_glpi_enterprise = glpi_dropdown_manufacturer.ID)  
	LEFT JOIN glpi_licenses  ON (glpi_software.ID = glpi_licenses.sID)  LEFT JOIN glpi_dropdown_os  ON (glpi_software.platform = glpi_dropdown_os.ID)  
	WHERE  glpi_software.deleted='0'  AND glpi_software.is_template='0'  AND  (  1 )  
	GROUP BY glpi_software.ID  
	ORDER BY glpi_software.name ASC  LIMIT 0, 20

Thank you for the bug report. Verified as described on 5.0/5.1/6.0 source
servers.

    -> glpi_software.name AS ITEM_0, 
    -> glpi_software.ID AS ITEM_0_2, 
    -> glpi_dropdown_manufacturer.name AS ITEM_1,  
    -> GROUP_CONCAT( DISTINCT glpi_licenses.version SEPARATOR '$$$$') AS ITEM_2, 
    -> glpi_dropdown_os.name AS ITEM_3, glpi_software.ID AS ID  FROM glpi_software 
    -> LEFT JOIN glpi_dropdown_manufacturer  ON (glpi_software.FK_glpi_enterprise =
    -> glpi_dropdown_manufacturer.ID)  
    -> LEFT JOIN glpi_licenses  ON (glpi_software.ID = glpi_licenses.sID)  LEFT JOIN
    -> glpi_dropdown_os  ON (glpi_software.platform = glpi_dropdown_os.ID)  
    -> WHERE  glpi_software.deleted='0'  AND glpi_software.is_template='0'  AND  (  1 )  
    -> GROUP BY glpi_software.ID  
    -> ORDER BY glpi_software.name ASC  LIMIT 0, 20;
ERROR 2013 (HY000): Lost connection to MySQL server during query
T1 >

simplified testcase and stack trace.

drop table if exists `t1`;
create table `t1` (`a` char(2),`b` int,key `b` (`b`)) engine=myisam;
drop table if exists `t2`;
create table `t2` (`c` int,key (`c`)) engine=myisam;
insert into `t2` values (1);
select  group_concat(distinct t1.a)from t2 left join t1  on (t2.c = t1.b);

mysqld-nt.exe!free_tree
mysqld-nt.exe!Unique::reset
mysqld-nt.exe!Item_func_group_concat::clear
mysqld-nt.exe!init_sum_functions
mysqld-nt.exe!end_send_group
mysqld-nt.exe!do_select
mysqld-nt.exe!JOIN::exec
mysqld-nt.exe!mysql_select
mysqld-nt.exe!handle_select
mysqld-nt.exe!mysql_execute_command
mysqld-nt.exe!mysql_parse
mysqld-nt.exe!dispatch_command
mysqld-nt.exe!do_command
mysqld-nt.exe!handle_one_connection
mysqld-nt.exe!pthread_start
mysqld-nt.exe!_callthreadstart
mysqld-nt.exe!_threadstart

possible duplicate of bug #35298 ?

Duplicate of bug #35298.