Bug #38600 Insert statement calling function is incorrectly parsed
Submitted: 6 Aug 2008 11:34 Modified: 5 Jan 2010 14:57
Reporter: James Gordon Email Updates:
Status: Closed Impact on me:
Category:MySQL Workbench Severity:S3 (Non-critical)
Version:5.0.23\5.0.24 OS:Windows (XP)
Assigned to: Sergei Tkachenko CPU Architecture:Any

[6 Aug 2008 11:34] James Gordon
Imported DB4 schema had a table

-- -----------------------------------------------------
-- Table `SystemUsers`
-- -----------------------------------------------------
  `Username` VARCHAR(20) NOT NULL ,
  `UserPassword` CHAR(32) BINARY NOT NULL ,
  `ReadOnly` BOOLEAN NULL ,
  PRIMARY KEY (`idSystemUsers`) ,

The model had an insert for the table, using a function call...

insert into systemusers(idsystemusers, username, password) values (1, 'System', MD5('system'));

When edited the editor converts to...

INSERT INTO `SystemUsers` (`idSystemUsers`, `Username`, `UserPassword`, `ReadOnly`) VALUES (1, 'System', 'MD5(\'system\')', );

How to repeat:
See above for details.
[6 Aug 2008 12:28] MySQL Verification Team
Thank you for the bug report. Could you please provide the DB4 sample schema?. Thanks in advance.
[6 Aug 2008 12:42] Sergei Tkachenko
It's about WB editor of table data (accessible through table editor) rather than about DBD4.
[1 Sep 2008 16:42] MySQL Verification Team
Thank you for the feedback.
[16 Jul 2009 17:43] Alfredo Kojima
Not sure if this is still relevant in 5.2
[21 Jul 2009 8:44] James Gordon
Just installed and run 5.1.16 and I still get the following incorrect sql generated.

INSERT INTO `TestTable` (`idTestTable`, `Password`) VALUES (1, 'md5(\'fred\')');

Should read.

INSERT INTO `TestTable` (`idTestTable`, `Password`) VALUES (1, md5('fred'));
[2 Nov 2009 21:04] Javier Ortiz
I would like to vote for this one. I wrote one not long ago that's fixed in latest version 5.1.18 using the now() function and is solved.
[4 Dec 2009 17:19] Sergei Tkachenko
Added escape sequence for marking field value as a function call.
To make WB treat field value as function call user must prepend actual expression with "\func " prefix. In this case when generating SQL script WB will use the part that goes after "\func " and won't do quotation. User is allowed to use any valid forms of expressions, not only single function calls. User takes responsibility to escape symbols in string literals involved in those expressions on his own.
[7 Dec 2009 14:29] Johannes Taxacher
fix/feature confirmed.
will be included in 5.2.11.

note for doc: please add explanation for that new behavior to table-editor-documentation
[5 Jan 2010 14:57] Tony Bedford
Documentation for Inserts tab and SQL Editor has been updated.

A 'change' entry has also been added to the 5.2.11 changelog:

It was impossible to use a function call as a field value in an editable result set, such as for a result set in the Query Editor or in the Inserts tab in the Table Editor. This was because MySQL Workbench automatically escaped string parameters passed to the function call. For example, if an attempt was made to enter into a field a function such as md5('fred'), MySQL Workbench would generate the SQL code md5(\'fred\').

MySQL Workbench has now been changed so that it is possible to enter a function, or any other expression, into a field using the \func prefix. For example, \func md5('fred') can be entered. MySQL Workbench will now ensure that the string 'fred' is not escaped.