[jira] Commented: (DERBY-1087) Updatable result sets behave different depending on the type of query used to generate the result set

["Fernanda Pizzorno (JIRA)" <de...@db.apache.org>]

    [ http://issues.apache.org/jira/browse/DERBY-1087?page=comments#action_12373119 ] 

Fernanda Pizzorno commented on DERBY-1087:
------------------------------------------

Proposed resolution to the two issues presented in my last comment:

1. Is actually the expected behavior in derby for forward-only updatable result sets in derby, so it is not a bug. I plan not to change this behavior.

2. I am considering to remove the optimization for covering indexes (that makes the results to be fetched directly from the index instead of the heap) in the case where the query produces an updatable result set, since the same problem will happen for both forward-only and scrollable updatable result sets. Does anybody see a problem with this?


Fernanda wrote:
____________________________________
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.

[...]
____________________________________


> Updatable result sets behave different depending on the type of query used to generate the result set
> -----------------------------------------------------------------------------------------------------
>
>          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