Hi Thomas Did you already have time to run the test case above? What's your opinion?
Thanks, Remo On Feb 11, 9:30 am, Remo <[email protected]> wrote: > 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.
