Bug #47122 The NULL value in INSERT statements is quoted
Submitted: 4 Sep 2009 7:03 Modified: 16 Sep 2009 10:42
Reporter: Leif Inge Sandberg Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Workbench Severity:S3 (Non-critical)
Version:5.2.2a OS:Windows
Assigned to: Sergei Tkachenko CPU Architecture:Any

[4 Sep 2009 7:03] Leif Inge Sandberg 
Description:
When adding inserts with a NULL value in a column, Workbench adds apostrophes to the NULL value. Which gives error when running the export script in MySQL Query Browser.

How to repeat:
CREATE  TABLE IF NOT EXISTS `PayType` (
  `ID` SMALLINT UNSIGNED NOT NULL ,
  `Name` VARCHAR(30) NOT NULL ,
  `Code` CHAR(1) NULL ,
  PRIMARY KEY (`ID`) )
ENGINE = InnoDB;

Add a line on the Inserts tab, apply the changes and export a Create script. This is what Workbench saves:

insert into PayType (`ID`, `Name`, `Code`) values (100, 'Cash', 'NULL');

Suggested fix:
Do not add any quotation marks around the NULL value in INSERT statements. It should be like this:

insert into PayType (`ID`, `Name`, `Code`) values (100, 'Cash', NULL);
[4 Sep 2009 8:06] Valeriy Kravchuk 
Thank you for the bug report.
[4 Sep 2009 11:52] Sergei Tkachenko 
To distinguish NULL value from `NULL` literal it has to be set using popup menu - right click on the cell(s) you want to nullify, then select 'Set selection to NULL' menu item. NULL values set this way will export as expected.
[4 Sep 2009 12:00] Leif Inge Sandberg 
When doing that, I get an empty string in the insert statement. I'd say that's even worse than 'null' :-)
[4 Sep 2009 12:18] Sergei Tkachenko 
Can't repeat that with snapshot version. Is it possible to look at wbm file (or its reduced version which is enough to reproduce) that causes mentioned bug?
[4 Sep 2009 12:46] Leif Inge Sandberg 
Mwb file with an insert where the value is set as NULL via right-click menu

Attachment: TestNull.mwb (, text), 4.61 KiB.
[4 Sep 2009 14:25] Sergei Tkachenko 
Failed to reproduce in snapshot version - works as expected with provided model. Please confirm for upcoming release of 5.2, should the bug be still there.
[11 Sep 2009 20:30] Johannes Taxacher 
confirmed working for 5.2.3
[16 Sep 2009 10:42] Tony Bedford 
An entry was added to the 5.2.3 changelog:

When adding inserts with a NULL value in a column, MySQL Workbench incorrectly added single quotes to the NULL value.

Note

Note, when entering a NULL value right-click the button next to the value and select Set selection to NULL, otherwise NULL will be interpreted as a string literal.