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)
requiredRowOrdering.sortNotNeeded();
else
requiredRowOrdering.sortNeeded();
}
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
or SCROLL_SENSITIVE).
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!
Army
--------
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
{
Class.forName("org.apache.derby.jdbc.EmbeddedDriver").newInstance();
Connection conn = DriverManager.getConnection(
"jdbc:derby:dbdb;create=true");
/* Note that default Statement returns ResultSets which are
* TYPE_FORWARD_ONLY and CONCUR_READ_ONLY.
*/
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);
conn.close();
}
private void runQuery(String index, Statement st, Connection conn)
throws SQLException
{
conn.setAutoCommit(false);
ResultSet rs = st.executeQuery(
"select c2, c3 from str --DERBY-PROPERTIES index=" + index +
"\nwhere c1 = 1 order by c2");
rs.next();
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");
st2.close();
int row = 2;
while (rs.next())
{
System.out.println("Row " + (row++) + ": " +
rs.getInt(1) + ", " + rs.getInt(2));
}
rs.close();
conn.rollback();
}
}
