Bug #107072 Incorrect "Can not delete from join view" when create view based on other view
Submitted: 20 Apr 2022 16:30 Modified: 21 Apr 2022 6:42
Reporter: Александр Ммммммм Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:8.0.28 OS:Any
Assigned to: CPU Architecture:Any

[20 Apr 2022 16:30] Александр Ммммммм 
Description:
When create view based on other view (all views without JOIN's, only EXISTS) I catch error "Can not delete from join view"

How to repeat:
CREATE TABLE documents (
    id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT
    , sum INT NOT NULL DEFAULT 0
)
;
CREATE TABLE document_positions (
    id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT
    , document_id INT UNSIGNED NOT NULL
    , price INT
    , quantity INT
    , CONSTRAINT document_positions_documnent FOREIGN KEY (document_id) REFERENCES documents (id)
)
;
CREATE OR REPLACE VIEW documents_v_1
AS
SELECT *
FROM documents
WHERE EXISTS (
    SELECT 1
    FROM document_positions
    WHERE documents.id = document_positions.document_id
)
;
CREATE OR REPLACE VIEW documents_v_2
AS
SELECT *
FROM documents_v_1
WHERE EXISTS (
    SELECT 1
    FROM document_positions
    WHERE documents_v_1.id = document_positions.document_id
)
;
DELETE FROM documents_v_2
;
[HY000][1395] Can not delete from join view 'test.documents_v_2'
[20 Apr 2022 16:36] Александр Ммммммм 
Documentation says
https://dev.mysql.com/doc/refman/8.0/en/view-updatability.html
"The flag is set to YES (true) if UPDATE and DELETE (and similar operations) are legal for the view."

SELECT IS_UPDATABLE
FROM information_schema.VIEWS
WHERE TABLE_NAME = 'documents_v_2'
;
+------------+---+
|IS_UPDATABLE|YES|
+------------+---+
[21 Apr 2022 6:42] MySQL Verification Team 
Hello Александр Ммммммм,

Thank you for the report and feedback.

regards,
Umesh