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/
