John English <john.fore...@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
