MySQL Bugs: #38600: Insert statement calling function is incorrectly parsed

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:	None
Category:	MySQL Workbench	Severity:	S3 (Non-critical)
Version:	5.0.23\5.0.24	OS:	Windows (XP)
Assigned to:	Sergei Tkachenko	CPU Architecture:	Any
Tags:	CHECKED

Description:
Imported DB4 schema had a table

-- -----------------------------------------------------
-- Table `SystemUsers`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `SystemUsers` (
  `idSystemUsers` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `Username` VARCHAR(20) NOT NULL ,
  `UserPassword` CHAR(32) BINARY NOT NULL ,
  `ReadOnly` BOOLEAN NULL ,
  PRIMARY KEY (`idSystemUsers`) ,
  UNIQUE INDEX SU_USERNAME_UK (`Username` ASC) )
AUTO_INCREMENT = 1000
PACK_KEYS = 0
ROW_FORMAT = DEFAULT;

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.

Thank you for the bug report. Could you please provide the DB4 sample schema?. Thanks in advance.

It's about WB editor of table data (accessible through table editor) rather than about DBD4.

Thank you for the feedback.

Not sure if this is still relevant in 5.2

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'));

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.

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.

fix/feature confirmed.
will be included in 5.2.11.

note for doc: please add explanation for that new behavior to table-editor-documentation

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.