Hi Tom,
here is some input for the issue with duplicated tables in the generated
SQL:
https://glassfish.dev.java.net/issues/show_bug.cgi?id=197
I added three cases below. I'm not sure whether they are all the same
issue, but at least they show the same symptom: some table is duplicated
in the SQL.
Case 1:
-------
JPQL: SELECT o FROM Customer c JOIN c.orders o
SQL : SELECT DISTINCT t0.ID, ...
FROM ORDER_TABLE t0, CUSTOMER_TABLE t2, ORDER_TABLE t1
WHERE ((t0.ID = t1.ID) AND (t1.CUST_ID = t2.ID))
The ORDER_TABLE is included twice, but since there is a foreign key join
the query result is ok.
I ran the following ReportQuery which generates exactly the same SQL:
ExpressionBuilder cBuilder = new ExpressionBuilder(Customer.class);
ExpressionBuilder oBuilder = new ExpressionBuilder(Order.class);
Expression expr = cBuilder.anyOf("orders").equal(oBuilder);
ReportQuery reportQuery = new ReportQuery(Customer.class, cBuilder);
reportQuery.dontMaintainCache();
reportQuery.setShouldReturnWithoutReportQueryResult(true);
reportQuery.setSelectionCriteria(expr);
reportQuery.addItem("o", oBuilder);
List result = (List)em.getActiveSession().executeQuery(reportQuery);
Case 2:
-------
JPQL: SELECT c FROM Customer c
WHERE c.customerId IN (SELECT o.orderId FROM c.orders o)
SQL: SELECT t0.ID, ... FROM CUSTOMER_TABLE t0
WHERE t0.ID IN
((SELECT DISTINCT t1.ID
FROM CUSTOMER_TABLE t2, ORDER_TABLE t1
WHERE (t1.CUST_ID = t2.ID)))
Please note that the subquery includes the CUSTOMER_TABLE in its FROM
clause where I think it should use the variable t0 defined in the outer
query. I'm not sure whether this affects the query result.
Case 3:
-------
JPQL: SELECT c FROM Order o JOIN o.customer c GROUP BY c
SQL: SELECT t0.ID, t0.NAME, ...
FROM CUSTOMER_TABLE t0, ORDER_TABLE t2, CUSTOMER_TABLE t1
WHERE ((t2.CUST_ID = t0.ID) AND (t1.ID (+) = t2.CUST_ID))
GROUP BY t1.ID, t1.NAME, ...
Please note, the SQL includes two variables for table CUSTOMER_TABLE: t0
and t1. The SELECT clause uses t0 and the GROUP BY clause uses the other
(t1). So the query runs into a SQLException: ORA-00979: not a GROUP BY
expression.
Regards Michael