Bug #27662 Query crashes server

• Submitted: 5 Apr 2007 7:00
• Modified: 5 Apr 2007 9:48
• Reporter: Vadim TKACHENKO
• Status: Can't repeat
• Category: MySQL Server
• Severity: S1 (Critical)
• Version: 5.0.37
• OS: Linux
• CPU Architecture: Any

[5 Apr 2007 7:00] Vadim TKACHENKO

Description:
Next query crashes mysql server:

SELECT DISTINCT sid FROM (( 
 SELECT sid FROM fr WHERE pid = 1782339 AND ap = 0
 ) UNION (
 SELECT pid FROM fr WHERE sid = 1782339 AND ap = 0
 )
 ORDER BY fid DESC) as s;

Table:
CREATE TABLE `fr` (
  `fid` int(10) unsigned NOT NULL auto_increment,
  `pid` int(10) unsigned NOT NULL,
  `sid` int(10) unsigned NOT NULL,
  `ap` tinyint(1) unsigned NOT NULL default '0',
  PRIMARY KEY  (`fid`),
  KEY `primary_user_id` (`pid`,`ap`),
  KEY `secondary_user_id` (`sid`,`ap`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

How to repeat:
I provide link to dump in private comment

[5 Apr 2007 8:10] Sveta Smirnova

Thank you for the report.

I get error while running the SELECT query:

ERROR 1054 (42S22): Unknown column 'fid' in 'order clause'

Please provide correct query.

[5 Apr 2007 8:18] Vadim TKACHENKO

Sveta,

Here is what I have trying to run this query:

mysql> SELECT DISTINCT sid FROM (( 
    ->  SELECT sid FROM fr WHERE pid = 1782339 AND ap = 0
    ->  ) UNION (
    ->  SELECT pid FROM fr WHERE sid = 1782339 AND ap = 0
    ->  )
    ->  ORDER BY fid DESC) as s;
ERROR 2013 (HY000): Lost connection to MySQL server during query

[5 Apr 2007 8:19] Vadim TKACHENKO

This is repeatable for me on both 5.0.37 and 5.0.22 servers

[5 Apr 2007 9:48] Sveta Smirnova

Thank you for the feedback.

Bug is repeatable with mySQL 5.0.37 but is not repeatable with current sources.