Hibernate: ManyToOne / multiple join columns with one column nullable=true

Question

I hope you can help me on that.

Table A has a multi-column join to Table B where one of the JoinColumns can be nullable...

@Entity
@Table(name = "TABLE_A")
public class TableA {

@ManyToOne(fetch = FetchType.EAGER, optional = false)
@JoinColumns({ 
        @JoinColumn(name = "KEY1_TABLE_A", referencedColumnName = "KEY1_TABLE_B"),
        @JoinColumn(name = "KEY2_TABLE_A", referencedColumnName = "KEY2_TABLE_B"),
        @JoinColumn(name = "GROUP_TABLE_A", referencedColumnName = "GROUP_TABLE_B", nullable = true)})
private TableB typeB;

}

In TableB object the columns

• TABLE_B#KEY1_TABLE_B (not null)
• TABLE_B#KEY2_TABLE_B (not null)
• TABLE_B#GROUP_TABLE_B (nullable)

are mapped as Strings. The touple KEY1_TABLE_B /KEY2_TABLE_B /GROUP_TABLE_B is a unique key.

The generated SQL is as follows (shortened)

SELECT
 *
FROM
     table_a this_
     INNER JOIN table_b b_ ON 
        this_.KEY1_TABLE_A = b_.KEY1_TABLE_B AND 
        this_.KEY2_TABLE_A = b_.KEY2_TABLE_B AND 
        this_.GROUP_TABLE_A = b_.GROUP_TABLE_B  <-- here is the issue: works only with "is not null" on Oracle
 WHERE
     this.XYZ=<some-conditions-here>;

if i would write the SQL directly it should be something like

on ... AND (
            (this_.GROUP_TABLE_A = b_.GROUP_TABLE_B) 
         OR (this_.GROUP_TABLE_A is null and b_.GROUP_TABLE_B is null)
           )

Thanks for your thoughts and ideas!

Answer 1

Seems the replay should have two parts.

1. @ManyToOne(..., optional = false) meens that the relation is mandatory or it is an INNER JOIN. INNER JOIN requires direct comparison in relation. So the output form of the query is correct.

2. NULL is a special status of the value, that should be translated as UNKNOWN value. Any direct comparison with NULL gives FALSE as a result. Even comparing NULL with NULL will give you FALSE as a result. That's why we have special unary operations in DB servers IS NULL/IS NOT NULL to check for NULLs. Keeping in mind that general logic is: NULL is not equal to another NULL, it is your local logic, based on data knowledge and unique keys presence, in this particular case that some NULLs could be considered as equal values. Hence, local logic requires local handwritten queries, methods, etc.

Remark. The question in the topic is similar to having a FK by multiple columns - Oracle has SIMPLE approach for such cases: FK is being validated ONLY if all values in FK are NOT NULL, if one of them is NULL then no validation happens. What you wrote in the sample of SQL means FULL approach in FK and checks additional condition (VALUE1 = VALUE2 or (VALUE1 IS NULL and VALUE2 IS NULL)). Oacle does not support it. And only few database servers do support for that.