Eric, I don't know about such a requirement either, it's just what i am used to. It feels awkward in a client application, if the order of items changes with every refresh. I don't like to add artificial (=without a functional requirement) sorting to my queries just to inhibit the "toggling". Could maybe someone from the H2 developers comment on this?
Thanks, Remo On Feb 9, 4:22 pm, Eric Faulhaber <[email protected]> wrote: > 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.
