Bug #80655 Error message from UPDATE of non-updatable part of join view is inaccurate
Submitted: 8 Mar 2016 9:10 Modified: 30 Jun 2020 0:19
Reporter: Roy Lyseng Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.6.31 OS:Any
Assigned to: CPU Architecture:Any

[8 Mar 2016 9:10] Roy Lyseng
Description:
In the example below, vtr is a view over a join of an updatable part and a non-updatable part.
The first UPDATE statement updates column b1 from the updatable part of the view,
which succeeds.
The second UPDATE statement tries to update column b2 from the non-updatable part
of the view (the view vmat2, which is materialized due to DISTINCT).
This statement fails with the error message:

ERROR 1288 (HY000): The target table dt2 of the UPDATE is not updatable

This is problematic, since it mentions a component of the updatable view and not the view itself.

5.6 and 5.7 checked.

How to repeat:
CREATE TABLE t1(a1 INTEGER PRIMARY KEY, b1 INTEGER);
CREATE TABLE t2(a2 INTEGER PRIMARY KEY, b2 INTEGER);

CREATE VIEW vmat2 AS SELECT DISTINCT * FROM t2;

CREATE VIEW vtr AS
SELECT * FROM t1 JOIN vmat2 AS dt2 ON t1.a1=dt2.a2;

UPDATE vtr SET b1=b1+1 WHERE a1=1;

UPDATE vtr SET b2=b2+1 WHERE a2=1;

DROP VIEW vtr, vmat2;
DROP TABLE t1, t2;

Suggested fix:
Report a different error message, e.g

Column b2 of view vtr is not updatable.
[8 Mar 2016 9:45] MySQL Verification Team
Hello Roy,

Thank you for the report and test case.

Thanks,
Umesh
[30 Jun 2020 0:19] Jon Stephens
Documented fix as follows in the MySQL 8.0.22 changelog:

    For a view based on a join having an updatable part and one that
    was not, the error message generated when attempting to update a
    column that was not updatable referenced the source table or
    view instead of the view actually named in the offending UPDATE
    statement.

Fixed as part of work done for WL#9384.

Closed.