Adding order by clause eliminates results

[Elliotte Harold <er...@gmail.com>]

I have a simple Derby database I'm using for testing. The following query
gives me the results I expect:

        statement.execute("SELECT root_URL FROM b");

Usually that's just 2 rows depending on how I've set up the test.

However if I change it to this I get no results:

        statement.execute("SELECT root_URL FROM b ORDER BY id");

That is, the ResultSet now contains zero rows.

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've tried several forms
of statement and connection (though all from within JDBC, not yet ij or
something else; and all with Scrollable result sets) and it really does seem
that the only thing that matters is if I have an ORDER BY clause or not.

No ORDER BY clause: all expected results (in no particular order)
ORDER BY clause: no results.

No exceptions are thrown on evaluation. (I do get some later exceptions that
are a direct result of not having any results to work with.)

Can anyone explain or justify this behavior? Am I missing something obvious?
Or is this a real bug? Thanks.

-- 
Elliotte Rusty Harold
erharold@gmail.com

Re: Adding order by clause eliminates results

[Mike Matrigali <mi...@sbcglobal.net>]

you may want to look at this recent discussion about updating an
open cursor, in the development discussion group.

http://thread.gmane.org/gmane.comp.apache.db.derby.devel/44225/focus=44299


Elliotte Harold wrote:
> Interesting. That does indeed fix it, and it does make some sense.
> 
> What's really surprising here is that the SELECT before the INSERT ever 
> worked.
> 
> I'm still not sure if this qualifies as a Derby bug or not, but it does 
> feel weird that adding ORDER BY to the query can change the result set.
> 
> -- 
> Elliotte Rusty Harold
> erharold@gmail.com <ma...@gmail.com>

Re: Adding order by clause eliminates results

[Elliotte Harold <er...@gmail.com>]

Interesting. That does indeed fix it, and it does make some sense.

What's really surprising here is that the SELECT before the INSERT ever
worked.

I'm still not sure if this qualifies as a Derby bug or not, but it does feel
weird that adding ORDER BY to the query can change the result set.

-- 
Elliotte Rusty Harold
erharold@gmail.com

Re: Adding order by clause eliminates results

[Bryan Pendleton <bp...@amberpoint.com>]

> I think that the basic issue here involves result set caching,

Actually now I'm doubting myself, because DERBY-822 was a
*client* change, and this test program is for the *embedded* driver.

So the DERBY-822 comment is irrelevant, sorry about that.

Still, I think that the basic idea is the same: you are executing
the query and getting the result set *before* you have issued
the INSERT statement that populates the row, and it must be the
case that, depending on whether or not there is an ORDER BY clause,
the driver either computes the results of the query at the time
that you do statement.execute() and statement.getResultSet(), or
it computes the results of the query at the time that you do
results.last().

thanks,

bryan

Re: Adding order by clause eliminates results

[Bryan Pendleton <bp...@amberpoint.com>]

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

Re: Adding order by clause eliminates results

[Elliotte Harold <er...@gmail.com>]

On 6/18/07, Bryan Pendleton <bp...@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
        Class.forName(driver).newInstance();
        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)");
        conn.close();
        manager = new ConfigManager(url, properties);

        super.setUp();

    }


    protected void tearDown() throws Exception {
        try {
            DriverManager.getConnection("jdbc:derby:derbyDB;shutdown=true");
        }
        catch (SQLException expected) {
             if (expected.getErrorCode() != 45000) throw expected;
        }
        super.tearDown();
    }


    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(
                    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();
        }

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

    }

}

thanks,
>
> bryan
>
>
>


-- 
Elliotte Rusty Harold
erharold@gmail.com

Re: Adding order by clause eliminates results

[Bryan Pendleton <bp...@amberpoint.com>]

> 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.

thanks,

bryan