I think there is a bug in a query statement translated by hibernate. When running a query like this :
SELECT COUNT(o) FROM org.ictrace.server.pm.DMWeightBean o WHERE ((o.sale.reference = :reference) OR (o.purchase.localReference = :reference1))

This shows in the hibernate logs :

2005-11-16 11:13:09,838 DEBUG [org.hibernate.hql.ast.QueryTranslatorImpl] HQL: SELECT COUNT(o) FROM org.ictrace.server.pm.DMWeightBean o WHERE ((o.sale.reference = :reference) OR (o.purchase.localReference = :reference1))
2005-11-16 11:13:09,838 DEBUG [org.hibernate.hql.ast.QueryTranslatorImpl] SQL: select count(dmweightbe0_.UNID_UOID) as col_0_0_ from t_Weight dmweightbe0_, t_WeightGroup dmweightgr1_, t_WeightGroup dmweightgr2_ where dmweightbe0_.f_Purchase_UOID=dmweightgr2_.UNID_UOID and dmweightbe0_.f_Sale_UOID=dmweightgr1_.UNID_UOID and (dmweightgr1_.f_Reference=? or dmweightgr2_.f_LocalReference=?)

This is wrong because there shouldn't be a "AND" UNID_UOID selections.

We are using JBoss 4.0.2 RC1 with EJB3 if this helps.

this is a theta styled join criteria, nothing wrong here.

the dotted notation . makes an implicit inner join

how should the query look like in this case ?

Let me rephrase, I see no error in the SQL query regarding the HQL one.

I tend to not agree with Emmanuel on this one. I may be wrong but if so I would like an explanation.
So, lets take the WHERE clause from the HQL : "WHERE ((o.sale.reference = :reference) OR (o.purchase.localReference = :reference1))"

This means that it should select "o" if one of the conditions is meet(and regardless of what is happening to the other condition), right?

Then from the log we see that this is translated in :
where dmweightbe0_.f_Purchase_UOID=dmweightgr2_.UNID_UOID and dmweightbe0_.f_Sale_UOID=dmweightgr1_.UNID_UOID and (dmweightgr1_.f_Reference=? or dmweightgr2_.f_LocalReference=?)

And I think that the theta join is wrong, since it uses "and"
"dmweightbe0_.f_Purchase_UOID=dmweightgr2_.UNID_UOID and dmweightbe0_.f_Sale_UOID=dmweightgr1_.UNID_UOID"

And this implies that both FK, f_Purchase_UOID and f_Sale_UOID must match which is wrong.

Shouldn't the where clause be something like :
where
(dmweightbe0_.f_Purchase_UOID=dmweightgr2_.UNID_UOID and dmweightgr2_.f_LocalReference=? )
and
(dmweightbe0_.f_Sale_UOID=dmweightgr1_.UNID_UOID and dmweightgr1_.f_Reference=? )


I'm not very experienced with this but this look more logical to me. Could you please explain why/where I might be wrong?

Regards, Andrei Chiritescu

Sorry, I have made a little mistake(used AND instead of OR) so, here is the correct query:
where
(dmweightbe0_.f_Purchase_UOID=dmweightgr2_.UNID_UOID and dmweightgr2_.f_LocalReference=? )
OR
(dmweightbe0_.f_Sale_UOID=dmweightgr1_.UNID_UOID and dmweightgr1_.f_Reference=? )

Why, because *you* say so? Based on what? Your gut?

I understand what you are saying from a wishful thinking position, but that has nothing to do with reality, nor how Hibernate has treated this forever, nor what the ejb3 spec actually says on the subject.

Have a look at the Hibernate documentation regarding implicit / explicit joins.

Oh, but that's just vendor interpretation, right? Nope. Go and have a look at the EJB3 spec (section 4.4.4 Path Expressions):

So the right way to do it is: