Hi Thomas All of your criteria are fulfilled. There's no (obvious) change in the db between two runs. I realized that the issue cannot be reliably reproduced with just a few records. That's why my test case is a little voluminous. Watch the result's order carefully, only a few records change their order. Thanks!
DROP TABLE NOON_REPORT_FIELD IF EXISTS; CREATE TABLE NOON_REPORT_FIELD ( NOON_REPORT_FIELD_ID INTEGER NOT NULL, TYPE INTEGER DEFAULT 0 NOT NULL, CHOICE_CODE_GROUP INTEGER, LABEL VARCHAR DEFAULT '' NOT NULL, DESCRIPTION VARCHAR, IS_DELETED SMALLINT DEFAULT 0 NOT NULL, FIELD_GROUP_ID INTEGER, SORT_POSITION INTEGER, PRIMARY KEY (NOON_REPORT_FIELD_ID) ); DROP TABLE NOON_REPORT_FIELD_INST IF EXISTS; CREATE TABLE NOON_REPORT_FIELD_INST ( INSTALLATION_ID INTEGER DEFAULT 0 NOT NULL, NOON_REPORT_FIELD_ID INTEGER DEFAULT 0 NOT NULL , PRIMARY KEY (INSTALLATION_ID,NOON_REPORT_FIELD_ID) ); INSERT INTO NOON_REPORT_FIELD(NOON_REPORT_FIELD_ID,TYPE,CHOICE_CODE_GROUP,LABEL,DESCRIPTION,IS_DELETED,FIELD_GROUP_ID) VALUES (1,4,null,'Date [UTC]',null,0,null); INSERT INTO NOON_REPORT_FIELD(NOON_REPORT_FIELD_ID,TYPE,CHOICE_CODE_GROUP,LABEL,DESCRIPTION,IS_DELETED,FIELD_GROUP_ID) VALUES (2,0,null,'Time [UTC]',null,0,null); INSERT INTO NOON_REPORT_FIELD(NOON_REPORT_FIELD_ID,TYPE,CHOICE_CODE_GROUP,LABEL,DESCRIPTION,IS_DELETED,FIELD_GROUP_ID) VALUES (3,0,null,'Ship Pos. Lat.','Ships Position Latitude',0,null); INSERT INTO NOON_REPORT_FIELD(NOON_REPORT_FIELD_ID,TYPE,CHOICE_CODE_GROUP,LABEL,DESCRIPTION,IS_DELETED,FIELD_GROUP_ID) VALUES (4,0,null,'Ship Pos. Long.','Ships Position Longitude',0,null); INSERT INTO NOON_REPORT_FIELD(NOON_REPORT_FIELD_ID,TYPE,CHOICE_CODE_GROUP,LABEL,DESCRIPTION,IS_DELETED,FIELD_GROUP_ID) VALUES (5,0,null,'Next Port',null,0,null); INSERT INTO NOON_REPORT_FIELD(NOON_REPORT_FIELD_ID,TYPE,CHOICE_CODE_GROUP,LABEL,DESCRIPTION,IS_DELETED,FIELD_GROUP_ID) VALUES (6,4,null,'EDA next Port','Estimated Date of Arrival at next Port',0,null); INSERT INTO NOON_REPORT_FIELD(NOON_REPORT_FIELD_ID,TYPE,CHOICE_CODE_GROUP,LABEL,DESCRIPTION,IS_DELETED,FIELD_GROUP_ID) VALUES (7,0,null,'ETA next Port','Estimated Time of Arrival at next Port',0,null); INSERT INTO NOON_REPORT_FIELD(NOON_REPORT_FIELD_ID,TYPE,CHOICE_CODE_GROUP,LABEL,DESCRIPTION,IS_DELETED,FIELD_GROUP_ID) VALUES (8,2,null,'Distance to Go [NM]','Distance to the next Port', 0,null); INSERT INTO NOON_REPORT_FIELD(NOON_REPORT_FIELD_ID,TYPE,CHOICE_CODE_GROUP,LABEL,DESCRIPTION,IS_DELETED,FIELD_GROUP_ID) VALUES (9,2,null,'Current Speed (SOG) [kt]','Current Speed over Ground',0,null); INSERT INTO NOON_REPORT_FIELD(NOON_REPORT_FIELD_ID,TYPE,CHOICE_CODE_GROUP,LABEL,DESCRIPTION,IS_DELETED,FIELD_GROUP_ID) VALUES (10,2,null,'Current Speed (STW) [kt]','Current Speed through Water',0,null); INSERT INTO NOON_REPORT_FIELD(NOON_REPORT_FIELD_ID,TYPE,CHOICE_CODE_GROUP,LABEL,DESCRIPTION,IS_DELETED,FIELD_GROUP_ID) VALUES (11,2,null,'Req. Speed for ETA [kt]','Required Speed to reach Port at ETA',0,null); INSERT INTO NOON_REPORT_FIELD(NOON_REPORT_FIELD_ID,TYPE,CHOICE_CODE_GROUP,LABEL,DESCRIPTION,IS_DELETED,FIELD_GROUP_ID) VALUES (12,2,null,'Average Speed (SOG) [kt]','Average Speed Noon/Noon Over Ground',0,null); INSERT INTO NOON_REPORT_FIELD(NOON_REPORT_FIELD_ID,TYPE,CHOICE_CODE_GROUP,LABEL,DESCRIPTION,IS_DELETED,FIELD_GROUP_ID) VALUES (13,2,null,'Average Speed (STW) [kt]','Average Speed Noon/Noon through Water',0,null); INSERT INTO NOON_REPORT_FIELD(NOON_REPORT_FIELD_ID,TYPE,CHOICE_CODE_GROUP,LABEL,DESCRIPTION,IS_DELETED,FIELD_GROUP_ID) VALUES (14,2,null,'Draft fwd [m]',null,0,null); INSERT INTO NOON_REPORT_FIELD(NOON_REPORT_FIELD_ID,TYPE,CHOICE_CODE_GROUP,LABEL,DESCRIPTION,IS_DELETED,FIELD_GROUP_ID) VALUES (15,2,null,'Draft aft [m]',null,0,null); INSERT INTO NOON_REPORT_FIELD(NOON_REPORT_FIELD_ID,TYPE,CHOICE_CODE_GROUP,LABEL,DESCRIPTION,IS_DELETED,FIELD_GROUP_ID) VALUES (16,2,null,'ROB LSFO [mt]','ROB Low Sulphur Fuel Oil',0,null); INSERT INTO NOON_REPORT_FIELD(NOON_REPORT_FIELD_ID,TYPE,CHOICE_CODE_GROUP,LABEL,DESCRIPTION,IS_DELETED,FIELD_GROUP_ID) VALUES (17,2,null,'ROB HSFO [mt]','ROB High Sulphur Fuel Oil',0,null); INSERT INTO NOON_REPORT_FIELD(NOON_REPORT_FIELD_ID,TYPE,CHOICE_CODE_GROUP,LABEL,DESCRIPTION,IS_DELETED,FIELD_GROUP_ID) VALUES (18,2,null,'ROB MDO [mt]','ROB Marine Diesel Oil',0,null); INSERT INTO NOON_REPORT_FIELD(NOON_REPORT_FIELD_ID,TYPE,CHOICE_CODE_GROUP,LABEL,DESCRIPTION,IS_DELETED,FIELD_GROUP_ID) VALUES (19,2,null,'Current Main Engine Speed [rpm]',null,0,null); INSERT INTO NOON_REPORT_FIELD(NOON_REPORT_FIELD_ID,TYPE,CHOICE_CODE_GROUP,LABEL,DESCRIPTION,IS_DELETED,FIELD_GROUP_ID) VALUES (20,2,null,'Average Main Engine Speed [rpm]','Average Engine Speed Noon/Noon',0,null); INSERT INTO NOON_REPORT_FIELD_INST(INSTALLATION_ID,NOON_REPORT_FIELD_ID) VALUES (85268,1); INSERT INTO NOON_REPORT_FIELD_INST(INSTALLATION_ID,NOON_REPORT_FIELD_ID) VALUES (85268,2); INSERT INTO NOON_REPORT_FIELD_INST(INSTALLATION_ID,NOON_REPORT_FIELD_ID) VALUES (85268,3); INSERT INTO NOON_REPORT_FIELD_INST(INSTALLATION_ID,NOON_REPORT_FIELD_ID) VALUES (85268,4); INSERT INTO NOON_REPORT_FIELD_INST(INSTALLATION_ID,NOON_REPORT_FIELD_ID) VALUES (85268,5); INSERT INTO NOON_REPORT_FIELD_INST(INSTALLATION_ID,NOON_REPORT_FIELD_ID) VALUES (85268,6); INSERT INTO NOON_REPORT_FIELD_INST(INSTALLATION_ID,NOON_REPORT_FIELD_ID) VALUES (85268,7); INSERT INTO NOON_REPORT_FIELD_INST(INSTALLATION_ID,NOON_REPORT_FIELD_ID) VALUES (85268,8); INSERT INTO NOON_REPORT_FIELD_INST(INSTALLATION_ID,NOON_REPORT_FIELD_ID) VALUES (85268,9); INSERT INTO NOON_REPORT_FIELD_INST(INSTALLATION_ID,NOON_REPORT_FIELD_ID) VALUES (85268,10); INSERT INTO NOON_REPORT_FIELD_INST(INSTALLATION_ID,NOON_REPORT_FIELD_ID) VALUES (85268,11); INSERT INTO NOON_REPORT_FIELD_INST(INSTALLATION_ID,NOON_REPORT_FIELD_ID) VALUES (85268,12); INSERT INTO NOON_REPORT_FIELD_INST(INSTALLATION_ID,NOON_REPORT_FIELD_ID) VALUES (85268,13); INSERT INTO NOON_REPORT_FIELD_INST(INSTALLATION_ID,NOON_REPORT_FIELD_ID) VALUES (85268,14); INSERT INTO NOON_REPORT_FIELD_INST(INSTALLATION_ID,NOON_REPORT_FIELD_ID) VALUES (85268,15); INSERT INTO NOON_REPORT_FIELD_INST(INSTALLATION_ID,NOON_REPORT_FIELD_ID) VALUES (85268,16); INSERT INTO NOON_REPORT_FIELD_INST(INSTALLATION_ID,NOON_REPORT_FIELD_ID) VALUES (85268,17); INSERT INTO NOON_REPORT_FIELD_INST(INSTALLATION_ID,NOON_REPORT_FIELD_ID) VALUES (85268,18); INSERT INTO NOON_REPORT_FIELD_INST(INSTALLATION_ID,NOON_REPORT_FIELD_ID) VALUES (85268,19); INSERT INTO NOON_REPORT_FIELD_INST(INSTALLATION_ID,NOON_REPORT_FIELD_ID) VALUES (85268,20); INSERT INTO NOON_REPORT_FIELD_INST(INSTALLATION_ID,NOON_REPORT_FIELD_ID) VALUES (85268,21); INSERT INTO NOON_REPORT_FIELD_INST(INSTALLATION_ID,NOON_REPORT_FIELD_ID) VALUES (85268,22); INSERT INTO NOON_REPORT_FIELD_INST(INSTALLATION_ID,NOON_REPORT_FIELD_ID) VALUES (85268,23); INSERT INTO NOON_REPORT_FIELD_INST(INSTALLATION_ID,NOON_REPORT_FIELD_ID) VALUES (85268,24); INSERT INTO NOON_REPORT_FIELD_INST(INSTALLATION_ID,NOON_REPORT_FIELD_ID) VALUES (85268,25); INSERT INTO NOON_REPORT_FIELD_INST(INSTALLATION_ID,NOON_REPORT_FIELD_ID) VALUES (85268,30); INSERT INTO NOON_REPORT_FIELD_INST(INSTALLATION_ID,NOON_REPORT_FIELD_ID) VALUES (85268,31); INSERT INTO NOON_REPORT_FIELD_INST(INSTALLATION_ID,NOON_REPORT_FIELD_ID) VALUES (85268,32); -- the test query SELECT NOON_REPORT_FIELD_INST.NOON_REPORT_FIELD_ID, NOON_REPORT_FIELD_INST.INSTALLATION_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; On Feb 10, 2:30 pm, Thomas Mueller <[email protected]> wrote: > Hi, > > > I expect the order to be deterministic. > > It is not a bug if it is non-deterministic, but I would expect that > the order stays if: > > - no rows were inserted or updated > - the query parameters are exactly the same > - ANALYZE was not run between running the queries > - the table structure didn't change > > Maybe I forgot something. There are many variables... If you have a > reproducible test case please post it. > > Regards, > Thomas -- 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.
