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
