[DERBY-4226] Column updatability wrong in corner case with explicit FOR UPDATE OF <column-list>

• Type: Bug
• Status: Open
• Priority: Minor
• Resolution: Unresolved
• Affects Version/s: 10.1.1.0, 10.1.2.1, 10.1.3.1, 10.2.1.6, 10.2.2.0, 10.3.1.4, 10.3.2.1, 10.3.3.0, 10.4.1.3, 10.4.2.0, 10.5.1.1
• Component/s: JDBC, SQL
• Labels: derby_triage10_5_2
• Urgency: Normal
• Issue & fix info: Repro attached
• Assignee: Unassigned
• Reporter: Dag H. Wanvik
• Created: 12/May/09 18:09
• Updated: 15/Feb/11 21:09

Description

The first of the two queries below allow updating column 3 in the result set;

rs.updateInt(3, <new-value>)

the second query fails, giving 42X31 (embedded) and XJ124 (client).
It seems they should have the same updatability for column 3:

SELECT id,a,id from t1 for update
SELECT id,a,id from t1 for update of id

This is the case both for TYPE_FORWARD_ONLY and TYPE_SCROLL_INSENSITIVE result sets.

Both queries allow the first column (first instance of id) to be updated.

In the cases that work, if the rs is scroll insensitive, both instances of id are updated and will show the new value if read back after updateRow has been performed.

It is not obvious, perhaps, if updating column 3 should be allowed (if so, only if column 1 is not updated?)
or, maybe the behavior should be that only the column that is actually updated (in a scrollable insensitive result set) should be updated, i.e. any other instances would not reflect the change unless they are themselves updated.

Note that any expressions dependent on the column are not recomputed, i.e.

select id, i+4

will not show a changed value of id+4 in an updatable scrollable result set, if id is updated.