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: | |
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
[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.