db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Bryan Pendleton <bpendle...@amberpoint.com>
Subject Re: Adding order by clause eliminates results
Date Mon, 18 Jun 2007 18:53:28 GMT
Thanks for the repro! This is an interesting little situation.

I think that the basic issue here involves result set caching,
which is a feature that went into Derby in 10.2, I believe.

You can read more about that feature here:
http://issues.apache.org/jira/browse/DERBY-822

In your program, notice that you issue the SELECT statement
and open the result set *before* you have issued the INSERT.

Under certain circumstances, Derby will pre-fetch the rows of
the result set, and I believe that is what it is doing here.
The ORDER BY is altering whether or not the DERBY-822 pre-fetch
occurs or not, and thus is changing the results you see.

Knut Anders, if you see this, can you confirm that I'm on track here?

If you move the following lines from the ConfigManager constructor
down into the manager.getCount() method, you'll get the right
results, because the SELECT statement then gets actually *executed*
after the INSERT, rather than before it.

             Statement statement = conn.createStatement(
                     ResultSet.TYPE_SCROLL_INSENSITIVE,
                     ResultSet.CONCUR_READ_ONLY);
             // why does this return no results when we add an order by clause?
             statement.execute("SELECT root_URL FROM foo ORDER BY id");
             results = statement.getResultSet();

By the way, in case anyone else is interested in running this
test program, I think there is a tiny typo in the test program:

     private final static String driver = " org.apache.derby.jdbc.EmbeddedDriver";

There should not be a space at the start of that string.

thanks,

bryan


Mime
View raw message