Wierd stuff creating a query with many joins

robcos · **Posted:** Wed Feb 25, 2004 11:16 am

Hi guys.
I'm builduing a complex query at runtime.

It looks like:

Code:

DEBUG [net.sf.hibernate.hql.QueryTranslator] - HQL: select count(distinct recipient) from com.nmt.msp.resource.CategoryRecipientResource as categoryRecipient  join categoryRecipient.recipient as recipient  join categoryRecipient.category as category0  join categoryRecipient.category as category1  where recipient.system =:my_system and category0 IN (:my_category0) and category1 IN (:my_category1) AND (recipient.deleted = 0)

The QueryTranslator makes it to:

Code:

DEBUG [net.sf.hibernate.hql.QueryTranslator] - SQL: select count(distinct recipientr1_.id) as x0_0_ from categoryrecipient categoryre0_ inner join recipient recipientr1_ on categoryre0_.recipientid=recipientr1_.id inner join category simplecate2_ on categoryre0_.categoryid=simplecate2_.id where (recipientr1_.systemid=? )and(simplecate2_.id IN(?))and(simplecate2_.id IN(?))AND((recipientr1_.deleted=0 ))

Note the where conditions:

Code:

where recipient.system =:my_system and category0 IN (:my_category0) and category1 IN (:my_category1) AND (recipient.deleted = 0)

Code:

where (recipientr1_.systemid=? )and(simplecate2_.id IN(?))and(simplecate2_.id IN(?))AND((recipientr1_.deleted=0 ))

The join on category0 has desappeared. The where clause mentions twice simplecate2_ .

What's wrong?

/Roberto.

Quote:

#Hibernate properties generated through Ant
#Wed Feb 25 15:57:49 CET 2004
hibernate.dialect=net.sf.hibernate.dialect.MySQLDialect
hibernate.jdbc.use_streams_for_binary=true
hibernate.show_sql=true
hibernate.use_outer_join=true
hibernate.jdbc.batch_size=0
hibernate.cache.use_query_cache=false

emmanuel · **Posted:** Wed Feb 25, 2004 12:06 pm

I think you cannot do 2 joins on the same collection (same table). Try do do it in plain SQL and if successful raise a bug to JIRA.

robcos · **Posted:** Thu Feb 26, 2004 3:34 am

I found out that my query had not much sense. Anyway this query is accepted by mysql 4.0:

Code:

select count(distinct recipientr1_.id) as x0_0_ from categoryrecipient categoryre0_ inner join recipient recipientr1_ on categoryre0_.recipientid=recipientr1_.id inner join category simplecate1_ on categoryre0_.categoryid=simplecate1_.id inner join category simplecate2_ on categoryre0_.categoryid=simplecate2_.id where (simplecate1_.id IN(1))and(simplecate2_.id IN(2));

It uses two joins on the same table.
Should I report a bug?

emmanuel · **Posted:** Thu Feb 26, 2004 7:04 am

Yes please