You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Andreas Korneliussen (JIRA)" <de...@db.apache.org> on 2006/05/31 17:03:29 UTC
[jira] Created: (DERBY-1361) positioned updates and deletes allowed
after a commit without repositioning the cursor - if the table is indexed
on the columns selected
positioned updates and deletes allowed after a commit without repositioning the cursor - if the table is indexed on the columns selected
----------------------------------------------------------------------------------------------------------------------------------------
Key: DERBY-1361
URL: http://issues.apache.org/jira/browse/DERBY-1361
Project: Derby
Type: Bug
Components: JDBC
Versions: 10.2.0.0
Environment: Java 1.4
Reporter: Andreas Korneliussen
After a commit, the cursor should be positioned before the next row (not on a row). However in Derby 10.2, I now see the following behavior if the table has an index on the selected columns:
ij> create table t (id int primary key);
ij> commit;
ij> insert into t values 1,2,3,4,5,6,7,8,9;
9 rows inserted/updated/deleted
ij> commit;
ij> get cursor c1 as 'select * from t for update';
ij> next c1;
ID
-----------
1
ij> commit;
ij> update t set id=id+1000 where current of c1;
1 row inserted/updated/deleted
ij> commit;
ij> update t set id=id+1000 where current of c1;
1 row inserted/updated/deleted
ij>
The expected behavior is that the UPDATE .. WHERE CURRENT OF fails with:
ERROR 24000: Invalid cursor state - no current row.
The problem applies to both scrollable and forward-only updatable cursors.
Note: If the table is created *without* the index, I do see the expected behavior.
I am marking this as a regression, since this issue is not reproducible on older, released versions of derby (I have tested with 10.1.2.1).
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Closed: (DERBY-1361) positioned updates and deletes allowed
after a commit without repositioning the cursor - if the table is indexed
on the columns selected
Posted by "Andreas Korneliussen (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1361?page=all ]
Andreas Korneliussen closed DERBY-1361:
---------------------------------------
> positioned updates and deletes allowed after a commit without repositioning the cursor - if the table is indexed on the columns selected
> ----------------------------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-1361
> URL: http://issues.apache.org/jira/browse/DERBY-1361
> Project: Derby
> Type: Bug
> Components: JDBC
> Versions: 10.2.0.0
> Environment: Java 1.4
> Reporter: Andreas Korneliussen
> Assignee: Andreas Korneliussen
> Fix For: 10.2.0.0
> Attachments: DERBY-1361.diff, DERBY-1361.stat, DERBY-1361v2.diff, DERBY-1361v2.stat
>
> After a commit, the cursor should be positioned before the next row (not on a row). However in Derby 10.2, I now see the following behavior if the table has an index on the selected columns:
> ij> create table t (id int primary key);
> ij> commit;
> ij> insert into t values 1,2,3,4,5,6,7,8,9;
> 9 rows inserted/updated/deleted
> ij> commit;
> ij> get cursor c1 as 'select * from t for update';
> ij> next c1;
> ID
> -----------
> 1
> ij> commit;
> ij> update t set id=id+1000 where current of c1;
> 1 row inserted/updated/deleted
> ij> commit;
> ij> update t set id=id+1000 where current of c1;
> 1 row inserted/updated/deleted
> ij>
> The expected behavior is that the UPDATE .. WHERE CURRENT OF fails with:
> ERROR 24000: Invalid cursor state - no current row.
> The problem applies to both scrollable and forward-only updatable cursors.
> Note: If the table is created *without* the index, I do see the expected behavior.
> I am marking this as a regression, since this issue is not reproducible on older, released versions of derby (I have tested with 10.1.2.1).
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Assigned: (DERBY-1361) positioned updates and deletes
allowed after a commit without repositioning the cursor - if the table is
indexed on the columns selected
Posted by "Andreas Korneliussen (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1361?page=all ]
Andreas Korneliussen reassigned DERBY-1361:
-------------------------------------------
Assign To: Andreas Korneliussen
> positioned updates and deletes allowed after a commit without repositioning the cursor - if the table is indexed on the columns selected
> ----------------------------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-1361
> URL: http://issues.apache.org/jira/browse/DERBY-1361
> Project: Derby
> Type: Bug
> Components: JDBC
> Versions: 10.2.0.0
> Environment: Java 1.4
> Reporter: Andreas Korneliussen
> Assignee: Andreas Korneliussen
>
> After a commit, the cursor should be positioned before the next row (not on a row). However in Derby 10.2, I now see the following behavior if the table has an index on the selected columns:
> ij> create table t (id int primary key);
> ij> commit;
> ij> insert into t values 1,2,3,4,5,6,7,8,9;
> 9 rows inserted/updated/deleted
> ij> commit;
> ij> get cursor c1 as 'select * from t for update';
> ij> next c1;
> ID
> -----------
> 1
> ij> commit;
> ij> update t set id=id+1000 where current of c1;
> 1 row inserted/updated/deleted
> ij> commit;
> ij> update t set id=id+1000 where current of c1;
> 1 row inserted/updated/deleted
> ij>
> The expected behavior is that the UPDATE .. WHERE CURRENT OF fails with:
> ERROR 24000: Invalid cursor state - no current row.
> The problem applies to both scrollable and forward-only updatable cursors.
> Note: If the table is created *without* the index, I do see the expected behavior.
> I am marking this as a regression, since this issue is not reproducible on older, released versions of derby (I have tested with 10.1.2.1).
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-1361) positioned updates and deletes
allowed after a commit without repositioning the cursor - if the table is
indexed on the columns selected
Posted by "Andreas Korneliussen (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1361?page=comments#action_12417059 ]
Andreas Korneliussen commented on DERBY-1361:
---------------------------------------------
For the scrollable case, I found two problems:
1. The network client will do a parsing of the SQL and reposition the cursor when there is a positioned update (if it is a scrollable cursor). Since the cursor is repositioned, no exception is thrown when doing it immediately after a commit.
The repositioning logic can be removed.
2. Scrollable cursors will not reposition the scancontroller of indexscans. This means that the scancontroller for the index may be closed. Using the scancontroller to depend on this logic does not seem sufficient.
I will go in another direction: on a commit, every activations will be notified. The activations has references to a ResultSet, and they can notify the resultsets that they should be positioned between the current row and the next row (this can be done by setting currentRow=null)
As for the two different error messages, I think that this change may force me to choose one of these. It does not seem to be entirely deterministic when I get one and not the other. I think the correct message is the one with SQLSTate 24000.
> positioned updates and deletes allowed after a commit without repositioning the cursor - if the table is indexed on the columns selected
> ----------------------------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-1361
> URL: http://issues.apache.org/jira/browse/DERBY-1361
> Project: Derby
> Type: Bug
> Components: JDBC
> Versions: 10.2.0.0
> Environment: Java 1.4
> Reporter: Andreas Korneliussen
> Assignee: Andreas Korneliussen
>
> After a commit, the cursor should be positioned before the next row (not on a row). However in Derby 10.2, I now see the following behavior if the table has an index on the selected columns:
> ij> create table t (id int primary key);
> ij> commit;
> ij> insert into t values 1,2,3,4,5,6,7,8,9;
> 9 rows inserted/updated/deleted
> ij> commit;
> ij> get cursor c1 as 'select * from t for update';
> ij> next c1;
> ID
> -----------
> 1
> ij> commit;
> ij> update t set id=id+1000 where current of c1;
> 1 row inserted/updated/deleted
> ij> commit;
> ij> update t set id=id+1000 where current of c1;
> 1 row inserted/updated/deleted
> ij>
> The expected behavior is that the UPDATE .. WHERE CURRENT OF fails with:
> ERROR 24000: Invalid cursor state - no current row.
> The problem applies to both scrollable and forward-only updatable cursors.
> Note: If the table is created *without* the index, I do see the expected behavior.
> I am marking this as a regression, since this issue is not reproducible on older, released versions of derby (I have tested with 10.1.2.1).
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Resolved: (DERBY-1361) positioned updates and deletes
allowed after a commit without repositioning the cursor - if the table is
indexed on the columns selected
Posted by "Andreas Korneliussen (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1361?page=all ]
Andreas Korneliussen resolved DERBY-1361:
-----------------------------------------
Fix Version: 10.2.0.0
Resolution: Fixed
Committed revision 417366.
> positioned updates and deletes allowed after a commit without repositioning the cursor - if the table is indexed on the columns selected
> ----------------------------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-1361
> URL: http://issues.apache.org/jira/browse/DERBY-1361
> Project: Derby
> Type: Bug
> Components: JDBC
> Versions: 10.2.0.0
> Environment: Java 1.4
> Reporter: Andreas Korneliussen
> Assignee: Andreas Korneliussen
> Fix For: 10.2.0.0
> Attachments: DERBY-1361.diff, DERBY-1361.stat, DERBY-1361v2.diff, DERBY-1361v2.stat
>
> After a commit, the cursor should be positioned before the next row (not on a row). However in Derby 10.2, I now see the following behavior if the table has an index on the selected columns:
> ij> create table t (id int primary key);
> ij> commit;
> ij> insert into t values 1,2,3,4,5,6,7,8,9;
> 9 rows inserted/updated/deleted
> ij> commit;
> ij> get cursor c1 as 'select * from t for update';
> ij> next c1;
> ID
> -----------
> 1
> ij> commit;
> ij> update t set id=id+1000 where current of c1;
> 1 row inserted/updated/deleted
> ij> commit;
> ij> update t set id=id+1000 where current of c1;
> 1 row inserted/updated/deleted
> ij>
> The expected behavior is that the UPDATE .. WHERE CURRENT OF fails with:
> ERROR 24000: Invalid cursor state - no current row.
> The problem applies to both scrollable and forward-only updatable cursors.
> Note: If the table is created *without* the index, I do see the expected behavior.
> I am marking this as a regression, since this issue is not reproducible on older, released versions of derby (I have tested with 10.1.2.1).
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-1361) positioned updates and deletes allowed
after a commit without repositioning the cursor - if the table is indexed
on the columns selected
Posted by "Andreas Korneliussen (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1361?page=all ]
Andreas Korneliussen updated DERBY-1361:
----------------------------------------
Attachment: DERBY-1361.diff
DERBY-1361.stat
Attaching a patch for this issue.
The patch (DERBY-1361.diff + stat) does the following: when a commit is issued, the resultset will clear the current row. A positioned update / delete will be prohibited until there is a fetch (reposition).
The sqlstate for the error message has been set to 24000.
The client driver will no longer do the unnecessary repositioning before positioned updates/deletes (unnecessary since the resultset is already positioned)
Testing: 4 new testcases added to SURTest
Some masterfiles have been hand-edited with new error message / sqlstate.
I have run the tests which have been changed, however I have not yet run derbyall with the patch.
> positioned updates and deletes allowed after a commit without repositioning the cursor - if the table is indexed on the columns selected
> ----------------------------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-1361
> URL: http://issues.apache.org/jira/browse/DERBY-1361
> Project: Derby
> Type: Bug
> Components: JDBC
> Versions: 10.2.0.0
> Environment: Java 1.4
> Reporter: Andreas Korneliussen
> Assignee: Andreas Korneliussen
> Attachments: DERBY-1361.diff, DERBY-1361.stat
>
> After a commit, the cursor should be positioned before the next row (not on a row). However in Derby 10.2, I now see the following behavior if the table has an index on the selected columns:
> ij> create table t (id int primary key);
> ij> commit;
> ij> insert into t values 1,2,3,4,5,6,7,8,9;
> 9 rows inserted/updated/deleted
> ij> commit;
> ij> get cursor c1 as 'select * from t for update';
> ij> next c1;
> ID
> -----------
> 1
> ij> commit;
> ij> update t set id=id+1000 where current of c1;
> 1 row inserted/updated/deleted
> ij> commit;
> ij> update t set id=id+1000 where current of c1;
> 1 row inserted/updated/deleted
> ij>
> The expected behavior is that the UPDATE .. WHERE CURRENT OF fails with:
> ERROR 24000: Invalid cursor state - no current row.
> The problem applies to both scrollable and forward-only updatable cursors.
> Note: If the table is created *without* the index, I do see the expected behavior.
> I am marking this as a regression, since this issue is not reproducible on older, released versions of derby (I have tested with 10.1.2.1).
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-1361) positioned updates and deletes
allowed after a commit without repositioning the cursor - if the table is
indexed on the columns selected
Posted by "Dag H. Wanvik (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1361?page=comments#action_12414170 ]
Dag H. Wanvik commented on DERBY-1361:
--------------------------------------
I only see the error if the index is covering.
It may be related to DERBY-1087. For the forward-only case,
the behavior is correct prior to that patch.
> positioned updates and deletes allowed after a commit without repositioning the cursor - if the table is indexed on the columns selected
> ----------------------------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-1361
> URL: http://issues.apache.org/jira/browse/DERBY-1361
> Project: Derby
> Type: Bug
> Components: JDBC
> Versions: 10.2.0.0
> Environment: Java 1.4
> Reporter: Andreas Korneliussen
>
> After a commit, the cursor should be positioned before the next row (not on a row). However in Derby 10.2, I now see the following behavior if the table has an index on the selected columns:
> ij> create table t (id int primary key);
> ij> commit;
> ij> insert into t values 1,2,3,4,5,6,7,8,9;
> 9 rows inserted/updated/deleted
> ij> commit;
> ij> get cursor c1 as 'select * from t for update';
> ij> next c1;
> ID
> -----------
> 1
> ij> commit;
> ij> update t set id=id+1000 where current of c1;
> 1 row inserted/updated/deleted
> ij> commit;
> ij> update t set id=id+1000 where current of c1;
> 1 row inserted/updated/deleted
> ij>
> The expected behavior is that the UPDATE .. WHERE CURRENT OF fails with:
> ERROR 24000: Invalid cursor state - no current row.
> The problem applies to both scrollable and forward-only updatable cursors.
> Note: If the table is created *without* the index, I do see the expected behavior.
> I am marking this as a regression, since this issue is not reproducible on older, released versions of derby (I have tested with 10.1.2.1).
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-1361) positioned updates and deletes
allowed after a commit without repositioning the cursor - if the table is
indexed on the columns selected
Posted by "Andreas Korneliussen (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1361?page=comments#action_12416186 ]
Andreas Korneliussen commented on DERBY-1361:
---------------------------------------------
I am following the proposal outlined above. As part of testing my changes, I found that Derby offers two different SQL States / Error Codes for what is approximately the same situation:
If a cursor is on beforeFirst or afterLast, and the user tries a positioned update / delete or ResultSet.updateRow(), the user get error:
ERROR XCL08: Cursor 'C1' is not on a row.
If the cursor is positioned on a row, and the user does commit(), the cursor position is before the next row, and the user gets error:
ERROR 24000: Invalid cursor state - no current row.
> positioned updates and deletes allowed after a commit without repositioning the cursor - if the table is indexed on the columns selected
> ----------------------------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-1361
> URL: http://issues.apache.org/jira/browse/DERBY-1361
> Project: Derby
> Type: Bug
> Components: JDBC
> Versions: 10.2.0.0
> Environment: Java 1.4
> Reporter: Andreas Korneliussen
> Assignee: Andreas Korneliussen
>
> After a commit, the cursor should be positioned before the next row (not on a row). However in Derby 10.2, I now see the following behavior if the table has an index on the selected columns:
> ij> create table t (id int primary key);
> ij> commit;
> ij> insert into t values 1,2,3,4,5,6,7,8,9;
> 9 rows inserted/updated/deleted
> ij> commit;
> ij> get cursor c1 as 'select * from t for update';
> ij> next c1;
> ID
> -----------
> 1
> ij> commit;
> ij> update t set id=id+1000 where current of c1;
> 1 row inserted/updated/deleted
> ij> commit;
> ij> update t set id=id+1000 where current of c1;
> 1 row inserted/updated/deleted
> ij>
> The expected behavior is that the UPDATE .. WHERE CURRENT OF fails with:
> ERROR 24000: Invalid cursor state - no current row.
> The problem applies to both scrollable and forward-only updatable cursors.
> Note: If the table is created *without* the index, I do see the expected behavior.
> I am marking this as a regression, since this issue is not reproducible on older, released versions of derby (I have tested with 10.1.2.1).
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-1361) positioned updates and deletes
allowed after a commit without repositioning the cursor - if the table is
indexed on the columns selected
Posted by "Andreas Korneliussen (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1361?page=comments#action_12415365 ]
Andreas Korneliussen commented on DERBY-1361:
---------------------------------------------
It seems to me that it is the store layer which has the knowledge on if a cursor has lost its position as a consequence of a commit. After a commit, the scanstate will be SCAN_HOLD_INPROGRESS.
The optimization in DERBY-1087 causes IndexRowToBaseRowResultSet (in SQL execution layer) to no longer need to ask its source resultset for the current row (and thereby doing extra qualification ). Since it does not ask its source resultset, the source resultset no longer access the store layer and gets the exception that the cursor is not on a row.
I would propose to fix this by letting IndexRowToBaseRowResultSet call a new method (isCurrentPositionValid()) on its source resultset. The source resultset (i.e TableScanResultSet) may implement this by calling a new method in store's ScanController to check that the position is valid.
In terms of internal interface changes, this means that if I follow the proposal, these two interfaces will be extended with a new method:
org.apache.derby.iapi.sql.execute.NoPutResultSet#isCurrentPositionValid() <- allows resultsets to call their source resultsets for this info
org.apache.derby.iapi.store.access.ScanController#isCurrentPositionValid() <- allows resultsets which access the ScanControllers to get this info from store layer
An alternative fix is to continue calling source.getCurrentRow(), however that will mean that the row gets requalified before the update (-> reintroduces the problems of DERBY-1087).
Please let me know if anyone has better aproaches of solving this issue.
> positioned updates and deletes allowed after a commit without repositioning the cursor - if the table is indexed on the columns selected
> ----------------------------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-1361
> URL: http://issues.apache.org/jira/browse/DERBY-1361
> Project: Derby
> Type: Bug
> Components: JDBC
> Versions: 10.2.0.0
> Environment: Java 1.4
> Reporter: Andreas Korneliussen
> Assignee: Andreas Korneliussen
>
> After a commit, the cursor should be positioned before the next row (not on a row). However in Derby 10.2, I now see the following behavior if the table has an index on the selected columns:
> ij> create table t (id int primary key);
> ij> commit;
> ij> insert into t values 1,2,3,4,5,6,7,8,9;
> 9 rows inserted/updated/deleted
> ij> commit;
> ij> get cursor c1 as 'select * from t for update';
> ij> next c1;
> ID
> -----------
> 1
> ij> commit;
> ij> update t set id=id+1000 where current of c1;
> 1 row inserted/updated/deleted
> ij> commit;
> ij> update t set id=id+1000 where current of c1;
> 1 row inserted/updated/deleted
> ij>
> The expected behavior is that the UPDATE .. WHERE CURRENT OF fails with:
> ERROR 24000: Invalid cursor state - no current row.
> The problem applies to both scrollable and forward-only updatable cursors.
> Note: If the table is created *without* the index, I do see the expected behavior.
> I am marking this as a regression, since this issue is not reproducible on older, released versions of derby (I have tested with 10.1.2.1).
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-1361) positioned updates and deletes
allowed after a commit without repositioning the cursor - if the table is
indexed on the columns selected
Posted by "Kristian Waagan (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1361?page=comments#action_12417790 ]
Kristian Waagan commented on DERBY-1361:
----------------------------------------
I reviewed the second patch. It looks good and can be committed.
It should be noted that the patch also rewrites a few lines of code in 'BaseJDBCTestCase' (functionality is not changed).
> positioned updates and deletes allowed after a commit without repositioning the cursor - if the table is indexed on the columns selected
> ----------------------------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-1361
> URL: http://issues.apache.org/jira/browse/DERBY-1361
> Project: Derby
> Type: Bug
> Components: JDBC
> Versions: 10.2.0.0
> Environment: Java 1.4
> Reporter: Andreas Korneliussen
> Assignee: Andreas Korneliussen
> Attachments: DERBY-1361.diff, DERBY-1361.stat, DERBY-1361v2.diff, DERBY-1361v2.stat
>
> After a commit, the cursor should be positioned before the next row (not on a row). However in Derby 10.2, I now see the following behavior if the table has an index on the selected columns:
> ij> create table t (id int primary key);
> ij> commit;
> ij> insert into t values 1,2,3,4,5,6,7,8,9;
> 9 rows inserted/updated/deleted
> ij> commit;
> ij> get cursor c1 as 'select * from t for update';
> ij> next c1;
> ID
> -----------
> 1
> ij> commit;
> ij> update t set id=id+1000 where current of c1;
> 1 row inserted/updated/deleted
> ij> commit;
> ij> update t set id=id+1000 where current of c1;
> 1 row inserted/updated/deleted
> ij>
> The expected behavior is that the UPDATE .. WHERE CURRENT OF fails with:
> ERROR 24000: Invalid cursor state - no current row.
> The problem applies to both scrollable and forward-only updatable cursors.
> Note: If the table is created *without* the index, I do see the expected behavior.
> I am marking this as a regression, since this issue is not reproducible on older, released versions of derby (I have tested with 10.1.2.1).
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-1361) positioned updates and deletes allowed
after a commit without repositioning the cursor - if the table is indexed
on the columns selected
Posted by "Andreas Korneliussen (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1361?page=all ]
Andreas Korneliussen updated DERBY-1361:
----------------------------------------
Attachment: DERBY-1361v2.diff
DERBY-1361v2.stat
Attaching an updated patch. Two more master files had to be updated because of the error code change. With this latest patch, I have run derbyall with no failures.
I will until next week days before I commit this patch, in case anyone would like to give some review comments.
> positioned updates and deletes allowed after a commit without repositioning the cursor - if the table is indexed on the columns selected
> ----------------------------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-1361
> URL: http://issues.apache.org/jira/browse/DERBY-1361
> Project: Derby
> Type: Bug
> Components: JDBC
> Versions: 10.2.0.0
> Environment: Java 1.4
> Reporter: Andreas Korneliussen
> Assignee: Andreas Korneliussen
> Attachments: DERBY-1361.diff, DERBY-1361.stat, DERBY-1361v2.diff, DERBY-1361v2.stat
>
> After a commit, the cursor should be positioned before the next row (not on a row). However in Derby 10.2, I now see the following behavior if the table has an index on the selected columns:
> ij> create table t (id int primary key);
> ij> commit;
> ij> insert into t values 1,2,3,4,5,6,7,8,9;
> 9 rows inserted/updated/deleted
> ij> commit;
> ij> get cursor c1 as 'select * from t for update';
> ij> next c1;
> ID
> -----------
> 1
> ij> commit;
> ij> update t set id=id+1000 where current of c1;
> 1 row inserted/updated/deleted
> ij> commit;
> ij> update t set id=id+1000 where current of c1;
> 1 row inserted/updated/deleted
> ij>
> The expected behavior is that the UPDATE .. WHERE CURRENT OF fails with:
> ERROR 24000: Invalid cursor state - no current row.
> The problem applies to both scrollable and forward-only updatable cursors.
> Note: If the table is created *without* the index, I do see the expected behavior.
> I am marking this as a regression, since this issue is not reproducible on older, released versions of derby (I have tested with 10.1.2.1).
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-1361) positioned updates and deletes
allowed after a commit without repositioning the cursor - if the table is
indexed on the columns selected
Posted by "Andreas Korneliussen (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1361?page=comments#action_12417072 ]
Andreas Korneliussen commented on DERBY-1361:
---------------------------------------------
BTW, this is what SQL foundation, part 2, 4.29 Cursors, says about this situation:
"A holdable cursor that has been held open retains its position when the new SQL-transaction is initiated. However, before either an <update statement: positioned> or a <delete statement: positioned> is permitted to reference that cursor in the new SQL-transaction, a <fetch statement> must be issued against the cursor."
I assume, since Derby does not have a <fetch statement>, a repositioning which does implict fetch is required.
> positioned updates and deletes allowed after a commit without repositioning the cursor - if the table is indexed on the columns selected
> ----------------------------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-1361
> URL: http://issues.apache.org/jira/browse/DERBY-1361
> Project: Derby
> Type: Bug
> Components: JDBC
> Versions: 10.2.0.0
> Environment: Java 1.4
> Reporter: Andreas Korneliussen
> Assignee: Andreas Korneliussen
>
> After a commit, the cursor should be positioned before the next row (not on a row). However in Derby 10.2, I now see the following behavior if the table has an index on the selected columns:
> ij> create table t (id int primary key);
> ij> commit;
> ij> insert into t values 1,2,3,4,5,6,7,8,9;
> 9 rows inserted/updated/deleted
> ij> commit;
> ij> get cursor c1 as 'select * from t for update';
> ij> next c1;
> ID
> -----------
> 1
> ij> commit;
> ij> update t set id=id+1000 where current of c1;
> 1 row inserted/updated/deleted
> ij> commit;
> ij> update t set id=id+1000 where current of c1;
> 1 row inserted/updated/deleted
> ij>
> The expected behavior is that the UPDATE .. WHERE CURRENT OF fails with:
> ERROR 24000: Invalid cursor state - no current row.
> The problem applies to both scrollable and forward-only updatable cursors.
> Note: If the table is created *without* the index, I do see the expected behavior.
> I am marking this as a regression, since this issue is not reproducible on older, released versions of derby (I have tested with 10.1.2.1).
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-1361) positioned updates and deletes
allowed after a commit without repositioning the cursor - if the table is
indexed on the columns selected
Posted by "Dag H. Wanvik (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1361?page=comments#action_12414171 ]
Dag H. Wanvik commented on DERBY-1361:
--------------------------------------
The problem exists not only for positioned operations, but also
for ResultSet#updateRow and ResultSet#deleteRow.
> positioned updates and deletes allowed after a commit without repositioning the cursor - if the table is indexed on the columns selected
> ----------------------------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-1361
> URL: http://issues.apache.org/jira/browse/DERBY-1361
> Project: Derby
> Type: Bug
> Components: JDBC
> Versions: 10.2.0.0
> Environment: Java 1.4
> Reporter: Andreas Korneliussen
>
> After a commit, the cursor should be positioned before the next row (not on a row). However in Derby 10.2, I now see the following behavior if the table has an index on the selected columns:
> ij> create table t (id int primary key);
> ij> commit;
> ij> insert into t values 1,2,3,4,5,6,7,8,9;
> 9 rows inserted/updated/deleted
> ij> commit;
> ij> get cursor c1 as 'select * from t for update';
> ij> next c1;
> ID
> -----------
> 1
> ij> commit;
> ij> update t set id=id+1000 where current of c1;
> 1 row inserted/updated/deleted
> ij> commit;
> ij> update t set id=id+1000 where current of c1;
> 1 row inserted/updated/deleted
> ij>
> The expected behavior is that the UPDATE .. WHERE CURRENT OF fails with:
> ERROR 24000: Invalid cursor state - no current row.
> The problem applies to both scrollable and forward-only updatable cursors.
> Note: If the table is created *without* the index, I do see the expected behavior.
> I am marking this as a regression, since this issue is not reproducible on older, released versions of derby (I have tested with 10.1.2.1).
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira