Bug #105634 Results are Read Only even if condition mentioned for editable results is met
Submitted: 19 Nov 2021 8:12 Modified: 3 Feb 15:42
Reporter: Sven Brunk Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Workbench Severity:S3 (Non-critical)
Version:8.0.24 OS:Windows
Assigned to: CPU Architecture:Any

[19 Nov 2021 8:12] Sven Brunk
Description:
I issued a query to the database like this:
SELECT a.*
FROM table1 a
INNER JOIN table1 b ON a.COLUMN2 = b.COLUMN2 AND a.COLUMN3 = b.COLUMN3
WHERE a.PRIMARYKEY <> b.PRIMARYKEY AND a.COLUMN2 != '';

As a result I got the correct data, but I can't remove the duplicates (before adding a unique key) directly from the workbench for the reason:
"Statement must be a SELECT for columns of a single table with a primary key for its results to be editable"

Besides this being a join to the same table, I only request the fields for one single table and it contains the primary key. So it should be possible to delete rows from table1 aka a in this case.

How to repeat:
1. Query with a select as above
2. observe the results being Read Only (Info message on the bottom of the result grid
[19 Nov 2021 11:45] MySQL Verification Team
Please provide the complete test case: create table statement, insert data and query, Thanks in advance.
[20 Dec 2021 1:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[20 Dec 2021 7:05] Sven Brunk
DROP TABLE IF EXISTS `myTable`;

CREATE TABLE `myTable` (
  `id` mediumint(8) unsigned NOT NULL auto_increment,
  `guid` varchar(36) NOT NULL,
  `alphanumeric` varchar(255),
  `text` TEXT default NULL,
  PRIMARY KEY (`id`)
) AUTO_INCREMENT=1;

INSERT INTO `myTable` (`guid`,`alphanumeric`,`text`)
VALUES
  ("CB0B6533-B062-E58E-8EA1-E5D4A116246D","ZNE35YAI6IC","diam luctus lobortis. Class aptent taciti sociosqu"),
  ("F876B557-9B5D-3093-B1D8-E75F27FE2B49","YZQ85LYS6XS","sociis natoque penatibus et"),
  ("9C8DBAC5-85CC-B70F-8A68-E57757179428","TFE74CRR6IL","Aliquam fringilla cursus"),
  ("5D238786-57A9-EBD3-498D-BB5A07798ABE","OFF78FPJ3GO","imperdiet ornare. In faucibus. Morbi vehicula. Pellentesque tincidunt tempus"),
  ("6451683D-64AD-36D9-2D7E-ADD5EA43B667","KEN03YXZ7BB","interdum libero dui nec"),
  ("A091D1A1-923D-C3BA-2A11-C94124B7368F","RGV20FZB4PV","elit, a feugiat tellus"),
  ("A89E9E5E-31A9-8FB9-BC3B-BAC8864DCB76","EOM84FLB1JN","lorem, eget mollis lectus pede et risus. Quisque"),
  ("7C65ADDC-4E89-D773-7D1B-189584642CE8","YIL48BQI4AW","ut odio vel"),
  ("29CEB6C6-874B-B9BF-EC9C-8043B611D7E9","IQU35HBH5UL","tellus lorem eu metus. In lorem. Donec"),
  ("8E5DC664-A626-AAE5-A753-506B252C8825","DBJ24URJ7LZ","risus. Duis a mi fringilla mi lacinia mattis.");
INSERT INTO `myTable` (`guid`,`alphanumeric`,`text`)
VALUES
  ("55E25841-3371-DC46-182A-13E7DC259532","PDA14RJK6SD","Mauris nulla. Integer urna. Vivamus molestie dapibus ligula. Aliquam erat"),
  ("C9190DE4-51C7-8B9B-57D6-DDA66D54D137","RXP52NGU9WT","eu"),
  ("3B938802-F78C-DDAB-4C51-C07C3C369615","DCK70XJA4IC","odio tristique"),
  ("30467A27-7D29-1C88-BB6A-4D84D1B290CC","FEO65BRN1UX","posuere, enim nisl elementum"),
  ("161449A6-4744-BC76-B78C-6E108A24BA53","IGN54PQB6XT","Cum sociis natoque"),
  ("981BDC56-6A99-5B8C-7293-0EB12B3A2E6F","VWO46JLU1TR","erat vel pede blandit"),
  ("C94B8BB2-3F58-E8CD-7B4D-5765DFA25875","KGG31TNU2MD","Aliquam nec enim. Nunc ut erat. Sed nunc est,"),
  ("C0E96C9A-C6EE-74DC-871F-8D2822CCE6C5","KDX41FIS7IT","vitae risus. Duis a mi fringilla mi lacinia"),
  ("913C73A7-E2E3-CFDE-0DE4-5CC4C990B8C7","JGB65WOK2JB","Aliquam ornare, libero at auctor ullamcorper, nisl arcu"),
  ("A77E7EE3-761B-45C4-FD19-183BA14C6B2B","CLC71YRL2ET","vulputate dui, nec tempus mauris");
INSERT INTO `myTable` (`guid`,`alphanumeric`,`text`)
VALUES
  ("56B8AD90-7CEC-BA2E-5BA6-226F0D213476","EEX10NQD6UM","ac sem ut"),
  ("157D56BD-DC96-ECB7-9B2C-A79701166AF5","DXB57QUK5BM","Maecenas iaculis aliquet diam."),
  ("C928C89F-736B-65BE-921D-C89C056C3E71","MIV80EXD6GT","ac, feugiat non, lobortis quis, pede. Suspendisse dui."),
  ("71AB2240-655A-052B-A9BC-52ED8580348B","QQO30ABF2DL","quam vel sapien imperdiet ornare. In"),
  ("90A5FE40-B807-06CC-0B3A-1D2D969816B1","BGZ44ZVG6VO","id magna et ipsum cursus vestibulum. Mauris magna."),
  ("35BC0E96-799B-129A-D8A1-393EBDB1A133","WQC56UQK6PR","at,"),
  ("CD3AB289-0321-4B1E-B289-24A7B382AE61","PPV26SND0VO","odio. Etiam ligula tortor, dictum eu,"),
  ("6B14D946-65CC-6AB5-C461-56151F614582","VUU81LFO9BR","vel, venenatis vel,"),
  ("DA14E9B9-1BDF-87CC-F00E-4C91C51209BC","GHU21CTJ3YG","Mauris magna. Duis dignissim"),
  ("A92483C2-E9C8-D699-4BC7-844933D46239","TJX18CIM3DY","ut,");
INSERT INTO `myTable` (`guid`,`alphanumeric`,`text`)
VALUES
  ("BCCC7D76-DBEB-6CD5-97ED-36A65D079393","YNC62GQF4IH","per inceptos hymenaeos. Mauris ut quam vel sapien"),
  ("72A50161-565D-8A9B-24DC-7B2E6F3735E2","STC26TRC5IQ","tempus risus. Donec egestas. Duis"),
  ("697A4D72-E537-D7D1-BBC9-135D5843DB83","MKX33FFP3DW","Sed dictum. Proin eget odio."),
  ("EDD26531-CDB8-96AB-73D1-B19A781625A6","WGD88LFP0GU","enim"),
  ("CE72C397-A982-0395-DA8E-20A2EEAD81DA","SCF53NFS0TV","a, enim. Suspendisse aliquet, sem"),
  ("B3B71732-0367-5256-8A53-0737EF87C219","XPG33QGA1VQ","vitae dolor. Donec fringilla. Donec feugiat metus sit amet ante."),
  ("E3FBD655-6A98-48AB-09A2-C8B345BE3122","BDX33INE6QM","faucibus ut, nulla. Cras eu tellus"),
  ("A07DA97D-5886-FE67-E7D4-25BD375A8B65","DBB55VHE8YE","arcu imperdiet ullamcorper."),
  ("77AEE2DC-5995-29B8-6C2C-982B7634CAE9","ALI33RUH1JC","ligula eu enim. Etiam imperdiet dictum magna. Ut"),
  ("5CA77569-84D8-C25F-9AAD-CE3D34437FD6","MIV58BGD1FM","Aenean gravida nunc sed pede. Cum sociis natoque");
INSERT INTO `myTable` (`guid`,`alphanumeric`,`text`)
VALUES
  ("A3B2C1F5-90A6-577A-6E45-8E3DC59A1491","RRO71GDE8DE","mauris ipsum porta elit, a feugiat tellus lorem eu metus."),
  ("75948611-4831-F3D2-DB1D-BFD578740D5B","YUO73DQY7YG","erat neque non quam. Pellentesque habitant"),
  ("18A884B5-4471-625A-49EC-11366382D9FD","ECD46LNP4DR","sodales nisi magna sed dui."),
  ("A1A76C07-5852-55F6-3F64-B2924142CB13","GFZ62PGB2HY","luctus ut, pellentesque eget, dictum placerat, augue."),
  ("D2C9835E-91D2-D3C2-0905-780B9E863F6D","PMH24QTV7VO","Quisque libero lacus, varius et, euismod et,"),
  ("2C5EBDB5-CE32-AD18-2DBB-FE05762B9FE8","VME21GQF5AI","ridiculus mus. Proin vel nisl. Quisque fringilla euismod enim."),
  ("33ACE45C-6811-29CA-9E48-1B725AB31C00","UEE63DXS7PX","at lacus."),
  ("1421AAC5-FBD7-AB2B-3C1E-4E39A32973FA","EJB85GAU2QP","nascetur ridiculus mus. Proin vel nisl. Quisque fringilla"),
  ("735D83B8-2E62-5324-FDA6-C48104592602","MKJ47FMH1HR","tellus lorem eu metus. In lorem. Donec elementum, lorem"),
  ("E5E78B3E-6B45-5EB7-58FA-8054D27A5F7E","DXM65BXD3OH","rhoncus. Nullam velit dui,");

# Insert duplicates
INSERT INTO myTable(`guid`,`alphanumeric`,`text`) VALUES
('BCCC7D76-DBEB-6CD5-97ED-36A65D079393',	'YNC62GQF4IH',	'per inceptos hymenaeos. Mauris ut quam vel sapien'),
('72A50161-565D-8A9B-24DC-7B2E6F3735E2',	'STC26TRC5IQ',	'tempus risus. Donec egestas. Duis');

# inner join 
SELECT a.*
FROM myTable a
INNER JOIN myTable b ON a.guid = b.guid AND a.alphanumeric = b.alphanumeric WHERE a.id <> b.id AND a.alphanumeric != '';
[20 Dec 2021 7:06] Sven Brunk
Screenshot of result in MySQL Workbench

Attachment: 2021-12-20_MySQL_Workbench.png (image/png, text), 34.75 KiB.

[3 Feb 12:07] MySQL Verification Team
Hello Sven Brunk,

Thank you for the details.
Imho this is not a bug. If you want to delete duplicate records from the same table, you can write delete statement instead of select in the above provided query.
Please see the below query. This will delete duplicate records from the same table.

DELETE a.*
FROM myTable a
INNER JOIN myTable b ON a.guid = b.guid AND a.alphanumeric = b.alphanumeric 
WHERE a.id <> b.id AND a.alphanumeric != '';

Regards,
Ashwini Patil
[3 Feb 12:16] Sven Brunk
You didn't test that, did you? ;)
[3 Feb 12:17] Sven Brunk
Suggestion does NOT work

Attachment: Screenshot 2022-02-03 131601.png (image/png, text), 32.75 KiB.

[3 Feb 12:21] MySQL Verification Team
Hello Sven Brunk,

Please note that you need to disable the safe updates while deleting the duplicate records. 

i.e SET SQL_SAFE_UPDATES = 0;

Be sure to set this back with SET SQL_SAFE_UPDATES = 1 when you're done.

Regards,
Ashwini Patil
[3 Feb 12:26] MySQL Verification Team
Please find the screenshot below

Attachment: 105634_test_results.PNG (image/png, text), 138.13 KiB.

[3 Feb 15:42] Sven Brunk
I think you are misunderstanding the ticket. The point here is that this change should be possible WITH safe updates as you have the primary key field of the table available. (Not with the delete statement, but when you use the SELECT)