db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Army <qoz...@gmail.com>
Subject Re: Updating rows with an open cursor...what is the expected behavior?
Date Tue, 12 Jun 2007 19:08:38 GMT
Many thanks to Knut Anders and Mike for their respective replies.

mike> in the above case Derby figured out order by c2 could be done using
mike> index on (c1, c2) because c1 was fixed by where c1=1.  So I assume
mike> the query plan did sort elimination in favor of the index scan.

Yes, that's exactly correct.  The optimizer chose a "sort avoidance" plan 
because it noticed that the index had the correct ordering (and thus no sorting 
is required if we use the index).  Then in OptimizerImpl.rememberBestCost(...) 
we see that a sort avoidance plan was chosen, so we call "sortNotNeeded()" on 
the OrderByList, per:

	/* Remember if a sort is not needed for this plan */
	if (requiredRowOrdering != null)
		if (planType == Optimizer.SORT_AVOIDANCE_PLAN)

The call to "sortNotNeeded()" effectively means that we will call 
"markOrderingDependent()" on the IndexRowToBaseRowNode, which in turn means that 
the Index Scan will *not* bulk fetch rows; it will get them *one* at a time. 
This then correlates with Knut Anders observation:

knut> if you do an index scan, you automatically get the rows in the
knut> correct order and you only need one pass to return the results.
knut> When the first row is returned, the underlying scan has probably
knut> only seen that one row, so any updates to other rows will be seen.

So I think we have a pretty clear idea of *why* the updates are seen with an 
Index Scan (and not with a Table Scan)--which is great.

The remaining question is whether or not this is correct.  I'm hoping Knut 
Anders is right when he wrote:

knut> there's no guarantee (I think) as to when the rows are actually read.
knut> But I'll leave it to the scholars to find the chapter and verse in the
knut> appropriate spec... ;)

That's what I'm looking for :)  Note that I was able to reproduce this behavior 
with a JDBC program, inlined at the end of this email.  That program uses the 
Connection.createStatement() method, which means that result sets from that 
statement should default to TYPE_FORWARD_ONLY (as opposed to SCROLL_INSENSITIVE 

So it seems like the question is: should a "forward only" result set be 
sensitive to updates to rows that it hasn't read yet?  Or is this documented 
somewhere in JDBC as "not guaranteed" (or whatever the correct term is)?  Unless 
there is doc saying that such a thing leads to undefined behavior (per Knut 
Anders' suggestion), it seems odd (to me) that the "sensitivity" of a 
forward-only result set apparently depends on the underlying Derby scan type...

But for now this is a "good enough" answer.  I don't think this blocks 
DERBY-2805, it was just a question that came from investigation of that issue.

Thank you again to Mike and Knut Anders for the feedback; it's much appreciated!



JDBC repro for the original question (called "d2805" because that was the issue 
which prompted the question):

import java.sql.*;

public class d2805
     public static void main(String [] args)
         try {
             (new d2805()).go(args);
         } catch (Exception e) {
             System.out.println("-=- OOPS: " + e.getMessage());

         System.out.println("\n[ Done. ]\n");


     private void go(String [] args) throws Exception
         Connection conn = DriverManager.getConnection(

         /* Note that default Statement returns ResultSets which are
         Statement st = conn.createStatement();

         try {
             st.execute("drop table str");
         } catch (SQLException se) {}

         st.execute("create table str(c1 int, c2 int, c3 int)");
         st.execute("insert into str values (1, 1, 2), (1, 2, 3), " +
             "(1, 3, 4), (1, 4, 5)");
         st.execute("create index str1 on str(c1, c2)");

         System.out.println("\nRunning w/ Index Scan:\n");
         runQuery("str1", st, conn);

         System.out.println("\nRunning w/ Table Scan:\n");
         runQuery("null", st, conn);


     private void runQuery(String index, Statement st, Connection conn)
         throws SQLException
         ResultSet rs = st.executeQuery(
             "select c2, c3 from str --DERBY-PROPERTIES index=" + index +
             "\nwhere c1 = 1 order by c2");

         System.out.println("Row 1: " + rs.getInt(1) + ", " + rs.getInt(2));

         Statement st2 = conn.createStatement();
         st2.executeUpdate("update str set c2 = 4 where c2 = 2");

         int row = 2;
         while (rs.next())
             System.out.println("Row " + (row++) + ": " +
                 rs.getInt(1) + ", " + rs.getInt(2));


View raw message