[hibernate-issues] [Hibernate-JIRA] Closed: (HHH-1433) many-to-many with additional 'with' join clause generates bad sql

[Steve Ebersole (JIRA)]

     [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-1433?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Steve Ebersole closed HHH-1433.
-------------------------------


Closing stale resolved issues

> 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).

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira