MySQL Bugs: #29477: VIEW-s insertability

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

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.

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 |
+------+-------+

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?

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.

Pushed into 6.0.5-alpha

Pushed into 5.1.24-rc

Pushed into 5.0.56

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.)

Fix also documented for 5.1.23-ndb-6.2.14.

Fix also documented for 5.1.23-ndb-6.3.11.