Remo,
AFAIK, there is no formal requirement for an undefined sort to be
stable/deterministic across query executions. On occasion, I have
witnessed what you are describing, but I would not consider this a bug,
even though it may be different than other database implementations.
Regards,
Eric
Remo wrote:
Eric,
I completely agree that the order is not defined in my case. But in
accordance with other DBMS (HSQLDB, MySql) I ran the query, I expect
the order to be deterministic.
I tried to demonstrate the effect with a small test case, but did not
succeed, as it always worked as expected. So I still assume that a bug
causes this behavior in some rare cases.
Regards,
Remo
On Feb 8, 6:11 pm, Eric Faulhaber <[email protected]> wrote:
Remo,
If all of the records in the table have the same values for the
columns in your ORDER BY clause, you effectively are not telling the
database how you want your results to be sorted, so the order in which
you get your results back is undefined. There is no guarantee or
requirement that results will come back in the same order every time
you execute the same query, beyond what you specify in your ORDER BY
clause.
If you expect to see your results in the same order every time you run
your query, you will need to sort explicitly on some unique constraint
as the least significant criterion in your ORDER BY clause. If your
table has a primary key, that's a good candidate. The resulting sort
order may not have business meaning, but it will be stable as long as
records are not added, changed, or deleted from the table between
query runs.
Regards,
Eric Faulhaber
On Feb 8, 11:13 am, Remo <[email protected]> wrote:
I encountered a query where the sort order is correct, but not stable,
i.e. not every run of the query returns the rows in the same order.
Unfortunately, I was not able to create a simple test case. The query
is:
SELECT
NOON_REPORT_FIELD_INST.INSTALLATION_ID,
NOON_REPORT_FIELD_INST.NOON_REPORT_FIELD_ID,
NOON_REPORT_FIELD.SORT_POSITION
FROM NOON_REPORT_FIELD_INST
JOIN NOON_REPORT_FIELD ON NOON_REPORT_FIELD_INST.NOON_REPORT_FIELD_ID
= NOON_REPORT_FIELD.NOON_REPORT_FIELD_ID
ORDER BY NOON_REPORT_FIELD_INST.INSTALLATION_ID,
NOON_REPORT_FIELD.SORT_POSITION
It might help to know, that INSTALLATION_ID is the same for all rows
and SORT_POSITION is NULL for all rows.
I will provide the DB with data if necessary for further analysis.
Thanks,
Remo
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
