openJPA outer join on optional many-to-one when have order by clause

Question

I have a scenario, that I would like the generated SQL always using Outer Join for optional ManyToOne. I am using OpenJPA 2.2.1.x.

Two entities: Event and Rule, Event have an optional ManyToOne unidirectional relationship to Rule. Following are the two entity classes:

@Entity 
public class Event { 
    @Id 
    private String uuid; 

    private Date eventTime; 

    @ManyToOne(optional=true) 
    private Rule rule; 
} 

@Entity 
public class Rule { 
    @Id 
    private String uuid; 

    private int rowNum; 
} 

If in JPQL I use ORDER BY on Event attribute(for ex, eventTime), it generates LEFT OUTER JOIN between event and rule table. JPQL:

SELECT e from Event e order by e.eventTime desc 

SQL:

SELECT t0.uuid, t0.eventTime, t1.uuid, t1.rowNum FROM Event t0 LEFT OUTER JOIN Rule t1 ON t0.RULE_UUID = t1.uuid ORDER BY t0.eventTime DESC 

If in JPQL I use ORDER BY on Rule attribute(for ex, rowNum), it generates INNER JOIN between event and rule table. JPQL:

SELECT e from Event e order by order by e.rule.rowNum asc 

SQL:

SELECT t0.uuid, t0.eventTime, t1.uuid, t1.rowNum FROM Event t0 INNER JOIN Rule t1 ON t0.RULE_UUID = t1.uuid ORDER BY t1.rowNum ASC 

My questions are:

1. Is this the correct behaviour that based on different ORDER BY clause, two different types of JOIN are generated?
2. Is there any way we can make openJPA always generate OUTER JOIN, other than use native SQL? (I tried LEFT OUTER JOIN FETCH, it doesn't help :-().

Thanks in advance,

David Liu

Answer 1

Using LEFT JOIN could achieve the goal.

SELECT e FROM  Event e LEFT JOIN e.rule r  ORDER BY r.rowNum ASC

which will generate the following SQL:

SELECT t0.uuid, t0.eventTime, t2.uuid, t2.rowNum, t1.rowNum FROM Event t0 LEFT OUTER JOIN Rule t1 ON t0.RULE_UUID = t1.uuid LEFT OUTER JOIN Rule t2 ON t0.RULE_UUID = t2.uuid ORDER BY t1.rowNum ASC

There is something in the JPQL need to pay attention: the ORDER BY must be applied to r.rowNum rather than e.rule.rowNum, otherwise, it will generate an INNER JOIN.

You may have noticed that the generated SQL have two OUTER JOINs on RULE table.