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
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 <steve.cameron...@gmail.com> 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
>
