bidirectional one-to-one mapping with foreign key assosiation and redundant queries

Description

I have simple one-to-one mapping for Blank and Ticket entities

Mapping documents:

So I want to fetch all in one query:

But redundant query is executing for each Blank in result list
The generated SQL (show_sql=true):

if i remove one-to-one tag from Blank mapping then HQL "select t from Ticket t left join fetch t.Blank b" is work fine with only one query to database

Hibernate version: 1.2.1
Name and version of the database you are usingB2Dialect, DB2 9.5-C

Environment

None

Activity

Show:
Fabio Maulo
September 29, 2008, 2:22 PM

Do you can provide a failing NUnit test case ?
Thanks.

John M
February 16, 2009, 9:17 AM

I am also having this issue, and I'm concerned that there could be more consequences than just extra queries. We inherited a system with, among others, the tables "Modules" and "ScoringDetails". Primary keys are Modules.ModuleId and ScoringDetails.ScoringDetailId. ScoringDetails also has a foreign key to Modules on ModuleId. We discovered that this is actually a one-to-one relationship, so I mapped it originally like this:

<class name="Module" table="Modules">
<id name="Key" column="ModuleId" type="Int64">
<generator class="native"/>
</id>
<one-to-one name="ScoringDetail" class="ScoringDetail" property-ref="Module" cascade="all" fetch="join"/>
</class>
<class name="ScoringDetail" table="ScoringDetails">
<id name="Key" column="ScoringDetailId" type="Int64">
<generator class="native"/>
</id>
<many-to-one name="Module" column="ModuleId" class="Module" unique="true"/>
</class>

In my tests the module with ModuleId=1 was related to the scoring detail with ScoringDetailId=17. In an isolated test with lots of debugging logs on, I loaded the module. The logging looked essentially like this (simplified obviously):

1. Fetching Entity Module#1
2. SELECT * FROM Modules m LEFT JOIN ScoringDetails sd ON Modules.ModuleId = ScoringDetails.ModuleId WHERE m.ModuleId = 1
3. Result row Module#1, ScoringDetail#17
4. Hydrating Entity ScoringDetail#17
5. Hydrating Entity Module#1
6. resolving associations for Module#1
7. loading entity ScoringDetail#1
8. SELECT * FROM ScoringDetail sd WHERE sd.ModuleId = 1
9. Result row ScoringDetail#17

So the first part was right - it did a join fetch and got both entities in one query. However, the next part is where things went wrong. I think Hibernate is incorrectly using the one-to-one key, ScoringDetail.ModuleId, when it looks up the entity for associations (line 7), even though all the rest of the places it uses ScoringDetail.ScoringDetailId, which is the id/primary key. This is the error that leads to the redundant query.

The part I'm afraid of is what if ScoringDetail#1 is already in the cache from some other load? Would Hibernate then load the wrong entity for the association?

In my case, as a workaround, I am ignoring ScoringDetailId and just using ModuleId as the id/primary key for the ScoringDetails table. (ModuleId is required and unique.) This makes the association work correctly.

Fabio Maulo
June 5, 2009, 4:11 PM

can you provide a failing NUnit test case ?

Fabio Maulo
August 5, 2010, 10:00 AM

One year waiting and counting... candidate to be closed as incomplete.

Assignee

Unassigned

Reporter

Lukin Vasiliy

Components

Affects versions

Priority

Trivial
Configure