Hibernate version:2.1.6

Mapping documents:
<hibernate-mapping>
<class name="hqltest.Person" table="testperson">
<id name="id" column="ID" type="string"><generator class="uuid.hex"/></id>
<property name="name"><column name="NAME"/></property>
</class>
<class name="hqltest.Company" table="testcompany">
<id name="id" column="ID" type="string"><generator class="uuid.hex"/></id>
<many-to-one name="ceo" class="hqltest.Person" column="CEOID"/>
<many-to-one name="cfo" class="hqltest.Person" column="CFOID"/>
</class>
</hibernate-mapping>


Code between sessionFactory.openSession() and session.close():
Session session = sessionFactory.openSession();
Query query = session.createQuery("from Company c where c.ceo.name like '%1%'");
System.out.println("num companies: " + query.list().size());
query = session.createQuery("from Company c where c.ceo.name like '%1%' or c.cfo.name like '%1%'");
System.out.println("num companies: " + query.list().size());
session.close();



Name and version of the database you are using:Oracle 9

The generated SQL (show_sql=true):
select company0_.ID as ID, company0_.CEOID as CEOID, company0_.CFOID as CFOID from testcompany company0_, testperson person1_ where (person1_.NAME like '%1%' and company0_.CEOID=person1_.ID)
select company0_.ID as ID, company0_.CEOID as CEOID, company0_.CFOID as CFOID from testcompany company0_, testperson person1_, testperson person2_ where (person1_.NAME like '%1%' and company0_.CEOID=person1_.ID)or(person2_.NAME like '%1%' and company0_.CFOID=person2_.ID)



I have two simple classes I created for this test, Person and Company. Person contains an id and a name. Company contains an id, name, cfo and ceo. The cfo and ceo are Person objects defined in the hibernate mapping using a many-to-one.

Here is the relevant sql for this test:
create table testperson (id varchar2(100), name varchar2(100));

insert into testperson values ('1', 'name1');
insert into testperson values ('2', 'name2');
insert into testperson values ('3', 'name3');
insert into testperson values ('4', 'name4');
insert into testperson values ('5', 'name5');
insert into testperson values ('6', 'name6');

create table testcompany (id varchar2(100), name varchar2(100), ceoid varchar2(100), cfoid varchar2(100));

insert into testcompany values ('1', 'company1', '1','2');
insert into testcompany values ('2', 'company2', '3','4');
insert into testcompany values ('3', 'company3', '5','6');

When running the first query [from Company c where c.ceo.name like '%1%], the sql generated is what you'd expect. It does the select, the from includes the company and person table, and the where clause does the join between them.

However, in the second query I specify that I want to search where either the ceo name or the cfo name is %1% [from Company c where c.ceo.name like '%1%' or c.cfo.name like '%1%']. The sql constructed here isn't what I would expect. It joins to the Person table twice, which is fine, but specifies the join relationship as part of the OR'ed condition. This results in a cross-product of results. Instead of returning 1 result in the example above, it returns 6 (the number of results expected * the number of records in the Person table).

The sql generated is:
select company0_.ID as ID, company0_.CEOID as CEOID, company0_.CFOID as CFOID from testcompany company0_, testperson person1_, testperson person2_ where (person1_.NAME like '%1%' and company0_.CEOID=person1_.ID)or(person2_.NAME like '%1%' and company0_.CFOID=person2_.ID)

But shouldn't it be:
select company0_.ID as ID, company0_.CEOID as CEOID, company0_.CFOID as CFOID from testcompany company0_, testperson person1_, testperson person2_ where (person1_.NAME like '%1%' or person2_.NAME like '%1%') and company0_.CEOID=person1_.ID and company0_.CFOID=person2_.ID

Translating the HQL query to english, "Give me all companyies whose cfo or ceo is named like '1'", the corresponding SQL isn't accurrate.

This seems like a bug to me, but I would have thought it would have been hit a while ago. Thoughts, ideas? I've worked around this managing my own joins, which works fine, but I would have expected hibernate to handle this.


Thanks,

-- dave

Any input from anybody? This seems like a bug to me.

This won't be changed since we stick with the SQL behavior.
Add do

What do you mean "stick with the SQL behavior"? What behavior are you referring to?

The sql could also be written as:
select company0_.ID as ID, company0_.CEOID as CEOID, company0_.CFOID as CFOID from testcompany company0_, testperson person1_, testperson person2_ where (person1_.NAME like '%1%' or person2_.NAME like '%1%') and company0_.CEOID=person1_.ID and company0_.CFOID=person2_.ID

which would return results more appropriate to the hql query string.

With the current situation, I'm forced to manage my own joins in the hql instead of going through the hibernate managed relationships. But it's unclear to me why the hql->sql conversion can't go through the same logic that my code is forced to go though.