On 6/18/07, Bryan Pendleton <bpendleton@amberpoint.com> wrote:
> Does this make any sense to anyone? I don't see how adding an ORDER BY
> clause should change the *number* of results I get.

I don't see how it should, either.

It would be wonderful if you could construct a simple standalone
reproduction of the problem that you could share with the list.

OK. Here's a stripped down test case that fails with the ORDER BY clause and passes without it. Change the string "SELECT root_URL FROM foo ORDER BY id" to ""SELECT root_URL FROM foo" to make it pass.

import java.sql.*;
import java.util.Properties;

import junit.framework.TestCase;

public class Derbytest extends TestCase {

    private ConfigManager manager;
    private final static String driver = " org.apache.derby.jdbc.EmbeddedDriver";
    private final static String url = "jdbc:derby:derbyDB;";
    private Properties properties = new Properties();   
    protected void setUp() throws Exception {
        // Load JDBC driver
        Connection conn = DriverManager.getConnection(url + "create=true", properties);
        // TODO We need to refactor this out into a separate setup in an Ant SQL task
        Statement s = conn.createStatement();
        try {
            s.execute("DROP TABLE foo");
        catch (SQLException ex) { // table doesn't already exist
            if (!ex.getSQLState().equals("42Y55")) {
                throw ex;
        s.execute("CREATE TABLE foo " +
                "(id int NOT NULL, " +
                "root_URL VARCHAR(255) NOT NULL)");
        manager = new ConfigManager(url, properties);
    protected void tearDown() throws Exception {
        try {
        catch (SQLException expected) {
             if (expected.getErrorCode() != 45000) throw expected;
    public void testGetOne() throws SQLException {
        Connection conn = DriverManager.getConnection(url, properties);
        Statement s = conn.createStatement ();
        s.execute("INSERT INTO foo (id, root_url) VALUES (1, 'http://example.com/')");
        int numberBoards = manager.getCount();
        assertEquals(1, numberBoards);
    class ConfigManager {
        private Connection conn;
        private ResultSet results;
         * @param url JDBC connection string
         * @param properties JDBC properties
         * @throws SQLException if the database connection fails
        public ConfigManager(String url, Properties properties) throws SQLException {
            conn = DriverManager.getConnection (url, properties);
            Statement statement = conn.createStatement(
            // 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();

         * @throws SQLException
        public int getCount() throws SQLException {
            return results.getRow();





Elliotte Rusty Harold