JPQL and joins

Have you ever asked yourself if JPQL (Java Persistence Query Language) queries written by you REALLY do what you want, or you just prepare them, commit to the code repository, and forget about them with a little help from pizza and beer ;) ...

Let's take a look at very simple example, we have Person entity:

@Entity
@Table(name = "PERSONS")
public class Person implements Serializable {

    private String firstName;
    private Long id;
    private Person spouse;

    @Column(name = "FIRST_NAME")
    public String getFirstName() {
        return firstName;
    }

    @Id
    public Long getId() {
        return id;
    }

    @OneToOne(optional = true)
    @JoinColumn(name = "SPOUSE_ID")
    public Person getSpouse() {
        return spouse;
    }
...
}

and we want to fetch all persons matching some criteria.

Suppose that PERSONS table has following entries:

ID	SPOUSE_ID	FIRST_NAME
1	NULL	John
2	NULL	Mary
3	4	Adam
4	3	Eva

We will build and execute very simple JPQL query:

Query query = entityManager.createQuery(
    "select person from Person person where person.spouse.firstName = 'Eva' or 1 = 1");
Assert.assertEquals("Result list is too small,", 4, query.getResultList().size());

Our JPQL query should return all persons from the database, because of the "1 = 1" expression in where clause. Let's try how it works with different JPA Providers.

EclipseLink (2.2.0) converts our JPQL query into following SQL query:

SELECT t1.ID, t1.FIRST_NAME, t1.SPOUSE_ID 
FROM PERSONS t0, PERSONS t1 
WHERE (((t0.FIRST_NAME = 'Eva') OR (1 = 1)) AND (t0.ID = t1.SPOUSE_ID))

which leads to assertion error:

java.lang.AssertionError: Result list too small, expected:<4> but was:<2>small 

Hibernate (3.4.0 GA) uses following SQL:

select person0_.id as id1_, person0_.FIRST_NAME as FIRST2_1_, person0_.SPOUSE_ID as SPOUSE3_1_ 
from PERSONS person0_, PERSONS person1_ 
where person0_.SPOUSE_ID=person1_.id and (person1_.FIRST_NAME='Eva' or 1=1)

which leads to the same assertion failure.

Only OpenJPA (2.1.0) uses SQL expected by us:

SELECT t0.id, t0.FIRST_NAME, t1.id, t1.FIRST_NAME 
FROM PERSONS t0 
    LEFT OUTER JOIN PERSONS t1 ON t0.SPOUSE_ID = t1.id 
WHERE (t1.FIRST_NAME = 'Eva' OR 1 = 1)

How can we force EclipseLink and Hibernate to use left join? We have to modify JPQL query in following way:

select person 
from Person person 
    left join person.spouse spouse 
where spouse.firstName = 'Eva' or 1 = 1

As you see I've added explicit left join and changed first where clause expression to use this join.
Now JPQL query returns all 4 records regardless of used JPA Provider.