Issue with updatable views
Posted by Matthias Rogel on 7. January 2014
It’s sometimes amazing, how many bugs there are still with elementary SQL.
Here is one concerning updatable views:
sokrates@12.1 > create table t ( v varchar2(30) ); Table created. sokrates@12.1 > create view v as 2 select v as dontdothatman, v as canbelostwheninserted 3 from t; View created. sokrates@12.1 > insert /* this is fine */ into v 2 values('fine', 'fine'); 1 row created. sokrates@12.1 > select * from v; DONTDOTHATMAN CANBELOSTWHENINSERTED ------------------------------ ------------------------------ fine fine sokrates@12.1 > insert /* exception expected because 1st value is lost */ into v 2 values('this one is lost', 'why isnt that one lost ?'); 1 row created. sokrates@12.1 > select * from v; DONTDOTHATMAN CANBELOSTWHENINSERTED ------------------------------ ------------------------------ fine fine why isnt that one lost ? why isnt that one lost ?
Jeffrey Kemp said
I wouldn’t class this as a bug, more that the SQL standard allows you to do silly things like this 🙂
Matthias Rogel said
see Bug ID 14836272: UPDATE ON A VIEW DOES NOT FAIL WITH ORA-1732 ALTHOUGH THE VIEW IS NON-UPDATABLE
Jeffrey Kemp said
So that view shouldn’t be updateable in the first place?
Matthias Rogel said
Probably yes (it shouldn’t be updateable at all).
http://docs.oracle.com/cd/E17952_01/refman-5.1-en/view-updatability.html ( MySQL 5.1 Reference Manual, Bug ID 14836272 refers to it – I don’t know why ) states “…a view is not updatable if it contains any of the following…Multiple references to any column of a base table…”
I cannot find this restriction in http://docs.oracle.com/cd/E16655_01/server.121/e17209/statements_8004.htm#SQLRF54782 (Oracle® Database SQL Language Reference 12c Release 1 (12.1)), though probably it should also be stated there.