@Thomas I do have an ORDER BY in my query. But due to equal values, there is not one single correct order. If the db engine uses stable sorting algorithms, the rows should be returned in the same order over multiple runs.
@Eric Right, I don't want to depend on a particular db vendor. I just experienced this effects with H2 for the first time and up to now assumed that it's a SQL standard to return a consistent ordering. Regards, Remo On Feb 9, 10:17 pm, Eric Faulhaber <[email protected]> wrote: > Remo, > > It seems you *do* have a functional requirement which justifies an SQL > change: you want results presented in a consistent order upon refresh. > > FWIW, my advice is to not allow your application to depend upon a > particular database implementation choice (which can vary from vendor to > vendor, and even version to version), whether or not you convince the H2 > developers to change this behavior. > > Regards, > Eric > > Remo wrote: > > 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.
