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:
CREATE TABLE `test`.`a` (
`id` INT NOT NULL AUTO_INCREMENT,
`some_field` VARCHAR(45) NULL,
`B_id` INT(11) NULL,
`C_id` INT(11) NULL,
`D_id` INT(11) NULL,
PRIMARY KEY (`id`));
CREATE TABLE `test`.`b` (
`id` INT NOT NULL AUTO_INCREMENT,
`some_field` VARCHAR(45) NULL,
PRIMARY KEY (`id`));
CREATE TABLE `test`.`c` (
`id` INT NOT NULL AUTO_INCREMENT,
`some_field` VARCHAR(45) NULL,
`D_id` INT(11) NULL,
PRIMARY KEY (`id`));
CREATE TABLE `test`.`d` (
`id` INT NOT NULL AUTO_INCREMENT,
`some_field` VARCHAR(45) NULL,
`E_id` INT(11) NULL,
PRIMARY KEY (`id`));
CREATE TABLE `test`.`e` (
`id` INT NOT NULL AUTO_INCREMENT,
`some_field` VARCHAR(45) NULL,
PRIMARY KEY (`id`));
The structure looks like this:
A
|
---------------
| | |
B C D
| |
D E
|
E
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.
So the query hibernate puts together will look something like this:
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>>
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.
Is there a way we could achive to load the whole dataset eagerly? Is there some configuration variable we are not aware of, or is this a bug in Hibernate?