Hi Tom, Michael, Gordon,
>
>> I tried another experiment using the following EJBQL:
>> SELECT COUNT(o), COUNT(c) FROM Customer c JOIN c.orders o
>> The generated SQL uses two COUNT(*) in the SELECT clause:
>> SELECT COUNT(*), COUNT(*) FROM CMP3_ORDER t0, CMP3_CUSTOMER t1
>> WHERE (t0.CUST_ID = t1.CUST_ID)
>> I'm really not sure what the correct SQL should look like. I thought
>> we could do a COUNT on the primary key columns instead of the
>> COUNT(*), but this would not work in case of multiple primary key
>> columns.
>>
>>
> That's a really good test case. I think counting the primary key
> columns should be fine. For multiple primary key columns we can just
> pick one of them (since they are primary keys, none of them will be
> null).
>
You can not always just pick only one of the pk columns. If the query
includes DISTINCT and a navigation, e.g.
SELECT COUNT(DISTINCT (d.company)) from DEPARTMENT d WHERE d.DEPTID < 20
counting the primary key columns doesn't work. E.g. the following table
DEPARTMENT
DEPTID NAME COMPANYFOUNDED
1 C1 1998
2 C1 1997
COMPANY
NAME FOUNDED
C1 1998
C1 1997
Assuming that the COMPANY class has a composite PK (e.g. NAME, FOUNDED).
In the example above, the implementation can't pick the NAME column. In
order to satisfy the DISTINCT, you would have to generate something like
select DISTINCT(NAME, FOUNDED) FROM ... where ....
The above query can not directly be mapped to SQL, as SQL doesn't allow
applying DISTINCT to multiple columns. SJSAS8.x solves this problem by
generating a correlated exists query:
select count(*) from "COMPANY" t1 where exists (select * from
"DEPARTMENT" t0 where t0."COMPANYNAME" = t1."NAME" and
t0."COMPANYFOUNDED" = t1."FOUNDED" and t0."DEPTID" < 20)
What do you think?
Thanks,
-- markus.