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 <jtal...@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*