MySQL Bugs: #78156: Generated Update Statements Must Use Null-safe Operators In Where Clause

Bug #78156	Generated Update Statements Must Use Null-safe Operators In Where Clause
Submitted:	20 Aug 2015 18:15	Modified:	20 Aug 2015 18:54
Reporter:	jjj nnn	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Workbench	Severity:	S3 (Non-critical)
Version:	6.3.4.0	OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
Create schema and table with statements listed in the bug.

Select the table with the one row. In the result grid (in Workbench) change value of the first row to 'test2'. It will generate the following statement:

UPDATE `bugtest`.`new_table` SET `value`='test2' WHERE `id`='1' and`additional_key`=NULL;

=> 0 row(s) affected

This statement will not update anything since additional_key is compared to NULL with a non null-safe operator '='.

How to repeat:
CREATE SCHEMA bugtest;
CREATE TABLE `bugtest`.`new_table` (
  `id` INT NOT NULL COMMENT '',
  `value` VARCHAR(45) NULL COMMENT '',
  `additional_key` VARCHAR(45) NULL COMMENT '',
  INDEX `main_key` (`id` ASC, `additional_key` ASC)  COMMENT ''
);
INSERT INTO bugtest.new_table VALUES(1, "test", NULL);

Suggested fix:
The generated query where clause must be either: WHERE ... '<=> NULL' or ' IS NULL'.

update query

Attachment: update_null_where.png (image/png, text), 224.60 KiB.

Thank you for the bug report.