[ http://issues.apache.org/jira/browse/DERBY-1087?page=comments#action_12369446 ]
Fernanda Pizzorno commented on DERBY-1087: ------------------------------------------ I have tried without the index and it actually fails too when the query is "SELECT a, b FROM t1 WHERE a <= 5". So actually there are two different problems: 1. when you use "SELECT a, b FROM t1 WHERE a <= 5", the statement that updates the row will change the value of a so that it no longer qualifies (a is not <= 5) and that why you get the error that the cursor is not on a row. 2. when you use "SELECT a FROM t1", since all of the projected columns are covered by the index, the results are not fetched from the heap, the index is used instead. When the update statement changes the value of a, the entry for a is no longer in the same position in the b-tree, causing the error. I will make the title of the JIRA issue less specific so that it covers both cases. > Updatable result sets behave different depending on whether indexes are used > or not > ----------------------------------------------------------------------------------- > > Key: DERBY-1087 > URL: http://issues.apache.org/jira/browse/DERBY-1087 > Project: Derby > Type: Bug > Reporter: Fernanda Pizzorno > Assignee: Fernanda Pizzorno > > Running the following code with different queries in the first statement > produces different results. > Where t1 has two columns: "a" of type int is the primary key, and "b" of type > varchar(50); and contains 10 rows of data. > Statement st1 = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, > ResultSet.CONCUR_UPDATABLE); > Statement st2 = conn.createStatement(); > > ResultSet rs = st1.executeQuery("SELECT a, b FROM t1"); > rs.next(); > rs.next(); > st2.executeUpdate("UPDATE t1 SET a = a + 20 WHERE a = " + > rs.getInt(1)); > try { > rs.updateInt(1, rs.getInt(1) + 30); > rs.updateRow(); > } catch (SQLException se) { > System.out.println(se.getMessage()); > } > rs.close(); > > rs = st2.executeQuery("SELECT a FROM t1"); > while(rs.next()) { > System.out.println("A = " + rs.getInt(1)); > } > rs.close(); > > st2.close(); > st1.close(); > If the first query is "select a, b from t1", the output will be: > A = 1 > A = 3 > A = 4 > A = 5 > A = 6 > A = 7 > A = 8 > A = 9 > A = 10 > A = 32 > If the first query is "SELECT a, b FROM t1 WHERE a <= 5", the output will be: > Cursor 'SQLCUR0' is not on a row. > A = 1 > A = 3 > A = 4 > A = 5 > A = 6 > A = 7 > A = 8 > A = 9 > A = 10 > A = 22 > If the first query is "SELECT a FROM t1", the output will be: > Cursor 'SQLCUR0' is not on a row. > A = 1 > A = 3 > A = 4 > A = 5 > A = 6 > A = 7 > A = 8 > A = 9 > A = 10 > A = 22 -- 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
