Description:
I try to create a view with "CHECK OPTION" set, but CREATE VIEW gives error:
ERROR 1368 (HY000): CHECK OPTION on non-updatable view 'db.view_name'
I create that view without "CHECK OPTION", and then the view turns out to be (at least partially) updatable: information_schema.VIEWS says so, and I can successfully run UPDATE statements on the view.
This happens, among others, when the view contains an inner join in the FROM clause with a non-updatable view, but the update does not touch any column from that non-updatable view.
if the .frm file is changed manually to set "with_check_option=1" or "with_check_option=2", then the check actually works.
How to repeat:
mysql> create database bug;
Query OK, 1 row affected (0.00 sec)
mysql> use bug;
mysql> create table t1 ( Num SERIAL , data VARCHAR(50), PRIMARY KEY (Num) );
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO t1 VALUES (1, 'one');
Query OK, 1 row affected (0.00 sec)
mysql> create table t2 ( Num SERIAL , comment VARCHAR(50), PRIMARY KEY (Num) );
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO t2 VALUES (1, 'smallest non-zero number');
Query OK, 1 row affected (0.00 sec)
mysql> CREATE VIEW v0 AS SELECT t2.* FROM t2 UNION SELECT 0, "nothing" ;
mysql> CREATE VIEW v1 AS SELECT t1.* FROM t1 INNER JOIN v0 ON t1.Num=v0.Num WITH CHECK OPTION;
ERROR 1368 (HY000): CHECK OPTION on non-updatable view 'perf.v1'
mysql> CREATE VIEW v1 AS SELECT t1.* FROM t1 INNER JOIN v0 ON t1.Num=v0.Num;
Query OK, 0 rows affected (0.00 sec)
mysql> select TABLE_SCHEMA, TABLE_NAME, CHECK_OPTION, IS_UPDATABLE, DEFINER, SECURITY_TYPE from information_schema.VIEWS WHERE TABLE_SCHEMA='bug';
+--------------+------------+--------------+--------------+---------+---------------+
| TABLE_SCHEMA | TABLE_NAME | CHECK_OPTION | IS_UPDATABLE | DEFINER | SECURITY_TYPE |
+--------------+------------+--------------+--------------+---------+---------------+
| perf | v0 | NONE | NO | root@% | DEFINER |
| perf | v1 | NONE | YES | root@% | DEFINER |
+--------------+------------+--------------+--------------+---------+---------------+
2 rows in set (0.00 sec)
mysql> UPDATE v1 SET data='One';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
Suggested fix:
Use the same algorithm to check view updatability at creation time and in information_schema.VIEWS and when checking whether an actual update can be done.
