db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Andreas Korneliussen <Andreas.Kornelius...@Sun.COM>
Subject Re: [jira] Updated: (DERBY-231) "FOR UPDATE" required for updatable result set to work
Date Fri, 04 Nov 2005 13:16:55 GMT
Daniel John Debrunner wrote:
> Andreas Korneliussen wrote:
> 
> 
>>Daniel John Debrunner wrote:
>>
>>
>>>Bernt M. Johnsen wrote:
>>>
>>>
>>>
>>>>The patch looks sound. I'll commit when I have run derbyall and
>>>>experimented a bit on my own.
>>>
>>>
>>>
>>>So the patch removes this warning
>>>
>>>- ** NOTE: THIS IS NOT COMPATIBLE WITH THE ISO/ANSI STANDARD!!!
>>>- **
>>>- ** According to ANSI, cursors are updatable by default, unless
>>>- ** they can't be (e.g. they contain joins).  But this would mean
>>>- ** that we couldn't use an index on any single-table select,
>>>- ** unless it was declared FOR READ ONLY.  This would be pretty
>>>- ** terrible, so we are breaking the ANSI rules and making all
>>>- ** cursors (i.e. select statements) read-only by default.
>>>- ** Users will have to say FOR UPDATE if they want a cursor to
>>>- ** be updatable.  Later, we may have an ANSI compatibility
>>>- ** mode so we can pass the NIST tests.
>>>
>>>but I can't see why it is removed. It seems that Derby will stil not
>>>support a positioned update/delete on
>>>
>>>SELECT * FROM T
>>>
>>>whereas the comment implies according to the SQL standard it should.
>>>
>>
>>
>>Hi,
>>Thanks for reviewing the changes.
>>
>>The fix changes the default behavior for cursors, and cursors are now
>>updatable, unless they can't be (e.g they contain joins), and unless the
>>concurrency mode is READ_ONLY. The default behaviour is used if and only
>>if no update clause is specified (FOR UPDATE / FOR READ ONLY). On a
>>single table SELECT, we do not have to use "FOR READ ONLY" to use an
>>index, since the update mode will be READONLY if the concurrency mode is
>>READ_ONLY. Users do not have to say "FOR UPDATE" if they want a cursor
>>to be updatable, they can use concurrency mode UPDATABLE.
>>
>>As you can see, the comment is therefore no longer valid.
> 
> 
> I believe the comment still is valid.
> 
> According to the SQL standard a statement like SELECT * FROM T is
> updateable, Derby does not make that statement updatable in all cases.
> And by updateable, I mean through positioned updated/delete.
> 
> The SQL standard does not require that the client's JDBC result set is
> updateable to make the statement updateable, obviouly because the SQL
> standard is self contained and independent of the JDBC spec.
> 
> I'm not disagreeing with your changes, just the removal of the comment.
> Maybe the comment could be updated. You have improved the situation, but
> there are still cases where Derby conflicts with the standard.
> 
> Dan.
> 
Based on the comments from you and Bernt, I would suggest a new comment 
like:

NOTE: THIS IS NOT COMPATIBLE WITH THE ISO/ANSI SQL STANDARD.

According to the SQL-standard:
If updatability is not specified, a SELECT * FROM T will be implicitely
read only in the context of a cursor which is insensitive, scrollable or
have an order by clause. Otherwise it is implicitely updatable.

In Derby, we make a SELECT * FROM T updatable if the concurrency mode is
ResultSet.CONCUR_UPDATE. If we do make all SELECT * FROM T  updatable
by default, we cannot use an index on any single-table select, unless it
was declared FOR READ ONLY. This would be pretty terrible, so we are
breaking the ANSI rules. Later, we may have an ANSI compatibility mode 
so we can pass the NIST tests.


-- Andreas

Mime
View raw message