Hi Craig,
Craig L Russell wrote On 03/01/07 15:55,:
> Hi Michael,
>
> On Mar 1, 2007, at 11:56 AM, Michael Bouschen wrote:
>
>
>>Hi,
>>
>>I have a question about the SQL that should be generated for a a
>>JPQL query using a count aggregate in the SELECT clause that counts
>>a joined variable, e.g.:
>> SELECT c.name, COUNT(o) FROM Customer c LEFT JOIN c.orders o GROUP
>>BY c.name
>>So what is the right SQL for the JPQL COUNT expression?
>>
>>I think there are three cases and I need some help for case (3):
>>(1) The entity class Order has a single primary key. Then the SQL
>>should be "COUNT(o.PK)" where PK is the primary key column of the
>>order table.
>
>
> Right.
>
>
>>(2) Order has a composite primary key and the COUNT does not use
>>DISTINCT. Then the SQL can do a COUNT on an arbitrary primary key
>>column.
>
>
> Right.
>
>
>>(3) Order has a composite primary key and the JPQL query does a
>>COUNT(DISTINCT o)). I have no idea what exactly is the right SQL!
>
>
> It seems this is only an issue for many-many relationships, because
> otherwise in your example there is no difference between COUNT(o) and
> COUNT(DISTINCT o).
Hmmm... Order-lineItem is OneToMany with a duplicated part of the PK. Right?
thanks,
-marina
>
> I don't know the proper SQL for the many-many case. Maybe we should
> throw an exception during parsing to the effect "Cannot COUNT
> (DISTINCT) for ManyToMany joined compound primary key entities." If
> no one has a good idea how to implement this, and we know it's not a
> CTS issue, we should ask the Maintenance Lead if this could be
> documented as a JPA restriction.
>
> Craig
>
>>Any idea is appreciated!
>>
>>Regards Michael
>
>
> Craig Russell
> Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
> 408 276-5638 mailto:Craig.Russell_at_sun.com
> P.S. A good JDO? O, Gasp!
>