Mailing-List: contact users-help@openjpa.apache.org; run by ezmlm
Precedence: bulk
Reply-To: users@openjpa.apache.org
Received-SPF: pass (athena.apache.org: domain of catalina.wei@gmail.com
 designates 64.233.178.247 as permitted sender)
DomainKey-Signature: a=rsa-sha1; c=nofws;
        d=gmail.com; s=gamma;
        h=message-id:date:from:to:subject:mime-version:content-type;
        b=VrBXUUD+y61ctFuQz549oFyDTqQMOc/Nzgd6ZjEgWw36+InxMDY6q7WHRjyUA3hMIzgPZSlzj6CaN5lKfTMOSGPf8xPP0vi1elRZlmFvlv4FTTWURtIX79F7gIaKPhSWpmgPPq35hG3bq4XLq2y1/tp/ivZCB6GGY8fbDqhspFI=
Message-ID: <f374e8990712121530j696a34a1kea200c33d8e16734@mail.gmail.com>
Date: Wed, 12 Dec 2007 15:30:32 -0800
From: "catalina wei" <catalina.wei@gmail.com>
To: users@openjpa.apache.org
Subject: SELECT DISTINCT ... JOIN FETCH returns duplicates
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_30702_22120176.1197502232106"

------=_Part_30702_22120176.1197502232106
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

Shelley,

This problem is a problem related to OPENJPA-135.

That issue reported that OpenJPA implementation not conforming to JPA spec
where duplicate is expected but OpenJPA is not

returning duplicates (for JOIN FETCH queries without DISTINCT keyword).

By SELECT *DISTINCT *.. JOIN Fetch, it is supposed to eliminate duplicates
while OpenJPA is doing exactly the opposite (not eliminating the
duplicates).

I will look into OPENJPA-135 as soon as I have a chance to work on it. (This
JIRA issue has been assigned  to me sometime back in Aug 2007.)

Catalina
==========================================================

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!
>
>

------=_Part_30702_22120176.1197502232106--