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:
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

[27 Aug 2010 20:47] Matthew Hall
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.
[27 Aug 2010 20:47] Matthew Hall
The error presented when attempting to execute the example query

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

[28 Aug 2010 11:22] Valeriy Kravchuk
Please, send problematic query as a text, to copy-paste.
[31 Aug 2010 6:07] Valeriy Kravchuk
I see no problem parsing this query on Mac OS 10.5.x.
[31 Aug 2010 14:21] Matthew Hall
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.
[6 Sep 2010 23:04] MySQL Verification Team
Could you please try version 5.2.27. Thanks in advance.
[7 Sep 2010 15:05] Matthew Hall
The bug is still reproducible for me in 5.2.27.  I'll record a short video reproducing the problem and attach it.
[7 Sep 2010 15:10] Matthew Hall
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.
[28 Oct 2010 23:46] Alfredo Kojima
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?
[1 Nov 2010 19:11] Matthew Hall
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.
[1 Nov 2010 21:37] Alfredo Kojima
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.
[3 Nov 2010 19:19] Alfredo Kojima
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.
[9 Mar 2011 2:38] jonathan greenleaf
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.
[7 Feb 2012 22:23] Philip Olson
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.