CODEWITHSUNDEEP
sqlhibernatehqlquerydsl
seminolas
seminolas

Reputation: 409

Unwanted implicit inner join in HQL query

I'm trying to write a QueryDSL expression for selecting a column value. It's a few joins away from the 'from' table:

a.b.c.get(0).field

where b may be null object, but if not then it will have at most 1 record in c collection. what I want is something like 

new CaseBuilder()
 .when(a.b.isNotNull().and(a.b.c.isNotEmpty()))
 .then(a.b.c.get(0).field.stringValue())
 .otherwise(Expressions.stringTemplate("''"))

This implicitly produces inner joins with b and c tables in SQL, which is not what I want because that returns no results when b is in fact null. Adding explicit left outer joins doesn't stop the implicit joins anyway. I'm pretty sure I'm not thinking about this the right way, please help me get unstuck :-)

Upvotes: 1

Views: 348

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 221106

When I had added support for implicit joins to jOOQ, I had researched the topic and stumbled upon this "limitation" / design in Hibernate as well. I've reported this issue to the Hibernate-dev mailing list recently as I clearly think this is a bug: http://lists.jboss.org/pipermail/hibernate-dev/2018-February/017299.html

I don't see why any projection expression should "inadvertently" produce a filter on the FROM clause. This appears to be quite contrary to the intuition that one might build when thinking in terms of relational algebra.

You can read the replies to that email, particularly this rationale by Steve Ebersole:

I was saying that I can no longer look at the HQL/JPQL and tell what kind of SQL joins will be used for that if it is dependent on the mapped association. This approach was mentioned earlier in the thread. But you clarified that you mean that implicit joins ought to just always be interpreted as an outer join.

You could plugin in your own query handling and interpret implicit joins however you want. That is current not the most trivial task though.

I don't think this will get fixed any time soon in Hibernate. So, the workaround here is to build all of your outer joins explicitly and not use any implicit joins whenever you expect them to produce outer joins.

Upvotes: 1

Related Questions