Andreas Korneliussen wrote:
> 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. Looks good, I would remove the last sentence, there would be no need for a compatibility mode and not something I would like to see. Derby is supporting a subset of what the standard allows. We could support the correct behaviour with no correctness issues with backwards compatibility, as long as the performance issues were addressed. Bernt wrote: > Dan wrote: >>but it would be good to come to clear agreement on if SELECT * FROM >>> T can be updated with a positioned update/delete and a read-only >>> ResultSet. (Even if Derby doesn't support it today, it would be nice >>> to know or not if it is meant to be supported). > > > It definitely would mean lower performance due to locking issues. Though some work was already done on this to ensure a simple FOR UPDATE with no column list added for locking behaviour did not degrade performance. I'd have to research the details, but it's in the code. Dan.
