MySQL Bugs: #99026: Select Statement is editable, but View based on this statement is read-only

Bug #99026	Select Statement is editable, but View based on this statement is read-only
Submitted:	23 Mar 2020 0:18	Modified:	23 Mar 2020 7:58
Reporter:	Jonathan Ornstein	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Workbench: SQL Editor	Severity:	S2 (Serious)
Version:	8.0.19, 8.0.20	OS:	Ubuntu (18.04)
Assigned to:		CPU Architecture:	x86

Description:
When I create select statement a View based on a single table which has a primary key, executing this select statement yields a perfectly editable recordset in MySQL workbench. However, when I create a View based on this exact same Select statement, the dataset is NOT editable anymore upon execution of the View. 

Strangely, when I create a query from an external application such as Ms Access on this table using ODBC, it IS possible to create an editable version of the query.

  

How to repeat:

  1) Create a database "testdb"
2) create a table with 3 columns: `ID` (=PK) , `FldDescription`  , `ForeignID` 
CREATE TABLE `testdb`.`new_table` (
  `ID` INT NOT NULL AUTO_INCREMENT,
  `FldDescription` VARCHAR(45) NULL,
  `ForeignID` INT NOT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE INDEX `ID_UNIQUE` (`ID` ASC) VISIBLE);
3) create and execute a select statement on the new table and add some data
a) SELECT * FROM testdb.new_table;
b) insert some dummy data:
INSERT INTO `testdb`.`new_table` (`FldDescription`, `ForeignID`) VALUES ('hello world', '12');
INSERT INTO `testdb`.`new_table` (`FldDescription`, `ForeignID`) VALUES ('hello again', '25');

4) now create a view based on th is table:
USE `testdb`;
CREATE  OR REPLACE VIEW `new_view` AS
SELECT * FROM testdb.new_table;

5) Save and open this view:
SELECT * FROM testdb.new_view;

6) the data is now read-only. I have tried this MANY times.

Suggested fix:
no idea. workaround using external dbms Access is a security nightmare.

Hello Jonathan,

Thank you for the report.

regards,
Umesh

just to specify: identical installations of MySQL Server 8.0.19 on 3 VPS servers running Ubuntu 18.04 Bionic all show the same problem. I run Workbench on a Windows 10 Pro machine. regards!

Bug #99549 marked as duplicate of this one