Questions about "left join on" clause in HQL query with fetch graph

Hibernate ORM
1

Hello everybody,

I face a problem in Hibernate 6 when using LEFT JOIN ON and fetchGraph in HQL queries.

I made a small project with the sources : Hibernate6_debug_case/src/main/java/org/example/Main.java at main · geektortoise/Hibernate6_debug_case · GitHub
I’m available to create a test case based on hibernate-test-case-templates but I just wanted to have your feedback first to be sure that I didn’t miss an obvious point.

a) First of all, I had the problem with fetchGraph & inner joins but I saw it was fixed in version 6.4.3 ([HHH-17629] - Hibernate JIRA)
However, I still have issues when it is a LEFT JOIN.

    select distinct c from Customer c
	left join c.addresses ad on ad.streetID = 'avenue'

With a jakarta.persistence.fetchgraph that will fetch the “addresses” (field of Customer), the HQL query will generate two left joins, one with the condition and another without.

    select ...
    from
        customer c1_0
    left join
        address a1_0
            on c1_0.CUST_STREET_ID=a1_0.STREET_ID
            and (a1_0.ZIP_CODE in (4000, 4020, 4030))
            and a1_0.STREET_ID='avenue'
    left join
        address a2_0
            on c1_0.CUST_STREET_ID=a2_0.STREET_ID
            and (a2_0.ZIP_CODE in (4000, 4020, 4030))

This is a breaking change related to Hibernate 5.

b) Then I realized that even without the “on” clause, the left join is still duplicated

    select distinct c from Customer c
	left join c.addresses ad on ad.streetID = 'avenue'

generate

    select ...
    from
        customer c1_0
    left join
        address a1_0
            on c1_0.CUST_STREET_ID=a1_0.STREET_ID
            and (a1_0.ZIP_CODE in (4000, 4020, 4030))
    left join
        address a2_0
            on c1_0.CUST_STREET_ID=a2_0.STREET_ID
            and (a2_0.ZIP_CODE in (4000, 4020, 4030))

c) Then I use the Filter & FilterDef annotations (as you advised) on the “adresses” collection field of Customer object but this solution is at a session level.
Previously, we could be able to set exclusive ON clauses into a same query : each one in a different subquery.

An example can be the following on the Adress domain.

@FilterDef(name = "onlyAvenue", defaultCondition=" STREET_ID = 'avenue'")
@FilterDef(name = "onlyBoulevard", defaultCondition=" STREET_ID = 'boulevard'")

I think there is not an equivalent solution at the moment for the old “left join on” clause.
Am I correct ?

Can the points a & b be flagged as bugs or are they already known by the team and will not be fixed ?

Have a nice day.

2

When using a custom on condition, joins can never be reused for fetches since the results will be different due to the additional filter.

When using a normal left join without an on condition, in your case there’s still a @Where clause adding an additional restriction to the join. Now, the same condition will be applied to the join generated for the fetch, but at this moment we are not able to differentiate between that and an explicit condition added to the on clause like in your first point.

Hibernate 6 tries to always preserve the correct cardinality for the fetched association, and while this might produce unneeded joins in some cases, it’s definitely more correct than 5 in other instances. You can open a new Jira issue in our tracker to report your findings for point b), but I’m not sure we’ll be able to improve on this easily.

1 Like