The out of order results are actually valid results that exist elsewhere in the set of results, so it seems more likely some kind of reference issue. Does DN needs a primary key even on views to differentiate between entities?
On Fri, Jul 1, 2016 at 6:21 PM, Stephen Cameron <[email protected]> wrote: > Retricting the result set to only the 13th Jan gives an 'uncorrupted' > resultset > > List<ActivityParticipantAttendance> attendances = > repository.allMatches(new QueryDefault( > ActivityParticipantAttendance.class, > "allParticipantActivityForPeriodAndRegion", "startDateTime", > new DateTime("2016-01-13"), "endDateTime", new > DateTime("2016-01-14"), "attended", true, "region", this.regionName)); > > ArtGroup,2016-01-13T09:30:00.000+11:00,Rozynski,Noella,1933-12-25,240 > ArtGroup,2016-01-13T09:30:00.000+11:00,Huigsloot,Betty,1934-05-07,240 > ArtGroup,2016-01-13T09:30:00.000+11:00,Knight,Tony ,1943-01-09,210 > ArtGroup,2016-01-13T09:30:00.000+11:00,Blackley,Shirley,1937-12-07,240 > ArtGroup,2016-01-13T09:30:00.000+11:00,Wilson,Margaret,1931-04-11,210 > ArtGroup,2016-01-13T09:30:00.000+11:00,Johnson,Tara,1934-02-06,210 > ArtGroup,2016-01-13T09:30:00.000+11:00,Brownlow,Jacqueline,1950-07-22,210 > ArtGroup,2016-01-13T09:30:00.000+11:00,Cracknell,Anne,1936-08-09,210 > ArtGroup,2016-01-13T09:30:00.000+11:00,UNKNOWN113,UNKNOWN,2016-06-24,210 > > vs > > ArtGroup,2016-01-13T09:30:00.000+11:00,Rozynski,Noella,1933-12-25,240 > ArtGroup,2016-01-20T09:30:00.000+11:00,Huigsloot,Betty,1934-05-07,240 > ArtGroup,2016-01-27T09:30:00.000+11:00,Knight,Tony ,1943-01-09,210 > ArtGroup,2016-01-13T09:30:00.000+11:00,Blackley,Shirley,1937-12-07,240 > ArtGroup,2016-01-13T09:30:00.000+11:00,Wilson,Margaret,1931-04-11,210 > ArtGroup,2016-01-13T09:30:00.000+11:00,Johnson,Tara,1934-02-06,210 > ArtGroup,2016-01-27T09:30:00.000+11:00,Brownlow,Jacqueline,1950-07-22,210 > ArtGroup,2016-01-27T09:30:00.000+11:00,Cracknell,Anne,1936-08-09,210 > ArtGroup,2016-01-13T09:30:00.000+11:00,UNKNOWN113,UNKNOWN,2016-06-24,210 > > > > On Fri, Jul 1, 2016 at 4:59 PM, Dan Haywood <[email protected]> > wrote: > >> duly corrected. thanks. >> >> On 1 July 2016 at 07:57, Martin Grigorov <[email protected]> wrote: >> >> > Hi Dan, >> > >> > On Fri, Jul 1, 2016 at 8:19 AM, Dan Haywood < >> [email protected]> >> > wrote: >> > >> > > It's a bit hard to comment on this in isolation; you haven't included >> the >> > > mappings of the relevant classes, nor the JDOQL for the query being >> > > invoked. >> > > >> > > Perhaps you could use the logging jdbc driver (see >> persistor.properties) >> > to >> > > grab the SQL being submitted. >> > > >> > > Or, if you point at SQL Server (now on all platforms), then its >> Profiler >> > > >> > >> > "now on all platforms" is not correct :-) >> > "now" actually means ~2018 >> > "all platforms" means "Linux", but not OSX, BSD, Solaris. And even Linux >> > means just Ubuntu and RHEL >> > >> > I am interested to see whether they will provide the UI management >> tools as >> > well >> > >> > will let you grab the SQL from the server side. >> > > >> > > And finally, you might want to experiment with rewriting the query >> either >> > > using DN's own query API or using the type-safe "Q*" queries, and >> submit >> > > via IsisJdoSupport#getPersistenceManager(). >> > > >> > > Meanwhile, if you could provide a test case of some sort and describe >> the >> > > exact steps to reproduce the problem, I'll try to take a look over the >> > next >> > > day or two. >> > > >> > > Thx >> > > Dan >> > > >> > > PS: yes, I do agree that ORMs are great except when they aren't. >> > > >> > > >> > > >> > > On 1 July 2016 at 07:12, Stephen Cameron <[email protected]> >> > > wrote: >> > > >> > > > Hi, >> > > > >> > > > I have an ViewModel based on a database view. The view returns an >> > ordered >> > > > set of results, well it does at the database level. >> > > > >> > > > When I execute the same query via Isis and Datanucleus, I am seeing >> a >> > > kind >> > > > of corruption of the results where a small set of Date values is >> > > different >> > > > to what I see in the direct query on the view via MySQL Workbench. >> > > > >> > > > Below is some results from the code version to illustrate the >> problem: >> > > > >> > > > StyxValleyReserveFULL/clo,2016-01-11T09:00:00.000+11:00,Geard >> > > > ,Graeme,2016-06-24,540 >> > > > StyxValleyReserveFULL/clo,2016-01-11T09:00:00.000+11:00,Hamilton >> > > > ,Jane,1955-04-03,540 >> > > > >> > > > >> > > >> > >> StyxValleyReserveFULL/clo,2016-01-11T09:00:00.000+11:00,Turner,Kathleen,1928-06-10,540 >> > > > >> > > > >> > > >> > >> StyxValleyReserveFULL/clo,2016-01-11T09:00:00.000+11:00,Taylor,Rebel,1931-12-17,540 >> > > > >> > > > >> > > >> > >> StyxValleyReserveFULL/clo,2016-01-11T09:00:00.000+11:00,Jacques,Marlene,1943-11-16,540 >> > > > StyxValleyReserveFULL/clo,2016-01-11T09:00:00.000+11:00,Healy >> > > > ,Shirley,1936-03-14,540 >> > > > StyxValleyReserveFULL/clo,2016-01-11T09:00:00.000+11:00,Knight,Tony >> > > > ,1943-01-09,540 >> > > > StyxValleyReserveFULL/clo,2016-01-11T09:00:00.000+11:00,Van de >> > > > Vusse,Mathilda,1931-09-14,540 >> > > > >> > > > >> > > >> > >> StyxValleyReserveFULL/clo,2016-01-11T09:00:00.000+11:00,Johnston,Priscilla,1942-12-16,540 >> > > > >> ArtGroup,2016-01-13T09:30:00.000+11:00,Rozynski,Noella,1933-12-25,240 >> > > > >> ArtGroup,2016-01-20T09:30:00.000+11:00,Huigsloot,Betty,1934-05-07,240 >> > > > ArtGroup,2016-01-27T09:30:00.000+11:00,Knight,Tony ,1943-01-09,210 >> > > > >> ArtGroup,2016-01-13T09:30:00.000+11:00,Blackley,Shirley,1937-12-07,240 >> > > > >> ArtGroup,2016-01-13T09:30:00.000+11:00,Wilson,Margaret,1931-04-11,210 >> > > > ArtGroup,2016-01-13T09:30:00.000+11:00,Johnson,Tara,1934-02-06,210 >> > > > >> > >> ArtGroup,2016-01-27T09:30:00.000+11:00,Brownlow,Jacqueline,1950-07-22,210 >> > > > ArtGroup,2016-01-27T09:30:00.000+11:00,Cracknell,Anne,1936-08-09,210 >> > > > >> > ArtGroup,2016-01-13T09:30:00.000+11:00,UNKNOWN113,UNKNOWN,2016-06-24,210 >> > > > SouthArmPeninsulaBusTripF,2016-01-14T09:00:00.000+11:00,Houlgrave >> > > > ,Keryl,1945-10-02,480 >> > > > >> > > > >> > > >> > >> SouthArmPeninsulaBusTripF,2016-01-14T09:00:00.000+11:00,Butler,Steve,1957-04-06,480 >> > > > >> > > > >> > > >> > >> SouthArmPeninsulaBusTripF,2016-01-14T09:00:00.000+11:00,Smith,Joyce,1926-07-26,480 >> > > > >> > > > >> > > >> > >> SouthArmPeninsulaBusTripF,2016-01-14T09:00:00.000+11:00,Wagner,Lorelies,1937-11-12,480 >> > > > >> > > > >> > > >> > >> SouthArmPeninsulaBusTripF,2016-01-14T09:00:00.000+11:00,Sargent,Merle,1935-10-12,480 >> > > > >> > > > >> > > >> > >> SouthArmPeninsulaBusTripF,2016-01-14T09:00:00.000+11:00,Morice,Ann,1940-04-10,480 >> > > > >> > > > >> > > >> > >> SouthArmPeninsulaBusTripF,2016-01-14T09:00:00.000+11:00,Evans,Jean,1927-12-22,480 >> > > > >> > > > >> > > >> > >> SouthArmPeninsulaBusTripF,2016-01-14T09:00:00.000+11:00,Klein,Helga,1938-09-05,480 >> > > > SouthArmPeninsulaBusTripF,2016-01-14T09:00:00.000+11:00,Klein,Bill >> > > > (Helmut),1938-08-25,480 >> > > > >> > > > >> > > >> > >> SouthArmPeninsulaBusTripF,2016-01-14T09:00:00.000+11:00,Scheepers,Froukje,1934-05-22,480 >> > > > >> > > > >> > > >> > >> SouthArmPeninsulaBusTripF,2016-01-14T09:00:00.000+11:00,Brown,Derek,1936-04-01,480 >> > > > >> > > > The same set of results via query >> > > > >> > > > StyxValleyReserveFULL/clo 2016-01-11 09:00:00 Geard Graeme >> 2016-06-24 >> > 540 >> > > > StyxValleyReserveFULL/clo 2016-01-11 09:00:00 Hamilton Jane >> 1955-04-03 >> > > 540 >> > > > StyxValleyReserveFULL/clo 2016-01-11 09:00:00 Turner Kathleen >> > 1928-06-10 >> > > > 540 >> > > > StyxValleyReserveFULL/clo 2016-01-11 09:00:00 Taylor Rebel >> 1931-12-17 >> > 540 >> > > > StyxValleyReserveFULL/clo 2016-01-11 09:00:00 Jacques Marlene >> > 1943-11-16 >> > > > 540 >> > > > StyxValleyReserveFULL/clo 2016-01-11 09:00:00 Healy Shirley >> 1936-03-14 >> > > 540 >> > > > StyxValleyReserveFULL/clo 2016-01-11 09:00:00 Knight Tony 1943-01-09 >> > 540 >> > > > StyxValleyReserveFULL/clo 2016-01-11 09:00:00 Van de Vusse Mathilda >> > > > 1931-09-14 540 >> > > > StyxValleyReserveFULL/clo 2016-01-11 09:00:00 Johnston Priscilla >> > > 1942-12-16 >> > > > 540 >> > > > ArtGroup 2016-01-13 09:30:00 Rozynski Noella 1933-12-25 240 >> > > > ArtGroup 2016-01-13 09:30:00 Huigsloot Betty 1934-05-07 240 >> > > > ArtGroup 2016-01-13 09:30:00 Knight Tony 1943-01-09 210 >> > > > ArtGroup 2016-01-13 09:30:00 Blackley Shirley 1937-12-07 240 >> > > > ArtGroup 2016-01-13 09:30:00 Wilson Margaret 1931-04-11 210 >> > > > ArtGroup 2016-01-13 09:30:00 Johnson Tara 1934-02-06 210 >> > > > ArtGroup 2016-01-13 09:30:00 Brownlow Jacqueline 1950-07-22 210 >> > > > ArtGroup 2016-01-13 09:30:00 Cracknell Anne 1936-08-09 210 >> > > > ArtGroup 2016-01-13 09:30:00 UNKNOWN113 UNKNOWN 2016-06-24 210 >> > > > SouthArmPeninsulaBusTripF 2016-01-14 09:00:00 Houlgrave Keryl >> > 1945-10-02 >> > > > 480 >> > > > SouthArmPeninsulaBusTripF 2016-01-14 09:00:00 Butler Steve >> 1957-04-06 >> > 480 >> > > > SouthArmPeninsulaBusTripF 2016-01-14 09:00:00 Smith Joyce 1926-07-26 >> > 480 >> > > > SouthArmPeninsulaBusTripF 2016-01-14 09:00:00 Wagner Lorelies >> > 1937-11-12 >> > > > 480 >> > > > SouthArmPeninsulaBusTripF 2016-01-14 09:00:00 Sargent Merle >> 1935-10-12 >> > > 480 >> > > > SouthArmPeninsulaBusTripF 2016-01-14 09:00:00 Morice Ann 1940-04-10 >> 480 >> > > > SouthArmPeninsulaBusTripF 2016-01-14 09:00:00 Evans Jean 1927-12-22 >> 480 >> > > > SouthArmPeninsulaBusTripF 2016-01-14 09:00:00 Klein Helga 1938-09-05 >> > 480 >> > > > SouthArmPeninsulaBusTripF 2016-01-14 09:00:00 Klein Bill (Helmut) >> > > > 1938-08-25 >> > > > 480 >> > > > SouthArmPeninsulaBusTripF 2016-01-14 09:00:00 Scheepers Froukje >> > > 1934-05-22 >> > > > 480 >> > > > SouthArmPeninsulaBusTripF 2016-01-14 09:00:00 Brown Derek 1936-04-01 >> > 480 >> > > > As you can see, the dates (second column) for the ArtGroup are >> > sometimes >> > > > wrong. >> > > > >> > > > This is the only date column in the view so I cannot have picked the >> > > wrong >> > > > one! >> > > > >> > > > The code that generates the results is as follows: >> > > > >> > > > List<ActivityParticipantAttendance> attendances = >> > > > repository.allMatches(new QueryDefault( >> > > > ActivityParticipantAttendance.class, >> > > > "allParticipantActivityForPeriodAndRegion", "startDateTime", >> > > > this.startDateTime, "endDateTime", this.endDateTime, >> > > > "attended", true, "region", this.regionName)); >> > > > for (ActivityParticipantAttendance attend : attendances) { >> > > > >> > > > >> > > > >> > > >> > >> System.out.print(attend.getActivityAbbreviatedName()+","+attend.getStartDateTime()+","); >> > > > System.out.print(attend.getSurname()+ "," + >> attend.getFirstName()+"," + >> > > > attend.getBirthDate()+","); >> > > > System.out.println(attend.getMinutesAttended()); >> > > > >> > > > ... >> > > > >> > > > } >> > > > >> > > > This is kind of bizarre you will agree. >> > > > >> > > > The joys of ORM continue. >> > > > >> > > > Having puzzled at this for most of today, any insights, or >> > > commiseration, >> > > > welcomed. >> > > > >> > > > Steve >> > > > >> > > >> > >> > >
