MySQL Bugs: #6443: Views: failed WHERE clause doesn't prevent updatability

Bug #6443	Views: failed WHERE clause doesn't prevent updatability
Submitted:	4 Nov 2004 20:47	Modified:	7 Jul 2005 17:15
Reporter:	Paul DuBois	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.2	OS:
Assigned to:	Oleksandr Byelkin	CPU Architecture:	Any

Description:
In strict mode, an INSERT into a base table fails if it
does not supply a value for a column that is NOT NULL
and does not have a default value. Similarly, an INSERT
into a view that does not include this column fails.
However, if the view includes a WHERE test on this column,
an insert into the view succeeds.

How to repeat:
DROP TABLE IF EXISTS t;
DROP VIEW IF EXISTS v;
DROP VIEW IF EXISTS v2;
set sql_mode='strict_all_tables';
CREATE TABLE t (col1 INT NOT NULL, col2 INT NOT NULL) ENGINE = INNODB;
CREATE VIEW v (vcol1) AS SELECT col1 FROM t;
CREATE VIEW v2 (vcol1) AS SELECT col1 FROM t WHERE col2 > 2;
# this should fail... and does
INSERT INTO t (col1) VALUES(12);
# this should fail... and does
INSERT INTO v (vcol1) VALUES(12);
# this should fail... but does not
INSERT INTO v2 (vcol1) VALUES(12);

For the first two INSERT statements, an error occurs:

ERROR 1364 (HY000): Field 'col2' doesn't have a default value

The third INSERT succeeds. It inserts 0 into t.col2.

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/internals/26575

pushed to 5.0.9

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Bugfix documented for 5.0.9; closed.