The following hibernate query returns 1100 records(OK) :
-FROM tournament.Match

The following hibernate queries return about 10 records (OK):
-FROM tournament.Match as match WHERE match.participation1.contender.id IN (23,43)
-FROM tournament.Match as match WHERE match.participation2.contender.id IN (23,43)

problem:
The following hibernate query returns more than 3000 results which is impossible since there are only 1100 tournament.Match records(NOT OK):
-FROM tournament.Match as match WHERE match.participation1.contender.id IN (23,43) OR match.participation2.contender.id IN (23,43)

The same match records are being returned multiple times.


Is the cause of this problem my misunderstanding of the query language or is hibernate suffering from my previous mistakes?


If anyone could help me find the cause of this problem, thanks

Check the generated SQL. If you can't figure out yourself, post your (simplified) mappings and the generated SQL.

This is probably the correct result. Look at the SQL to see why.

I can't understand how this could be the correct result.

FROM tournament.Match ==> returns 1100 record
FROM tournament.Match WHERE ...==> returns over 3000 records (the same records repeated +/-200 times)

In my understanding a WHERE clause should never be a reason to increase the number of results. A WHERE clause only should be able to decrease the number of results. (so I'm tempted to think that hibernate is making an obvious mistake here, but I'm sure it's just me)

I will now break down my program until it's simple enough to understand the generated SQL or to post mappings if still necessary. I will post my findings within a few days.

Thanks for your replies, keep up the good work

if your mapping is wrong, the result is wrong but the sql is ok

by doing a where clause joined condition are generated, if the mapping lacks 3 joined (relations) --> you can have 3 times the same results

thanks for the advice delpouve, but I still couldn't solve it

I stripped down the problem until the bare essentials:

I've got 3 classes: Game, participation and Person

a game consist out of 2 participations who play against eachother.
A participation consist out of a Person and a boolean to check if the person has allready paid for the match.


The Game Class:
/**
*@hibernate.class
*/
public class Game{

private Participation participation1;
private Participation participation2;

Long id;

public Game (){}

public Game (Participation participation1, Participation participation2)
{
this.participation1=participation1;
this.participation2=participation2;
}

/**
*@hibernate.id generator-class="native"
*/
public Long getId() {return id;}
public void setId(Long id) {this.id = id;}

/**
*@hibernate.many-to-one
* cascade="all"
*/
public Participation getParticipation1(){return participation1;}
public void setParticipation1(Participation participation1){this.participation1 = participation1;}

/**
*@hibernate.many-to-one
* cascade="all"
*/
public Participation getParticipation2(){return participation2;}
public void setParticipation2(Participation participation2){this.participation2 = participation2;}

public String toString()
{
return participation1 + " against " + participation2;
}

}


The participation class:
/**
* @hibernate.class
*/
public class Participation
{
private Long id;
private Person person;
boolean paid;


public Participation () {}

public Participation (Person person)
{
this.person = person;
paid=false;
}

/**
*@hibernate.id generator-class="native"
*/
public Long getId(){return id;}
public void setId(Long id) {this.id = id;}

/**
*@hibernate.many-to-one
* cascade="all"
*/
public Person getPerson(){return person;}
public void setPerson(Person person){this.person=person;}

/**
*@hibernate.property
*/
public boolean getPaid(){return paid;}
public void setPaid(boolean paid){this.paid=paid;}

public String toString()
{
return person.toString();
}
}

The Person Class:
/**
*@hibernate.class
*/
public class Person
{
Long id;

private String name;

public Person() {}

public Person (String name)
{
this.name = name;
}

/**
*@hibernate.id generator-class="native"
*/
public Long getId(){return id;}
public void setId(Long id){this.id = id;}

/**
*@hibernate.property
*/
public String getName () { return this.name;}
public void setName (String name) { this.name = name;}

public String toString()
{
return name;
}
}

Xdoclet generated the following mappings:

The mapping of the Game class:
<?xml version="1.0"?>

<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 2.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">

<hibernate-mapping>
<class
name="equality.tornooi.Game"
dynamic-update="false"
dynamic-insert="false"
>

<id
name="id"
column="id"
type="java.lang.Long"
>
<generator class="native">
</generator>
</id>

<many-to-one
name="participation1"
class="equality.tornooi.Participation"
cascade="all"
outer-join="auto"
update="true"
insert="true"
column="participation1"
/>

<many-to-one
name="participation2"
class="equality.tornooi.Participation"
cascade="all"
outer-join="auto"
update="true"
insert="true"
column="participation2"
/>

<!--
To add non XDoclet property mappings, create a file named
hibernate-properties-Game.xml
containing the additional properties and place it in your merge dir.
-->

</class>

</hibernate-mapping>


The mapping of the Participation class:
<?xml version="1.0"?>

<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 2.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">

<hibernate-mapping>
<class
name="equality.tornooi.Participation"
dynamic-update="false"
dynamic-insert="false"
>

<id
name="id"
column="id"
type="java.lang.Long"
>
<generator class="native">
</generator>
</id>

<many-to-one
name="person"
class="equality.tornooi.Person"
cascade="all"
outer-join="auto"
update="true"
insert="true"
column="person"
/>

<property
name="paid"
type="boolean"
update="true"
insert="true"
column="paid"
/>

<!--
To add non XDoclet property mappings, create a file named
hibernate-properties-Participation.xml
containing the additional properties and place it in your merge dir.
-->

</class>

</hibernate-mapping>

The mapping of the Person class:
<?xml version="1.0"?>

<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 2.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">

<hibernate-mapping>
<class
name="equality.tornooi.Person"
dynamic-update="false"
dynamic-insert="false"
>

<id
name="id"
column="id"
type="java.lang.Long"
>
<generator class="native">
</generator>
</id>

<property
name="name"
type="java.lang.String"
update="true"
insert="true"
column="name"
/>

<!--
To add non XDoclet property mappings, create a file named
hibernate-properties-Person.xml
containing the additional properties and place it in your merge dir.
-->

</class>

</hibernate-mapping>


Ok, so far the basic classes, now I will show where things go wrong

I created some Games by executing the following statement a few times:
hibSession.save(new Game(new Participation(new Person("nameOfPerson1")),new Participation(new Person("nameOfPerson2"))));

When I later on try to execute the following query (to get the all games from a person(s)):
Query q = hibSession.createQuery(FROM Game as game WHERE game.participation1.person.id IN (1) OR game.participation2.person.id IN (1))
I get the same records 10 times.

This is the SQL generated by hibernate:
Hibernate: select game0_.id as id, game0_.participation1 as particip2_, game0_.participation2 as particip3_ from Game game0_, Participation participat1_, Participation participat2_ where (participat1_.person IN(1) and game0_.participation1=participat1_.id)OR(participat2_.person IN(1) and game0_.participation2=participat2_.id)
Hibernate: select participat0_.id as id1_, participat0_.person as person1_, participat0_.paid as paid1_, person1_.id as id0_, person1_.name as name0_ from Participation participat0_ left outer join Person person1_ on participat0_.person=person1_.id where participat0_.id=?
Hibernate: select participat0_.id as id1_, participat0_.person as person1_, participat0_.paid as paid1_, person1_.id as id0_, person1_.name as name0_ from Participation participat0_ left outer join Person person1_ on participat0_.person=person1_.id where participat0_.id=?


Analyzing the first generated SQL statement (where the problem is situated, since this line returns to much records):
select game0_.id as id,
game0_.participation1 as particip2_
game0_.participation2 as particip3_
from Game game0_,
Participation participat1_,
Participation participat2_ <==one join to much
where (participat1_.person IN(1) and game0_.participation1=participat1_.id)
OR(participat2_.person IN(1) and game0_.participation2=participat2_.id)


Correcting the SQL, I think I want hibernate to generate something like this:
select game0_.id as id,
game0_.participation1 as particip2_,
game0_.participation2 as particip3_
from Game game0_,
Participation participat1_
where (participat1_.person IN(1) and game0_.participation1=participat1_.id)
OR(participat1_.person IN(1) and game0_.participation2=participat1_.id);

But for some reason, hibernate doesn't do that.I get to much records back. Can anyone please help me out. I've been looking at this problem far to long now

This is semantically correct (think about null values). If you want a single join, don't do it as an implicit join inside the or condition, do it as an explicit from-clause join.

Sorry, I just can't figure it out. The problem can't be that difficult. I just want the games played by a person. It is a 3 table database. In ordinary JDBC I would solve this in notime:

select game.id
from Game game,Participation participation
where (participation.person IN(?) and game.participation1 =participation.id)
OR(participation.person IN(?) and game.participation2=participation.id);

? is replaced by the personID

But in HQL, I just can't find it. Of course I could just use this handcoded JDBC, but I'd prefer not. I've been trying things with explicit joins but then I get "outer or full join must be followed by path expression" Exceptions and so on. And even though the joining would work. I still can't see how to solve this problem without using an 'OR' in a 'WHERE' clause. I've been reading the HQL chapter in the refernce manual, reading forum posts, ... .

So If anyone could please post the HQL query which will give me the games played by a person(one result per game), I might get finally on the way with HQL. I would be really greatefull.

If you are using Hibernate 2.1.2
use SQLApi if you really don't manage to this with HQL
you'll find examples in the doc

select game.* as {your class}
from Game game,Participation participation
where (participation.person IN(?) and game.participation1 =participation.id)
OR(participation.person IN(?) and game.participation2=participation.id); ....

Thank you, that did the trick.

on first site, this query works fine:
Query sqlQuery = hibSession.createSQLQuery("select {game.*} from Game {game} ,Participation participation where (participation.person IN(1) and game.participation1 =participation.id) OR(participation.person IN(1) and game.participation2=participation.id)", "game", Game.class);

I should print the new version of the Hibernate Reference. Apperently some interesting new feature are available new. I was thinking of something like this when I was trying to solve my proble. "If there only was a way to ask it in straight SQL".

Although I believe that there should be a way to ask it in HQL, but that's became more a principle matter now. (I hope, this was a very much simplified representation of the problem. I hope it will servive the more complicated reality)

Thanks again

In HQL the following query produces the right result:
SELECT DISTINCT FROM Game as game WHERE game.participation1.person.id IN (1) OR game.participation2.person.id IN (1)

Although I'm sure this result is not retrieved the most efficient way. I may not see the complete picture here, but I've got the feeling that hibernate could be made more efficient.

Hibernate generates 1 join to much:
select distinct ....
from Game game0_,
Participation participat1_,
Participation participat2_ <==one join to much
where (participat1_.person IN(1) and game0_.participation1=participat1_.id)
OR(participat2_.person IN(1) and game0_.participation2=participat2_.id)

I think hibernate should be able to see that particpation1 and participation2 apply to the same table and therefore conclude that 1 join with the participation table is sufficient.


I think this is what hibernate should produce:
select game0_.id as id,
game0_.participation1 as particip2_,
game0_.participation2 as particip3_
from Game game0_,
Participation participat1_
where (participat1_.person IN(1) and game0_.participation1=participat1_.id)
OR(participat1_.person IN(1) and game0_.participation2=participat1_.id);

I don't get the issue with the null values or how to perform this query with an explicit from-clause join mentioned by Gavin . Forgive me my ignorance

greetz

This is honestly all very correct since Hibernate has inner join semantics for path expressions (if we would have chosen outer join semantics, it would handle this situation better, but I think usually inner join is more convenient). So, all you do is use a from-clause join:



Easy.

I've done some very basic benchmarking. And I'm not pretending I'm a benchmark specialist. Many other factors may be playing here.

I've inserted 10000 games records the following way
for(int i=0;i<10000;i++)
{
String p1 = "name" + (i+1);
String p2 = "name" + (i+2);
hibSession.save(new Game(new Participation(new Person(p1)),new Participation(new Person(p2))));
}

Then on my mysql database I performed the following queries:

Query1: How I would write my own sql
mysql> select game0_.id as id,
-> game0_.participation1 as particip2_,
-> game0_.participation2 as particip3_
-> from Game game0_,
-> Participation participat1_
-> where (participat1_.person IN(1) and game0_.participation1=participat1_.id)
-> OR(participat1_.person IN(1) and game0_.participation2=participat1_.id);
+----+------------+------------+
| id | particip2_ | particip3_ |
+----+------------+------------+
| 1 | 1 | 2 |
+----+------------+------------+
1 row in set (0.02 sec)


Query2:generated by hibernate
SELECT game from Game game
inner join game.participation1 p1
inner join game.participation2 p2
where p1.person.id in (1) or p2.person.id in (1)

resulted in the folowing query generated by hibernate:


mysql> select game0_.id as id,
-> game0_.participation1 as particip2_,
-> game0_.participation2 as particip3_
-> from Game game0_
-> inner join Participation participat1_ on game0_.participation1=participat1_.id
-> inner join Participation participat2_ on game0_.participation2=participat2_.id
-> where (participat1_.person in(1))or(participat2_.person in(1));
+----+------------+------------+
| id | particip2_ | particip3_ |
+----+------------+------------+
| 1 | 1 | 2 |
+----+------------+------------+
1 row in set (0.11 sec)


As you cas see from the timings made by mysql: My handwrited sql is 5 times faster. I may be wrong and have forgotten about other factors. But I've tested every query 10 times switching between them. And the result remained.


==>Inner, right or left join. It is one join to much. Hibernate should be able to see that game.participation1 and game.participation2 apply on the same table. And therefore conclude that one join is enough.



I can see the following answers to my question:
a)In a perfect world, hibernate would see that the participations aply on the same table and therefore hibernate can conclude that one join is sufficient. But for now, this isn't the case.
b)It is better that hibernate uses 2 joins because in other situations 1 join causes problems.(maybe hibernate can't be certain that the participations apply on the same table. Because participation might be a superclass)

A simple a or b will stop me from asking more questions because I'm not intending to waste your time with allready resolved issues. I'm not well enough informed to understand the complete picture.

Greetz

I just want to tell, since I enabled lazy loading, hibernate is fast enough for me.
It's only a principal matter(to me)

Hibernate rocks

Please just use the alternative query I showed you!!

I am well aware that the query you wrote is inefficient. That is the cost of some other nice semantics we chose. I can't make that query be efficient without breaking other stuff.

This is a case of "doctor, it hurts when I do this".