Hi John, Here's my perspective on what you posted:
1) Anytime you issue a SELECT statement with an ORDER BY, and the rows don't come back in that order, that's a bug. As you point out, it would be best if you could narrow this down to a simple reproducible case when you report it. However, even if you can't, it's worth reporting the problem, together with whatever information you have. For instance, the query plan information that you posted is very valuable and may give enough clues to allow somebody else to reproduce the problem. When you do report the problem, it would be nice if you could clearly indicate which query plan is for the run which gets the results in the wrong order, and which is for the run with the right order. For example, upload the query plans as separate attachments with names that clearly indicate which is which. 2) I agree with Knut Anders's hunch that this seems like a "sort avoidance" bug. Normally, the query plan for a query with an ORDER BY clause has to include an explicit sort of the results to get them in the right order. However, sometimes the optimizer can determine that an earlier node in the query plan has guaranteed that it already has the results in the correct order, in which case the sort is unnecessary and will not be performed. Such a node would be a INDEX SCAN node, and as Mike observed there do appear to be such INDEX SCAN nodes in the query plan. It looks like there are index scans on both the ITEM_USAGE and TESTS tables, but since the ORDER BY clause specifies "tests.item,table" the index scan for ITEM_USAGE is presumably not relevant. 3) I see that your query plan output includes: Scroll Insensitive ResultSet: Number of opens = 1 Rows seen = 30 Number of reads from hash table = 30 Number of writes to hash table = 30 This is interesting, and makes me think that maybe you have some special connection settings which are causing the query plan to take all of your results (which might have been correctly ordered), and throw them into an in-memory hash table, which then causes the rows to become randomly ordered. Do you by any chance specify ResultSet.TYPE_SCROLL_INSENSITIVE, when you create the statement object in your JDBC code? If so, does the behavior change if you change this setting? Hope this helps, bryan
