@JoinFormula querying for field with NULL value is not using IS NULL operator

Hibernate ORM
1

Hibernate 5.4.10.Final
MySQL 5.7.25

Given the following relationship mapping:

  @OneToOne(cascade=CascadeType.ALL)
  @LazyToOne(LazyToOneOption.PROXY)
  @Fetch(value=FetchMode.SELECT)
  @JoinColumnsOrFormulas({
    @JoinColumnOrFormula(formula=@JoinFormula(value="id", referencedColumnName="id"))
    ,@JoinColumnOrFormula(formula=@JoinFormula(value="NULL", referencedColumnName="partnerId"))
  })
  private RevenueModel revenueModelForMe;

We expect this to result in the following SQL where clause:

where revenuemod0_.id=11 and revenuemod0_.partnerId IS NULL

but instead we are seeing an arithmetic comparison, which will not work:

where revenuemod0_.id=11 and revenuemod0_.partnerId=null

Any advice greatly appreciated! Thanks.

2

That’s not how this works and it’s also not easy to add support for this. So if you really want to join against a NULL value, you will have to introduce a sentinel value for this purpose and use the sentinel value instead of NULL. To be honest, it does seem to me like this is something that you shouldn’t model in your entity model, but should rather query through JPQL/HQL: select e, r from Entity e left join RevenueModel r on e.id = r.id and r.partnerId is null

3

Thank you for your response Christian. The nulls are the sentinel value, and this is a legacy schema that we’re having to back into. Given that a second relationship is needed for revenueModelsForPartners (where partnerId is not null), we may have to workaround this by defining separate RevenueModel classes, each having different class-level @Where annotations (we found that relation-level @Where's don’t apply to the join). Any other suggestions besides adhoc hql?

4

Like I wrote, you can use a sentinel value:

  @JoinColumnsOrFormulas({
    @JoinColumnOrFormula(formula=@JoinFormula(value="id", referencedColumnName="id"))
    ,@JoinColumnOrFormula(formula=@JoinFormula(value="0", referencedColumnName="coalesce(partnerId, 0)"))
  })
5

Having a function in the referencedColumnName is not a valid mapping in Hibernate 5.4.10.

Caused by: org.hibernate.MappingException: Unable to find column with logical name coalesce(partnerId, 0) in table revenueModel

6

In that case, I would recommend you introduce a generated column on the table of RevenueModel: partnerIdNonNull GENERATED ALWAYS AS (coalesce(partnerId,0)) VIRTUAL NOT NULL

Also see MySQL :: MySQL 5.7 Reference Manual :: 13.1.18.7 CREATE TABLE and Generated Columns