db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Knut Anders Hatlen <knut.hat...@oracle.com>
Subject Re: Peculiar sorting behaviour?
Date Tue, 02 Apr 2013 15:34:36 GMT
John English <john.foreign@gmail.com> writes:

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

Is the query used in the webapp identical to the query used when testing
it outside the webapp, or are there small differences, for example that
one uses parameters instead of string literals? The optimizer can make a
more informed decision when a string literal is used, so it doesn't
necessarily come up with the same query execution plan.

If the webapp uses a parameter for the user name, you can test that in
ij like this:

    prepare ps as 'SELECT tests.id,tests.item,title FROM tests,item_usage
       WHERE username=? AND item_usage.item=tests.item
       ORDER BY tests.item,title';

    execute ps using 'values ''X''';

My guess is that it's the sort avoidance code that somehow gets confused
into thinking the result is already in the correct order and that the
sorting could be skipped. Hope you manage to reproduce it so we can fix
it.

Thanks,

-- 
Knut Anders

Mime
View raw message