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] Updated: (DERBY-1087) Updatable result sets behave different depending on the type of query used to generate the result set
Date Tue, 02 May 2006 15:14:47 GMT
     [ http://issues.apache.org/jira/browse/DERBY-1087?page=all ]

Fernanda Pizzorno updated DERBY-1087:
-------------------------------------

    Attachment: derby-1087.diff
                derby-1087.stat

Since I have not seen any objections to the resolution I proposed on the 4th of April, I am
attaching a patch that removes 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. This patch also includes a new test for both forward-only and scrollable
insensitive updatable result sets with covering indexes.

This removes an optimization and is not an ideal solution. Someone later might be interested
in looking updatable result sets work with the optimization (fetching data only from the index
and not using the heap).

Fernanda wrote:
_______________________________
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?
_______________________________ 

> 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

>   Components: JDBC, SQL
>     Reporter: Fernanda Pizzorno
>     Assignee: Fernanda Pizzorno
>  Attachments: derby-1087.diff, derby-1087.stat
>
> 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