[hibernate-issues] [JIRA] (HHH-14097) Versions 5.4.11.Final+ produce too many sql queries

[Julien Leloup (JIRA)]

Julien Leloup ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=5efca30c61665e0b9edf715c ) *updated* an issue

Hibernate ORM ( https://hibernate.atlassian.net/browse/HHH?atlOrigin=eyJpIjoiNGYyYTNhODU4MjY0NGIxNzlmYTY2ZjFhNjk0MjlkY2QiLCJwIjoiaiJ9 ) / Bug ( https://hibernate.atlassian.net/browse/HHH-14097?atlOrigin=eyJpIjoiNGYyYTNhODU4MjY0NGIxNzlmYTY2ZjFhNjk0MjlkY2QiLCJwIjoiaiJ9 ) HHH-14097 ( https://hibernate.atlassian.net/browse/HHH-14097?atlOrigin=eyJpIjoiNGYyYTNhODU4MjY0NGIxNzlmYTY2ZjFhNjk0MjlkY2QiLCJwIjoiaiJ9 ) Versions 5.4.11.Final+ produce too many sql queries ( https://hibernate.atlassian.net/browse/HHH-14097?atlOrigin=eyJpIjoiNGYyYTNhODU4MjY0NGIxNzlmYTY2ZjFhNjk0MjlkY2QiLCJwIjoiaiJ9 )

Change By: Julien Leloup ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=5efca30c61665e0b9edf715c )

Hi,

From version 5.4.11.Final, more SQL queries than usual are generated under certain conditions, please see the little project I made to try to understand : [https://gitlab.com/julien.leloup/spring-data-jpa-doc|https://gitlab.com/julien.leloup/spring-data-jpa-doc]

This project is a Spring Data JPA project and uses :

* spring-boot-starter-parent 2.2.4-RELEASE
* H2 database (on a PostgreSQL database the problem is the same)
* Hibernate-core 5.4.12.Final

In this project there are 5 entities with the following relations :
A ----- C   (1-N)
B ----- C   (1-N)
C ----- D   (1-N)
D ----- E   (N-1)
All the relations are bi-directionnal and use a FetchType LAZY

On Hibernate-core 5.4.10.Final and prior, if I execute the following query :

{code:java}@EntityGraph(attributePaths = {"a", "b", "dList", "dList.e"})
CEntity getById(Integer id);{code}

it produces only 1 SQL request, which is correct if I understood correctly Spring Data JPA :

{code:java}Hibernate: select centity0_.id as id1_2_0_, bentity1_.id as id1_1_1_, dlist2_.id as id1_3_2_, eentity3_.id as id1_4_3_, aentity4_.id as id1_0_4_, centity0_.a_id as a_id3_2_0_, centity0_.b_id as b_id4_2_0_, centity0_.label as label2_2_0_, bentity1_.label as label2_1_1_, dlist2_.c_id as c_id3_3_2_, dlist2_.e_id as e_id4_3_2_, dlist2_.label as label2_3_2_, dlist2_.c_id as c_id3_3_0_, dlist2.id as id1_3_0_, eentity3.label as label2_4_3_, aentity4_.label as label2_0_4_ from c centity0_ left outer join b bentity1_ on centity0_.b_id=bentity1_.id left outer join d dlist2_ on centity0_.id=dlist2_.c_id left outer join e eentity3_ on dlist2_.e_id=eentity3_.id left outer join a aentity4_ on centity0_.a_id=aentity4_.id where centity0_.id=?{code}

But From Hibernate-core version 5.4.11.Final, *the same code* produces 3 requests :

{code:java}Hibernate: select centity0_.id as id1_2_0_, aentity1_.id as id1_0_1_, bentity2_.id as id1_1_2_, dlist3_.id as id1_3_3_, eentity4_.id as id1_4_4_, centity0_.a_id as a_id3_2_0_, centity0_.b_id as b_id4_2_0_, centity0_.label as label2_2_0_, aentity1_.label as label2_0_1_, bentity2_.label as label2_1_2_, dlist3_.c_id as c_id3_3_3_, dlist3_.e_id as e_id4_3_3_, dlist3_.label as label2_3_3_, dlist3_.c_id as c_id3_3_0_, dlist3.id as id1_3_0_, eentity4.label as label2_4_4_ from c centity0_ left outer join a aentity1_ on centity0_.a_id=aentity1_.id left outer join b bentity2_ on centity0_.b_id=bentity2_.id left outer join d dlist3_ on centity0_.id=dlist3_.c_id left outer join e eentity4_ on dlist3_.e_id=eentity4_.id where centity0_.id=?{code}

{code:java}Hibernate: select dlist0_.e_id as e_id4_3_0_, dlist0_.id as id1_3_0_, dlist0_.id as id1_3_1_, dlist0_.c_id as c_id3_3_1_, dlist0_.e_id as e_id4_3_1_, dlist0_.label as label2_3_1_ from d dlist0_ where dlist0_.e_id=?{code}

{code:java}Hibernate: select dlist0_.e_id as e_id4_3_0_, dlist0_.id as id1_3_0_, dlist0_.id as id1_3_1_, dlist0_.c_id as c_id3_3_1_, dlist0_.e_id as e_id4_3_1_, dlist0_.label as label2_3_1_ from d dlist0_ where dlist0_.e_id=?{code}

Could you please investigate ?

Note : first I opened the following ticket to Spring Data Jpa team :
[https://jira.spring.io/browse/DATAJPA-1747|https://jira.spring.io/browse/DATAJPA-1747]
Their answer was "Until demonstrated otherwise, I'll have to assume this is caused by Hibernate."

Regards

( https://hibernate.atlassian.net/browse/HHH-14097#add-comment?atlOrigin=eyJpIjoiNGYyYTNhODU4MjY0NGIxNzlmYTY2ZjFhNjk0MjlkY2QiLCJwIjoiaiJ9 ) Add Comment ( https://hibernate.atlassian.net/browse/HHH-14097#add-comment?atlOrigin=eyJpIjoiNGYyYTNhODU4MjY0NGIxNzlmYTY2ZjFhNjk0MjlkY2QiLCJwIjoiaiJ9 )

Get Jira notifications on your phone! Download the Jira Cloud app for Android ( https://play.google.com/store/apps/details?id=com.atlassian.android.jira.core&referrer=utm_source%3DNotificationLink%26utm_medium%3DEmail ) or iOS ( https://itunes.apple.com/app/apple-store/id1006972087?pt=696495&ct=EmailNotificationLink&mt=8 ) This message was sent by Atlassian Jira (v1001.0.0-SNAPSHOT#100131- sha1:aac02e2 )
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.jboss.org/pipermail/hibernate-issues/attachments/20200703/95f9bbe8/attachment.html