Does fetch all properties do left join under the hood?

My entity looks like below: to simplify I am just showing two fields. We decided to do fetch all to load all properties at once.

    @Entity     
    public class Person{
    @OneToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY)
    private Set<PhoneNumber> phoneNumbers = new HashSet<>(0);
     @ManyToOne(fetch = FetchType.LAZY)
        @JoinColumn(name = "AGENCY_ID")
        private Agency agency;

left join:(This returns duplicate records.)

select person
from Person person left join person.agency ag left join person.phoneNumbers 
where upper(person.name) LIKE '%SU%' and upper(person.status.rcode)   = 'ACTIVE'
order by person.agency.name asc, person.id asc

left join with distinct : This gives "ORA-01791: not a SELECTed expression" because i am using agency field in order clause.

select distinct person
    from Person person left join person.agency ag left join person.phoneNumbers 
    where upper(person.name) LIKE '%SU%' and upper(person.Status.rcode)   = 'ACTIVE'
    order by person.agency.name asc, person.id asc

left join fetch : This works fine, no duplicates . but has big performance hit for pulling 2000 person records. taking about 15 seconds vs 1 with just left join.

select  person
        from Person person left join fetch person.agency ag left join fetch person.phoneNumbers 
        where upper(person.name) LIKE '%SU%' and upper(person.Status.rcode)   = 'ACTIVE'
        order by person.agency.name asc, person.id asc

fetch all properties: No duplicates. performs better.But.. When I try to query a person like below (this person doesn't have any agency): it returns the person record (that means it is doing a left join).

 select person
    from Person person 
    fetch all properties
    where upper(person.name) LIKE '%SU%' and upper(person.status) = 'ACTIVE'
    order by  person.id asc

But when I do below, I don't get the person record back. (Difference is added field of agency in order by clause, seems like it is not doing left join in this case.)

select person
from Person person 
fetch all properties
where upper(person.name) LIKE '%SU%' and upper(person.status) = 'ACTIVE'
order by person.agency.name asc, person.id asc

What i am looking is to avoid duplicates, sortable by all fields of person with good performance.

Solution

The navigation path syntax (person.agency) is always translated into an inner join, that's why persons with no agency are not included in the result set.

You have to explicitly write an outer join to avoid the implicit inner join:

select person
from Person person left outer join person.agency a
where upper(person.name) LIKE '%SU%' and upper(person.status) = 'ACTIVE'
order by a.name asc, person.id asc

EDIT

distinct in the left join case does not work because it does not make sense to order by columns that are not included in the columns mentioned in the select distinct clause.

If you want to eagerly load lazy associations in the query, then you have no choice but to do left join fetch. But duplicates are returned also. To eliminate them, simply add the returned list to a LinkedHashSet to preserve the returned order:

List<Persons> result = new ArrayList(new LinkedHashSet(list))

Regarding the bad performance of this approach, Hibernate has nothing to do with it. You maybe have some eagerly loaded entities in the entities that are left-join-fetched (thus suffering from the n+1 selects problem), or the duplicated entities are really large (thus making and transferring a large result set).

You may want to use @BatchSize instead of left-join-fetching collections to avoid performance issues.