many-to-many with additional 'with' join clause generates bad sql ----------------------------------------------------------------- Key: HHH-1433 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-1433 Project: Hibernate Core Issue Type: Bug Components: query-hql Affects Versions: 3.1 rc2, 3.1.2 Environment: java 1.4 - hibernate 3.1.2 - mysql 5 Reporter: Dieter Cailliau Assignee: Steve Ebersole Priority: Minor Fix For: 3.1.3 Say we have a many-to-many relation between Service and Package. I want all Service-Package combinations, for packages having title 'ok'. If for some service, there is no such a package, then i want the service anyways (with nulls in other places). select service,package from Service service left join service.packages package with package.title='ok' This produces sql select ... from SERVICE service0_ left outer join PACKAGE_SERVICE packages1_ on service0_.ID=packages1_.SERVICE_ID and (package2_.TITLE='ok') -- [this line is bad] left outer join PACKAGE package2_ on packages1_.PACKAGE_ID=package2_.ID and (package2_.TITLE='ok') What happend? The hql-with-clause is present twice in the sql. The first occurence (at the join-table) is wrong: package2_ is not defined at that point. If i move the with clause into a where clause, it works fine, but this does not give me the "services that have no such package" (= it is equivalent with the inner join, but not with the left join).