Can't understand inner join behaviour

lester · **Posted:** Tue Feb 05, 2008 11:29 am

Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

I have three objects: User, Permission and History. User has bidirectional one-to-one association with Permission, Permission has bidirectional one-to-many association with History.

Hibernate version: 3.2.5.ga

Mapping documents:

User.hbm.xml

Quote:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping>

<class name="gor.domain.User"
table="USER"
proxy="gor.domain.User">

<id name="id" column="ID" type="integer" unsaved-value="null">
<generator class="identity"/>
</id>

<version name="version" column="TX_VERSION"/>

<property name="name" column="NAME"/>
<property name="email" column="EMAIL"/>
<property name="phone" column="PHONE"/>

<component name="address">
<property name="address" column="ADDRESS"/>
<property name="city" column="CITY"/>
</component>

<one-to-one name="permission" class="gor.domain.Permission" cascade="all"/>

<bag name="preferences" cascade="all">
<key column="USER_ID"/>
<one-to-many class="gor.domain.Preference"/>
</bag>

</class>

</hibernate-mapping>

Permission.hbm.xml

Quote:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping>

<class name="gor.domain.Permission"
table="PERMISSION"
proxy="gor.domain.Permission">

<id name="id" column="ID" type="integer" unsaved-value="null">
<generator class="foreign">
<param name="property">user</param>
</generator>
</id>

<version name="version" column="TX_VERSION"/>

<property name="operation" column="OPERATION"/>
<property name="object" column="OBJECT"/>

<one-to-one name="user" class="gor.domain.User"/>

<bag name="historyRecords" cascade="all" lazy="false">
<key column="PERMISSION_ID"/>
<one-to-many class="gor.domain.History"/>
</bag>

</class>

</hibernate-mapping>

History.hbm.xml

Quote:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping>

<class name="gor.domain.History"
table="HISTORY"
proxy="gor.domain.History">

<id name="id" column="ID" type="integer" unsaved-value="null">
<generator class="identity"/>
</id>

<version name="version" column="TX_VERSION"/>

<property name="event" column="EVENT"/>

<many-to-one name="permission" column="PERMISSION_ID" class="gor.domain.Permission"/>

</class>

</hibernate-mapping>

Code between sessionFactory.openSession() and session.close():

hbmResult = HbmSessionFactory.getSession().createQuery(
"select u.name, p From User u inner join u.permission p where u.id=1 order by u.id"
).list();

Name and version of the database you are using:

HSQLDB 1.8.0.7

The generated SQL (show_sql=true):

Hibernate: select user0_.NAME as col_0_0_, permission1_.ID as col_1_0_, permission1_.ID as ID1_, permission1_.TX_VERSION as TX2_1_, permission1_.OPERATION as OPERATION1_, permission1_.OBJECT as OBJECT1_ from USER user0_ inner join PERMISSION permission1_ on user0_.ID=permission1_.ID where user0_.ID=1 order by user0_.ID

Hibernate: select user0_.ID as ID0_2_, user0_.TX_VERSION as TX2_0_2_, user0_.NAME as NAME0_2_, user0_.EMAIL as EMAIL0_2_, user0_.PHONE as PHONE0_2_, user0_.ADDRESS as ADDRESS0_2_, user0_.CITY as CITY0_2_, permission1_.ID as ID1_0_, permission1_.TX_VERSION as TX2_1_0_, permission1_.OPERATION as OPERATION1_0_, permission1_.OBJECT as OBJECT1_0_, user2_.ID as ID0_1_, user2_.TX_VERSION as TX2_0_1_, user2_.NAME as NAME0_1_, user2_.EMAIL as EMAIL0_1_, user2_.PHONE as PHONE0_1_, user2_.ADDRESS as ADDRESS0_1_, user2_.CITY as CITY0_1_ from USER user0_ left outer join PERMISSION permission1_ on user0_.ID=permission1_.ID left outer join USER user2_ on permission1_.ID=user2_.ID where user0_.ID=?

Hibernate: select historyrec0_.PERMISSION_ID as PERMISSION4_1_, historyrec0_.ID as ID1_, historyrec0_.ID as ID2_0_, historyrec0_.TX_VERSION as TX2_2_0_, historyrec0_.EVENT as EVENT2_0_, historyrec0_.PERMISSION_ID as PERMISSION4_2_0_ from HISTORY historyrec0_ where historyrec0_.PERMISSION_ID=?

The questions are:

1) Hibernate returns list of size=1 with array of objects with length=2: correct User's name, but null as the Permission. Associated permission exists in DB. Why it's null?

2) Why Hibernate executes second query (selected bold above) with two outer joins?

At the same time if i slightly change HQL query to this:

hbmResult = HbmSessionFactory.getSession().createQuery(
"select u, p From User u inner join u.permission p where u.id=1 order by u.id"
).list();

Hibernate returns User and corresponding Permission and doesn't execute second query:

The generated SQL (show_sql=true):

Hibernate: select user0_.ID as ID0_0_, permission1_.ID as ID1_1_, user0_.TX_VERSION as TX2_0_0_, user0_.NAME as NAME0_0_, user0_.EMAIL as EMAIL0_0_, user0_.PHONE as PHONE0_0_, user0_.ADDRESS as ADDRESS0_0_, user0_.CITY as CITY0_0_, permission1_.TX_VERSION as TX2_1_1_, permission1_.OPERATION as OPERATION1_1_, permission1_.OBJECT as OBJECT1_1_ from USER user0_ inner join PERMISSION permission1_ on user0_.ID=permission1_.ID where user0_.ID=1 order by user0_.ID

Hibernate: select historyrec0_.PERMISSION_ID as PERMISSION4_1_, historyrec0_.ID as ID1_, historyrec0_.ID as ID2_0_, historyrec0_.TX_VERSION as TX2_2_0_, historyrec0_.EVENT as EVENT2_0_, historyrec0_.PERMISSION_ID as PERMISSION4_2_0_ from HISTORY historyrec0_ where historyrec0_.PERMISSION_ID=?

Could someone explain me the difference between these two cases?

Problems with Session and transaction handling?

Read this: http://hibernate.org/42.html

lester · **Posted:** Tue Feb 05, 2008 11:55 am

I don't know, if it's related to my question, but:

In according with Hibernate source code, Permission is excluded from result due to value of scalarColumnNames instance variable of class QueryLoader doesn't contain any column name, related to Permission, and, therefore, it isn't processed in getResultColumnOrRow method of the same class.

Variable scalarColumnNames (scalar column names from select clause) is populated through method generateColumnNames of class NameGenerator which, in turn, accepts array of types (in my case this is StringType and OneToOneType) and consider columnSpan property (as far i understand, it means column count in mapping) of each type. For StringType columnSpan=1 and for OneToOneType columnSpan=0. That's why generateColumnNames method returns only User's name column name.

It seems reasonable that scalarColumnNames variable doesn't contain any Permission column, because Permission isn't scalar property, but association to entity. But why Permission isn't returned from query...

lester · **Posted:** Thu Feb 07, 2008 7:47 am

I've also just realized this differ from Hibernate2 behaviour:

Hibernate2 uses ManyToOneType to get columnSpan value in method generateColumnNames of class QueryTranslator. For ManyToOneType this value is returned by getColumnSpan method and equals to real count of mapped columns (for one-to-one association it will be one column - foreign key column), i.e 1.

Hibernate3 uses OneToOneType to get columnSpan value in method generateColumnNames (this method was extracted to NameGenerator class in Hibernate3). For OneToOneType this value is returned by getColumnSpan method and set to 0.

Hibernate2 generated query from HQL:

select permission1_.ID as ID, permission1_.TX_VERSION as TX_VERSION,
permission1_.OPERATION as OPERATION, permission1_.OBJECT as OBJECT,
user0_.NAME as x0_0_, permission1_.ID as x1_0_
from USER user0_
inner join PERMISSION permission1_ on user0_.ID=permission1_.ID
where (user0_.ID=1 ) order by user0_.ID

Hibernate3 generated query from HQL:

select user0_.NAME as col_0_0_, permission1_.ID as col_1_0_,
permission1_.ID as ID1_, permission1_.TX_VERSION as TX2_1_,
permission1_.OPERATION as OPERATION1_,
permission1_.OBJECT as OBJECT1_
from USER user0_
inner join PERMISSION permission1_ on user0_.ID=permission1_.ID
where user0_.ID=1 order by user0_.ID

That's why Hibernate2 consider PERMISSION_ID column and Hibernate3 doesn't!

lester · **Posted:** Thu Feb 07, 2008 7:50 am

dublicate

lester · **Posted:** Thu Feb 07, 2008 7:57 am

dublicate

lester · **Posted:** Thu Feb 07, 2008 7:59 am

dublicate

lester · **Posted:** Thu Feb 07, 2008 8:02 am

dublicate

lester · **Posted:** Thu Feb 07, 2008 8:05 am

dublicate

lester · **Posted:** Thu Feb 07, 2008 8:10 am

Sorry for repeating the last message! I wasn't able to connect to the forum and pressed submit button several times. I can't delete redundant messages by myself, so it's request to moderator.

lester · **Posted:** Thu Feb 07, 2008 8:42 am

It seems, I've also understand the purpose of the second query. If i add constrained="true" attribute to the one-to-one mapping between Permission and User, like this:

Permission.hbm.xml

Code:

<one-to-one name="user" class="gor.domain.User" constrained="true"/>

Then Hibernate3 doesn't execute second query. Below is the quote from docs:

Quote:

Mapping one-to-one association with constrained="true", adds a foreign key constraint linking the primary key of the PERMISSION table to the primary key of the USER table. In other words, the database guarantees that an PERMISSION row’s primary key references a valid USER primary key. (As a side effect, Hibernate can now also enable lazy loading of users when a permission is loaded. The foreign key constraint means that a user has to exist for a particular permission, so a proxy can be enabled without hitting the database. Without this constraint, Hibernate has to hit the database to find out if there is a user for the permission; the proxy would then be redundant).

Clay · **Joined:** Mon Mar 07, 2005 6:23 pm **Posts:** 21

Quote:

1) Hibernate returns list of size=1 with array of objects with length=2: correct User's name, but null as the Permission. Associated permission exists in DB. Why it's null?

2) Why Hibernate executes second query (selected bold above) with two outer joins?

The default fetch method for a one-to-one is "select" which is probably why you're getting the extra queries.

I have no idea why the Permission object is null. I assume the query works when executed directly?

lester · **Posted:** Thu Feb 07, 2008 12:25 pm

Quote:

I have no idea why the Permission object is null. I assume the query works when executed directly?

Of course , Clay, query returns value of PERMISSION_ID column and other columns correctly (it's true for queries generated from Hibernate 3.2.5.ga and from Hibernate 2.1.7)

lester · **Posted:** Fri Feb 08, 2008 11:29 am

I spent some time observing Hibernate3 and Hibernate2 sources, trying to find out how Hibernate gets corrsponding types in generateColumnNames method.

Hibernate3 gets OneToOneType from HbmBinder class. HbmBinder instantiates OneToOne class and calls getType method on it, which, in turn, returns OneToOneType.

Hibernate2 gets ManyToOneType from property entityType of class AbstractEntityPersister. This property is assigned (in constructor of AbstractEntityPersister class) to return value of method entity of class Hibernate (static method). It always returns ManyToOneType.