Extra left join on same table in hibernate 6.x when using entityGraph and JPA specifications

Hibernate ORM
1

When we are using entityGraph and JPA specifications together and did join for OneToMany releationship in specification, Hibernate 6 while generating SQL joining same table multiple times leads wrong response from query. We are getting wrong resultSet as criteria is not getting applied for extra join table.
In below example exp_rule_trnch joined 2 times one join due to exp_rule_trnch present in entityGraph and other is due to join present in specification for exp_rule_trnch. EffectiveDate clause applicable for e4_0 and not for e2_0 which gives all records for e2_0 which leads wrong result.
If we remove same entity from entityGraph then we can see extra queries getting fired for exp_rule_trnch table.
Is there any solution for this so that one table joins one time only in query.
Or can we ovierride any class of hibernate where we can check if join is there for same table then dont do extra left join.

Specification join is needed.
In springboot 2.x we dont see extra left join which is coming from entityGraph.
Or its avoiding joining same table multiple times.

EntityGraph-

				@NamedAttributeNode(value = "scheme"), @NamedAttributeNode(value = "transactionCodeRule"),
				@NamedAttributeNode(value = "**expenseRuleTranches**", subgraph = "filterRuleAndExpenseScale") } ```

And Specification-

``` getJoinOnEffectiveDate(effectiveDate, root.join(**ExpenseRule_.EXPENSE_RULE_TRANCHES, JoinType.LEFT**),
						builder); ```

``` Select * from
FROM
    compass.expense_rules    e1_0
    LEFT JOIN compass.exp_rule_trnch   e2_0 ON e1_0.expr_key = e2_0.expr_key
    LEFT JOIN compass.filter_rules     f1_0 ON f1_0.fltr_key = e2_0.fltr_key
    LEFT JOIN compass.exp_scales       e3_0 ON e3_0.exsc_key = e2_0.exsc_key
    LEFT JOIN compass.case_seq_rules   c1_0 ON c1_0.csrl_key = e2_0.csrl_key
    LEFT JOIN compass.tr_code_grp      t1_0 ON t1_0.trgp_key = e2_0.trgp_key
    LEFT JOIN compass.salary_def       s1_0 ON s1_0.slry_key = e2_0.slry_key
    LEFT JOIN compass.exp_rule_trnch   e4_0 ON e1_0.expr_key = e4_0.expr_key
                                             AND e4_0.eff_dt <= ?
                                             AND ( e4_0.xpir_dt >= ?
                                                   OR e4_0.xpir_dt IS NULL )
    JOIN compass.case_data        s4_0 ON s4_0.case_key = e1_0.case_key
    LEFT JOIN compass.tr_code_rules    t2_0 ON t2_0.tr_cd = e1_0.tr_cd
WHERE
    s4_0.cont_no = ?
    AND e1_0.eff_dt <= ?
    AND ( e1_0.xpir_dt >= ?
          OR e1_0.xpir_dt IS NULL )
    AND 1 = 1 ```
2

Hibernate ORM 5 wrongly used existing association joins with extra on conditions as basis for fetching data. This is now fixed in ORM 6. Note that filtering fetched associations like collections through extra on conditions is risky, because that will make the observed/read state different from the database state. If you alter your entity somehow, you might loose data because Hibernate has no way of knowing what those on conditions really are.

The supported way of filtering collections is to use Hibernate filters, which are applied during DML statements as well to retain the correct semantics.

3

Thanks [beikov] for immediate response and info.
Do you know which hibernate class doing this joins or making this query. If we got that then will try to override that class method and check if duplicate join is happening then avoid that join and keep distinct join in query

4

If you want to filter the data that you read through join conditions, then you simply shouldn’t fetch entities. Use a DTO model instead e.g. Blaze-Persistence Entity-Views.

5

Entity view usage not fits for our coding structure. We have many specifications in our code and we want to go with them. To resolve n+1 problem we are using entity graph which leads extra left join on same table. Can we avoid this left join and keep entitygraph as well?
Or can override hibernate class to skip join happening due to entitygraph if there is already one join present

6

Entity view usage not fits for our coding structure. We have many specifications in our code and we want to go with them.

If by “specifications” you mean Spring Data Specification, then know that you can use that along with entity views: Blaze Persistence - Entity View Module

To resolve n+1 problem we are using entity graph which leads extra left join on same table. Can we avoid this left join and keep entitygraph as well?
Or can override hibernate class to skip join happening due to entitygraph if there is already one join present

You can also use Hibernate filters for filtering the contents of a collection: Hibernate ORM User Guide

7

Hi- We can see this issue resolved with new version of springBoot version-v3.2.3 with upgraded version of hibernate -6.4.4 final. Do you have any update on this?

8

beikov-
We can see this issue resolved with new version of springBoot version-v3.2.3 with upgraded version of hibernate -6.4.4 final. Do you have any update on this?

9

What sort of update do you want? You write it is resolved, so what is your question? You have to be a bit more specific.