MySQL Bugs: #76964: SQL Editor always updates the first table in a UNION (ALL) query

Bug #76964	SQL Editor always updates the first table in a UNION (ALL) query
Submitted:	7 May 2015 6:58	Modified:	20 Jan 2017 18:05
Reporter:	Michael Chane	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench: SQL Editor	Severity:	S3 (Non-critical)
Version:	6.2.3, 6.3.3	OS:	Windows (Microsoft Windows 7 Home Basic)
Assigned to:		CPU Architecture:	Any
Tags:	sql editor, UNION, WBBugReporter

Description:
If your query is a UNION/UNION ALL statement which contains data from different table or different database, the Workbench SQL Editor will always update the wrong table, using the right primary key.

How to repeat:
First, creates two different tables with the same structure, and fill them with some data:

CREATE TABLE `test`.`a` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `txt` VARCHAR(10) NULL,
  PRIMARY KEY (`id`));

CREATE TABLE `test`.`b` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `txt` VARCHAR(10) NULL,
  PRIMARY KEY (`id`));

INSERT INTO `test`.`a` (txt) VALUES ('abc');
INSERT INTO `test`.`a` (txt) VALUES ('123');
INSERT INTO `test`.`a` (txt) VALUES ('abc123');
INSERT INTO `test`.`b` (txt) VALUES ('Hello!');

And then create a query editor, and execute a UNION ALL query:

SELECT * FROM `test`.`a`
UNION ALL
SELECT * FROM `test`.`b`

In the data grid, change the values in the rows from the second data table (table b in this example), 'Hello!'--> 'Bonjour!' and click the Apply button.

It says in the review dialog:
UPDATE `test`.`a` SET `txt`='Bonjour!' WHERE `id`='1';

Suggested fix:
Each row remembers where its values come from so that the SQL Editor can execute the correct statement.
Otherwise, simply ban such modification as long as the data grid is from different data table.

Hello Michael Chane,

Thank you for the report and test case.
Observed this with WB 6.3.3 on Win7.

Thanks,
Umesh

Posted by developer:
 
Fixed as of the upcoming MySQL Workbench 6.3.9 release, and here's the changelog entry:

In a query, UNION and UNION ALL statements that contained data from
different tables or different databases returned data in the Result Grid
that could be edited and applied to the wrong table. This fix returns
read-only data that cannot be edited directly for such queries.

Thank you for the bug report.