MySQL Bugs: #16110: insert permitted into view col w/o default value

Bug #16110	insert permitted into view col w/o default value
Submitted:	31 Dec 2005 3:40	Modified:	23 Jul 2006 3:09
Reporter:	Kolbe Kegel	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Views	Severity:	S3 (Non-critical)
Version:	5.0.17	OS:	Linux (Linux)
Assigned to:	Georgi Kodinov	CPU Architecture:	Any

Description:
No error or warning is issued when a row inserted into a view omits a column with no default value in the base table, regardless of whether or not a strict SQL mode is in effect.

How to repeat:
drop table if exists t1,v1;
create table t1 (col1 int not null, col2 int null default null);
create view v1 as select col1, col2 from t1;
insert into t1 (col2) values (1);
insert into v1 (col2) values (2);

set sql_mode = STRICT_ALL_TABLES;

insert into t1 (col2) values (3);
insert into v1 (col2) values (4);

select * from t1;

Suggested fix:
The server should issue either an error or warning, depending on whether or not a strict SQL mode is in effect. The warning/error issued could be ER_NO_DEFAULT_FOR_VIEW_FIELD.

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/8407

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/8683

When compiling INSERT statements the check whether columns are provided values
depends on the flag whether a field is used in that query (Field::query_id).
However the check for updatability of VIEW columns (check_view_insertability())
was calling fix_fields() and thus setting the Field::query_id even for the 
view fields that are not referenced in the current INSERT statement.
So the correct check for columns without default values 
( check_that_all_fields_are_given_values() ) is assuming that all the VIEW
columns were mentioned in the INSERT field list and was issuing no 
warnings or errors.

Fixed in 5.0.25

Noted in 5.0.25 changelog.

When a row was inserted through a view but did not specify a
value for a column that had no default value in the base
table, no warning or error occurred. Now a warning occurs, or
an error in strict SQL mode.