Hi, I have a similar feeling with this behavior , but if some order has to be there we can set one like PKey ( not big deal ) .
If changes needed to make default ordering stable can affect performance negatively , my vote if for performance ! Regards, Dario El 09/02/10 13:20, Remo escribió: > 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 >>>>> > -- Saludos, Dario -- 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.
