Hi,
we have a rather strange problem. We've tried to find solutions but honestly we couldn't even create the search phrase that would give us any useful results, so here it is.
We have a table structure like this:
{code:java}
A
|
---------------
| | |
B C D
| |
D E
|
E
{code}
the problem is with the D-E table structure, which is referenced twice. When we try to eager load this dataset, once hibernate loaded this structure once, it won't load it again, but stops at joining D without E and triggers a separate query to load the second D-E connection.
So the query hibernate puts together will look something like this:
{code: mysql sql }
SELECT a.some_field, ...,
FROM A as a
LEFT OUTER JOIN B b on a.B_id = b.id
LEFT OUTER JOIN C c on a.C_id = c.id
LEFT OUTER JOIN D d on c.D_id = d.id
LEFT OUTER JOIN E e on d.E_id = e.id
LEFT OUTER JOIN D d on a.D_id = d.id
<<LEFT JOIN to E from A->D is missing here and sent separately>>
{code}
This structure repeats a lot in our original data structure, these are always OneToOne connections, never looping into each other, it's a clear tree structure where we re-use the same structures over and over again, but it's never too deep (max join depth is 5), it contains no loops and many times not all connections are used, so the generated query itself is large, but the dataset behind it often contains a lot of empty connections.
We've had a discussion about this here: https://discourse.hibernate.org/t/hibernate-with-eager-loading-issues-two-queries-instead-of-one/2293 where Vlad recommended to create a bug for this.
We also created a test case template which can be accessed here: https://github.com/csore-a-tamas/hibernate-test-case-templates
The database scripts can be accessed here: https://github.com/csore-a-tamas/hibernatetest/blob/master/mysql_queries
Let us know if you need anything else from our side.
Thanks! |
|
