db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Bryan Pendleton <bpendleton.de...@gmail.com>
Subject Re: Peculiar sorting behaviour?
Date Wed, 03 Apr 2013 01:11:43 GMT
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


Mime
View raw message