Hi Michael,
Thanks for the additional investigation. It looks like there are some
cases where the duplicated tables cause data issues (rather than just
performance issues). We will make this issue a priority.
-Tom
Michael Bouschen wrote:
>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
>
>
>
>
>
--
Tom Ware
Principal Software Engineer
Oracle Canada Inc.
Direct: (613) 783-4598
Email: tom.ware_at_oracle.com