Re: strange query resultset

[Stephen Cameron]

I do think this is a bug, so will try to provide a test case that
demonstrates its occurence.

On Fri, Jul 1, 2016 at 10:29 PM, Stephen Cameron  wrote:

> Hi Oscar,
>
> Yes that suggestion has worked, thanks very much.
>
> It's somewhat disturbing the results I was seeing never-the-less, that
> some results in the list could be replaced, apparently, with others. It was
> more than just a case of ordering, which I assumed was being handled by the
> database view.
>
> Regards
> Steve
>
> Steve
>
> On Fri, Jul 1, 2016 at 9:09 PM, Óscar Bou - GOVERTIS 
> wrote:
>
>> Hi Stephen.
>>
>> HAve you properly implemented the Comparable interface?
>> Perhaps you’re inserting on a SortedSet or similar List structure.
>>
>> If so, some results could potentially be detected as the same of others
>> previously inserted on the SortedSet, hence not available.
>>
>> Cheers,
>>
>> Oscar
>>
>>
>> El 1 jul 2016, a las 12:44, Stephen Cameron 
>> escribió:
>>
>> 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 <
>> steve.cameron...@gmail.com>
>> wrote:
>>
>> Retricting the result set to only the 13th Jan gives an 'uncorrupted'
>> resultset
>>
>>List 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 > >
>> wrote:
>>
>> duly corrected.  thanks.
>>
>> On 1 July 2016 at 07:57, Martin Grigorov  wrote:
>>
>> Hi Dan,
>>
>> On Fri, Jul 1, 2016 at 8:19 AM, Dan Haywood <
>>
>> d...@haywood-associates.co.uk>
>>
>> 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 
>> 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

Re: strange query resultset

[Stephen Cameron]

Hi Oscar,

Yes that suggestion has worked, thanks very much.

It's somewhat disturbing the results I was seeing never-the-less, that some
results in the list could be replaced, apparently, with others. It was more
than just a case of ordering, which I assumed was being handled by the
database view.

Regards
Steve

Steve

On Fri, Jul 1, 2016 at 9:09 PM, Óscar Bou - GOVERTIS 
wrote:

> Hi Stephen.
>
> HAve you properly implemented the Comparable interface?
> Perhaps you’re inserting on a SortedSet or similar List structure.
>
> If so, some results could potentially be detected as the same of others
> previously inserted on the SortedSet, hence not available.
>
> Cheers,
>
> Oscar
>
>
> El 1 jul 2016, a las 12:44, Stephen Cameron 
> escribió:
>
> 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 <
> steve.cameron...@gmail.com>
> wrote:
>
> Retricting the result set to only the 13th Jan gives an 'uncorrupted'
> resultset
>
>List 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 
> wrote:
>
> duly corrected.  thanks.
>
> On 1 July 2016 at 07:57, Martin Grigorov  wrote:
>
> Hi Dan,
>
> On Fri, Jul 1, 2016 at 8:19 AM, Dan Haywood <
>
> d...@haywood-associates.co.uk>
>
> 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 
> 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
> 

Re: strange query resultset

[Óscar Bou - GOVERTIS]

Hi Stephen.

HAve you properly implemented the Comparable interface?
Perhaps you’re inserting on a SortedSet or similar List structure.

If so, some results could potentially be detected as the same of others 
previously inserted on the SortedSet, hence not available.

Cheers,

Oscar


> El 1 jul 2016, a las 12:44, Stephen Cameron  
> escribió:
> 
> 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 
> wrote:
> 
>> Retricting the result set to only the 13th Jan gives an 'uncorrupted'
>> resultset
>> 
>>List 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 
>> wrote:
>> 
>>> duly corrected.  thanks.
>>> 
>>> On 1 July 2016 at 07:57, Martin Grigorov  wrote:
>>> 
 Hi Dan,
 
 On Fri, Jul 1, 2016 at 8:19 AM, Dan Haywood <
>>> d...@haywood-associates.co.uk>
 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 
> 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
>> 
>> 
> 
 
>>> 

Re: strange query resultset

[Stephen Cameron]

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 
wrote:

> Retricting the result set to only the 13th Jan gives an 'uncorrupted'
> resultset
>
> List 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 
> wrote:
>
>> duly corrected.  thanks.
>>
>> On 1 July 2016 at 07:57, Martin Grigorov  wrote:
>>
>> > Hi Dan,
>> >
>> > On Fri, Jul 1, 2016 at 8:19 AM, Dan Haywood <
>> d...@haywood-associates.co.uk>
>> > 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 
>> > > 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
>> > > > 

Re: strange query resultset

[Stephen Cameron]

Retricting the result set to only the 13th Jan gives an 'uncorrupted'
resultset

List 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 
wrote:

> duly corrected.  thanks.
>
> On 1 July 2016 at 07:57, Martin Grigorov  wrote:
>
> > Hi Dan,
> >
> > On Fri, Jul 1, 2016 at 8:19 AM, Dan Haywood <
> d...@haywood-associates.co.uk>
> > 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 
> > > 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
> > > > 

Re: strange query resultset

[Dan Haywood]

duly corrected.  thanks.

On 1 July 2016 at 07:57, Martin Grigorov  wrote:

> Hi Dan,
>
> On Fri, Jul 1, 2016 at 8:19 AM, Dan Haywood 
> 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 
> > 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
> 

Re: strange query resultset

[Martin Grigorov]

Hi Dan,

On Fri, Jul 1, 2016 at 8:19 AM, Dan Haywood 
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 
> 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 

Re: strange query resultset

[Stephen Cameron]

"you haven't included the mappings of the relevant classes, nor the JDOQL
for the query being invoked."

JDOQL?
SELECT 'au.com.scds.chats.dom.report.view.ActivityParticipantAttendance' AS
nucleus_type,a0.activityabbreviatedname,a0.activityname,a0.arrivingtransporttype,a0.attended,a0.birthdate,a0.departingtransporttype,a0.firstname,a0.minutesattended,a0.participantstatus,a0.regionname,a0.slk,a0.startdatetime,a0.surname
FROM activityparticipantattendance a0 WHERE a0.startdatetime >= <2016-01-01
00:00:00.0> AND a0.startdatetime < <2016-02-01 00:00:00.0> AND a0.attended
=  AND a0.regionname = <'SOUTH'>

class

https://github.com/Stephen-Cameron-Data-Services/isis-chats/blob/master/reports/src/main/java/au/com/scds/chats/dom/report/view/ActivityParticipantAttendance.java

On Fri, Jul 1, 2016 at 4:30 PM, Stephen Cameron 
wrote:

> Thanks Dan,
>
> I'll try a few of your suggestions, been meaning to try the type-safe
> queries.
>
>
>
>
>
>
> On Fri, Jul 1, 2016 at 4:19 PM, Dan Haywood 
> 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
>> 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 
>> 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
>> >
>> >
>> 

Re: strange query resultset

[Stephen Cameron]

Thanks Dan,

I'll try a few of your suggestions, been meaning to try the type-safe
queries.






On Fri, Jul 1, 2016 at 4:19 PM, Dan Haywood 
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
> 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 
> 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 

Re: strange query resultset

[Dan Haywood]

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  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 

strange query resultset

[Stephen Cameron]

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