JPA join column with null values

Question

I have a query that works in plain SQL but is not working on JPA and can't figure out why. As you can guess from the title I have a clue but I don't know how to "fix" it.

Here's the actual important code:

@Id 
@Basic(optional = false) 
@Column(name = "id", nullable = false) 
private Integer id; 

@Basic(optional = false) 
@Column(name = "read_permission", nullable = false) 
private boolean readPermission; 

@Basic(optional = false) 
@Column(name = "write_permission", nullable = false) 
private boolean writePermission; 

@Basic(optional = false) 
@Column(name = "execute_permission", nullable = false) 
private boolean executePermission; 

@Basic(optional = false) 
@Column(name = "admin_permission", nullable = false) 
private boolean adminPermission; 

@JoinColumn(name = "xinco_core_data_id", referencedColumnName = "id", nullable=true) 
@ManyToOne(fetch = FetchType.LAZY) 
private XincoCoreData xincoCoreDataId; 
@JoinColumn(name = "xinco_core_group_id", referencedColumnName = "id", nullable=true) 
@ManyToOne(fetch = FetchType.LAZY) 
private XincoCoreGroup xincoCoreGroupId; 

@JoinColumn(name = "xinco_core_node_id", referencedColumnName = "id", nullable=true) 
@ManyToOne(fetch = FetchType.LAZY) 
private XincoCoreNode xincoCoreNodeId; 

@JoinColumn(name = "xinco_core_user_id", referencedColumnName = "id", nullable=true) 
@ManyToOne(fetch = FetchType.LAZY) 
private XincoCoreUser xincoCoreUserId; 

And here's the working sql:

select * from xinco_core_ace where xinco_core_user_id = 1 order by xinco_core_user_id, xinco_core_node_id, xinco_core_data_id; 

And here's what I'm attempting to do:

SELECT xca FROM XincoCoreAce xca WHERE xca.xincoCoreUserId.id = 1 ORDER BY xca.xincoCoreUserId.id, xca.xincoCoreGroupId.id, xca.xincoCoreNodeId.id, xca.xincoCoreDataId.id

The issue, I think, is that the xca.xincoCoreUserId.id, xca.xincoCoreGroupId.id, xca.xincoCoreNodeId.id, xca.xincoCoreDataId.id can be nulls.

Any idea? Hopefully is easier to read :P

Answer 1

Also happened to me with a simpler query:

select t from Task t where t.worker is not null order by t.worker.normalizedName

Found that any task result entity where the attribute worker is null (the task is unassigned) would be discarded. Later I found out that this is because path navigation in JPA is done using inner joins (the specification says so) and this will exclude any result where a part of the path is null..

This bug report describes accurately the issue:

https://bugs.eclipse.org/bugs/show_bug.cgi?id=363798

Unfortunately, this is not an implementation bug and you will have to refactor your entities/queries to avoid these kind of situations.