MySQL Bugs: #75920: Comments are parsed wrongly

Bug #75920	Comments are parsed wrongly
Submitted:	16 Feb 2015 18:21	Modified:	20 Mar 2015 20:42
Reporter:	Victor Porton	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S2 (Serious)
Version:	5.5.40	OS:	FreeBSD
Assigned to:		CPU Architecture:	Any

Description:
The following code produces the error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 0, 25' at line 34 

SET @UID = 10;
SELECT * FROM (
      SELECT VisitsDetails.Object AS MyShow, Visits.Duration
      FROM VisitsDetails
           INNER JOIN Visits ON Visits.ID=VisitsDetails.VisitID
           INNER JOIN VisitsSessions ON VisitsSessions.ID=Visits.Session
           INNER JOIN Shows ON Shows.ID=VisitsDetails.Object
      WHERE SiteSection='show' AND
            VisitsSessions.User=@UID AND Visits.Duration IS NOT NULL AND
            Shows.Archive=0
    UNION
      SELECT Seances.ShowID AS MyShow, Visits.Duration
      FROM VisitsDetails
           INNER JOIN Visits ON Visits.ID=VisitsDetails.VisitID
           INNER JOIN VisitsSessions ON VisitsSessions.ID=Visits.Session
           INNER JOIN Shows ON Shows.ID=VisitsDetails.Object
           INNER JOIN Seances ON VisitsDetails.Object=Seances.ID
      WHERE SiteSection='seance' AND
            VisitsSessions.User=@UID AND Visits.Duration IS NOT NULL AND
            Shows.Archive=0
    ) t
    WHERE
          # есть непроданные билеты:
          EXISTS(
            SELECT * FROM Seances INNER JOIN NewSeats ON NewSeats.SeanceID=Seances.ID
            WHERE Seances.ShowID=MyShow AND Seances.Active=1 AND
            NewSeats.Owner=0 AND NewSeats.Status=2 AND
            # но данный юзер их не покупал:
            NOT EXISTS(SELECT * FROM PreOrders INNER JOIN Orders ON Orders.ID=PreOrders.OrderID WHERE VisitorID=@UID AND PreOrders.SeanceID=Seances.ID)
          )
    ORDER BY Duration DESC
    LIMIT 5

If I remove the comments, there is no error:

SET @UID = 10;
SELECT * FROM (
      SELECT VisitsDetails.Object AS MyShow, Visits.Duration
      FROM VisitsDetails
           INNER JOIN Visits ON Visits.ID=VisitsDetails.VisitID
           INNER JOIN VisitsSessions ON VisitsSessions.ID=Visits.Session
           INNER JOIN Shows ON Shows.ID=VisitsDetails.Object
      WHERE SiteSection='show' AND
            VisitsSessions.User=@UID AND Visits.Duration IS NOT NULL AND
            Shows.Archive=0
    UNION
      SELECT Seances.ShowID AS MyShow, Visits.Duration
      FROM VisitsDetails
           INNER JOIN Visits ON Visits.ID=VisitsDetails.VisitID
           INNER JOIN VisitsSessions ON VisitsSessions.ID=Visits.Session
           INNER JOIN Shows ON Shows.ID=VisitsDetails.Object
           INNER JOIN Seances ON VisitsDetails.Object=Seances.ID
      WHERE SiteSection='seance' AND
            VisitsSessions.User=@UID AND Visits.Duration IS NOT NULL AND
            Shows.Archive=0
    ) t
    WHERE
          EXISTS(
            SELECT * FROM Seances INNER JOIN NewSeats ON NewSeats.SeanceID=Seances.ID
            WHERE Seances.ShowID=MyShow AND Seances.Active=1 AND
            NewSeats.Owner=0 AND NewSeats.Status=2 AND
            NOT EXISTS(SELECT * FROM PreOrders INNER JOIN Orders ON Orders.ID=PreOrders.OrderID WHERE VisitorID=@UID AND PreOrders.SeanceID=Seances.ID)
          )
    ORDER BY Duration DESC
    LIMIT 5

Sorry, I cannot provide our database structure, it's private.

How to repeat:
SET @UID = 10;
SELECT * FROM (
      SELECT VisitsDetails.Object AS MyShow, Visits.Duration
      FROM VisitsDetails
           INNER JOIN Visits ON Visits.ID=VisitsDetails.VisitID
           INNER JOIN VisitsSessions ON VisitsSessions.ID=Visits.Session
           INNER JOIN Shows ON Shows.ID=VisitsDetails.Object
      WHERE SiteSection='show' AND
            VisitsSessions.User=@UID AND Visits.Duration IS NOT NULL AND
            Shows.Archive=0
    UNION
      SELECT Seances.ShowID AS MyShow, Visits.Duration
      FROM VisitsDetails
           INNER JOIN Visits ON Visits.ID=VisitsDetails.VisitID
           INNER JOIN VisitsSessions ON VisitsSessions.ID=Visits.Session
           INNER JOIN Shows ON Shows.ID=VisitsDetails.Object
           INNER JOIN Seances ON VisitsDetails.Object=Seances.ID
      WHERE SiteSection='seance' AND
            VisitsSessions.User=@UID AND Visits.Duration IS NOT NULL AND
            Shows.Archive=0
    ) t
    WHERE
          # есть непроданные билеты:
          EXISTS(
            SELECT * FROM Seances INNER JOIN NewSeats ON NewSeats.SeanceID=Seances.ID
            WHERE Seances.ShowID=MyShow AND Seances.Active=1 AND
            NewSeats.Owner=0 AND NewSeats.Status=2 AND
            # но данный юзер их не покупал:
            NOT EXISTS(SELECT * FROM PreOrders INNER JOIN Orders ON Orders.ID=PreOrders.OrderID WHERE VisitorID=@UID AND PreOrders.SeanceID=Seances.ID)
          )
    ORDER BY Duration DESC
    LIMIT 5

Suggested fix:
Parse comments in a right way

On MySQL 5.5.41 I get the error

-- Error Code: 1146
-- Table 'test.visitsdetails' doesn't exist

.. what is expected, of course. But it also means that the parser has approved the statement. 

I am using SQLyog as the client. Obviously the client will need to use either a cyrillic or a unicode charset (SQLyog uses UT8 as client chracter set).

I think this may be a client issue whre teh client corrupts the cyrillic string somehow. But I cannot exclude either that 5.5.40 has a bug that was fixed in 5.5.41.  

 

-- Peter
-- not a MySQL/Oracle person

.. and I (both server and client) am on Windows

phpMyAdmin 4.2.10.1

Thank you for the report.

I cannot repeat any strange errors and get same result as Peter: "ERROR 1146 (42S02): Table 'test.VisitsDetails' doesn't exist" If you use PhpMyAdmin this can be its issue. Please try with command line MySQL client and inform us if problem still exits.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".