MySQL Bugs: #85689: Applying changes made to a table row in Form Editor may update a different row

Bug #85689	Applying changes made to a table row in Form Editor may update a different row
Submitted:	29 Mar 2017 17:01	Modified:	3 Apr 2017 13:11
Reporter:	Paolo Petranzan	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Workbench: SQL Editor	Severity:	S3 (Non-critical)
Version:	>=3.6.8, 3.6.9	OS:	Windows (6.1 (Build 7601, Service Pack 1, x64))
Assigned to:		CPU Architecture:	Any
Tags:	Form Editor, workbench

Description:
Apparently, changes made to a table row in the Form Editor are applied using values for the primary key coming from a row which may not be the same, depending on the position of the row in the default order being different from the position of the row when it was loaded in the Form Editor.

How to repeat:
Select a table and click on "Select Rows" in the context menu; the Result Grid should open. Now change the order of the rows clicking on a column header, then pick a row whose position in the grid (this is important) has been affected by the change of order.
Make some changes to the row, then Apply: the row will be correctly updated. Apparently, you're still editing the same row, but you're not: make some more changes, then Apply. Looking at the WHERE clause in the preview, you will see that the primary key of the row you're about to update is not that of the row you were editing; in fact, you're about to update the row that, in the default order, was in the same position as the edited row after the order change.

Thank you for the bug report. I tried to repeat this issue with the following table and data:

create table bugorder (id int not null auto_increment primary key, letter char(1));
insert into bugorder (letter) values ('a'),('b'),('f'),('g'),('h'),('c'),('d'),('e');

Notice above that the column letter is out of order regarding the primary key column.
Then with WorkBench 6.3.9 I did the 'Select Rows' action and then clicked the letter column header to change the prior order of the grid (now the letter is in alphabetic order and the integer number of primary key out of order). Then picked and edited the row with letter c which was affected by the column header click, changed the letter c by K and then applied the update. Clicking the finish button of the apply script refresh on my side the result grid, ordering now with the primary key, that breaks the sequence of your test case description because now I need to select a row to edit. Please provide step by step with create table and insert data and screenshots of the steps to try on my side. Thanks in advance.

Thank you for the feedback. Would be nice if you can attach screenshots to illustrate the issue. Thanks.

shot1

Attachment: shot1.png (image/png, text), 23.44 KiB.

shot2

Attachment: shot2.png (image/png, text), 23.43 KiB.

shot3

Attachment: shot3.png (image/png, text), 58.15 KiB.

shot4

Attachment: shot4.png (image/png, text), 54.73 KiB.

The starting point is "shot1", where the rows are ordered by the primary key "id". Please notice that the third row has id = 3.
Now let's get to "shot2", by ordering the rows by "letter" and selecting the third row, which now has id = 6.
Now we edit the selected row in Form Editor, change "letter", say from "c" to "z", and click on "Apply": we get to "shot3", where we see that the row about to be updated is the correct one (id = 6).
Let's Apply the changes and remain in the Form Editor. This is where the trouble begins. Let's change "letter" back to "c" and click on "Apply": this is "shot4", where we see that the row about to be updated is the wrong one, namely the row with id = 3.
To summarize, changing the order of the rows before entering the Form Editor to edit the third row leads to apply the changes to the row that was the third before the order change.

Thank you for the requested details.
Observed this on Win7 with WB 6.3.9.

Thanks,
Umesh

Screenshot..

Attachment: 85689.png (image/png, text), 53.59 KiB.