Bug #29477 VIEW-s insertability
Submitted: 2 Jul 2007 6:19 Modified: 25 Feb 2008 17:44
Reporter: Lachezar Balev Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Views Severity:S3 (Non-critical)
Version:5.1.17 OS:Linux (FC5)
Assigned to: Evgeny Potemkin CPU Architecture:Any
Triage: D2 (Serious) / R3 (Medium) / E3 (Medium)

[2 Jul 2007 6:19] Lachezar Balev
Description:
Hello!

In the MySQL 5.1 documentation, 21.2 Create View Syntax, it is written:

With respect to insertability (being updatable with INSERT statements), an updatable view is insertable if it also satisfies these additional requirements for the view columns:

* blah
* The view must contain all columns in the base table that do not have a default value.
* blah

A view can be insertable even if it does NOT meet the second condition. A warning is generated, but the view is still insertable.

Regards!

How to repeat:
mysql> create table accounts (name varchar(255) not null, money int(20) unsigned);
Query OK, 0 rows affected (0.01 sec)

The `name` column does not have a default value. It is declared `not null`.

mysql> create view vmoney as select money from accounts;
Query OK, 0 rows affected (0.01 sec)

The view `vmoney` does NOT contain all columns in the base table that do not have a default value.

mysql> insert into vmoney (money) values (5);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from accounts;
+------+-------+
| name | money |
+------+-------+
|      |     5 |
+------+-------+
1 row in set (0.01 sec)

The view is still insertable. A warning is generated though.

Suggested fix:
Maybe just fix the documentation.
[2 Jul 2007 6:30] Alexander Keremidarski
Verified as described.

mysql> insert into vmoney (money) values (5);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> show warnings;

Warning | 1423 | Field of view 'bugs.vmoney' underlying table doesn't have a default value

mysql> select * from accounts;
+------+-------+
| name | money |
+------+-------+
|      |     5 |
+------+-------+
[28 Sep 2007 7:57] Gleb Shchepa
This behavior was introduced by the bugfix for bug #16110:
In the strict mode INSERT in such view or regular table rises an error, and INSERT is aborted.
In the non-strict mode INSERT rises a warning, and record is successfully inserted in a table.

This behavior is well documented for regular tables.

What is the desired behavior for views? To produce error in the non-strict mode too?
[11 Jan 2008 17:27] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/40926

ChangeSet@1.2588, 2008-01-11 20:10:54+03:00, evgen@moonbone.local +3 -0
  Bug#29477: Not all fields of the target table were checked to have a default
  value when inserting into a view.
  
  The mysql_prepare_insert function checks all fields of the target table that
  directly or indirectly (through a view) are specified in the INSERT
  statement to have a default value. This check can be skipped if the INSERT
  statement doesn't mention any insert fields. In case of a view this allows
  fields that aren't mentioned in the view to bypass the check.
  
  Now fields of the target table are always checked to have a default value
  when insert goes into a view.
[24 Jan 2008 11:56] Bugs System
Pushed into 6.0.5-alpha
[24 Jan 2008 12:01] Bugs System
Pushed into 5.1.24-rc
[24 Jan 2008 12:02] Bugs System
Pushed into 5.0.56
[25 Feb 2008 17:44] Paul Dubois
Noted in 5.0.56, 5.1.24, 6.0.5 changelogs.

Views were treated as insertable even if some base table columns with
no default value were omitted from the view definition. (This is
contrary to the condition for insertability that a view must contain
all columns in the base table that do not have a default value.)
[6 Mar 2008 6:05] Jon Stephens
Fix also documented for 5.1.23-ndb-6.2.14.
[30 Mar 2008 9:51] Jon Stephens
Fix also documented for 5.1.23-ndb-6.3.11.