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).
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!
- application/pkcs7-signature attachment: smime.p7s