[MDEV-29587] Allowing insert into a view with columns that are not part the table Created: 2022-09-21  Updated: 2024-01-23  Resolved: 2024-01-23

Status: Closed
Project: MariaDB Server
Component/s: Views
Affects Version/s: 10.11
Fix Version/s: 10.11.7, 11.0.5, 11.1.4, 11.2.3

Type: Bug Priority: Critical
Reporter: Michael Widenius Assignee: Oleksandr Byelkin
Resolution: Fixed Votes: 0
Labels: None


 Description   

Creating a view with some extra virtual/computed fields are not insertable
The intention should be that 'extra computed fields' in a view should work identical
to how virtual fields works for a table.

  • If one inserts into the table without specifying the virtual fields, it should work without any
    warnings.
  • If one tries to insert into a virtual field, one should get an error/warning that the field is ignored.

    CREATE TABLE table1 (x INT);
    CREATE VIEW view1 AS SELECT x, x as x1 FROM table1;
    INSERT INTO view1(x) VALUES (1);
    ERROR 1471 (HY000): The target table view1 of the INSERT is not insertable-into
     
    An example with virtual columns
    MariaDB [test]> create table t1 (a int, b int generated always as (a+1));
    MariaDB [test]> insert into t1 (a) values(1);
    MariaDB [test]> insert into t1 values(1,2);
    ERROR 1906 (HY000): The value specified for generated column 'b' in table 't1' has been ignored
    MariaDB [test]> insert ignore into t1 values(1,2);
    Query OK, 1 row affected, 1 warning (0.018 sec)
    



 Comments   
Comment by Oleksandr Byelkin [ 2022-09-22 ]

CREATE TABLE table1 (x INT);
CREATE VIEW view1 AS SELECT x, x as x1 FROM table1;
INSERT INTO view1(x) VALUES (1);
DROP VIEW view1;
DROP TABLE table1;

Comment by Oleksandr Byelkin [ 2022-09-22 ]

It is check that fields are unique prevent inserting

Comment by Oleksandr Byelkin [ 2022-09-22 ]

Probably we should check only fields we are inserting to...

Comment by Oleksandr Byelkin [ 2022-09-22 ]

https://mariadb.com/kb/en/inserting-and-updating-with-views/#inserting-with-views

the view above looks like do not contradict the conditions

Comment by Sergei Golubchik [ 2022-09-22 ]

Note that view1 is not insertable-into in the SQL standard (2016). It says that

30) A <query specification> QS is insertable-into if QS is effectively updatable...

28) A <query specification> QS is effectively updatable if either QS is simply updatable or ... QS is generally updatable.

etc. and eventually

24) A <query specification> is potentially updatable if and only if all of the following conditions hold:
a) DISTINCT is not specified.
b) Of those <derived column>s in the <select list> that are column references that have a counterpart in a base table, no column of a table is referenced more than once in the <select list>.

So MariaDB current behavior is what standard specifies and the requested feature is an extension of the standard.


UPD: on a second thought, it seems that the above quote in bold should only cover cases like

create view v1 as select a, a from t1

making only these views not potentially updatable.

Comment by Oleksandr Byelkin [ 2022-09-22 ]

So we decided to make it exactly as generated field (if there is the "same" fields in the view, as it is in the test case) second (by order) counted as generated).

Comment by Michael Widenius [ 2024-01-23 ]

Review done, one comment removal requested. Ok to push

Generated at Thu Feb 08 10:09:45 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.