Bug #14482 Server crash when subselecting from the same table
Submitted: 30 Oct 2005 2:28 Modified: 30 Nov 2005 17:31
Reporter: Richard Thorne Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S1 (Critical)
Version:5.0.16-BK, 4.1.16-BK OS:Linux (Linux)
Assigned to: Evgeny Potemkin CPU Architecture:Any

[30 Oct 2005 2:28] Richard Thorne 
Description:
When performing the following query:

SELECT
	COUNT(*) AS incoming
FROM
	fleets f
WHERE
	(f.TargetID, f.TargetBaseID) IN (SELECT													ID, BaseID											 FROM													users u											 WHERE													(u.x, u.y) = (SELECT													x, y												  FROM													users												  WHERE 													ID='Z4N4g-PVR0p-3ZB3P-6h9t8-7x22b-iC9h3-09225-5xY81-ptc2t-ZdN53' AND														BaseID='0'))

MySQL freezes for a few seconds, then crashes; leaving no error logs in neither the system event log, nor the hostname.err error log file (Hostname being the name of the machine the error log is on of course).

The only thing I thought that could be causing this would be file locks placed by the OS, but --skip-external-locking is enabled by default in MySQL 5, and I checked it was using SHOW VARIABLES;

I am aware the above query is inefficient, but it was only created for testing some data. Regardless, it shouldn't cause the DBMS to crash.

How to repeat:
CREATE TABLE `fleets` (
  `ID` varchar(150) NOT NULL default '0',
  `BaseID` varchar(150) NOT NULL default '0',
  `fleet` tinyint(1) NOT NULL default '1',
  `S0` int(11) unsigned NOT NULL default '0',
  `S1` int(11) unsigned NOT NULL default '0',
  `S2` int(11) unsigned NOT NULL default '0',
  `S3` int(11) unsigned NOT NULL default '0',
  `S4` int(11) unsigned NOT NULL default '0',
  `S5` int(11) unsigned NOT NULL default '0',
  `S6` int(11) unsigned NOT NULL default '0',
  `S7` int(11) unsigned NOT NULL default '0',
  `S8` int(11) unsigned NOT NULL default '0',
  `S9` int(11) unsigned NOT NULL default '0',
  `S10` int(11) unsigned NOT NULL default '0',
  `S11` int(11) unsigned NOT NULL default '0',
  `S12` int(11) unsigned NOT NULL default '0',
  `S13` int(11) unsigned NOT NULL default '0',
  `S14` int(11) unsigned NOT NULL default '0',
  `S15` int(11) unsigned NOT NULL default '0',
  `S16` int(11) unsigned NOT NULL default '0',
  `S17` int(11) unsigned NOT NULL default '0',
  `S18` int(11) unsigned NOT NULL default '0',
  `S19` int(11) unsigned NOT NULL default '0',
  `S20` int(11) unsigned NOT NULL default '0',
  `S21` int(11) unsigned NOT NULL default '0',
  `S22` int(11) unsigned NOT NULL default '0',
  `S23` int(11) unsigned NOT NULL default '0',
  `S24` int(11) unsigned NOT NULL default '0',
  `S25` int(11) unsigned NOT NULL default '0',
  `S26` int(11) unsigned NOT NULL default '0',
  `S27` int(11) unsigned NOT NULL default '0',
  `S28` int(11) unsigned NOT NULL default '0',
  `S29` int(11) unsigned NOT NULL default '0',
  `S30` int(11) unsigned NOT NULL default '0',
  `S31` int(11) unsigned NOT NULL default '0',
  `S32` int(11) unsigned NOT NULL default '0',
  `S33` int(11) unsigned NOT NULL default '0',
  `S34` int(11) unsigned NOT NULL default '0',
  `S35` int(11) unsigned NOT NULL default '0',
  `S36` int(11) unsigned NOT NULL default '0',
  `S37` int(11) unsigned NOT NULL default '0',
  `S38` int(11) unsigned NOT NULL default '0',
  `S39` int(11) unsigned NOT NULL default '0',
  `S40` int(11) unsigned NOT NULL default '0',
  `Mission` tinyint(2) unsigned NOT NULL default '0',
  `TargetID` varchar(150) NOT NULL default '',
  `TargetBaseID` varchar(150) NOT NULL default '',
  `ETA` tinyint(2) NOT NULL default '0',
  `ReturnETA` tinyint(2) NOT NULL default '0',
  PRIMARY KEY  (`id`,`baseid`,`fleet`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `users` (
  `ID` varchar(150) NOT NULL default '',
  `BaseID` varchar(150) NOT NULL default '0',
  `x` int(11) unsigned NOT NULL,
  `y` int(11) unsigned NOT NULL,
  PRIMARY KEY  (`id`,`baseid`),
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
[30 Oct 2005 2:29] Richard Thorne 
Hrm, the text formatter evidently didn't like my tabbed query format much.
[30 Oct 2005 10:45] Valeriy Kravchuk 
Thank you for a problem report. I had reformatted your query slightly and now it looks like the following:

SELECT COUNT(*) AS incoming FROM fleets f
WHERE (f.TargetID, f.TargetBaseID) IN 
  (SELECT ID, BaseID FROM users u 
    WHERE (u.x, u.y) =
    (SELECT x, y FROM users 
      WHERE ID='Z4N4g-PVR0p-3ZB3P-6h9t8-7x22b-iC9h3-09225-5xY81-ptc2t-ZdN53'
      AND BaseID='0'))

How many rows are there in your tables? Can you upload a dump of your data?
Your my.cnf/my.ini files content may be useful too.

Please, send the results of EXPLAIN for this SELECT in your system.
[30 Oct 2005 10:46] Valeriy Kravchuk 
Thank you for a problem report. I had reformatted your query slightly and now it looks like the following:

SELECT COUNT(*) AS incoming FROM fleets f
WHERE (f.TargetID, f.TargetBaseID) IN 
  (SELECT ID, BaseID FROM users u 
    WHERE (u.x, u.y) =
    (SELECT x, y FROM users 
      WHERE ID='Z4N4g-PVR0p-3ZB3P-6h9t8-7x22b-iC9h3-09225-5xY81-ptc2t-ZdN53'
      AND BaseID='0'))

How many rows are there in your tables? Can you upload a dump of your data?
Your my.cnf/my.ini files content may be useful too.

Please, send the results of EXPLAIN for this SELECT in your system.
[30 Oct 2005 12:51] Richard Thorne 
Here's a screenshot of the output from EXPLAIN in phpmyadmin (Primarily in screenshot form because it's easier to read):

http://www.starsphere.net/Explain.png

There are very few rows in my tables, this is only a test setup, 8 rows in the "users" table and 32 rows in the "fleets" table.

Here is a data dump of the table data:

http://www.starsphere.net/dump.sql

Oh, and I made a typo with the "users" schema query, here is the correct one:

CREATE TABLE `users` (
  `ID` varchar(150) NOT NULL default '',
  `BaseID` varchar(150) NOT NULL default '0',
  `x` int(11) unsigned NOT NULL,
  `y` int(11) unsigned NOT NULL,
  PRIMARY KEY  (`id`,`baseid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
[30 Oct 2005 14:37] Valeriy Kravchuk 
Tables and data to repeat the crash

Attachment: 14482.sql (text/x-delimtext), 10.93 KiB.
[30 Oct 2005 14:45] Valeriy Kravchuk 
Verified using your test case (see the 14482.sql file attached for convenience) on Linux Fedora Core 1 using latest 5.0.16-BK (ChangeSet@1.1957, 2005-10-29 13:11:34+04:00, konstantin@mysql.com) and your query:

mysql> SELECT COUNT(*) AS incoming FROM fleets f
    -> WHERE (f.TargetID, f.TargetBaseID) IN
    ->   (SELECT ID, BaseID FROM users u
    ->     WHERE (u.x, u.y) =
    ->     (SELECT x, y FROM users
    ->       WHERE ID='Z4N4g-PVR0p-3ZB3P-6h9t8-7x22b-iC9h3-09225-5xY81-ptc2t-ZdN53'
    ->       AND BaseID='0'));
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>
Number of processes running now: 0
051030 17:38:28  mysqld restarted

Also verified with 4.1.16-BK-debug (ChangeSet@1.2450, 2005-10-27 10:20:38-07:00, jimw@mysql.com). The same crash. Nothing in the error log.
[5 Nov 2005 1:46] Richard Thorne 
A very odd occurance, if you change the single quotes around 'Z4N4g-PVR0p-3ZB3P-6h9t8-7x22b-iC9h3-09225-5xY81-ptc2t-ZdN53' and '0' to double quotes, e.g.

SELECT COUNT(*) AS incoming FROM fleets f
WHERE (f.TargetID, f.TargetBaseID) IN 
  (SELECT ID, BaseID FROM users u 
    WHERE (u.x, u.y) =
    (SELECT x, y FROM users 
      WHERE ID="Z4N4g-PVR0p-3ZB3P-6h9t8-7x22b-iC9h3-09225-5xY81-ptc2t-ZdN53"
      AND BaseID="0"))

The query works fine. Now, I know the use of double quotes isn't SQL standard (If memory serves the SQL standard says they're supposed to be used to quote reserved-words only, but MySQL seems to allow them for string literals as well), but this is really rather odd.

I hope this helps.
[5 Nov 2005 2:18] Richard Thorne 
On further testing, this seems to only work once, then causes the server to crash the second time you run it.
[10 Nov 2005 19:22] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/32155
[17 Nov 2005 17:14] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/32372
[24 Nov 2005 18:53] Evgeny Potemkin 
resolve_const_item() substitutes item which will evaluate to constant with
equvalent constant item, basing on the item's result type. In this case
subselect was resolved as constant, and resolve_const_item() was substituting
it's result's Item_caches to Item_null. Later Item_cache's function was called
for Item_null object, which caused server crash.

Fixed in 4.1.16, cset  1.2462.10.1
[25 Nov 2005 22:46] Evgeny Potemkin 
Fixed in 5.0.16
[30 Nov 2005 17:31] Paul DuBois 
Noted in 4.1.16, 5.0.16 changelogs.