Re: [eclipselink-dev] questions while running JPA JUnit tests

[Dies Koper <diesk@xxxxxxxxxxxxxxxxxxx>]

Hi Tom,

>   Does Symfoware support outer join in the Where clause?  We have 
> support for writing SQL statements in an alternate way for database 
> platforms that have that support.  Have a look at the 

No, only the LEFT/RIGHT (OUTER) JOIN syntax in the FROM clause is supported.

Do you think it could be safe to replace the inner join with an outer join?

I tried with a M:M test application and noticed that no matter the order 
of joining the tables in the left outer join of the JPQL query, the 
generated SQL query has the relationship join table on the left of the 
(inner) join:

SELECT t1.ID, t1.NAME, t1.SALARY
FROM   EMPLOYEE t1
     LEFT OUTER JOIN
       (EMPLOYEE_PROJECT t2 JOIN PROJECT t0 ON (t0.ID = t2.projects_ID))
     ON (t2.employees_ID = t1.ID)

SELECT t0.ID, t0.NAME, t0.SALARY
FROM   PROJECT t1
     LEFT OUTER JOIN
       (EMPLOYEE_PROJECT t2 JOIN EMPLOYEE t0 ON (t0.ID = t2.employees_ID))
     ON (t2.projects_ID = t1.ID)

In this case I believe I would still get the same result if I replace 
the inner join with a left outer join.
But are there cases where this replacement would give different results 
(i.e. where the table on the left could be a table other than a simple 
join table)?

Thanks,
Dies


> Dies Koper wrote:
> > > > 3. INNER JOIN
> > > >    Symfoware does not support (INNER) JOIN syntax.
> > > >    "JOIN" is used in SQLSelectStatement twice, at least one of which is
> > > > used when using a self-referencing many to many relationship and
> > > > performing a (JPQL) LEFT OUTER JOIN query. I can prevent the syntax
> > > > error by replacing the inner join by a left outer join for Symfoware,
> > > > but I'm worried that might select too many rows. I suppose a better
> > > > solution is to compare join keys in the where clause. What do you 
> > > > think?
> > > > Do you happen to know an easy way to do that (using existing code)? If
> > > > not, never mind, I'll do some more research myself.
> > >
> > > Could you please give an example of a statement that gives you an 
> > > issue, what the resulting SQL is, and what part of it is not 
> > > supported by Symfoware?
> >
> > Query query = em.createQuery("SELECT o from Person o LEFT OUTER JOIN
> > o.persons AS p");
> >
> > gives:
> >
> > SELECT t0.ID, t0.NAME, t1.ID, t1.NAME FROM {oj PERSON t0 LEFT OUTER JOIN
> > (PERSON_PERSON t2 JOIN PERSON t1 ON (t1.ID = t2.persons_ID)) ON
> > (t2.Person_ID = t0.ID)}
> >
> > Symfoware complains about "JOIN", saying that the join type is not
> > specified. Legal values are LEFT/RIGHT (OUTER).
> >
> > So I suppose I should change it to generate something like:
> >
> > SELECT t0.ID, t0.NAME, t1.ID, t1.NAME FROM {oj PERSON t0 LEFT OUTER JOIN
> > (SELECT PERSON_PERSON t2, PERSON t1 WHERE t1.ID = t2.persons_ID) ON
> > (t2.Person_ID = t0.ID)}