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.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.040 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.