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

[4 Nov 2004 20:47] Paul Dubois
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.
[1 Jul 2005 4:05] 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/internals/26575
[1 Jul 2005 4:53] Oleksandr Byelkin
pushed to 5.0.9
[7 Jul 2005 17:15] Jon Stephens
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.