MySQL Bugs: #1892: Sub Select returns results, then crashes the server.

Bug #1892	Sub Select returns results, then crashes the server.
Submitted:	19 Nov 2003 14:23	Modified:	21 Nov 2003 9:37
Reporter:	Stephen Bungay	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	4.1.0a	OS:	Windows (Windows 2000 server)
Assigned to:		CPU Architecture:	Any

Description:
  A query which returns the results of a join on two sub-selects returns results and then crashes the server... Perhaps it's the query.. perhaps I'm doing the wrong thing, however the query DOES execute and return the correct results, then promptly crashes, no error messages, it just vanishes from memory. A restart brings everything back up with no complaints.
  Note, the IN (133) may look like it should read =133, however it is possible for there to be more than one value so IN(133) just happens to be what was put in the Query based on the users selected options.
  I'm kind of treating the sub-selects as dynamically created named Views.

How to repeat:
Just run a query that performs an operation on two tables similar to the operation being performed in the following query..

SELECT RS1.ID, 
       RS1.DrawingName, 
       RS1.Abbreviation,
       RS1.RootCategoryID, 
       RS1.DrawingPath, 
       RS1.DrawingFile, 
       RS1.ModelFile, 
       RS1.Configuration, 
       RS2.RevisionID
FROM
(SELECT Distinct D.ID, 
        D.DrawingName, 
        DT.Abbreviation, 
        DT.RootCategoryID, 
        D.DrawingPath, 
        D.DrawingFile, 
        D.ModelPath, 
        D.ModelFile, 
        D.Configuration 
FROM (Drawings_ProjectSpecific AS D 
      INNER JOIN DrawingTypes AS DT ON D.DrawingTypeID = DT.ID) 
      LEFT JOIN DrawingPackages_ProjectSpecific AS DPPS ON D.ID = DPPS.Drawing_ProjectSpecificID 
WHERE D.ProjectID = 75 AND DT.RootCategoryID IN (133) AND DPPS.Drawing_ProjectSpecificID IS NOT NULL)
AS RS1 
INNER JOIN (SELECT MAX(ID) AS RevisionID, 
            DrawingID 
            FROM DrawingRevisions_ProjectSpecific
            GROUP BY DrawingID) AS RS2 ON RS2.DrawingID=RS1.ID

This query works in 4.1.0 and 4.1.1.  As with issue #1895 it will crash 4.1.0 when used with EXPLAIN (if you ran this with MySQL Control Center, it uses EXPLAIN automatically), but this is fixed in 4.1.1.

Thank you.

Just to elucidate, tests were carried out in MySQL Control Centre however the impetus for testing came about when a VB6 app in the development environment  reported that it's connection to the server was lost almost as soon (a recordset was returned) as ADO had executed the Query.

I hit submit before I should have.. sorry...
  As an addendum to my previous comment; the problem I had (I backed out the upgrade) existed outside the scope of MyCC and is reproduceable within VB6, should it therefore be grouped together with #1895? In my humble opinion, if #1895 can also be reproduced through an ADO connection via MyODBC 3.51.06 (NOT the debug version) from the VB6 dev environment then both entries are related and should probably be re-opened, else #1892 is a different problem.
  I'll test 4.1.1 when I get the time to see if the problem re-occurs.