MySQL Bugs: #9555: UNION causes mysql 4.1 server to crash

Bug #9555	UNION causes mysql 4.1 server to crash
Submitted:	1 Apr 2005 13:27	Modified:	1 Apr 2005 15:36
Reporter:	NOT_FOUND France	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.10a-nt	OS:	Windows (Win XP SP2)
Assigned to:		CPU Architecture:	Any

Description:
The following SQL is crashing the MySQL server:

(SELECT action.*, match(action.ActBy, action.ActText, action.ActStatus) against ('order') "Score"
FROM action
WHERE Score > 0.2
ORDER BY Score DESC)

UNION

(SELECT action.*, match(action.ActBy, action.ActText, action.ActStatus) against ('order') "Score"
FROM action
WHERE action.ActSeq like '%order%'
ORDER BY Score DESC)

ORDER BY Score DESC

When executed via the MySQL Query Browser (not tried other clients) the GPF dialog is displayed for mysql-nt.exe

szAppName : mysqld-nt.exe     szAppVer : 0.0.0.0     szModName : mysqld-nt.exe
szModVer : 0.0.0.0     offset : 00031d77

This is a vanilla install of mysql 4.1.10a

It seems to be limited to this specific case, simpler unions fail in the same way:

(SELECT * FROM action WHERE ActBy like '%order%' ORDER BY ActSeq)
UNION
(SELECT * FROM action WHERE ActSeq like '%order%' ORDER BY ActSeq)
ORDER BY ActSeq

also causes the server to crahs.

How to repeat:
USE test;
CREATE TABLE `action` (
  `Location` char(2) NOT NULL default '',
  `CallRef` varchar(50) NOT NULL default '',
  `ActSeq` varchar(50) NOT NULL default '0',
  `LastUpdated` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `ActTime` int(11) default NULL,
  `ActCompDate` datetime default NULL,
  `ActCompTime` datetime default NULL,
  `ActBy` varchar(50) default NULL,
  `ActAnalyst` varchar(50) default NULL,
  `ActText` longtext,
  `ActStatus` varchar(50) default NULL,
  `ActType` varchar(50) default NULL,
  `ActSubAction` varchar(50) default NULL,
  `ActActionType` varchar(50) default NULL,
  `ActActivity` varchar(50) default NULL,
  `SecurityLevel` int(2) default NULL,
  `ActPrivate` char(1) default NULL,
  PRIMARY KEY  (`Location`,`CallRef`,`ActSeq`),
  FULLTEXT KEY `TEXT` (`ActBy`,`ActText`,`ActStatus`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

(SELECT * FROM action WHERE ActBy like '%order%' ORDER BY ActSeq)
UNION
(SELECT * FROM action WHERE ActSeq like '%order%' ORDER BY ActSeq)
ORDER BY ActSeq

Suggested fix:
The work around in my particular case will be to add ActSeq to the fulltext index and avoid using UNION all together.

I wasn't able to repeat. Can you post yourmy.ini file ?
Thanks in advance:

    ->   PRIMARY KEY  (`Location`,`CallRef`,`ActSeq`),
    ->   FULLTEXT KEY `TEXT` (`ActBy`,`ActText`,`ActStatus`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.16 sec)

mysql>
mysql> (SELECT * FROM action WHERE ActBy like '%order%' ORDER BY ActSeq)
    -> UNION
    -> (SELECT * FROM action WHERE ActSeq like '%order%' ORDER BY ActSeq)
    -> ORDER BY ActSeq;
Empty set (0.06 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 4.1.10-nt |
+-----------+
1 row in set (0.00 sec)

C:\Program Files\MySQL\MySQL Server 4.1\my.ini

Attachment: my.ini (application/octet-stream, text), 9.02 KiB.

I have attached my my.ini file to this bug as requested.

I am now having problems with other simpler SQL's so it looks more like a general problem.  Even

 select * from action; 

crashes the server at the moment.  It seems I had turned on some query cache options (these can be seen in the attached my.ini) and have now turned these off and the test case works without these cache options.

Basically I had used the admin client to enable cache size and cache type options and upped the cache size from its original setting (16?) to 128.

I have just re-enabled these options, restarted the server and the query crashes the server again.

Just to clarify, I was changing the config and testing during the writing of the previous comment, hence it not working, then working, then not working again.

Yes I was able to repeat with a server optimized (not happens with
debug version). This is duplicate of bug:

http://bugs.mysql.com/bug.php?id=8346

I will change its status as verified again.

Thank you for the bug report.