Menu
▾
▴
nhibernate-issues
|
From: Roy J. (JIRA) <nh...@gm...> - 2011-05-13 08:34:59
|
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
|
|
From: Roy J. (JIRA) <nh...@gm...> - 2011-05-13 08:56:04
|
[ http://216.121.112.228/browse/NH-2703?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=21068#action_21068 ]
Roy Jacobs commented on NH-2703:
--------------------------------
It seems related to the fact that JoinWalker.MergeOuterJoins doesn't correctly sort the outer joins, but due to my unfamiliarity with the internals I'm not completely sure.
> 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
|
|
From: Roy J. (JIRA) <nh...@gm...> - 2011-05-13 13:28:11
|
[ http://216.121.112.228/browse/NH-2703?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=21071#action_21071 ]
Roy Jacobs commented on NH-2703:
--------------------------------
I did some more research:
The problem is that "JoinWalker.WalkEntityAssociationTree" is adding joins in the order in which the associations are added in the mapping.
So in my above test-case, if I place the "OtherChildren" association above "Children" in the mapping for the "Parent" class, the test succeeds.
Is this by design, or should this still be fixed?
> 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
|
|
From: Roy J. (JIRA) <nh...@gm...> - 2011-05-13 15:15:06
|
[ 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
|
|
From: Roy J. (JIRA) <nh...@gm...> - 2011-05-16 14:45:40
|
[ http://216.121.112.228/browse/NH-2703?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Roy Jacobs updated NH-2703:
---------------------------
Attachment: nh2703-fix.patch
Sorting associations based on dependencies
> 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, nh2703-fix.patch
>
>
> 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
|
|
From: Roy J. (JIRA) <nh...@gm...> - 2011-05-16 14:48:44
|
[ http://216.121.112.228/browse/NH-2703?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=21091#action_21091 ]
Roy Jacobs commented on NH-2703:
--------------------------------
I have attached a patch that solves the issue by sorting the associations with a 'with' clause based on dependencies. I took the topology sorting code from a blog (as indicated in the code).
The analysis of which aliases are used in the 'with' clause uses a regex to figure out the names of the aliases. I realize there must be a cleaner way to do this, but for the life of me I couldn't extract the aliases without it becoming a huge complicated mess (e.g. parsing PropertyExpressions and Subcriteria and so on).
As it is, this solution seems to work and all unit tests pass.
> 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, nh2703-fix.patch
>
>
> 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
|
|
From: Fabio M. (JIRA) <nh...@gm...> - 2011-05-30 18:24:41
|
[ http://216.121.112.228/browse/NH-2703?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Fabio Maulo resolved NH-2703.
-----------------------------
Resolution: Fixed
Fix Version/s: 3.2.0Beta2
> 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
> Fix For: 3.2.0Beta2
>
> Attachments: JoinTest.zip, nh2703-fix.patch
>
>
> 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
|
×
Want the latest updates on software, tech news, and AI?
Get latest updates about software, tech news, and AI from SourceForge directly in your inbox once a month.
Thanks for helping keep SourceForge clean.
X