Return-Path: Delivered-To: apmail-db-derby-user-archive@www.apache.org Received: (qmail 97253 invoked from network); 18 Jun 2007 18:19:57 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2) by minotaur.apache.org with SMTP; 18 Jun 2007 18:19:57 -0000 Received: (qmail 87373 invoked by uid 500); 18 Jun 2007 18:19:59 -0000 Delivered-To: apmail-db-derby-user-archive@db.apache.org Received: (qmail 87356 invoked by uid 500); 18 Jun 2007 18:19:58 -0000 Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: List-Id: Reply-To: "Derby Discussion" Delivered-To: mailing list derby-user@db.apache.org Received: (qmail 87340 invoked by uid 99); 18 Jun 2007 18:19:58 -0000 Received: from herse.apache.org (HELO herse.apache.org) (140.211.11.133) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 18 Jun 2007 11:19:58 -0700 X-ASF-Spam-Status: No, hits=2.0 required=10.0 tests=HTML_MESSAGE,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (herse.apache.org: domain of erharold@gmail.com designates 64.233.166.176 as permitted sender) Received: from [64.233.166.176] (HELO py-out-1112.google.com) (64.233.166.176) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 18 Jun 2007 11:19:54 -0700 Received: by py-out-1112.google.com with SMTP id f31so3423492pyh for ; Mon, 18 Jun 2007 11:19:32 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references; b=l+4huUS3Ot4COWlVRTDsTq2vsBM58bHKjbkAm8+J6w5CArzodlWVMTJFGcK64k+mVh3an1K+JJSK7EAvUU9VA8JFG67lNNk9lN0pAfL23EdMuGYQxSW5A6lBcqfXQED/GNiMT3XfY6jMqYVIVkvMwiDfL3csQ0gMvynj1Z/R8Sk= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references; b=KW8AK90PTi9zGjtmBkrqW8Jx3CNCxuci/kp36ew7S/dHaZ3xC9XTtCHDuxi40FYEKn7v8tehxUVxjpPFXTukIRmAdIRmrvj4EsVk43woaPETsCeHmeTKoPEUhQAOuTJBaBpEeoDWYZNs0+ist8MPJWMNmvjePlDBy9/KrfRKZM4= Received: by 10.65.150.18 with SMTP id c18mr9889150qbo.1182190772131; Mon, 18 Jun 2007 11:19:32 -0700 (PDT) Received: by 10.64.204.18 with HTTP; Mon, 18 Jun 2007 11:19:31 -0700 (PDT) Message-ID: <49aa580c0706181119v4edb5b01g6d82a139521e4760@mail.gmail.com> Date: Mon, 18 Jun 2007 14:19:31 -0400 From: "Elliotte Harold" To: "Derby Discussion" Subject: Re: Adding order by clause eliminates results In-Reply-To: <4676C863.7000800@amberpoint.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_5450_27438343.1182190771945" References: <49aa580c0706181000s48788015qd820542cc88a5958@mail.gmail.com> <4676C863.7000800@amberpoint.com> X-Virus-Checked: Checked by ClamAV on apache.org ------=_Part_5450_27438343.1182190771945 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline On 6/18/07, Bryan Pendleton 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 ------=_Part_5450_27438343.1182190771945 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 7bit Content-Disposition: inline

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
        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 ------=_Part_5450_27438343.1182190771945--