MySQL Bugs: #50781: Query generation for INSERT adds NULLs for columns untouched in editor

Bug #50781	Query generation for INSERT adds NULLs for columns untouched in editor
Submitted:	1 Feb 2010 8:08	Modified:	21 Apr 2010 13:43
Reporter:	Jeremy Bell	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench: SQL Editor	Severity:	S3 (Non-critical)
Version:	5.2.15a	OS:	Windows (Windows 7 x64)
Assigned to:	Sergei Tkachenko	CPU Architecture:	Any
Tags:	insert, nulls, query generator, regression

Description:
When adding a row using the SQL editor, e.g. I'm adding an item to a queue, I often leave many fields untouched so that the values will use their defaults.

Using the old Query Browser, the generated SQL statement does not include the columns for which I haven't touched and the statement executes successfully.

Using workbench, the generated SQL statement includes all untouched columns as NULL and the statement fails because some columns are set in the schema as NOT NULL with default values. This behaviour fails to replace the old behaviour in Query Browser.

How to repeat:
1. Use the SQL editor to bring up a table with two columns, at least one which is NOT NULL and has a default value.
2. Add a row by placing a value in a column other than the NOT NULL column.
3. Apply changes
4. The statement fails because it tries to set the NOT NULL column as NULL.

Suggested fix:
Improve the code to only generate columns for the INSERT statement which have been selected and edited by the user.

Thank you for the bug report. Verified just as described.

fix confirmed in repository

An entry has been added to the 5.2.18 changelog:

When editing a row in the SQL Editor, if a column was NOT NULL and had a default value, and a value was not entered for it, then after changing another column if changes were applied an error would be generated. This was because the editor attempted to set the NOT NULL column to NULL, rather than to its default value.