Re: [eclipselink-users] Difference in result from query in sql and eclip

[Leon Derks <leon.derks@xxxxxxxxxx>]

Hi

Everything seems to work fine, when I use the 1.0.1 release.
Thanks.

Leon

James Sutherland wrote:
> That is odd, the duplicates should be filtered when a distinct is used.  The
> SQL should also return the duplicates for any groups with multiple
> characteristics (EclipseLink should just filter the rows in memory when
> distinct is used), so that is odd as well.
>
> In your SQL do you execute the exact same SQL, or do you just select the
> name?  If you just select the name, then the duplicates will be filtered by
> the distinct, when the whole row is selected they will not.
>
> You could check the JPA Query's EclipseLink DatabaseQuery and check if
> shouldFilterDuplicates() has been set.  This should be true, which will
> cause the duplicates to be filtered.
>
> You may also wish to try the 1.0 or 1.0.1 release.
>
>
>
> Leon Derks-2 wrote:
>   
> > Hello
> >
> > In my java code I call the following dao method:
> > CharacteristicGroup group = 
> > characteristicDAO.findGroupWithCharacteristics(characteristic.getGroup().getId());
> > With is this named query: select distinct c from CharacteristicGroup c 
> > left join fetch c.characteristics where c.id = :characteristicGroupId.
> >
> > With FINE Logging I see that this query is executed.
> > SELECT DISTINCT t1.ID, t1.CODE, t1.NAME, t1.PROD_CAT, t1.SEQ_NR, t0.ID, 
> > t0.KEY, t0.CODE, t0.NAME, t0.UNIT_OF_MEASURE, t0.SEQ_NR, t0.GROUP_ID 
> > FROM PLI.PLI_CHARACTERISTICS t0, PLI.PLI_CHARACTERISTIC_GROUPS t1 WHERE 
> > ((t1.ID = ?) AND (t0.GROUP_ID (+) = t1.ID))
> >     bind => [168211]
> >
> > When I loop through the characteristics in the group. I get this result
> > ***Eclipelink result:***
> > model.Characteristic@7d3e09b1[ActTempPrt]seqnr: 0
> > model.Characteristic@7a95a1b5[DeltaTNom]seqnr: 4
> > model.Characteristic@dbf22a27[T-Storage]seqnr: 3
> > model.Characteristic@dbf22a27[T-Storage]seqnr: 3
> > model.Characteristic@7a95a1b5[DeltaTNom]seqnr: 4
> > model.Characteristic@ab905705[TCaseLife]seqnr: 6
> > model.Characteristic@9a2df565[TCaseMax]seqnr: 6
> > model.Characteristic@1714da44[T-Ambient]seqnr: 7
> >
> > As you can see  T-Storage and DeltTNom are in more than 1 time in the 
> > characteristics List.
> >
> > When I execute the sql in pl/sql developer I get the following result:
> > ***SQL Result:***
> > TCaseLife
> > FMarking
> > ActTempPrt
> > DeltaTNom
> > TCaseMax
> > T-Winding
> > T-Storage
> > T-Ambient
> >
> > A complete different result. Because no duplicate characteristics and 
> > for example T-Winding is in the sql result, but not in the eclipselink 
> > result.
> > The number of items are equal in both results
> >
> > I use Eclipselink M0.6.
> >
> > Does anyone has an explanation for this?
> >
> >     
>
>
> -----
> ---
> http://wiki.eclipse.org/User:James.sutherland.oracle.com James Sutherland 
> http://www.eclipse.org/eclipselink/
>  EclipseLink ,  http://www.oracle.com/technology/products/ias/toplink/
> TopLink 
> Wiki:  http://wiki.eclipse.org/EclipseLink EclipseLink , 
> http://wiki.oracle.com/page/TopLink TopLink 
> Forums:  http://forums.oracle.com/forums/forum.jspa?forumID=48 TopLink , 
> http://www.nabble.com/EclipseLink-f26430.html EclipseLink 
> Book:  http://en.wikibooks.org/wiki/Java_Persistence Java Persistence 
>