Bug #105634 Results are Read Only even if condition mentioned for editable results is met
Submitted: 19 Nov 2021 8:12 Modified: 20 Dec 2021 9:26
Reporter: Sven Brunk Email Updates:
Status: Analyzing Impact on me:
None 
Category:MySQL Workbench Severity:S3 (Non-critical)
Version:8.0.24 OS:Microsoft Windows
Assigned to: MySQL Verification Team 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.