Left fetch join => distinct

[Jim Talbut <jt...@spudsoft.co.uk>]

Hi,

This is from the definition of a named query:

                query = "SELECT o from AssessmentImpl o "
                         + "left join fetch o.assessmentResults "
                         + "where "
                         + "o.assessmentKey.dateCreated > 
:outstandingLimit "
                         + "and o.pollResultsAttempts < :pollCountLimit "
                         + "and ("
                         + " o.resultsAvailable = true "
                         + " or o.status = AssessmentStatus.Pending "
                         + " or o.status = AssessmentStatus.InProgress "
                         + ")")

The assessmentResults field is a OneToMany join, usually fetched lazily.
For the purposes of this query I want to pull in all the 
assessmentResults so I want it eagerly and I added the "left join fetch" 
line.
Unfortunately this adds a DISTINCT to the SQL query and fails because 
one of the columns is a LOB.

Why does adding "left fetch join" make it DISTINCT?
And is there any way to avoid it?

Thanks.

Jim

Re: Left fetch join => distinct

[Rick Curtis <cu...@gmail.com>]

I don't have time to dig into this issue today, but I know there is quite a
bit of history on the mailing list[1] revolving around left fetch join and
distinct....

Thanks,
Rick

[1] http://openjpa.markmail.org/search/?q=left%20fetch%20join%20distinct


On Tue, Apr 29, 2014 at 11:51 AM, Jim Talbut <jt...@spudsoft.co.uk> wrote:

> Hi,
>
> This is from the definition of a named query:
>
>                query = "SELECT o from AssessmentImpl o "
>                         + "left join fetch o.assessmentResults "
>                         + "where "
>                         + "o.assessmentKey.dateCreated > :outstandingLimit
> "
>                         + "and o.pollResultsAttempts < :pollCountLimit "
>                         + "and ("
>                         + " o.resultsAvailable = true "
>                         + " or o.status = AssessmentStatus.Pending "
>                         + " or o.status = AssessmentStatus.InProgress "
>                         + ")")
>
> The assessmentResults field is a OneToMany join, usually fetched lazily.
> For the purposes of this query I want to pull in all the assessmentResults
> so I want it eagerly and I added the "left join fetch" line.
> Unfortunately this adds a DISTINCT to the SQL query and fails because one
> of the columns is a LOB.
>
> Why does adding "left fetch join" make it DISTINCT?
> And is there any way to avoid it?
>
> Thanks.
>
> Jim
>
>


-- 
*Rick Curtis*