Hi Tom,
Shelly filed an EJB QL issue about a JOIN FETCH query using DISTINCT in
the SELECT clause:
https://glassfish.dev.java.net/issues/show_bug.cgi?id=395
The EJB QL is:
SELECT DISTINCT c from Customer c LEFT JOIN FETCH c.orders where
c.home.state IN('NY','RI')
The SELECT clause of the generated SQL statements select customer
columns and columns from the fetched orders:
SELECT DISTINCT t0.ID, t0.NAME, t0.FK5_FOR_CUSTOMER_TABLE, t0.CODE,
t0.COUNTRY, t0.FK6_FOR_CUSTOMER_TABLE,
t1.ID, t1.TOTALPRICE, t1.FK0_FOR_LINEITEM_TABLE, t1.FK4_FOR_CUSTOMER_TABLE
In the SQL query the DISTINCT covers all the columns of the SELECT
clause. A particular customer is combined with all his orders which is
what JOIN FETCH is supposed to do. But this does not implement the
DISTINCT of the EJB QL query, because it covers only the customer. I
have the feeling we have to do the DISTINCT in memory after the SQL
result set is processed.
What do you think?
Regards Michael
--
Michael Bouschen Tech_at_Spree Engineering GmbH
mailto:mbo.tech@spree.de http://www.tech.spree.de/
Tel.:++49/30/235 520-33 Buelowstr. 66
Fax.:++49/30/2175 2012 D-10783 Berlin