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
• Status: Closed
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 5.6.31
• OS: Any
• 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.