Hibernate version: 3.1
Name and version of the database you are using: MySql 5

Hi,
I have problem with following HQL query:

s.createQuery("select g.klucze.id,g.klucze.wartosc,count(distinct g) from Elem g" +
" where datediff (sysdate(),g.data) <= :dni and" +
" g.skasowany='' and g.klucze.blokada=''" +
" group by g.klucze.id,g.klucze.wartosc order by 3 desc");

the generated sql:

select klucz2_.id as col_0_0_, klucz4_.wartosc as col_1_0_, count(distinct elem0_.id) as col_2_0_, 0 as col_3_0_, 0.0 as col_4_0_ from Elem elem0_, elem_klucz klucze1_, Klucz klucz2_, elem_klucz klucze3_, Klucz klucz4_, elem_klucz klucze5_, Klucz klucz6_, elem_klucz klucze7_, Klucz klucz8_, elem_klucz klucze9_, Klucz klucz10_ where elem0_.id=klucze9_.elem_id and klucze9_.klucz_id=klucz10_.id and elem0_.id=klucze7_.elem_id and klucze7_.klucz_id=klucz8_.id and elem0_.id=klucze5_.elem_id and klucze5_.klucz_id=klucz6_.id and elem0_.id=klucze3_.elem_id and klucze3_.klucz_id=klucz4_.id and elem0_.id=klucze1_.elem_id and klucze1_.klucz_id=klucz2_.id and datediff(sysdate(), elem0_.data)<=? and elem0_.skasowany='' and klucz6_.blokada='' group by klucz8_.id , klucz10_.wartosc order by 3 desc limit ?

relation between Elem and Klucz is as follows:

@ManyToMany(cascade = CascadeType.PERSIST)
@BatchSize(size=30)
@JoinTable(
table = @Table(name="elem_klucz"),
joinColumns = @JoinColumn(name="elem_id"),
inverseJoinColumns = @JoinColumn(name="klucz_id")
)
@Cache(usage = CacheConcurrencyStrategy.NONSTRICT_READ_WRITE)
public Set<Klucz> getKlucze() {
return klucze;
}

The problem with this sql is clearly multiple Klucz table refereces instead of one, hence not working group by statement, please explain me why this multiple Klucz.elem_klucz table references (Klucz klucz2_, elem_klucz klucze3_, Klucz klucz4_, elem_klucz klucze5_) happen, where is an error.

Thanks
KM

Implicit joins are designed to work this way: each reference is independent of other implicit joins, even if they end up in the same place. Use explicit joins to force the query to use a single table alias.