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:Microsoft Windows (Windows 2000 server)
Assigned to: CPU Architecture:Any

[19 Nov 2003 14:23] Stephen Bungay
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
[21 Nov 2003 9:37] Dean Ellis
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.
[21 Nov 2003 11:26] Stephen Bungay
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.
[21 Nov 2003 11:46] Stephen Bungay
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.