db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From John English <john.fore...@gmail.com>
Subject Peculiar sorting behaviour?
Date Tue, 02 Apr 2013 09:39:25 GMT
I have a query that looks like this:

   SELECT tests.id,tests.item,title FROM tests,item_usage
   WHERE username='X' AND item_usage.item=tests.item
   ORDER BY tests.item,title

The item_usage table is defined like this:

   CREATE TABLE item_usage (
     username    VARCHAR(15)   NOT NULL,
     item        VARCHAR(15)   NOT NULL,
     value       SMALLINT      DEFAULT 0,
     CONSTRAINT item_usage_pk  PRIMARY KEY (username,item),
     CONSTRAINT item_usage_1   FOREIGN KEY (username)
                               REFERENCES users(username)
                               ON DELETE CASCADE,
     CONSTRAINT item_usage_2   FOREIGN KEY (item)
                               REFERENCES items(item)
                               ON DELETE CASCADE,
     CONSTRAINT item_usage_3   CHECK (value BETWEEN 0 AND 4)

If I run the query manually I get this, as expected:

   37  60001   Test 1
   42  60001   Test 2
   51  60001   Test 3
   17  61303   Test 2a
   16  61303   Test 2b
   7   7205731 Test 2a
   8   7205731 Test 2b

Now, this is actually part of a web app that should turn this into a list of 
options in a <select> item using the following code:

   while (query.next()) {
     println("<option value='" + query.getInt("id") + "'>"
             + encode(query.getString("item") + ": "
             + query.getString("title")) + "</option>");

What I actually get is this:

   <option value="17">61303: Test 2a</option>
   <option value="16">61303: Test 2b</option>
   <option value="7">7205731: Test 2a</option>
   <option value="8">7205731: Test 2b</option>
   <option value="37">60001: Test 1</option>
   <option value="42">60001: Test 2</option>
   <option value="51">60001: Test 3</option>

The results are sorted by item then by title, but the item order is the order in 
which they were originally inserted into the items table (where the item and 
item description are stored, referenced by item_usage.item) rather than by item 
code. If however I change the ORDER BY clause to sort by item_usage.item rather 
than tests.item, it works correctly, even though the two values are the same!

The same thing happens in another unrelated query involving item_usage, and the 
same workaround cures it.

I've tried without success to reproduce this behaviour in a simple example so 
that I could report it as a bug, but without success. It always works correctly 
except inside the webapp, so I'm completely baffled.

Can anyone suggest what might be going on here?

John English

View raw message