MySQL Bugs: #35236: Crash while executing query with ORDER BY

Bug #35236	Crash while executing query with ORDER BY
Submitted:	12 Mar 2008 1:02	Modified:	16 May 2008 16:34
Reporter:	Mike Wright	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.0.51a-debug	OS:	Linux (Linux movieplex 2.6.22.5-31-default #1 SMP 2007/09/21 22:29:00 UTC x86_64 x86_64 x86_64 GNU/Linux)
Assigned to:		CPU Architecture:	Any

Description:
From mysql commandline, I execute the following query which works:
SELECT title, subtitle, description, category, starttime, endtime, callsign, icon, channum, seriesid, programid FROM program, channel WHERE program.chanid = channel.chanid AND channel.chanid = 2082 AND starttime <= '2008-03-10T22:51:57'; 

However, adding 

ORDER BY starttime desc

Causes:

ERROR 2013 (HY000): Lost connection to MySQL server during query

Output from resolve_stack_dump:

0x5ab88d handle_segfault + 593
0x69b127 _ZN17QUICK_SELECT_DESCC1EP18QUICK_RANGE_SELECTj + 129
0x61dbaa _Z27create_tmp_field_from_fieldP3THDP5FieldPKcP8st_tableP10Item_fieldj + 5062
0x61dffa _Z27create_tmp_field_from_fieldP3THDP5FieldPKcP8st_tableP10Item_fieldj + 6166
0x637091 _ZN4JOIN4execEv + 8725
0x632e91 _Z12mysql_selectP3THDPPP4ItemP10TABLE_LISTjR4ListIS1_ES2_jP8st_orderSB_S2_SB_yP13select_resultP18st_select_lex_unitP13st_select + 895
0x6376bf _Z13handle_selectP3THDP6st_lexP13select_resultm + 485
0x5c720b _Z21mysql_execute_commandP3THD + 2353
0x5cf18a _Z11mysql_parseP3THDPKcjPS2_ + 616
0x5cfe9a _Z16dispatch_command19enum_server_commandP3THDPcj + 2850
0x5d16d7 _Z10do_commandP3THD + 569
0x5d1cd7 handle_one_connection + 1205
0x2ac1161c3020 _end + 357418872

How to repeat:
I am testing the latest build of mythtv 0.21 however, it looks like the client isn't the issue since it can be recreated by executing the same query from "mysql".

Thank you for a problem report. Please, send SHOW CREATE TABLE and SHOW TABLE STATUS results for these program and channel tables used. Can you upload some test data for the tables that demonstrate the behaviour described?

SHOW CREATE for table program:

| program | CREATE TABLE `program` (
  `chanid` int(10) unsigned NOT NULL default '0',
  `starttime` datetime NOT NULL default '0000-00-00 00:00:00',
  `endtime` datetime NOT NULL default '0000-00-00 00:00:00',
  `title` varchar(128) NOT NULL default '',
  `subtitle` varchar(128) NOT NULL default '',
  `description` text NOT NULL,
  `category` varchar(64) NOT NULL default '',
  `category_type` varchar(64) NOT NULL default '',
  `airdate` year(4) NOT NULL default '0000',
  `stars` float NOT NULL default '0',
  `previouslyshown` tinyint(4) NOT NULL default '0',
  `title_pronounce` varchar(128) NOT NULL default '',
  `stereo` tinyint(1) NOT NULL default '0',
  `subtitled` tinyint(1) NOT NULL default '0',
  `hdtv` tinyint(1) NOT NULL default '0',
  `closecaptioned` tinyint(1) NOT NULL default '0',
  `partnumber` int(11) NOT NULL default '0',
  `parttotal` int(11) NOT NULL default '0',
  `seriesid` varchar(40) NOT NULL default '',
  `originalairdate` date default NULL,
  `showtype` varchar(30) NOT NULL default '',
  `colorcode` varchar(20) NOT NULL default '',
  `syndicatedepisodenumber` varchar(20) NOT NULL default '',
  `programid` varchar(40) NOT NULL default '',
  `manualid` int(10) unsigned NOT NULL default '0',
  `generic` tinyint(1) default '0',
  `listingsource` int(11) NOT NULL default '0',
  `first` tinyint(1) NOT NULL default '0',
  `last` tinyint(1) NOT NULL default '0',
  `audioprop` set('STEREO','MONO','SURROUND','DOLBY','HARDHEAR','VISUALIMPAIR') NOT NULL,
  `subtitletypes` set('HARDHEAR','NORMAL','ONSCREEN','SIGNED') NOT NULL,
  `videoprop` set('HDTV','WIDESCREEN','AVC') NOT NULL,
  PRIMARY KEY  (`chanid`,`starttime`,`manualid`),
  KEY `endtime` (`endtime`),
  KEY `title` (`title`),
  KEY `title_pronounce` (`title_pronounce`),
  KEY `seriesid` (`seriesid`),
  KEY `id_start_end` (`chanid`,`starttime`,`endtime`),
  KEY `program_manualid` (`manualid`),
  KEY `previouslyshown` (`previouslyshown`),
  KEY `programid` (`programid`,`starttime`),
  KEY `starttime` (`starttime`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |

SHOW CREATE for table channel:

| channel | CREATE TABLE `channel` (
  `chanid` int(10) unsigned NOT NULL default '0',
  `channum` varchar(10) NOT NULL default '',
  `freqid` varchar(10) default NULL,
  `sourceid` int(10) unsigned default NULL,
  `callsign` varchar(20) NOT NULL default '',
  `name` varchar(64) NOT NULL default '',
  `icon` varchar(255) NOT NULL default 'none',
  `finetune` int(11) default NULL,
  `videofilters` varchar(255) NOT NULL default '',
  `xmltvid` varchar(64) NOT NULL default '',
  `recpriority` int(10) NOT NULL default '0',
  `contrast` int(11) default '32768',
  `brightness` int(11) default '32768',
  `colour` int(11) default '32768',
  `hue` int(11) default '32768',
  `tvformat` varchar(10) NOT NULL default 'Default',
  `commfree` tinyint(4) NOT NULL default '0',
  `visible` tinyint(1) NOT NULL default '1',
  `outputfilters` varchar(255) NOT NULL default '',
  `useonairguide` tinyint(1) default '0',
  `mplexid` smallint(6) default NULL,
  `serviceid` mediumint(8) unsigned default NULL,
  `atscsrcid` int(11) default NULL,
  `tmoffset` int(11) NOT NULL default '0',
  `atsc_major_chan` int(10) unsigned NOT NULL default '0',
  `atsc_minor_chan` int(10) unsigned NOT NULL default '0',
  `last_record` datetime NOT NULL,
  `default_authority` varchar(32) NOT NULL default '',
  `commmethod` int(11) NOT NULL default '-1',
  PRIMARY KEY  (`chanid`),
  KEY `channel_src` (`channum`,`sourceid`),
  KEY `sourceid` (`sourceid`,`xmltvid`,`chanid`),
  KEY `visible` (`visible`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |

What format would you like the data?  I can tar the mysql data files.  Would that work?

Thanks!

Yes, it will work. mysqldump results are also OK. Please, compress file(s) in any case.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Mike,

we still need some test data from you.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".