Bug #68941 *indented* UPDATE SET on 2 columns fails
Submitted: 12 Apr 2013 10:00 Modified: 12 Apr 2013 14:21
Reporter: Guido Serra Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Workbench: SQL Editor Severity:S3 (Non-critical)
Version:5.2.44 OS:MacOS (10.7.5)
Assigned to: CPU Architecture:Any

[12 Apr 2013 10:00] Guido Serra
Description:
UPDATE catalog_category
    SET
        lft = -lft, 
         rgt = -rgt;

this query, indented this way, does not work
remove the line breaks... and works like a charme

error reported:
Error Code: 1193. Unknown system variable 'lft'

funny thing... is that 
the indented version perfectly matches how the EBNF indents
http://dev.mysql.com/doc/refman/5.6/en/update.html

 -- here is my table (with indexes on lft,rgt)

'id_catalog_category', 'int(10) unsigned', 'NO', 'PRI', NULL, 'auto_increment'
'status', 'enum(\'active\',\'inactive\',\'inherited_inactive\',\'deleted\')', 'NO', '', NULL, ''
'lft', 'int(11)', 'NO', 'UNI', NULL, ''
'rgt', 'int(11)', 'NO', 'UNI', NULL, ''
'name', 'varchar(255)', 'NO', '', NULL, ''
'name_en', 'varchar(255)', 'YES', '', NULL, ''
'name_pt', 'varchar(255)', 'YES', '', NULL, ''
'url_key', 'varchar(255)', 'NO', '', NULL, ''
'fk_vertical', 'smallint(5) unsigned', 'YES', 'MUL', NULL, ''

How to repeat:
create a table representing a nested tree, with lft and rgt columns

execute the query with the new lines and indentation as above... it fails

remove the indentation and line breaks, put everything on a single line statement, and works like a charme
[12 Apr 2013 10:10] Guido Serra
reproducible in MySQL Workbench on OsX only... via mysql cli works

against MySQL Server Debian package 5.5.30-1~dotdeb.0
[12 Apr 2013 12:58] MySQL Verification Team
Please try version 5.2.47. I couldn't repeat with below hardware:

MySQL Workbench CE for Mac OS X version 5.2.47  revision 10398
Configuration Directory: /Users/miguel/Library/Application Support/MySQL/Workbench
Data Directory: /Applications/MySQLWorkbench.app/Contents/Resources
Cairo Version: 1.9.1
OS: Darwin 12.3.0
CPU: 4x Intel(R) Core(TM) i5-3210M CPU @ 2.50GHz, 16.0 GB RAM

Thanks.
[12 Apr 2013 14:18] Guido Serra
unreproducible on the latest 5.2.47, closing!
[12 Apr 2013 14:21] Guido Serra
retried on 5.2.44 after testing on 5.2.47 ... not reproducible anymore ... 

???????