Bug #28266 IS_UPDATABLE field on VIEWS table in I_S database is wrong
Submitted: 6 May 2007 3:35 Modified: 18 Jun 2007 19:48
Reporter: Lachlan Mulcahy Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Views Severity:S3 (Non-critical)
Version:5.0.37 OS:Any
Assigned to: Sergei Glukhov CPU Architecture:Any
Tags: bfsm_2007_05_31, IS_UPDATABLE VIEWS INFORMATION_SCHEMA

[6 May 2007 3:35] Lachlan Mulcahy
Description:
The IS_UPDATABLE field in the INFORMATION_SCHEMA.VIEWS table does not seem to correctly reflect whether the view is actually updatable.

How to repeat:
USE test;
CREATE TABLE t1 ( a INT );
INSERT INTO t1 VALUES (1), (2);
CREATE VIEW v1 AS SELECT a+1 as a FROM t1;
SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE table_name='v1';
UPDATE v1 SET a=5 WHERE a=2;

Suggested fix:
n/a
[16 May 2007 10:15] Timour Katchaounov
Upgraded to P2 as the report implies incorrect result.
[17 May 2007 10:45] Sergei Glukhov
'Updatable' means that you can make update|delete|insert for this view

create table t1 (f1 int);
insert into t1 values (1), (2);
create view v1 as select f1+1 as a from t1;
create algorithm=temptable view v2  as select f1 from t1;
select is_updatable from information_schema.views;
is_updatable
YES
NO
-----------
you can not update the view:
update v1 set a=5 where a=2;
ERROR HY000: Column 'a' is not updatable
-----------
but you can delete from the view:
delete from v1;
select * from t1;
f1
----------

I am going to mark this bug as 'not a bug', do you agree?
[29 May 2007 21:13] Trudy Pelzer
Gluh: I don't agree. The description in WL#941 and subsequent
newletter articles/white papers written about views tells me
that the IS_UPDATABLE flag is YES only if one can both update 
and delete from a view. If either operation is not supported,
then the flag should be set to NO. In the examples shown, one
can DELETE from the view, but the view cannot be updated (because 
its one column is based on an expression and so the TEMPTABLE
algorithm is used to evaluate the view).

Since both UPDATE *and* DELETE are not supported, the view is
not an updatable view and the IS_UPDATABLE flag should be NO.
Since that isn't the case, this is a bug.

Note also that not all updatable bugs can accept INSERT statements.
[31 May 2007 13:01] 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/commits/27814

ChangeSet@1.2484, 2007-05-31 17:58:49+05:00, gluh@mysql.com +5 -0
  Bug#28266 IS_UPDATABLE field on VIEWS table in I_S database is wrong
  IS_UPDATABLE flag is set to 'yes' when the view has at least one updatable column and
  algorithm is not 'temporary'.
[8 Jun 2007 13:42] 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/commits/28401

ChangeSet@1.2484, 2007-06-08 18:39:36+05:00, gluh@mysql.com +5 -0
  Bug#28266 IS_UPDATABLE field on VIEWS table in I_S database is wrong
  IS_UPDATABLE flag is set to 'yes' when the view has at least one updatable column and
  algorithm is not 'temporary'.
[9 Jun 2007 11:55] 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/commits/28456

ChangeSet@1.2525, 2007-06-09 16:52:37+05:00, gluh@mysql.com +5 -0
  Bug#28266 IS_UPDATABLE field on VIEWS table in I_S database is wrong
  IS_UPDATABLE flag is set to 'yes' when the view has at least one updatable column and
  the algorithm is not 'temporary'.
[14 Jun 2007 19:01] Bugs System
Pushed into 5.0.44
[14 Jun 2007 19:01] Bugs System
Pushed into 5.1.20-beta
[18 Jun 2007 19:48] Paul DuBois
Noted in 5.0.44, 5.1.20 changelogs.

The IS_UPDATABLE column in the INFORMATION_SCHEMA.VIEWS table was not
always set correctly.