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

I'm no JPA expert here, but I'd try to get the actual SQL that is run on the database. Maybe the FetchType.LAZY has something to do with the problem?

Answer 2

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