MySQL Bugs: #56334: Workbench doesn't always recognize queries correctly

Bug #56334	Workbench doesn't always recognize queries correctly
Submitted:	27 Aug 2010 20:47	Modified:	7 Feb 2012 22:23
Reporter:	Matthew Hall	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench: SQL Editor	Severity:	S3 (Non-critical)
Version:	5.2.26, 5.2.27	OS:	MacOS (10.6.4)
Assigned to:		CPU Architecture:	Any

Description:
MySQL Workbench's SQL editor sometimes seems to have problems when queries are pasted in, rather than typed out.  Attached is the screenshot of an example of this problem.  The built-in query processor seems to get confused and miscalculate which line a query begins on.  Attempting to execute a query that has been mis-processed results in the error message captured in the other screenshot.  The only workaround I've been able to come up with is to remove all of the line breaks within a query, so that the entire thing exists on a single line.

How to repeat:
Unfortunately, I have not been able to come up with a way to reliably reproduce this issue.

The error presented when attempting to execute the example query

Attachment: error.png (image/png, text), 25.85 KiB.

Please, send problematic query as a text, to copy-paste.

I see no problem parsing this query on Mac OS 10.5.x.

You're right, when I paste that query into Workbench, it doesn't have a problem parsing it either.  I'll try to provide a bit more background here.  A coworker of mine sent me a similar query looking for advice on how to make it accomplish what he wanted.  I pasted that query (from an email) into Workbench and edited it to look like the query above.  It was during this editing that parsing seemed to go haywire, and wouldn't correctly process the query even though it was semantically correct.  I'll include that original query in a private comment so that you can edit it to look like the one above and see if parsing goes wonky.

Could you please try version 5.2.27. Thanks in advance.

The bug is still reproducible for me in 5.2.27.  I'll record a short video reproducing the problem and attach it.

Oddly enough, it looks like I can only reproduce the bug when I copy and paste the original query I was sent from Microsoft Entourage.  I can't seem to reproduce it when I copy and paste the original query in the 31 Aug 16:22 comment.  If someone there uses Entourage, I could forward the original email and provide an extremely simple way to reproduce the problem.

Can you save the query once you paste it from Entourage and see if there's anything strange with it? If loading that file can repeat the problem, can you attach the file?

After editing the original script to a point that it has a parsing problem, I saved it to an SQL file and edited it using vi.  I can see numerous CTRL-M (^M) characters in the file that might be related to the problem.  Every new line I inserted into the file is displayed correctly in VI, but the original new lines that were part of the email appear as ^M.  Additionally, when I open the file using MySQL Workbench, I am prompted with the "Inconsistent Line Endings" window.  No matter which line ending format I select, MySQL workbench seems to have no problem parsing after opening the file.

That could be the cause of the problem, can you attach the file saved that showed the ^M characters in vi?
WB performs line ending checks and conversion when loading a file, but not when copy/pasting, which should explain the results you got; but I'd like to confirm that is the case with the file you got.

I can repeat opening the uploaded file with TextEdit and then copy/pasting the code from there and inserting a new line before the from.

The file contains only \r linebreaks instead of \r\n or \n, which is the reason the editor gets confused. Fix should be checking newlines and normalizing them on paste.

Workaround is to save and reload the code from a file.

This issue occurs in 5.2.29 and not just when copying and pasting from external sources.  One only has to watch the blue dot see when it is confused.  On Max OS X.  Think I need to go back to Query Analyzer / Administrator.

Fixed as of 5.2.38:

Pasting a query with "\r" line endings instead of "\r\n" or "\n" 
could cause Workbench to mangle the query. Line endings are now 
normalized after pasting, like they already were while loading files.