Making Mistakes In JPQL

Well, crap. My boss mentioned that I introduced a Bug in my JPQL, whilst fixing a Bug.

I think it's a Bug that is easy to create, and is therefore worth a little blogpost.

In Java the || and && operators are short-circuiting operators. This means the expression on the right of the operator will not be evaluated if the expression on the left already makes the entire expression true¹.

When using Hibernate (or any other ORM) I do tend to program JPQL in the same way as I program in Java. As this is translated into JPQL, the translation is sometimes not what you would expect.

The class diagram looked something like the following. Bear in mind that each Class represents a table in the database, as is usual in an ORM.

The problem

The following JPQL had an issue:

//@formatter:off
String queryStr = "SELECT person " +
                  "FROM Person person " +
                  "JOIN FETCH person.function " +
                  "WHERE person.lastname = :lastname " +
                  "AND person.fired = false " +
                  "AND (person.address is null OR person.address.deleted = false) ";
//@formatter:on

The idea here is to provide us with a list of all Persons with a specific last name, who are both not fired and may or may not have an existing address.

It will be translated by Hibernate (in our case) into the following SQL statement:

select * -- a lot of fields here
from Person person0_ inner join Function function1_ on person0_.FUNCTIONID=function1_.FUNCTIONID, Address address2_ 
where person0_.ADDRESSID=address2_.ADDRESSID 
and person0_.LASTNAME=? 
and person0_.FIRED='N' 
and (person0_.ADDRESSID is null or address2_.DELETED='N')

Can you spot the problem?

The Solution

The problem above, apparently, is that besides the explicit inner join (inner join Function) there is an implicit inner join upon the Address table (from Address address2_ where person0_.ADDRESSID=address2_.ADDRESSID). This means that only persons will be shown that have an existing address. The condition in the where at the bottom regarding "person0_.ADDRESSID is null" is ignored.

The following, slightly more complicated, JPQL solves this problem:

//@formatter:off
String queryStr = "SELECT person " +
                  "FROM Person person " +
                  "JOIN FETCH person.function " +
                  "LEFT JOIN person.address address " +
                  "WHERE person.lastname = :lastname " +
                  "AND person.fired = false " +
                  "AND (address is null OR address.deleted = false) ";
//@formatter:on

This will be translated appropriately by Hibernate into the following SQL Query:

SELECT * -- a lot of fields here
FROM Person person0_
   INNER JOIN FUNCTION function1_
      ON person0_.FUNCTIONID=function1_.FUNCTIONID
   LEFT OUTER JOIN Address address2_
      ON person0_.ADDRESSID     =address2_.ADDRESSID
WHERE person0_.LASTNAME   =?
AND person0_.FIRED        ='N'
AND (address2_.ADDRESSID IS NULL OR address2_.DELETED      ='N')

Testing

Of course, I would not have made such a crappy mistake, if I did my tests properly. Apparently, testing is hard, and it is easy to miss to test all eventualities.

In this case it is obvious I forgot to test for the Address==null case.

Well, we live and learn.

References

The Java^TM Tutorials - Equality, Relational, and Conditional Operators

http://docs.oracle.com/javase/tutorial/java/nutsandbolts/op2.html

StackOverflow - Explicit vs Implicit SQL Joins

http://stackoverflow.com/questions/44917/explicit-vs-implicit-sql-joins