Does fetch all properties do left join under the hood?

Question

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.

There is no such syntax "fetch all properties" in JPA. – Neil Stockton Feb 25 '16 at 05:15 — Neil Stockton, Feb 25 '16 at 05:15

score 1 · Accepted Answer · edited May 23 '17 at 12:07

1

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.

edited May 23 '17 at 12:07

Community

1
1

answered Feb 25 '16 at 11:28

Dragan Bozanovic

23,102
5
43
110

thanks dragon bozanovic. yeah i noticed that when i am playing with the query, the reason why we are doing fetch all here is to get the correct count of persons. When i do just "left join":This person has Set within, the query returns duplicate persons if the phone number set is >1. Then we tried "left join fetch" , this is having bad performance. So we moved to "fetch all properties", which is performing well, but not doing left join when using person.agency in order by clause. i am trying to write a query that solves all my problems. – rohith Feb 25 '16 at 16:51
this is what i tried now: "left join person.phoneNumber left join person.agency fetch all properties" this returns duplicate records. if i just do fetch all properties i am not seeing any duplicates. – rohith Feb 25 '16 at 16:56
@rohith In case you join with a collection, duplicates are natural to happen. Then just use `distinct` to eliminate duplicates: `select distinct person from ...` – Dragan Bozanovic Feb 25 '16 at 17:25
sorry i forgot to mention the problem i am having with distinct. i get "not a selected expression error", when i try to sorty by agency fields. My query: " select distinct person from Person person left join person.agency ag fetch all properties where upper(person.name) LIKE '%SU%' and upper(person.status) = 'ACTIVE' order by ag.name asc, person.id asc " – rohith Feb 25 '16 at 20:14
I suggest you forget about `fetch all properties` in this context. Does it work without it? – Dragan Bozanovic Feb 25 '16 at 20:54
nope. i get "ORA-01791: not a SELECTed expression" when i run : select distinct person from Person person left join person.agency ag where upper(person.name) LIKE '%SU%' and upper(person.personStatusType.rcode) = 'ACTIVE' order by ag.agencyIdentifierDescription asc, person.id asc – rohith Feb 25 '16 at 22:34
Yes, true, because of [this](https://community.oracle.com/message/681503#681503). However, for that query there is no chance you get duplicate records. Hibernate should not join with phone numbers automatically (and if it is a lazy collection the query will not suffer from the n+1 selects problem). Simply go without `distinct`, as I originally proposed in the answer. If I am still missing something, please edit your question with all the details. – Dragan Bozanovic Feb 25 '16 at 23:24
updated my question, with problems i am running into, and solution i am looking for. thanks for your time. – rohith Feb 26 '16 at 17:51
@rohith Thanks, I edited my answer according to your observations. – Dragan Bozanovic Feb 26 '16 at 19:08
thanks for your response. I accepted your answer "List result = new ArrayList(new LinkedHashSet(list))" seems to solve my problem. – rohith Apr 25 '16 at 23:18

Does fetch all properties do left join under the hood?

1 Answers1

Linked