JPA join column with null values

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_co开发者_运维百科re_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

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.

This is the actual query done (using eclipselink logging):

SELECT t1.id, t1.write_permission, t1.admin_permission, t1.execute_permission, t1.read_permission, t1.xinco_core_user_id, t1.xinco_core_data_id, t1.xinco_core_group_id, t1.xinco_core_node_id FROM xinco_core_data t4, xinco_core_node t3, xinco_core_group t2, xinco_core_ace t1, xinco_core_user t0 WHERE ((t3.id = ?) AND ((((t3.id = t1.xinco_core_node_id) AND (t0.id = t1.xinco_core_user_id)) AND (t2.id = t1.xinco_core_group_id)) AND (t4.id = t1.xinco_core_data_id))) ORDER BY t0.id ASC, t2.id ASC, t3.id ASC, t4.id ASC bind => [1]

For some reason having the Order by adds a lot of table crosschecking in which having nulls make the result come out empty.

Removing the order by gets the required result (out of order of course).

See this Eclipselink bug