I modeled a database relationship in JPA like this:
@Entity
@Data
@NoArgsConstructor(access = AccessLevel.PROTECTED) // Required for JPA
@AllArgsConstructor
public class OuterEntity {
@Id
private String outerId;
@JoinColumn(nullable = true)
@ManyToOne(fetch = FetchType.LAZY, optional = true)
private InnerEntity inner;
}
@Entity
@Data
@NoArgsConstructor(access = AccessLevel.PROTECTED) // Required for JPA
@AllArgsConstructor
public class InnerEntity {
@Id
private String innerId;
@Column
private boolean deleted;
}
And persisted some test data as follows:
InnerEntity inner = new InnerEntity("inner", false);
OuterEntity outerWithInner = new OuterEntity("outerWithInner", inner);
OuterEntity outerWithoutInner = new OuterEntity("outerWithoutInner", null);
em.persist(inner);
em.persist(outerWithInner);
em.persist(outerWithoutInner);
Performing a very simple query using the criteria API successfully gives two results:
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<OuterEntity> query = cb.createQuery(OuterEntity.class);
query.from(OuterEntity.class);
System.out.println(em.createQuery(query).getResultList());
// [OuterEntity(outerId=outerWithInner, inner=InnerEntity(innerId=inner, deleted=false)), OuterEntity(outerId=outerWithoutInner, inner=null)]
But once I add a filter on the inner entity like this:
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<OuterEntity> query = cb.createQuery(OuterEntity.class);
Root<OuterEntity> root = query.from(OuterEntity.class);
query.where(cb.or(cb.isNull(root.get("inner")), cb.isFalse(root.get("inner").get("deleted"))));
hibernate generates a cross join with where outerentit0_.innerId_innerId=innerentit1_.innerId in the resulting SQL in order to access the inner entity's table:
select
outerentit0_.outerId as outerid1_1_,
outerentit0_.inner_innerId as inner_in2_1_
from OuterEntity outerentit0_
cross join InnerEntity innerentit1_
where
outerentit0_.inner_innerId=innerentit1_.innerId
and (
outerentit0_.inner_innerId is null
or innerentit1_.deleted=0
)
This now only returns one entity:
[OuterEntity(outerId=outerWithInner, inner=InnerEntity(innerId=inner, deleted=false))]
Which is not the result I desire. I expected there to still be two results.
I believe this happens because the filter outerentit0_.inner_innerId=innerentit1_.innerId removes the outer entity that has null for its inner, because using = on nulls always evaluates to false.
Note that I explicitly set the join column to nullable and the many-to-one relationship to optional in the JPA-annotations, so I expected JPA to properly handle the null-case instead.
If I add either root.join("inner", JoinType.LEFT); or root.join("inner", JoinType.LEFT);, I get the correct result because the generated query is using a left outer join:
select
outerentit0_.outerId as outerid1_1_,
outerentit0_.inner_innerId as inner_in2_1_
from OuterEntity outerentit0_
left outer join InnerEntity innerentit1_
on outerentit0_.inner_innerId=innerentit1_.innerId
where
outerentit0_.inner_innerId is null
or innerentit1_.deleted=0
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<OuterEntity> query = cb.createQuery(OuterEntity.class);
Root<OuterEntity> root = query.from(OuterEntity.class);
root.join("inner", JoinType.LEFT);
cb.isFalse(root.get("inner").get("deleted"))));
List<OuterEntity> result = em.createQuery(query).getResultList();
System.out.println(result);
// [OuterEntity(outerId=outerWithInner, inner=InnerEntity(innerId=inner, deleted=false)), OuterEntity(outerId=outerWithoutInner, inner=null)]
Which is a workable solution I suppose, but I am very confused why hibernate would emit a cross join, which is subtly wrong for my expectations, the worst kind of wrong. What did I do to cause this behaviour? Am I required to always explicitly perform a left outer join when dealing with nullable relations like this?
