Return-Path: 
 <users-return-1147-apmail-openjpa-users-archive=openjpa.apache.org@openjpa.apache.org>
Delivered-To: apmail-openjpa-users-archive@locus.apache.org
Received: (qmail 94172 invoked from network); 11 Dec 2007 15:21:17 -0000
Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2)
  by minotaur.apache.org with SMTP; 11 Dec 2007 15:21:17 -0000
Received: (qmail 55999 invoked by uid 500); 11 Dec 2007 15:21:03 -0000
Delivered-To: apmail-openjpa-users-archive@openjpa.apache.org
Received: (qmail 55943 invoked by uid 500); 11 Dec 2007 15:21:03 -0000
Mailing-List: contact users-help@openjpa.apache.org; run by ezmlm
Precedence: bulk
List-Help: <mailto:users-help@openjpa.apache.org>
List-Unsubscribe: <mailto:users-unsubscribe@openjpa.apache.org>
List-Post: <mailto:users@openjpa.apache.org>
List-Id: <users.openjpa.apache.org>
Reply-To: users@openjpa.apache.org
Delivered-To: mailing list users@openjpa.apache.org
Received: (qmail 55913 invoked by uid 99); 11 Dec 2007 15:21:03 -0000
Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136)
    by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 11 Dec 2007 07:21:03 -0800
X-ASF-Spam-Status: No, hits=2.0 required=10.0
	tests=HTML_MESSAGE,NORMAL_HTTP_TO_IP,SPF_PASS
X-Spam-Check-By: apache.org
Received-SPF: pass (athena.apache.org: domain of randomshelley@gmail.com
 designates 209.85.198.191 as permitted sender)
Received: from [209.85.198.191] (HELO rv-out-0910.google.com) (209.85.198.191)
    by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 11 Dec 2007 15:20:42 +0000
Received: by rv-out-0910.google.com with SMTP id k20so2512646rvb
        for <users@openjpa.apache.org>; Tue, 11 Dec 2007 07:20:45 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed;
        d=gmail.com; s=gamma;
        h=domainkey-signature:received:received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references;
        bh=ZDZErlr+6LTi3qZGQeJ6Plw/y17TtPFvV16k+0b4Wuc=;
        b=q1QU8c7DAyNsTA01Lpm/kc7uTr8KXZ/IgC9nBxBWcpChEBt/+0nMtTafbk9buEZgqIbftG0q0DR/sv/yr/O6NBvm73XQ6jS2H0HPO1IQuxllKbq/3rQwX7PXRvlPIvQkKyebgUePak2ZMI69Xsz7oM3Nij2QRyad340fqbEyQhM=
DomainKey-Signature: a=rsa-sha1; c=nofws;
        d=gmail.com; s=gamma;
        h=message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references;
        b=e+khkdw1E01OcQJ6XBNrXbi/XBcSY0RpmVxtlJdrQSpwusQX+KUc6R8YkZGcFMgBZfDtsgDcl+TE031Gggc0yyo/2tarEI1Dhs6vTqUrUtFjBx6TGwjVFFNTT4FrqY+oUwUGTnVNTvNZ0fqnM6PCq4wqtIBX6P46QSc1wh/4Tk8=
Received: by 10.140.169.4 with SMTP id r4mr5060107rve.1197386445375;
        Tue, 11 Dec 2007 07:20:45 -0800 (PST)
Received: by 10.64.183.1 with HTTP; Tue, 11 Dec 2007 07:20:45 -0800 (PST)
Message-ID: <250fd9720712110720x6de29981l671f2a3303c377f5@mail.gmail.com>
Date: Tue, 11 Dec 2007 09:20:45 -0600
From: Shelley <randomshelley@gmail.com>
To: users@openjpa.apache.org
Subject: Re: SELECT DISTINCT returns duplicates
In-Reply-To: <638BF394-38BD-44FE-8EAD-3E717BFE29A1@SUN.com>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_42443_30723140.1197386445334"
References: <250fd9720712101425m6ccfeb33w3d480d0c9a3bc8df@mail.gmail.com>
	 <638BF394-38BD-44FE-8EAD-3E717BFE29A1@SUN.com>
X-Virus-Checked: Checked by ClamAV on apache.org

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

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_42443_30723140.1197386445334--