Hibernate generates a left outer join plus a inner join for the same relation into my query.
I have a simple @ManyToOne annotation for a reference from a alert object to a user object:
My jpql query is a little more complex:
The central outer join definition is this:
But Hibernate keeps creating a left outer join and a inner join for this relation whatever I do:
I already tried different queries and @ManyToOne attributes but none of them affected the sql outcome. I tried a similar relation in a different context but there it worked. Hibernate generated only a left outer join into the sql query. But I don't know which settings generate the two joins on the same relation.
I have added a simple maven test project. Just unzip the HibernateTest.zip and run 'mvn test'. A starting point for further testing could be the AppTest.testManyToOneInnerJoinIssue() test routine.
Thanks for your help in advance.
Java 1.6.0_20, Hibernate-EntityManager 3.5.5-Final, HSQLDB, Oracle 11g
Ok, after testing a little I found the problem. The inner join is generated because I refer in the 'select new' to the attribute annotated with @ManyToOne:
Hibernate does not evaluate the from clause. If there is a reference to the @ManyToOne reference in the select clause then it generates an inner join. It does not check if there is an outer join in the from clause defined.
If I remove the 'alert.responsibleUser' from the select clause then I get the left outer join without an inner join in the sql output.
In my opinion hibernate should check if an outer join is defined in the from clause before generating an inner join. Or it should overwrite the inner join with an left outer join if that is possible. But I don't know what the jpa spec says to it.
Or you could reuse the join, which is the correct thing for what you want
Hi Steve, thanks a lot. That's the trick. I just tested it.