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.
