db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Fernanda Pizzorno (JIRA)" <derby-...@db.apache.org>
Subject [jira] Commented: (DERBY-1087) Updatable result sets behave different depending on whether indexes are used or not
Date Wed, 08 Mar 2006 13:45:53 GMT
    [ 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


Mime
View raw message