openjpa-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Shelley <randomshel...@gmail.com>
Subject Re: SELECT DISTINCT returns duplicates
Date Tue, 11 Dec 2007 15:20:45 GMT
Thank you for your reply. Section 4.4.5.3 does not specify expected results
when using the DISTINCT keyword, however.  Regardless of the JOIN
specification, shouldn't the DISTINCT keyword be honored?

JSR 220 [4.8]: "The DISTINCT keyword is used to specify that duplicate
values must be eliminated from the query result. If DISTINCT is not
specified, duplicate values are not eliminated."


On Dec 10, 2007 5:04 PM, Craig L Russell <Craig.Russell@sun.com> wrote:

> Hi Shelley,
>
> I believe this behavior is spec compliant, although I agree --
> unexpected. Please see if this section of 4.4.5.3 Fetch Joins
> describes your scenario:
>
> <spec>
> SELECT d
> FROM Department d LEFT JOIN FETCH d.employees
> WHERE d.deptno = 1
>
> A fetch join has the same join semantics as the corresponding inner
> or outer join, except that the related objects specified on the right
> hand side of the join operation are not returned in the query result
> or otherwise referenced in the query. Hence, for example, if
> department1 has five employees, the above query returns five
> references to the department 1 entity.
> </spec>
>
> Does anyone know if there is a TCK test case for this?
>
> Craig
>
> On Dec 10, 2007, at 2:25 PM, Shelley wrote:
>
> > The JPA spec (4.8) indicates that duplicate values must be
> > eliminated from
> > the query result when the DISTINCT keyword is used; however, I've
> > run into
> > situations with OpenJPA where this does not seem to be working.
> > Using the
> > DISTINCT keyword on a SELECT JOIN query returns duplicate values.
> >
> > Here is a simple example that illustrates the problem. There are two
> > entities, one which defines a OneToMany relationship with the other
> > entity.
> >
> > @Entity(name = "ENTITY_ONE")
> > public class EntityOne {
> >
> >     @Id
> >     @TableGenerator(name = "ONE_GEN", pkColumnName = "GEN_NAME",
> > pkColumnValue = "ONE_GEN")
> >     @GeneratedValue(strategy = GenerationType.TABLE, generator =
> > "ONE_GEN")
> >     private Long id;
> >
> >     @OneToMany(cascade = CascadeType.ALL)
> >     private Set<EntityTwo> entityTwos;
> >
> >     public EntityOne() {
> >         this.entityTwos = new HashSet<EntityTwo>();
> >     }
> >
> >     public Set<EntityTwo> getEntityTwos() {
> >         return entityTwos;
> >     }
> >
> >     public Long getId() {
> >         return id;
> >     }
> > }
> >
> > @Entity(name = "ENTITY_TWO")
> > public class EntityTwo {
> >
> >     @Id
> >     @TableGenerator(name = "TWO_GEN", pkColumnName = "GEN_NAME",
> > pkColumnValue = "TWO_GEN")
> >     @GeneratedValue(strategy = GenerationType.TABLE, generator =
> > "TWO_GEN")
> >     private Long id;
> >
> >     private String text;
> >
> >     public EntityTwo() {
> >     }
> >
> >     public EntityTwo(String text) {
> >         this.text = text;
> >     }
> >
> >     public String getText() {
> >         return text;
> >     }
> >
> >     public Long getId() {
> >         return id;
> >     }
> >
> > }
> >
> > Two entity instances are persisted (eOne1 and eOne2):
> >
> >     EntityOne eOne1 = new EntityOne();
> >     Set<EntityTwo> eTwos1 = eOne1.getEntityTwos();
> >     eTwos1.add(new EntityTwo("test1"));
> >     eTwos1.add(new EntityTwo("test2"));
> >
> >     EntityOne eOne2 = new EntityOne();
> >     Set<EntityTwo> eTwos2 = eOne2.getEntityTwos();
> >     eTwos2.add(new EntityTwo("test1"));
> >     eTwos2.add(new EntityTwo("test3"));
> >
> > When the following EJB Query is executed:
> >    "SELECT DISTINCT e FROM ENTITY_ONE e LEFT JOIN FETCH
> > e.entityTwos WHERE
> > e.id IN (" + eOne1.getId() + "," + eOne2.getId() + ")"
> >
> > Four results are returned in the ResultList, even though there are
> > only two
> > distinct results. For example:
> >   [id=200] com.test.EntityOne@23d08b
> >   [id=200] com.test.EntityOne@23d08b
> >   [id=201] com.test.EntityOne@1a32ea4
> >   [id=201] com.test.EntityOne@1a32ea4
> >
> > This is likely occurring because a result is being returned for
> > each unique
> > EntityOne + EntityTwo; however, this seems to violate the spec. Is
> > there
> > something incorrect in my code or is this a bug?
> >
> > (Note: If I run this same code using a different persistence
> > provider, such
> > as TopLink or Hibernate, only 2 results are returned.)
>
> Craig Russell
> Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
> 408 276-5638 mailto:Craig.Russell@sun.com
> P.S. A good JDO? O, Gasp!
>
>

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message