db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "George S." <geor...@mhsoftware.com>
Subject Re: Peculiar sorting behaviour?
Date Tue, 02 Apr 2013 15:29:16 GMT
The most likely thing is that the query you're running in the web page 
is not what you think.


On 4/2/13 3:39 AM, John English wrote:
> 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:
>
>   ID  ITEM    TITLE
>   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?
>
> TIA,

-- 
George Sexton
MH Software, Inc.
303 438-9585
http://www.mhsoftware.com/


Mime
View raw message