MySQL Bugs: #105634: Results are Read Only even if condition mentioned for editable results is met

Bug #105634	Results are Read Only even if condition mentioned for editable results is met
Submitted:	19 Nov 2021 8:12	Modified:	3 Feb 2022 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

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

Please provide the complete test case: create table statement, insert data and query, Thanks in advance.

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".

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 != '';

Screenshot of result in MySQL Workbench

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

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

You didn't test that, did you? ;)

Suggestion does NOT work

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

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

Please find the screenshot below

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

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)