JPA with Eclipselink is ignoring left outer join and add wrong FROM condition

Question

I have a Menu entity that has a parent Menu:

public class Menu implements Serializable {
   ...
   @JoinColumn(name = "parent" , nullable = true, referencedColumnName = "id")
   @ManyToOne(targetEntity = Menu.class,fetch = FetchType.EAGER, cascade = {}, optional = true)
   //@BatchFetch(BatchFetchType.JOIN)
   @JoinFetch(JoinFetchType.OUTER)
   private Menu parent;
    ...
}

I would create the query:

SELECT m.*
  FROM menu m LEFT OUTER JOIN menu p ON (p.ID = m.parent)
  WHERE (m.idapp = 1) ORDER BY p.ID ASC NULLS FIRST, m.order ASC

I have a named query:

SELECT m FROM Menu m LEFT OUTER JOIN Menu mp 
WHERE m.applicazione.id = :idapp 
ORDER BY mp.id ASC NULLS FIRST, m.ordine ASC

but the result is:

SELECT ... 
FROM menu t1 LEFT OUTER JOIN menu t0 ON (t0.ID = t1.parent), menu t2 
WHERE (t1.idapp = ?) ORDER BY t2.ID ASC NULLS FIRST, t1.order ASC

It is totally wrong because table t2 createa carthesian product.

What is the problem? Why t2 is added?

I have added also JoinFetch annotation but it's ignored and i don't now why.

score 1 · Accepted Answer · answered Apr 16 '20 at 16:58

T1 is coming from your outer joined relationship, and the SQL shows it is correctly being used to bring in all parent instances associated to the menu instances your query is selecting over.

That Cartesian product with t2 is what you defined with the query "SELECT m FROM Menu m LEFT OUTER JOIN Menu mp ". The query should just be:

SELECT m FROM Menu m WHERE m.applicazione.id = :idapp ORDER BY m.ordine ASC

If you must use the parent relationship for ordering, it would be of the form:

SELECT m FROM Menu m LEFT OUTER JOIN m.parent mp WHERE m.applicazione.id = :idapp 
ORDER BY mp.id ASC NULLS FIRST, m.ordine ASC

note though that the SQL may have an extra table join, as behaviour is provider and mapping specific - fetch joins are not to be used in filtering as it would distort the results of the returned objects.

SELECT m FROM Menu m WHERE m.applicazione.id = :idapp 
ORDER BY m.parent ASC NULLS FIRST, m.ordine ASC

might also work within EclipseLink as it has the fk in Menue to work with without having to trigger a join, but isn't required to.

Yes, i forgot that mp is joined to its parent table, so T2. I used the last query. Another change I did is to remove @JoinFetch and used @BatchFetch(JOIN) — Daniele Licitra, Apr 20 '20 at 10:18

JPA with Eclipselink is ignoring left outer join and add wrong FROM condition

1 Answers1