[nhibernate-issues] [NHJIRA] Commented: (NH-2703) Using a "with" restriction in outer joins result in wrong SQL

[Roy J. (JIRA) <nh...@gm...>]

    [ http://216.121.112.228/browse/NH-2703?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=21072#action_21072 ] 

Roy Jacobs commented on NH-2703:
--------------------------------

If the joins could somehow respect the order that is also generated by "rootCriteria.IterateSubcriteria()" that would help already, because then it would be possible to manually influence the order of joins because they would reflect the order by which the subcriteria were added.

> Using a "with" restriction in outer joins result in wrong SQL
> -------------------------------------------------------------
>
>                 Key: NH-2703
>                 URL: http://216.121.112.228/browse/NH-2703
>             Project:  NHibernate
>          Issue Type: Bug
>          Components: QueryOver
>    Affects Versions: 3.2.0Beta1
>            Reporter: Roy Jacobs
>            Priority: Major
>         Attachments: JoinTest.zip
>
>
> I have a Parent class that has two types of children: "Child" in a list called "Children" and "Other" in a list called "OtherChildren".
> If I do a QueryOver on Parent, and outer join both Child and Other tables then everything works.
> If I add a "With" on the "Child" join, linking it to "Other" (e.g. child.MyProp == other.OtherProp) then the SQL that is generated does not work in SQL Server 2008:
> SELECT child2_.MyProp as y0_
> FROM Parent this_
> left outer join Child child2_ on this_.Id=child2_.parent_id and ( child2_.MyProp = other1_.OtherProp )
> left outer join Other other1_ on this_.Id=other1_.parent_id
> The error message is:
> System.Data.SqlClient.SqlException : The multi-part identifier "other1_.OtherProp" could not be bound.
> This is because SQL Server expects "other1_" to be joined *BEFORE* "child2_". If I manually change the join order around in the SQL, it works.
> Reshuffling the order in which I perform the joins in C# does not help, the emitted SQL is always incorrect.
> I have attached a failing test that can be added to NHibernate.Test which demonstrates this problem.

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