persistence@glassfish.java.net

Re: EJBQL: OUTER JOIN issue

From: Michael Bouschen <Michael.Bouschen_at_Sun.COM>
Date: Wed, 25 Jan 2006 16:53:49 +0100

Hi Tom,

I'm sorry. I just realized I included the wrong EJBQL in my email. The
generated SQL is correct.

> Hi Tom,
>
> the following EJBQL query uses an identification variable in the SELECT
> clause that is defined in an OUTER JOIN clause. The generated SQL misses
> an outer join, but uses an inner join instead.
> EJBQL: SELECT c FROM Order o JOIN o.customer c

This should be:
EJBQL: SELECT c FROM Order o LEFT OUTER JOIN o.customer c

> SQL: SELECT t0.ID, ... FROM CUSTOMER_TABLE t0, ORDER_TABLE t1
> WHERE (t1.CUST_ID = t0.ID)
>
> When adding a WHERE clause that uses the same identification variable,
> we run into issue of duplicated tables in the generated SQL.
> EJBQL: SELECT c FROM Order o JOIN o.customer c WHERE c.customerId= 1

This should be:
EJBQL: SELECT c FROM Order o LEFT OUTER JOIN o.customer c
        WHERE c.customerId= 1

Regards Michael

> SQL: SELECT t0.ID, ...
> FROM CUSTOMER_TABLE t0, CUSTOMER_TABLE t2, ORDER_TABLE t1
> WHERE (((t1.CUST_ID = t0.ID) AND (t2.ID = 1)) AND
> (t2.ID (+) = t1.CUST_ID))
>
> I will file a issue in the glassfish issue tracker for the outer join
> and do some more testing on the second issue with the duplicated table
> before creating an issue.
>
> Regards Michael