You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@openjpa.apache.org by Shelley <ra...@gmail.com> on 2007/12/10 23:25:57 UTC
SELECT DISTINCT returns duplicates
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.)
Re: SELECT DISTINCT returns duplicates
Posted by Shelley <ra...@gmail.com>.
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 <Cr...@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!
>
>
Re: SELECT DISTINCT returns duplicates
Posted by Craig L Russell <Cr...@Sun.COM>.
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!