Massive select query when executing the merge operation

Hibernate ORM
1

I updated the project from hibernate v4 to 6.4.9 and I encountered that SQL query increased a lot.
From:

Select <fields of table> from Table where ...

To query where every field is left joined which causes this error for the Firebird database: [Dynamic SQL Error; SQL error code = -204; Implementation limit exceeded; block size exceeds implementation restriction [SQLState:54000, ISC error code:335544381]

Surprisingly, the where condition is not the same. The older Hibernate uses the in condition on one of the table fields, now Hibernate uses just the id column.

2

When using merge Hibernate needs to read the state of the entity you’re trying to merge from the database, hence why the query is run. A lot has changed since Hibernate v4 (i.e. in the last 10 years), and with this little information it’s impossible to help you.

If you want specific help, please provide more context like your entity mappings and a code sample of the operation you’re trying to perform.

3

Hi,

I made two sample projects. Java sources are the same, but one is running Hibernate 4 and the other Hibernate 6. MergeDifferences.zip - Google Drive

When we execute both projects we can see these:
for Hibernate 4

Hibernate: select banktransa0_.id as id1_0_0_, banktransa0_.company_id as company_2_0_0_, banktransa0_.invoice_id as invoice_3_0_0_, banktransa0_.person_id as person_i4_0_0_, banktransa0_.teacher_id as teacher_5_0_0_ from BankTransaction banktransa0_ where banktransa0_.id=?
Hibernate: select invoice0_.id as id1_2_0_, invoice0_.company_id as company_2_2_0_, invoice0_.person_id as person_i3_2_0_, invoice0_.teacher_id as teacher_4_2_0_ from Invoice invoice0_ where invoice0_.id=?
Hibernate: select teacher0_.id as id1_4_0_, teacher0_.person_id as person_i3_4_0_, teacher0_.roomNumber as roomNumb2_4_0_ from Teacher teacher0_ where teacher0_.id=?
Hibernate: select person0_.id as id1_3_0_, person0_.firstname as firstnam2_3_0_, person0_.surname as surname3_3_0_ from Person person0_ where person0_.id=?
Hibernate: select invoice0_.id as id1_2_0_, invoice0_.company_id as company_2_2_0_, invoice0_.person_id as person_i3_2_0_, invoice0_.teacher_id as teacher_4_2_0_, company1_.id as id1_1_1_, company1_.name as name2_1_1_, person2_.id as id1_3_2_, person2_.firstname as firstnam2_3_2_, person2_.surname as surname3_3_2_, teacher3_.id as id1_4_3_, teacher3_.person_id as person_i3_4_3_, teacher3_.roomNumber as roomNumb2_4_3_, person4_.id as id1_3_4_, person4_.firstname as firstnam2_3_4_, person4_.surname as surname3_3_4_ from Invoice invoice0_ left outer join Company company1_ on invoice0_.company_id=company1_.id left outer join Person person2_ on invoice0_.person_id=person2_.id left outer join Teacher teacher3_ on invoice0_.teacher_id=teacher3_.id left outer join Person person4_ on teacher3_.person_id=person4_.id where invoice0_.id=?
Hibernate: update BankTransaction set company_id=?, invoice_id=?, person_id=?, teacher_id=? where id=?

and for Hibernate 6

Hibernate: select bt1_0.id,c1_0.id,c1_0.name,i1_0.id,c2_0.id,c2_0.name,p1_0.id,p1_0.firstname,p1_0.surname,t1_0.id,p2_0.id,p2_0.firstname,p2_0.surname,t1_0.roomNumber,p3_0.id,p3_0.firstname,p3_0.surname,t2_0.id,p4_0.id,p4_0.firstname,p4_0.surname,t2_0.roomNumber from BankTransaction bt1_0 left join Company c1_0 on c1_0.id=bt1_0.company_id left join Invoice i1_0 on i1_0.id=bt1_0.invoice_id left join Company c2_0 on c2_0.id=i1_0.company_id left join Person p1_0 on p1_0.id=i1_0.person_id left join Teacher t1_0 on t1_0.id=i1_0.teacher_id left join Person p2_0 on p2_0.id=t1_0.person_id left join Person p3_0 on p3_0.id=bt1_0.person_id left join Teacher t2_0 on t2_0.id=bt1_0.teacher_id left join Person p4_0 on p4_0.id=t2_0.person_id where bt1_0.id=?
Hibernate: select i1_0.id,c1_0.id,c1_0.name,p1_0.id,p1_0.firstname,p1_0.surname,t1_0.id,p2_0.id,p2_0.firstname,p2_0.surname,t1_0.roomNumber from Invoice i1_0 left join Company c1_0 on c1_0.id=i1_0.company_id left join Person p1_0 on p1_0.id=i1_0.person_id left join Teacher t1_0 on t1_0.id=i1_0.teacher_id left join Person p2_0 on p2_0.id=t1_0.person_id where i1_0.id=?
Hibernate: update BankTransaction set company_id=?,invoice_id=?,person_id=?,teacher_id=? where id=?

When we compare the SQL select, the first select from Hibernate 6 is divided into multiple selects in Hibernate 4. This left join select can be arbitrarily large and if we add enough columns referencing another column then this uber left join selection nukes the Firebird database.

4

I found solution for this problem. Set hibernate.max_fetch_depth to 2 or lower.