MySQL Bugs: #6442: Views: updatability not established at view creation time

Bug #6442	Views: updatability not established at view creation time
Submitted:	4 Nov 2004 20:33	Modified:	3 Oct 2008 9:26
Reporter:	Paul DuBois	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Views	Severity:	S3 (Non-critical)
Version:	5.0.2, 5.1.28	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
WL #941 indicates that view updatability should be
established at view creation time. This implies that
the SQL mode in effect at that time should control
updates. However, it appears that the SQL mode in
effect at the time of the update is what is actually
used.

How to repeat:
# Case 1:
# The INSERT should fail when the view was created in strict mode,
# because no value is supplied for underlying table column t.col2.
DROP TABLE IF EXISTS t;
DROP VIEW IF EXISTS v;
CREATE TABLE t (col1 INT NOT NULL, col2 INT NOT NULL) ENGINE = INNODB;
set sql_mode='strict_all_tables';
CREATE VIEW v (vcol1) AS SELECT col1 FROM t;
INSERT INTO v (vcol1) VALUES(12);

# Case 2:
# The INSERT should not fail because the view was not created in strict
# mode.  But in fact it does fail when strict mode is turned on before
# the insert.
DROP TABLE IF EXISTS t;
DROP VIEW IF EXISTS v;
CREATE TABLE t (col1 INT NOT NULL, col2 INT NOT NULL) ENGINE = INNODB;
CREATE VIEW v (vcol1) AS SELECT col1 FROM t;
set sql_mode='strict_all_tables';
INSERT INTO v (vcol1) VALUES(12);

In both cases, the INSERT fails with:

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

Note: the INSERT will succeed if sql_mode is '', so this is not just
a case of strict mode being applied all the time.

saving environment is not fully implemented now

Needs to be re-verified. We store view creation context in 5.1

We still have this problem in 5.1.28:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.1.28-rc-community MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> drop table t;
Query OK, 0 rows affected (0.06 sec)

mysql> drop view v;
ERROR 1051 (42S02): Unknown table 'v'
mysql> DROP TABLE IF EXISTS t;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> DROP VIEW IF EXISTS v;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE t (col1 INT NOT NULL, col2 INT NOT NULL) ENGINE = INNODB;
Query OK, 0 rows affected (0.09 sec)

mysql> set sql_mode='strict_all_tables';
Query OK, 0 rows affected (0.05 sec)

mysql> CREATE VIEW v (vcol1) AS SELECT col1 FROM t;
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO v (vcol1) VALUES(12);
ERROR 1423 (HY000): Field of view 'test.v' underlying table doesn't have a defau
lt value
mysql> DROP TABLE IF EXISTS t;
Query OK, 0 rows affected (0.05 sec)

mysql> DROP VIEW IF EXISTS v;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE t (col1 INT NOT NULL, col2 INT NOT NULL) ENGINE = INNODB;
Query OK, 0 rows affected (0.11 sec)

mysql> CREATE VIEW v (vcol1) AS SELECT col1 FROM t;
Query OK, 0 rows affected (0.00 sec)

mysql> set sql_mode='strict_all_tables';
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO v (vcol1) VALUES(12);
ERROR 1423 (HY000): Field of view 'test.v' underlying table doesn't have a defau
lt value
mysql> set sql_mode='';
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO v (vcol1) VALUES(12);
Query OK, 1 row affected, 1 warning (0.03 sec)

Seems to be fixed in (or before) 8.0.3:

mysql> # Case 1:
mysql> # The INSERT should fail when the view was created in strict mode,
mysql> # because no value is supplied for underlying table column t.col2.
mysql> DROP TABLE IF EXISTS t;
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> DROP VIEW IF EXISTS v;
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> CREATE TABLE t (col1 INT NOT NULL, col2 INT NOT NULL) ENGINE = INNODB;
Query OK, 0 rows affected (0.25 sec)

mysql> set sql_mode='strict_all_tables';
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> CREATE VIEW v (vcol1) AS SELECT col1 FROM t;
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO v (vcol1) VALUES(12);
ERROR 1423 (HY000): Field of view 'test.v' underlying table doesn't have a default value
mysql> 
mysql> # Case 2:
mysql> # The INSERT should not fail because the view was not created in strict
mysql> # mode.  But in fact it does fail when strict mode is turned on before
mysql> # the insert.
mysql> DROP TABLE IF EXISTS t;
Query OK, 0 rows affected (0.14 sec)

mysql> DROP VIEW IF EXISTS v;
Query OK, 0 rows affected (0.05 sec)

mysql> CREATE TABLE t (col1 INT NOT NULL, col2 INT NOT NULL) ENGINE = INNODB;
Query OK, 0 rows affected (0.26 sec)

mysql> CREATE VIEW v (vcol1) AS SELECT col1 FROM t;
Query OK, 0 rows affected (0.05 sec)

mysql> set sql_mode='strict_all_tables';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> INSERT INTO v (vcol1) VALUES(12);
ERROR 1423 (HY000): Field of view 'test.v' underlying table doesn't have a default value