Menu
▾
▴
nhibernate-issues
|
From: Harald M. M. (J. <nh...@gm...> - 2011-03-17 15:46:53
|
Query with || operator and navigations (many-to-one) creates wrong joins
------------------------------------------------------------------------
Key: NHLQ-102
URL: http://216.121.112.228/browse/NHLQ-102
Project: NHibernate.Linq
Issue Type: Bug
Components: Core
Affects Versions: 1.0.0
Reporter: Harald M. Müller
Priority: Critical
Attachments: NHib3.0.0Or.zip
The following query
var result = session.Query<MyBO>()
.Where(bo =>
(bo.BO1 != null && bo.BO1.I2 == 101) ||
(bo.BO2 != null && bo.BO2.J2 == 203)
)
;
result.ToList();
creates the following SQL on SQL Server 2008 (with .Net 4.0, MsSql2008Dialect):
exec sp_executesql N'select mybo0_.Id as Id0_, mybo0_.Name as Name0_, mybo0_.BO1Key as BO3_0_, mybo0_.OtherBO1Key as OtherBO4_0_, mybo0_.BO2Key as BO5_0_ from MyBO mybo0_, MyRef1 myref1x1_, MyRef2 myref2x2_ where mybo0_.BO1Key=myref1x1_.Id and mybo0_.BO2Key=myref2x2_.Id and ((mybo0_.BO1Key is not null) and myref1x1_.I2=@p0 or (mybo0_.BO2Key is not null) and myref2x2_.J2=@p1)',N'@p0 int,@p1 int',@p0=101,@p1=203
or, formatted:
SELECT mybo0_.id AS id0_,
mybo0_.name AS name0_,
mybo0_.bo1key AS bo3_0_,
mybo0_.otherbo1key AS otherbo4_0_,
mybo0_.bo2key AS bo5_0_
FROM mybo mybo0_,
myref1 myref1x1_,
myref2 myref2x2_
WHERE mybo0_.bo1key = myref1x1_.id -- inner (table) join
AND mybo0_.bo2key = myref2x2_.id -- also inner (table) join
AND ( ( mybo0_.bo1key IS NOT NULL )
AND myref1x1_.i2 = @p0
OR ( mybo0_.bo2key IS NOT NULL )
AND myref2x2_.j2 = @p1 )
This is wrong if the reference to ref1 or ref2 is null. The attachment contains a small project that shows the problem.
AFAIK, the problem has been always present when using HQL navigations (a.b.c....); it is - somewhat implicitly - documented in seciotn 13.7.:
Compound path expressions make the where clause extremely powerful. Consider:
from Eg.Cat cat where cat.Mate.Name is not null
This query translates to an SQL query with a table (inner) join.
In HQL, one can claim that this is "a feature" (and the workaround is to use manual JOINs). With Linq, this is no longer possible - the semantics of the || operator should be correctly implemented, shouldn't it? Therefore, I dared to mark this bug as "Critical".
(... or - please - show me that I'm wrong and that || + navigation work as expected!).
Remark: In our project, which started with NHib 0.99 six years ago, we(I) wrote a complete expression framework akin to Linq (but only with conditions, not with expressions). When we found this problem, I rewrote the HQL builder so that it then created (and still creates) correct INNER or OUTER JOINs, depending on the expression tree (one has to consider Is-Null/Is-Not-Null operators and Not nodes above these, as far as I remember ... I'd have to look into our code).
--
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: Harald M. M. (J. <nh...@gm...> - 2011-03-17 21:04:44
|
[ http://216.121.112.228/browse/NHLQ-102?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=20668#action_20668 ]
Harald M. Müller commented on NHLQ-102:
---------------------------------------
(Me again)
What I said at the end of the bug report is not correct. Actually, our solution is as follows:
First, note that inner joins are not necessary in HQL queries. They can *always* be replaced with a combination of
* an LEFT OUTER JOIN to some mapped class, say C
* and an "existence condition" saying C.PK IS NOT NULL (actually, an AND over all PK properties)
Therefore, a condition like
FROM MyBO bo WHERE
bo.BO1 IS NOT NULL AND bo.BO1.I2 = 101 OR
bo.BO2 IS NOT NULL AND bo.BO2.J2 = 203
can (and needs to) be written as
FROM MyBO bo
LEFT OUTER JOIN bo.BO1 bo1
LEFT OUTER JOIN bo.BO2 bo2
WHERE
(bo.PK IS NOT NULL and bo.BO1 IS NOT NULL) AND (bo1.PK IS NOT NULL and bo1.I2 = 101) OR
(bo.PK IS NOT NULL and bo.BO2 IS NOT NULL) AND (bo2.PK IS NOT NULL and bo2.J2 = 203)
>From here on, one can optimize various parts:
* Only OUTER JOINs to mapped classes referenced "below an OR" need to be kept; OUTER JOINs with no OR node above can be left as INNER JOINs.
* Also, existence conditions for BOs already referenced above each OR can be dropped (like the bo.PK IS NOT NULL in the example).
* Multiple occurrences of the same "existence condition" can be "pulled up" in the expression tree to the nearest OR operator.
However, we thought about this under the condition that the Expression/Linq-Any operator and Expression/Linq-Joins to other mapped classes are done via subselects, which have their own "JOIN list" (FROM part). We could do this because we know that we target SQL Server, whose NHib dialect allwos for subselects.
I'm not sure whether the simple replacement rule above and the optimization rules are also valid if Any/Join/etc. need to be done in a single SQL FROM without subselects ...
So much for a suggestion on a solution that works for us. How much of this can be integrated into NHib.Linq (are the AddLeftJoinsReWriter and LeftJoinDetector classes the right place; and sufficient?), I do not know. Maybe I find time to look into this.
Regards
Harald M.
> Query with || operator and navigations (many-to-one) creates wrong joins
> ------------------------------------------------------------------------
>
> Key: NHLQ-102
> URL: http://216.121.112.228/browse/NHLQ-102
> Project: NHibernate.Linq
> Issue Type: Bug
> Components: Core
> Affects Versions: 1.0.0
> Reporter: Harald M. Müller
> Priority: Critical
> Attachments: NHib3.0.0Or.zip
>
>
> The following query
> var result = session.Query<MyBO>()
> .Where(bo =>
> (bo.BO1 != null && bo.BO1.I2 == 101) ||
> (bo.BO2 != null && bo.BO2.J2 == 203)
> )
> ;
> result.ToList();
> creates the following SQL on SQL Server 2008 (with .Net 4.0, MsSql2008Dialect):
> exec sp_executesql N'select mybo0_.Id as Id0_, mybo0_.Name as Name0_, mybo0_.BO1Key as BO3_0_, mybo0_.OtherBO1Key as OtherBO4_0_, mybo0_.BO2Key as BO5_0_ from MyBO mybo0_, MyRef1 myref1x1_, MyRef2 myref2x2_ where mybo0_.BO1Key=myref1x1_.Id and mybo0_.BO2Key=myref2x2_.Id and ((mybo0_.BO1Key is not null) and myref1x1_.I2=@p0 or (mybo0_.BO2Key is not null) and myref2x2_.J2=@p1)',N'@p0 int,@p1 int',@p0=101,@p1=203
> or, formatted:
> SELECT mybo0_.id AS id0_,
> mybo0_.name AS name0_,
> mybo0_.bo1key AS bo3_0_,
> mybo0_.otherbo1key AS otherbo4_0_,
> mybo0_.bo2key AS bo5_0_
> FROM mybo mybo0_,
> myref1 myref1x1_,
> myref2 myref2x2_
> WHERE mybo0_.bo1key = myref1x1_.id -- inner (table) join
> AND mybo0_.bo2key = myref2x2_.id -- also inner (table) join
> AND ( ( mybo0_.bo1key IS NOT NULL )
> AND myref1x1_.i2 = @p0
> OR ( mybo0_.bo2key IS NOT NULL )
> AND myref2x2_.j2 = @p1 )
> This is wrong if the reference to ref1 or ref2 is null. The attachment contains a small project that shows the problem.
> AFAIK, the problem has been always present when using HQL navigations (a.b.c....); it is - somewhat implicitly - documented in seciotn 13.7.:
> Compound path expressions make the where clause extremely powerful. Consider:
> from Eg.Cat cat where cat.Mate.Name is not null
> This query translates to an SQL query with a table (inner) join.
> In HQL, one can claim that this is "a feature" (and the workaround is to use manual JOINs). With Linq, this is no longer possible - the semantics of the || operator should be correctly implemented, shouldn't it? Therefore, I dared to mark this bug as "Critical".
> (... or - please - show me that I'm wrong and that || + navigation work as expected!).
> Remark: In our project, which started with NHib 0.99 six years ago, we(I) wrote a complete expression framework akin to Linq (but only with conditions, not with expressions). When we found this problem, I rewrote the HQL builder so that it then created (and still creates) correct INNER or OUTER JOINs, depending on the expression tree (one has to consider Is-Null/Is-Not-Null operators and Not nodes above these, as far as I remember ... I'd have to look into our code).
--
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: Steve B. (JIRA) <nh...@gm...> - 2011-03-17 21:06:35
|
[ http://216.121.112.228/browse/NH-2583?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Steve Bohlen moved NHLQ-102 to NH-2583:
---------------------------------------
Component/s: (was: Core)
Linq Provider
Affects Version/s: (was: 1.0.0)
3.1.0
Key: NH-2583 (was: NHLQ-102)
Project: NHibernate (was: NHibernate.Linq)
> Query with || operator and navigations (many-to-one) creates wrong joins
> ------------------------------------------------------------------------
>
> Key: NH-2583
> URL: http://216.121.112.228/browse/NH-2583
> Project: NHibernate
> Issue Type: Bug
> Components: Linq Provider
> Affects Versions: 3.1.0
> Reporter: Harald M. Müller
> Priority: Critical
> Attachments: NHib3.0.0Or.zip
>
>
> The following query
> var result = session.Query<MyBO>()
> .Where(bo =>
> (bo.BO1 != null && bo.BO1.I2 == 101) ||
> (bo.BO2 != null && bo.BO2.J2 == 203)
> )
> ;
> result.ToList();
> creates the following SQL on SQL Server 2008 (with .Net 4.0, MsSql2008Dialect):
> exec sp_executesql N'select mybo0_.Id as Id0_, mybo0_.Name as Name0_, mybo0_.BO1Key as BO3_0_, mybo0_.OtherBO1Key as OtherBO4_0_, mybo0_.BO2Key as BO5_0_ from MyBO mybo0_, MyRef1 myref1x1_, MyRef2 myref2x2_ where mybo0_.BO1Key=myref1x1_.Id and mybo0_.BO2Key=myref2x2_.Id and ((mybo0_.BO1Key is not null) and myref1x1_.I2=@p0 or (mybo0_.BO2Key is not null) and myref2x2_.J2=@p1)',N'@p0 int,@p1 int',@p0=101,@p1=203
> or, formatted:
> SELECT mybo0_.id AS id0_,
> mybo0_.name AS name0_,
> mybo0_.bo1key AS bo3_0_,
> mybo0_.otherbo1key AS otherbo4_0_,
> mybo0_.bo2key AS bo5_0_
> FROM mybo mybo0_,
> myref1 myref1x1_,
> myref2 myref2x2_
> WHERE mybo0_.bo1key = myref1x1_.id -- inner (table) join
> AND mybo0_.bo2key = myref2x2_.id -- also inner (table) join
> AND ( ( mybo0_.bo1key IS NOT NULL )
> AND myref1x1_.i2 = @p0
> OR ( mybo0_.bo2key IS NOT NULL )
> AND myref2x2_.j2 = @p1 )
> This is wrong if the reference to ref1 or ref2 is null. The attachment contains a small project that shows the problem.
> AFAIK, the problem has been always present when using HQL navigations (a.b.c....); it is - somewhat implicitly - documented in seciotn 13.7.:
> Compound path expressions make the where clause extremely powerful. Consider:
> from Eg.Cat cat where cat.Mate.Name is not null
> This query translates to an SQL query with a table (inner) join.
> In HQL, one can claim that this is "a feature" (and the workaround is to use manual JOINs). With Linq, this is no longer possible - the semantics of the || operator should be correctly implemented, shouldn't it? Therefore, I dared to mark this bug as "Critical".
> (... or - please - show me that I'm wrong and that || + navigation work as expected!).
> Remark: In our project, which started with NHib 0.99 six years ago, we(I) wrote a complete expression framework akin to Linq (but only with conditions, not with expressions). When we found this problem, I rewrote the HQL builder so that it then created (and still creates) correct INNER or OUTER JOINs, depending on the expression tree (one has to consider Is-Null/Is-Not-Null operators and Not nodes above these, as far as I remember ... I'd have to look into our code).
--
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: Harald M. M. (J. <nh...@gm...> - 2011-03-19 13:06:53
|
[ http://216.121.112.228/browse/NH-2583?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Harald M. Müller updated NH-2583:
---------------------------------
Attachment: NH2583.7z
This is the source for an "NHSpecificTest" that shows that the logical or operator yields wrong SQL. For example, a query with .Where(... || true) does not return all objects.
(I think my original example had wrong data, therefore it did not show the bug conclusively).
> Query with || operator and navigations (many-to-one) creates wrong joins
> ------------------------------------------------------------------------
>
> Key: NH-2583
> URL: http://216.121.112.228/browse/NH-2583
> Project: NHibernate
> Issue Type: Bug
> Components: Linq Provider
> Affects Versions: 3.1.0
> Reporter: Harald M. Müller
> Priority: Critical
> Attachments: NH2583.7z, NHib3.0.0Or.zip
>
>
> The following query
> var result = session.Query<MyBO>()
> .Where(bo =>
> (bo.BO1 != null && bo.BO1.I2 == 101) ||
> (bo.BO2 != null && bo.BO2.J2 == 203)
> )
> ;
> result.ToList();
> creates the following SQL on SQL Server 2008 (with .Net 4.0, MsSql2008Dialect):
> exec sp_executesql N'select mybo0_.Id as Id0_, mybo0_.Name as Name0_, mybo0_.BO1Key as BO3_0_, mybo0_.OtherBO1Key as OtherBO4_0_, mybo0_.BO2Key as BO5_0_ from MyBO mybo0_, MyRef1 myref1x1_, MyRef2 myref2x2_ where mybo0_.BO1Key=myref1x1_.Id and mybo0_.BO2Key=myref2x2_.Id and ((mybo0_.BO1Key is not null) and myref1x1_.I2=@p0 or (mybo0_.BO2Key is not null) and myref2x2_.J2=@p1)',N'@p0 int,@p1 int',@p0=101,@p1=203
> or, formatted:
> SELECT mybo0_.id AS id0_,
> mybo0_.name AS name0_,
> mybo0_.bo1key AS bo3_0_,
> mybo0_.otherbo1key AS otherbo4_0_,
> mybo0_.bo2key AS bo5_0_
> FROM mybo mybo0_,
> myref1 myref1x1_,
> myref2 myref2x2_
> WHERE mybo0_.bo1key = myref1x1_.id -- inner (table) join
> AND mybo0_.bo2key = myref2x2_.id -- also inner (table) join
> AND ( ( mybo0_.bo1key IS NOT NULL )
> AND myref1x1_.i2 = @p0
> OR ( mybo0_.bo2key IS NOT NULL )
> AND myref2x2_.j2 = @p1 )
> This is wrong if the reference to ref1 or ref2 is null. The attachment contains a small project that shows the problem.
> AFAIK, the problem has been always present when using HQL navigations (a.b.c....); it is - somewhat implicitly - documented in seciotn 13.7.:
> Compound path expressions make the where clause extremely powerful. Consider:
> from Eg.Cat cat where cat.Mate.Name is not null
> This query translates to an SQL query with a table (inner) join.
> In HQL, one can claim that this is "a feature" (and the workaround is to use manual JOINs). With Linq, this is no longer possible - the semantics of the || operator should be correctly implemented, shouldn't it? Therefore, I dared to mark this bug as "Critical".
> (... or - please - show me that I'm wrong and that || + navigation work as expected!).
> Remark: In our project, which started with NHib 0.99 six years ago, we(I) wrote a complete expression framework akin to Linq (but only with conditions, not with expressions). When we found this problem, I rewrote the HQL builder so that it then created (and still creates) correct INNER or OUTER JOINs, depending on the expression tree (one has to consider Is-Null/Is-Not-Null operators and Not nodes above these, as far as I remember ... I'd have to look into our code).
--
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: Harald M. M. (J. <nh...@gm...> - 2011-03-23 21:48:54
|
[ http://216.121.112.228/browse/NH-2583?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Harald M. Müller updated NH-2583:
---------------------------------
Attachment: NH-2583a.diff
Hi all -
I have attached a first experimental file NH-2583a.diff which solves, IMHO, the fundamental problem with the transformation of the boolean or operator from Linq to Hql trees. The proof that it works in principle is in 2 attached test cases which do not pass with the current NHib 3.x trunk code, but pass with the changed code; and in the fact that all previous 314 NHib.Linq tests still pass.
The change is a more or less complete rewrite and extension of the AddLeftJoinReWriter (which is now called AddJoinReWriter, as it also adds inner joins at times). The code is, IMO, so large (a few hundred lines) that it will require a few dozens of test cases, which I have not yet written.
There is only one longer comment in the code - which is too little.
One remark may help to understand the complexity of the "AbstractOneSidedMemberExpressions" inheritance tree: The code here assumes that there are other expression types besides the boolean or that require outer joins; in fact, one such expression type is the ConditionExpression ?: - although I do not know whether the subsequent HQL handling accepts ?: ). Therefore, I already implemented code to show how such different expressions might be handled. I tried to avoid copy-paste code, yet "keep different things different."
The name "...OneSidedMemberExpressions" is bad. It means "a group of member expressions that occur only on "one side" of a compound expression such as ||, ?:.
I'd be glad if someone could discuss with me whether the description of the problem, my approach, and my code actually are correct and useful for NHib3.x.
Best regards
Harald M.
> Query with || operator and navigations (many-to-one) creates wrong joins
> ------------------------------------------------------------------------
>
> Key: NH-2583
> URL: http://216.121.112.228/browse/NH-2583
> Project: NHibernate
> Issue Type: Bug
> Components: Linq Provider
> Affects Versions: 3.1.0
> Reporter: Harald M. Müller
> Priority: Critical
> Attachments: NH-2583a.diff, NH2583.7z, NHib3.0.0Or.zip
>
>
> The following query
> var result = session.Query<MyBO>()
> .Where(bo =>
> (bo.BO1 != null && bo.BO1.I2 == 101) ||
> (bo.BO2 != null && bo.BO2.J2 == 203)
> )
> ;
> result.ToList();
> creates the following SQL on SQL Server 2008 (with .Net 4.0, MsSql2008Dialect):
> exec sp_executesql N'select mybo0_.Id as Id0_, mybo0_.Name as Name0_, mybo0_.BO1Key as BO3_0_, mybo0_.OtherBO1Key as OtherBO4_0_, mybo0_.BO2Key as BO5_0_ from MyBO mybo0_, MyRef1 myref1x1_, MyRef2 myref2x2_ where mybo0_.BO1Key=myref1x1_.Id and mybo0_.BO2Key=myref2x2_.Id and ((mybo0_.BO1Key is not null) and myref1x1_.I2=@p0 or (mybo0_.BO2Key is not null) and myref2x2_.J2=@p1)',N'@p0 int,@p1 int',@p0=101,@p1=203
> or, formatted:
> SELECT mybo0_.id AS id0_,
> mybo0_.name AS name0_,
> mybo0_.bo1key AS bo3_0_,
> mybo0_.otherbo1key AS otherbo4_0_,
> mybo0_.bo2key AS bo5_0_
> FROM mybo mybo0_,
> myref1 myref1x1_,
> myref2 myref2x2_
> WHERE mybo0_.bo1key = myref1x1_.id -- inner (table) join
> AND mybo0_.bo2key = myref2x2_.id -- also inner (table) join
> AND ( ( mybo0_.bo1key IS NOT NULL )
> AND myref1x1_.i2 = @p0
> OR ( mybo0_.bo2key IS NOT NULL )
> AND myref2x2_.j2 = @p1 )
> This is wrong if the reference to ref1 or ref2 is null. The attachment contains a small project that shows the problem.
> AFAIK, the problem has been always present when using HQL navigations (a.b.c....); it is - somewhat implicitly - documented in seciotn 13.7.:
> Compound path expressions make the where clause extremely powerful. Consider:
> from Eg.Cat cat where cat.Mate.Name is not null
> This query translates to an SQL query with a table (inner) join.
> In HQL, one can claim that this is "a feature" (and the workaround is to use manual JOINs). With Linq, this is no longer possible - the semantics of the || operator should be correctly implemented, shouldn't it? Therefore, I dared to mark this bug as "Critical".
> (... or - please - show me that I'm wrong and that || + navigation work as expected!).
> Remark: In our project, which started with NHib 0.99 six years ago, we(I) wrote a complete expression framework akin to Linq (but only with conditions, not with expressions). When we found this problem, I rewrote the HQL builder so that it then created (and still creates) correct INNER or OUTER JOINs, depending on the expression tree (one has to consider Is-Null/Is-Not-Null operators and Not nodes above these, as far as I remember ... I'd have to look into our code).
--
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: Patrick E. (JIRA) <nh...@gm...> - 2011-03-27 22:10:19
|
[ http://216.121.112.228/browse/NH-2583?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Patrick Earl reassigned NH-2583:
--------------------------------
Assignee: Patrick Earl
> Query with || operator and navigations (many-to-one) creates wrong joins
> ------------------------------------------------------------------------
>
> Key: NH-2583
> URL: http://216.121.112.228/browse/NH-2583
> Project: NHibernate
> Issue Type: Bug
> Components: Linq Provider
> Affects Versions: 3.1.0
> Reporter: Harald M. Müller
> Assignee: Patrick Earl
> Priority: Critical
> Attachments: NH-2583a.diff, NH2583.7z, NHib3.0.0Or.zip
>
>
> The following query
> var result = session.Query<MyBO>()
> .Where(bo =>
> (bo.BO1 != null && bo.BO1.I2 == 101) ||
> (bo.BO2 != null && bo.BO2.J2 == 203)
> )
> ;
> result.ToList();
> creates the following SQL on SQL Server 2008 (with .Net 4.0, MsSql2008Dialect):
> exec sp_executesql N'select mybo0_.Id as Id0_, mybo0_.Name as Name0_, mybo0_.BO1Key as BO3_0_, mybo0_.OtherBO1Key as OtherBO4_0_, mybo0_.BO2Key as BO5_0_ from MyBO mybo0_, MyRef1 myref1x1_, MyRef2 myref2x2_ where mybo0_.BO1Key=myref1x1_.Id and mybo0_.BO2Key=myref2x2_.Id and ((mybo0_.BO1Key is not null) and myref1x1_.I2=@p0 or (mybo0_.BO2Key is not null) and myref2x2_.J2=@p1)',N'@p0 int,@p1 int',@p0=101,@p1=203
> or, formatted:
> SELECT mybo0_.id AS id0_,
> mybo0_.name AS name0_,
> mybo0_.bo1key AS bo3_0_,
> mybo0_.otherbo1key AS otherbo4_0_,
> mybo0_.bo2key AS bo5_0_
> FROM mybo mybo0_,
> myref1 myref1x1_,
> myref2 myref2x2_
> WHERE mybo0_.bo1key = myref1x1_.id -- inner (table) join
> AND mybo0_.bo2key = myref2x2_.id -- also inner (table) join
> AND ( ( mybo0_.bo1key IS NOT NULL )
> AND myref1x1_.i2 = @p0
> OR ( mybo0_.bo2key IS NOT NULL )
> AND myref2x2_.j2 = @p1 )
> This is wrong if the reference to ref1 or ref2 is null. The attachment contains a small project that shows the problem.
> AFAIK, the problem has been always present when using HQL navigations (a.b.c....); it is - somewhat implicitly - documented in seciotn 13.7.:
> Compound path expressions make the where clause extremely powerful. Consider:
> from Eg.Cat cat where cat.Mate.Name is not null
> This query translates to an SQL query with a table (inner) join.
> In HQL, one can claim that this is "a feature" (and the workaround is to use manual JOINs). With Linq, this is no longer possible - the semantics of the || operator should be correctly implemented, shouldn't it? Therefore, I dared to mark this bug as "Critical".
> (... or - please - show me that I'm wrong and that || + navigation work as expected!).
> Remark: In our project, which started with NHib 0.99 six years ago, we(I) wrote a complete expression framework akin to Linq (but only with conditions, not with expressions). When we found this problem, I rewrote the HQL builder so that it then created (and still creates) correct INNER or OUTER JOINs, depending on the expression tree (one has to consider Is-Null/Is-Not-Null operators and Not nodes above these, as far as I remember ... I'd have to look into our code).
--
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: Harald M. M. (J. <nh...@gm...> - 2011-03-28 07:44:23
|
[ http://216.121.112.228/browse/NH-2583?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Harald M. Müller updated NH-2583:
---------------------------------
Attachment: NH-2583b.diff
Added (from my email) to this JIRA issue to keep the information consistent.
Harald M.
> Query with || operator and navigations (many-to-one) creates wrong joins
> ------------------------------------------------------------------------
>
> Key: NH-2583
> URL: http://216.121.112.228/browse/NH-2583
> Project: NHibernate
> Issue Type: Bug
> Components: Linq Provider
> Affects Versions: 3.1.0
> Reporter: Harald M. Müller
> Assignee: Patrick Earl
> Priority: Critical
> Attachments: NH-2583a.diff, NH-2583b.diff, NH2583.7z, NHib3.0.0Or.zip
>
>
> The following query
> var result = session.Query<MyBO>()
> .Where(bo =>
> (bo.BO1 != null && bo.BO1.I2 == 101) ||
> (bo.BO2 != null && bo.BO2.J2 == 203)
> )
> ;
> result.ToList();
> creates the following SQL on SQL Server 2008 (with .Net 4.0, MsSql2008Dialect):
> exec sp_executesql N'select mybo0_.Id as Id0_, mybo0_.Name as Name0_, mybo0_.BO1Key as BO3_0_, mybo0_.OtherBO1Key as OtherBO4_0_, mybo0_.BO2Key as BO5_0_ from MyBO mybo0_, MyRef1 myref1x1_, MyRef2 myref2x2_ where mybo0_.BO1Key=myref1x1_.Id and mybo0_.BO2Key=myref2x2_.Id and ((mybo0_.BO1Key is not null) and myref1x1_.I2=@p0 or (mybo0_.BO2Key is not null) and myref2x2_.J2=@p1)',N'@p0 int,@p1 int',@p0=101,@p1=203
> or, formatted:
> SELECT mybo0_.id AS id0_,
> mybo0_.name AS name0_,
> mybo0_.bo1key AS bo3_0_,
> mybo0_.otherbo1key AS otherbo4_0_,
> mybo0_.bo2key AS bo5_0_
> FROM mybo mybo0_,
> myref1 myref1x1_,
> myref2 myref2x2_
> WHERE mybo0_.bo1key = myref1x1_.id -- inner (table) join
> AND mybo0_.bo2key = myref2x2_.id -- also inner (table) join
> AND ( ( mybo0_.bo1key IS NOT NULL )
> AND myref1x1_.i2 = @p0
> OR ( mybo0_.bo2key IS NOT NULL )
> AND myref2x2_.j2 = @p1 )
> This is wrong if the reference to ref1 or ref2 is null. The attachment contains a small project that shows the problem.
> AFAIK, the problem has been always present when using HQL navigations (a.b.c....); it is - somewhat implicitly - documented in seciotn 13.7.:
> Compound path expressions make the where clause extremely powerful. Consider:
> from Eg.Cat cat where cat.Mate.Name is not null
> This query translates to an SQL query with a table (inner) join.
> In HQL, one can claim that this is "a feature" (and the workaround is to use manual JOINs). With Linq, this is no longer possible - the semantics of the || operator should be correctly implemented, shouldn't it? Therefore, I dared to mark this bug as "Critical".
> (... or - please - show me that I'm wrong and that || + navigation work as expected!).
> Remark: In our project, which started with NHib 0.99 six years ago, we(I) wrote a complete expression framework akin to Linq (but only with conditions, not with expressions). When we found this problem, I rewrote the HQL builder so that it then created (and still creates) correct INNER or OUTER JOINs, depending on the expression tree (one has to consider Is-Null/Is-Not-Null operators and Not nodes above these, as far as I remember ... I'd have to look into our code).
--
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: Harald M. M. (J. <nh...@gm...> - 2011-03-29 19:23:29
|
[ http://216.121.112.228/browse/NH-2583?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Harald M. Müller updated NH-2583:
---------------------------------
Attachment: NH-2583c.diff
Corrected issues:
* Added 4 self joining tests (SelfJoinTestFixture)
* Added code to create outer joins for ! operator + "DeMorgan test case" --> works now
* Corrected dumb bug in member expression union/difference in FindMemberPathsBelowOuterJoinNodes.FindOneSideOnlyExpressionsOfTwoWayExpression --> now all tests of depth 3 also work correctly.
Not done:
* Researched/documented/changed different logic in VisitSelect/VisitOrder and VisitWhere.
Harald M.
> Query with || operator and navigations (many-to-one) creates wrong joins
> ------------------------------------------------------------------------
>
> Key: NH-2583
> URL: http://216.121.112.228/browse/NH-2583
> Project: NHibernate
> Issue Type: Bug
> Components: Linq Provider
> Affects Versions: 3.1.0
> Reporter: Harald M. Müller
> Assignee: Patrick Earl
> Priority: Critical
> Attachments: NH-2583a.diff, NH-2583b.diff, NH-2583c.diff, NH2583.7z, NHib3.0.0Or.zip
>
>
> The following query
> var result = session.Query<MyBO>()
> .Where(bo =>
> (bo.BO1 != null && bo.BO1.I2 == 101) ||
> (bo.BO2 != null && bo.BO2.J2 == 203)
> )
> ;
> result.ToList();
> creates the following SQL on SQL Server 2008 (with .Net 4.0, MsSql2008Dialect):
> exec sp_executesql N'select mybo0_.Id as Id0_, mybo0_.Name as Name0_, mybo0_.BO1Key as BO3_0_, mybo0_.OtherBO1Key as OtherBO4_0_, mybo0_.BO2Key as BO5_0_ from MyBO mybo0_, MyRef1 myref1x1_, MyRef2 myref2x2_ where mybo0_.BO1Key=myref1x1_.Id and mybo0_.BO2Key=myref2x2_.Id and ((mybo0_.BO1Key is not null) and myref1x1_.I2=@p0 or (mybo0_.BO2Key is not null) and myref2x2_.J2=@p1)',N'@p0 int,@p1 int',@p0=101,@p1=203
> or, formatted:
> SELECT mybo0_.id AS id0_,
> mybo0_.name AS name0_,
> mybo0_.bo1key AS bo3_0_,
> mybo0_.otherbo1key AS otherbo4_0_,
> mybo0_.bo2key AS bo5_0_
> FROM mybo mybo0_,
> myref1 myref1x1_,
> myref2 myref2x2_
> WHERE mybo0_.bo1key = myref1x1_.id -- inner (table) join
> AND mybo0_.bo2key = myref2x2_.id -- also inner (table) join
> AND ( ( mybo0_.bo1key IS NOT NULL )
> AND myref1x1_.i2 = @p0
> OR ( mybo0_.bo2key IS NOT NULL )
> AND myref2x2_.j2 = @p1 )
> This is wrong if the reference to ref1 or ref2 is null. The attachment contains a small project that shows the problem.
> AFAIK, the problem has been always present when using HQL navigations (a.b.c....); it is - somewhat implicitly - documented in seciotn 13.7.:
> Compound path expressions make the where clause extremely powerful. Consider:
> from Eg.Cat cat where cat.Mate.Name is not null
> This query translates to an SQL query with a table (inner) join.
> In HQL, one can claim that this is "a feature" (and the workaround is to use manual JOINs). With Linq, this is no longer possible - the semantics of the || operator should be correctly implemented, shouldn't it? Therefore, I dared to mark this bug as "Critical".
> (... or - please - show me that I'm wrong and that || + navigation work as expected!).
> Remark: In our project, which started with NHib 0.99 six years ago, we(I) wrote a complete expression framework akin to Linq (but only with conditions, not with expressions). When we found this problem, I rewrote the HQL builder so that it then created (and still creates) correct INNER or OUTER JOINs, depending on the expression tree (one has to consider Is-Null/Is-Not-Null operators and Not nodes above these, as far as I remember ... I'd have to look into our code).
--
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: Harald M. M. (J. <nh...@gm...> - 2011-03-29 21:35:55
|
[ http://216.121.112.228/browse/NH-2583?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Harald M. Müller updated NH-2583:
---------------------------------
Attachment: NH-2583d.diff
Another update:
(a) More "DeMorgan-like" tests (but DeMorgan only holds in a "limited sense"); and simple tests that show e.g. that !(a.B.C == 1) is not the same as a.B.C != 1 under that interesting "navigational logic" (still, one can always add the guards manually - then the semantics is an in Linq/C#).
The "mass test framework" needed a few small updates for this.
(b) Using the expressionMap both in SelectAndOrderByJoinDetector AND WhereJoinDetector (but there, it is more "roundabout" via the CreateAnd...() methods).
All (now) 3353 NHib tests still green :-)
Harald M.
> Query with || operator and navigations (many-to-one) creates wrong joins
> ------------------------------------------------------------------------
>
> Key: NH-2583
> URL: http://216.121.112.228/browse/NH-2583
> Project: NHibernate
> Issue Type: Bug
> Components: Linq Provider
> Affects Versions: 3.1.0
> Reporter: Harald M. Müller
> Assignee: Patrick Earl
> Priority: Critical
> Attachments: NH-2583a.diff, NH-2583b.diff, NH-2583c.diff, NH-2583d.diff, NH2583.7z, NHib3.0.0Or.zip
>
>
> The following query
> var result = session.Query<MyBO>()
> .Where(bo =>
> (bo.BO1 != null && bo.BO1.I2 == 101) ||
> (bo.BO2 != null && bo.BO2.J2 == 203)
> )
> ;
> result.ToList();
> creates the following SQL on SQL Server 2008 (with .Net 4.0, MsSql2008Dialect):
> exec sp_executesql N'select mybo0_.Id as Id0_, mybo0_.Name as Name0_, mybo0_.BO1Key as BO3_0_, mybo0_.OtherBO1Key as OtherBO4_0_, mybo0_.BO2Key as BO5_0_ from MyBO mybo0_, MyRef1 myref1x1_, MyRef2 myref2x2_ where mybo0_.BO1Key=myref1x1_.Id and mybo0_.BO2Key=myref2x2_.Id and ((mybo0_.BO1Key is not null) and myref1x1_.I2=@p0 or (mybo0_.BO2Key is not null) and myref2x2_.J2=@p1)',N'@p0 int,@p1 int',@p0=101,@p1=203
> or, formatted:
> SELECT mybo0_.id AS id0_,
> mybo0_.name AS name0_,
> mybo0_.bo1key AS bo3_0_,
> mybo0_.otherbo1key AS otherbo4_0_,
> mybo0_.bo2key AS bo5_0_
> FROM mybo mybo0_,
> myref1 myref1x1_,
> myref2 myref2x2_
> WHERE mybo0_.bo1key = myref1x1_.id -- inner (table) join
> AND mybo0_.bo2key = myref2x2_.id -- also inner (table) join
> AND ( ( mybo0_.bo1key IS NOT NULL )
> AND myref1x1_.i2 = @p0
> OR ( mybo0_.bo2key IS NOT NULL )
> AND myref2x2_.j2 = @p1 )
> This is wrong if the reference to ref1 or ref2 is null. The attachment contains a small project that shows the problem.
> AFAIK, the problem has been always present when using HQL navigations (a.b.c....); it is - somewhat implicitly - documented in seciotn 13.7.:
> Compound path expressions make the where clause extremely powerful. Consider:
> from Eg.Cat cat where cat.Mate.Name is not null
> This query translates to an SQL query with a table (inner) join.
> In HQL, one can claim that this is "a feature" (and the workaround is to use manual JOINs). With Linq, this is no longer possible - the semantics of the || operator should be correctly implemented, shouldn't it? Therefore, I dared to mark this bug as "Critical".
> (... or - please - show me that I'm wrong and that || + navigation work as expected!).
> Remark: In our project, which started with NHib 0.99 six years ago, we(I) wrote a complete expression framework akin to Linq (but only with conditions, not with expressions). When we found this problem, I rewrote the HQL builder so that it then created (and still creates) correct INNER or OUTER JOINs, depending on the expression tree (one has to consider Is-Null/Is-Not-Null operators and Not nodes above these, as far as I remember ... I'd have to look into our code).
--
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: Harald M. M. (J. <nh...@gm...> - 2011-03-30 21:30:32
|
[ http://216.121.112.228/browse/NH-2583?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Harald M. Müller updated NH-2583:
---------------------------------
Attachment: LogicalOperatorsInNHibernateLinq.txt
NH-2583e.diff
This is now, to my best knowledge, the final version of code handling the || and ! operators correctly when paths (member expressions with more than one dot) are involved. Also, there is code to handle bool ?: conditions if HQL understands these.
Two changes to the previous diff are worth mentioning:
(a) In the test framework, also the "equivalentLinq2ObjectsCondition" is used in a test query - this essentially doubles the number of tests.
(b) The former "ExpressionSwapper" has been renamed to "MemberExpressionSwapper", and its replacement Dictionary is now of type Dictionary<MemberExpression, QuerySourceReferenceExpression>, as it *only* replaces member expressions with QuerySourceReferenceExpressions. This clarifies and restricts its usage.
All 3353 tests are green (using SQL Server 2008 Express).
In addition to the patch file NH-2583e.diff, I have also attached a "rationale and design document" LogicalOperatorsInNHibernateLinq.txt, which explains much from meta-considerations down to code details; and also a possible optimization not implemented.
What is still missing is verified/correct handling of the Any operator - but this should be done separately.
> Query with || operator and navigations (many-to-one) creates wrong joins
> ------------------------------------------------------------------------
>
> Key: NH-2583
> URL: http://216.121.112.228/browse/NH-2583
> Project: NHibernate
> Issue Type: Bug
> Components: Linq Provider
> Affects Versions: 3.1.0
> Reporter: Harald M. Müller
> Assignee: Patrick Earl
> Priority: Critical
> Attachments: LogicalOperatorsInNHibernateLinq.txt, NH-2583a.diff, NH-2583b.diff, NH-2583c.diff, NH-2583d.diff, NH-2583e.diff, NH2583.7z, NHib3.0.0Or.zip
>
>
> The following query
> var result = session.Query<MyBO>()
> .Where(bo =>
> (bo.BO1 != null && bo.BO1.I2 == 101) ||
> (bo.BO2 != null && bo.BO2.J2 == 203)
> )
> ;
> result.ToList();
> creates the following SQL on SQL Server 2008 (with .Net 4.0, MsSql2008Dialect):
> exec sp_executesql N'select mybo0_.Id as Id0_, mybo0_.Name as Name0_, mybo0_.BO1Key as BO3_0_, mybo0_.OtherBO1Key as OtherBO4_0_, mybo0_.BO2Key as BO5_0_ from MyBO mybo0_, MyRef1 myref1x1_, MyRef2 myref2x2_ where mybo0_.BO1Key=myref1x1_.Id and mybo0_.BO2Key=myref2x2_.Id and ((mybo0_.BO1Key is not null) and myref1x1_.I2=@p0 or (mybo0_.BO2Key is not null) and myref2x2_.J2=@p1)',N'@p0 int,@p1 int',@p0=101,@p1=203
> or, formatted:
> SELECT mybo0_.id AS id0_,
> mybo0_.name AS name0_,
> mybo0_.bo1key AS bo3_0_,
> mybo0_.otherbo1key AS otherbo4_0_,
> mybo0_.bo2key AS bo5_0_
> FROM mybo mybo0_,
> myref1 myref1x1_,
> myref2 myref2x2_
> WHERE mybo0_.bo1key = myref1x1_.id -- inner (table) join
> AND mybo0_.bo2key = myref2x2_.id -- also inner (table) join
> AND ( ( mybo0_.bo1key IS NOT NULL )
> AND myref1x1_.i2 = @p0
> OR ( mybo0_.bo2key IS NOT NULL )
> AND myref2x2_.j2 = @p1 )
> This is wrong if the reference to ref1 or ref2 is null. The attachment contains a small project that shows the problem.
> AFAIK, the problem has been always present when using HQL navigations (a.b.c....); it is - somewhat implicitly - documented in seciotn 13.7.:
> Compound path expressions make the where clause extremely powerful. Consider:
> from Eg.Cat cat where cat.Mate.Name is not null
> This query translates to an SQL query with a table (inner) join.
> In HQL, one can claim that this is "a feature" (and the workaround is to use manual JOINs). With Linq, this is no longer possible - the semantics of the || operator should be correctly implemented, shouldn't it? Therefore, I dared to mark this bug as "Critical".
> (... or - please - show me that I'm wrong and that || + navigation work as expected!).
> Remark: In our project, which started with NHib 0.99 six years ago, we(I) wrote a complete expression framework akin to Linq (but only with conditions, not with expressions). When we found this problem, I rewrote the HQL builder so that it then created (and still creates) correct INNER or OUTER JOINs, depending on the expression tree (one has to consider Is-Null/Is-Not-Null operators and Not nodes above these, as far as I remember ... I'd have to look into our code).
--
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: Harald M. M. (J. <nh...@gm...> - 2011-04-05 21:38:23
|
[ http://216.121.112.228/browse/NH-2583?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Harald M. Müller updated NH-2583:
---------------------------------
Attachment: NH-2583f.diff
"Outer join semantics" ((||-4) semantics) implemented, including inner joins. Right now, there is no description of (a) test concepts [different to previous versions for exceptional cases]; (b) algorithms - overall and for inner join optimization; (c) design/code.
> Query with || operator and navigations (many-to-one) creates wrong joins
> ------------------------------------------------------------------------
>
> Key: NH-2583
> URL: http://216.121.112.228/browse/NH-2583
> Project: NHibernate
> Issue Type: Bug
> Components: Linq Provider
> Affects Versions: 3.1.0
> Reporter: Harald M. Müller
> Assignee: Patrick Earl
> Priority: Critical
> Attachments: LogicalOperatorsInNHibernateLinq.txt, NH-2583a.diff, NH-2583b.diff, NH-2583c.diff, NH-2583d.diff, NH-2583e.diff, NH-2583f.diff, NH2583.7z, NHib3.0.0Or.zip
>
>
> The following query
> var result = session.Query<MyBO>()
> .Where(bo =>
> (bo.BO1 != null && bo.BO1.I2 == 101) ||
> (bo.BO2 != null && bo.BO2.J2 == 203)
> )
> ;
> result.ToList();
> creates the following SQL on SQL Server 2008 (with .Net 4.0, MsSql2008Dialect):
> exec sp_executesql N'select mybo0_.Id as Id0_, mybo0_.Name as Name0_, mybo0_.BO1Key as BO3_0_, mybo0_.OtherBO1Key as OtherBO4_0_, mybo0_.BO2Key as BO5_0_ from MyBO mybo0_, MyRef1 myref1x1_, MyRef2 myref2x2_ where mybo0_.BO1Key=myref1x1_.Id and mybo0_.BO2Key=myref2x2_.Id and ((mybo0_.BO1Key is not null) and myref1x1_.I2=@p0 or (mybo0_.BO2Key is not null) and myref2x2_.J2=@p1)',N'@p0 int,@p1 int',@p0=101,@p1=203
> or, formatted:
> SELECT mybo0_.id AS id0_,
> mybo0_.name AS name0_,
> mybo0_.bo1key AS bo3_0_,
> mybo0_.otherbo1key AS otherbo4_0_,
> mybo0_.bo2key AS bo5_0_
> FROM mybo mybo0_,
> myref1 myref1x1_,
> myref2 myref2x2_
> WHERE mybo0_.bo1key = myref1x1_.id -- inner (table) join
> AND mybo0_.bo2key = myref2x2_.id -- also inner (table) join
> AND ( ( mybo0_.bo1key IS NOT NULL )
> AND myref1x1_.i2 = @p0
> OR ( mybo0_.bo2key IS NOT NULL )
> AND myref2x2_.j2 = @p1 )
> This is wrong if the reference to ref1 or ref2 is null. The attachment contains a small project that shows the problem.
> AFAIK, the problem has been always present when using HQL navigations (a.b.c....); it is - somewhat implicitly - documented in seciotn 13.7.:
> Compound path expressions make the where clause extremely powerful. Consider:
> from Eg.Cat cat where cat.Mate.Name is not null
> This query translates to an SQL query with a table (inner) join.
> In HQL, one can claim that this is "a feature" (and the workaround is to use manual JOINs). With Linq, this is no longer possible - the semantics of the || operator should be correctly implemented, shouldn't it? Therefore, I dared to mark this bug as "Critical".
> (... or - please - show me that I'm wrong and that || + navigation work as expected!).
> Remark: In our project, which started with NHib 0.99 six years ago, we(I) wrote a complete expression framework akin to Linq (but only with conditions, not with expressions). When we found this problem, I rewrote the HQL builder so that it then created (and still creates) correct INNER or OUTER JOINs, depending on the expression tree (one has to consider Is-Null/Is-Not-Null operators and Not nodes above these, as far as I remember ... I'd have to look into our code).
--
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: Harald M. M. (J. <nh...@gm...> - 2011-04-12 19:26:43
|
[ http://216.121.112.228/browse/NH-2583?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Harald M. Müller updated NH-2583:
---------------------------------
Attachment: JoinOptimizationForPureOuterJoinSemantics.txt
NH-2583g.diff
This is now an implementation of "pure outer join semantics" for the logical operators ||, &&, and !. The document "JoinOptimizationForPureOuterJoinSemantics.txt" describes why and how inner joins are created by the code.
All ca. 400 "relevant tests" (NHib.Linq and NH-2583 tests) are green.
(However, there seem to be around 73 tests that are currently red on the trunk for other reasons - e.g. HqlBulkOperations: NHibernate.MappingException: Could not compile the mapping document: NHibernate.Test.BulkManipulation.SimpleClass.hbm.xml ---> NHibernate.DuplicateMappingException: Duplicate class/entity mapping NHibernate.Test.BulkManipulation.SimpleClass etc.)
> Query with || operator and navigations (many-to-one) creates wrong joins
> ------------------------------------------------------------------------
>
> Key: NH-2583
> URL: http://216.121.112.228/browse/NH-2583
> Project: NHibernate
> Issue Type: Bug
> Components: Linq Provider
> Affects Versions: 3.1.0
> Reporter: Harald M. Müller
> Assignee: Patrick Earl
> Priority: Critical
> Attachments: JoinOptimizationForPureOuterJoinSemantics.txt, LogicalOperatorsInNHibernateLinq.txt, NH-2583a.diff, NH-2583b.diff, NH-2583c.diff, NH-2583d.diff, NH-2583e.diff, NH-2583f.diff, NH-2583g.diff, NH2583.7z, NHib3.0.0Or.zip
>
>
> The following query
> var result = session.Query<MyBO>()
> .Where(bo =>
> (bo.BO1 != null && bo.BO1.I2 == 101) ||
> (bo.BO2 != null && bo.BO2.J2 == 203)
> )
> ;
> result.ToList();
> creates the following SQL on SQL Server 2008 (with .Net 4.0, MsSql2008Dialect):
> exec sp_executesql N'select mybo0_.Id as Id0_, mybo0_.Name as Name0_, mybo0_.BO1Key as BO3_0_, mybo0_.OtherBO1Key as OtherBO4_0_, mybo0_.BO2Key as BO5_0_ from MyBO mybo0_, MyRef1 myref1x1_, MyRef2 myref2x2_ where mybo0_.BO1Key=myref1x1_.Id and mybo0_.BO2Key=myref2x2_.Id and ((mybo0_.BO1Key is not null) and myref1x1_.I2=@p0 or (mybo0_.BO2Key is not null) and myref2x2_.J2=@p1)',N'@p0 int,@p1 int',@p0=101,@p1=203
> or, formatted:
> SELECT mybo0_.id AS id0_,
> mybo0_.name AS name0_,
> mybo0_.bo1key AS bo3_0_,
> mybo0_.otherbo1key AS otherbo4_0_,
> mybo0_.bo2key AS bo5_0_
> FROM mybo mybo0_,
> myref1 myref1x1_,
> myref2 myref2x2_
> WHERE mybo0_.bo1key = myref1x1_.id -- inner (table) join
> AND mybo0_.bo2key = myref2x2_.id -- also inner (table) join
> AND ( ( mybo0_.bo1key IS NOT NULL )
> AND myref1x1_.i2 = @p0
> OR ( mybo0_.bo2key IS NOT NULL )
> AND myref2x2_.j2 = @p1 )
> This is wrong if the reference to ref1 or ref2 is null. The attachment contains a small project that shows the problem.
> AFAIK, the problem has been always present when using HQL navigations (a.b.c....); it is - somewhat implicitly - documented in seciotn 13.7.:
> Compound path expressions make the where clause extremely powerful. Consider:
> from Eg.Cat cat where cat.Mate.Name is not null
> This query translates to an SQL query with a table (inner) join.
> In HQL, one can claim that this is "a feature" (and the workaround is to use manual JOINs). With Linq, this is no longer possible - the semantics of the || operator should be correctly implemented, shouldn't it? Therefore, I dared to mark this bug as "Critical".
> (... or - please - show me that I'm wrong and that || + navigation work as expected!).
> Remark: In our project, which started with NHib 0.99 six years ago, we(I) wrote a complete expression framework akin to Linq (but only with conditions, not with expressions). When we found this problem, I rewrote the HQL builder so that it then created (and still creates) correct INNER or OUTER JOINs, depending on the expression tree (one has to consider Is-Null/Is-Not-Null operators and Not nodes above these, as far as I remember ... I'd have to look into our code).
--
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: Harald M. M. (J. <nh...@gm...> - 2011-04-12 20:48:11
|
[ http://216.121.112.228/browse/NH-2583?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Harald M. Müller updated NH-2583:
---------------------------------
Attachment: NH-2583g2ndTry.patch
I could not apply my previous patch (NH-2583g.diff) to a newly loaded version of the trunk. So I modified the files there manually once more and created this new patch file. Hopefully it works better!
> Query with || operator and navigations (many-to-one) creates wrong joins
> ------------------------------------------------------------------------
>
> Key: NH-2583
> URL: http://216.121.112.228/browse/NH-2583
> Project: NHibernate
> Issue Type: Bug
> Components: Linq Provider
> Affects Versions: 3.1.0
> Reporter: Harald M. Müller
> Assignee: Patrick Earl
> Priority: Critical
> Attachments: JoinOptimizationForPureOuterJoinSemantics.txt, LogicalOperatorsInNHibernateLinq.txt, NH-2583a.diff, NH-2583b.diff, NH-2583c.diff, NH-2583d.diff, NH-2583e.diff, NH-2583f.diff, NH-2583g.diff, NH-2583g2ndTry.patch, NH2583.7z, NHib3.0.0Or.zip
>
>
> The following query
> var result = session.Query<MyBO>()
> .Where(bo =>
> (bo.BO1 != null && bo.BO1.I2 == 101) ||
> (bo.BO2 != null && bo.BO2.J2 == 203)
> )
> ;
> result.ToList();
> creates the following SQL on SQL Server 2008 (with .Net 4.0, MsSql2008Dialect):
> exec sp_executesql N'select mybo0_.Id as Id0_, mybo0_.Name as Name0_, mybo0_.BO1Key as BO3_0_, mybo0_.OtherBO1Key as OtherBO4_0_, mybo0_.BO2Key as BO5_0_ from MyBO mybo0_, MyRef1 myref1x1_, MyRef2 myref2x2_ where mybo0_.BO1Key=myref1x1_.Id and mybo0_.BO2Key=myref2x2_.Id and ((mybo0_.BO1Key is not null) and myref1x1_.I2=@p0 or (mybo0_.BO2Key is not null) and myref2x2_.J2=@p1)',N'@p0 int,@p1 int',@p0=101,@p1=203
> or, formatted:
> SELECT mybo0_.id AS id0_,
> mybo0_.name AS name0_,
> mybo0_.bo1key AS bo3_0_,
> mybo0_.otherbo1key AS otherbo4_0_,
> mybo0_.bo2key AS bo5_0_
> FROM mybo mybo0_,
> myref1 myref1x1_,
> myref2 myref2x2_
> WHERE mybo0_.bo1key = myref1x1_.id -- inner (table) join
> AND mybo0_.bo2key = myref2x2_.id -- also inner (table) join
> AND ( ( mybo0_.bo1key IS NOT NULL )
> AND myref1x1_.i2 = @p0
> OR ( mybo0_.bo2key IS NOT NULL )
> AND myref2x2_.j2 = @p1 )
> This is wrong if the reference to ref1 or ref2 is null. The attachment contains a small project that shows the problem.
> AFAIK, the problem has been always present when using HQL navigations (a.b.c....); it is - somewhat implicitly - documented in seciotn 13.7.:
> Compound path expressions make the where clause extremely powerful. Consider:
> from Eg.Cat cat where cat.Mate.Name is not null
> This query translates to an SQL query with a table (inner) join.
> In HQL, one can claim that this is "a feature" (and the workaround is to use manual JOINs). With Linq, this is no longer possible - the semantics of the || operator should be correctly implemented, shouldn't it? Therefore, I dared to mark this bug as "Critical".
> (... or - please - show me that I'm wrong and that || + navigation work as expected!).
> Remark: In our project, which started with NHib 0.99 six years ago, we(I) wrote a complete expression framework akin to Linq (but only with conditions, not with expressions). When we found this problem, I rewrote the HQL builder so that it then created (and still creates) correct INNER or OUTER JOINs, depending on the expression tree (one has to consider Is-Null/Is-Not-Null operators and Not nodes above these, as far as I remember ... I'd have to look into our code).
--
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: Harald M. M. (J. <nh...@gm...> - 2011-04-13 19:09:22
|
[ http://216.121.112.228/browse/NH-2583?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=20905#action_20905 ]
Harald M. Müller commented on NH-2583:
--------------------------------------
A fix for this along the lines of my code probably would also fix NH-2451, wouldn't it?
> Query with || operator and navigations (many-to-one) creates wrong joins
> ------------------------------------------------------------------------
>
> Key: NH-2583
> URL: http://216.121.112.228/browse/NH-2583
> Project: NHibernate
> Issue Type: Bug
> Components: Linq Provider
> Affects Versions: 3.1.0
> Reporter: Harald M. Müller
> Assignee: Patrick Earl
> Priority: Critical
> Attachments: JoinOptimizationForPureOuterJoinSemantics.txt, LogicalOperatorsInNHibernateLinq.txt, NH-2583a.diff, NH-2583b.diff, NH-2583c.diff, NH-2583d.diff, NH-2583e.diff, NH-2583f.diff, NH-2583g.diff, NH-2583g2ndTry.patch, NH2583.7z, NHib3.0.0Or.zip
>
>
> The following query
> var result = session.Query<MyBO>()
> .Where(bo =>
> (bo.BO1 != null && bo.BO1.I2 == 101) ||
> (bo.BO2 != null && bo.BO2.J2 == 203)
> )
> ;
> result.ToList();
> creates the following SQL on SQL Server 2008 (with .Net 4.0, MsSql2008Dialect):
> exec sp_executesql N'select mybo0_.Id as Id0_, mybo0_.Name as Name0_, mybo0_.BO1Key as BO3_0_, mybo0_.OtherBO1Key as OtherBO4_0_, mybo0_.BO2Key as BO5_0_ from MyBO mybo0_, MyRef1 myref1x1_, MyRef2 myref2x2_ where mybo0_.BO1Key=myref1x1_.Id and mybo0_.BO2Key=myref2x2_.Id and ((mybo0_.BO1Key is not null) and myref1x1_.I2=@p0 or (mybo0_.BO2Key is not null) and myref2x2_.J2=@p1)',N'@p0 int,@p1 int',@p0=101,@p1=203
> or, formatted:
> SELECT mybo0_.id AS id0_,
> mybo0_.name AS name0_,
> mybo0_.bo1key AS bo3_0_,
> mybo0_.otherbo1key AS otherbo4_0_,
> mybo0_.bo2key AS bo5_0_
> FROM mybo mybo0_,
> myref1 myref1x1_,
> myref2 myref2x2_
> WHERE mybo0_.bo1key = myref1x1_.id -- inner (table) join
> AND mybo0_.bo2key = myref2x2_.id -- also inner (table) join
> AND ( ( mybo0_.bo1key IS NOT NULL )
> AND myref1x1_.i2 = @p0
> OR ( mybo0_.bo2key IS NOT NULL )
> AND myref2x2_.j2 = @p1 )
> This is wrong if the reference to ref1 or ref2 is null. The attachment contains a small project that shows the problem.
> AFAIK, the problem has been always present when using HQL navigations (a.b.c....); it is - somewhat implicitly - documented in seciotn 13.7.:
> Compound path expressions make the where clause extremely powerful. Consider:
> from Eg.Cat cat where cat.Mate.Name is not null
> This query translates to an SQL query with a table (inner) join.
> In HQL, one can claim that this is "a feature" (and the workaround is to use manual JOINs). With Linq, this is no longer possible - the semantics of the || operator should be correctly implemented, shouldn't it? Therefore, I dared to mark this bug as "Critical".
> (... or - please - show me that I'm wrong and that || + navigation work as expected!).
> Remark: In our project, which started with NHib 0.99 six years ago, we(I) wrote a complete expression framework akin to Linq (but only with conditions, not with expressions). When we found this problem, I rewrote the HQL builder so that it then created (and still creates) correct INNER or OUTER JOINs, depending on the expression tree (one has to consider Is-Null/Is-Not-Null operators and Not nodes above these, as far as I remember ... I'd have to look into our code).
--
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: Patrick E. (JIRA) <nh...@gm...> - 2011-04-26 17:53:04
|
[ http://216.121.112.228/browse/NH-2583?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=20969#action_20969 ]
Patrick Earl commented on NH-2583:
----------------------------------
Unfortunately the tests for this roughly doubled the build time. We should cut that down significantly.
> Query with || operator and navigations (many-to-one) creates wrong joins
> ------------------------------------------------------------------------
>
> Key: NH-2583
> URL: http://216.121.112.228/browse/NH-2583
> Project: NHibernate
> Issue Type: Bug
> Components: Linq Provider
> Affects Versions: 3.1.0
> Reporter: Harald M. Müller
> Assignee: Patrick Earl
> Priority: Critical
> Attachments: JoinOptimizationForPureOuterJoinSemantics.txt, LogicalOperatorsInNHibernateLinq.txt, NH-2583a.diff, NH-2583b.diff, NH-2583c.diff, NH-2583d.diff, NH-2583e.diff, NH-2583f.diff, NH-2583g.diff, NH-2583g2ndTry.patch, NH2583.7z, NHib3.0.0Or.zip
>
>
> The following query
> var result = session.Query<MyBO>()
> .Where(bo =>
> (bo.BO1 != null && bo.BO1.I2 == 101) ||
> (bo.BO2 != null && bo.BO2.J2 == 203)
> )
> ;
> result.ToList();
> creates the following SQL on SQL Server 2008 (with .Net 4.0, MsSql2008Dialect):
> exec sp_executesql N'select mybo0_.Id as Id0_, mybo0_.Name as Name0_, mybo0_.BO1Key as BO3_0_, mybo0_.OtherBO1Key as OtherBO4_0_, mybo0_.BO2Key as BO5_0_ from MyBO mybo0_, MyRef1 myref1x1_, MyRef2 myref2x2_ where mybo0_.BO1Key=myref1x1_.Id and mybo0_.BO2Key=myref2x2_.Id and ((mybo0_.BO1Key is not null) and myref1x1_.I2=@p0 or (mybo0_.BO2Key is not null) and myref2x2_.J2=@p1)',N'@p0 int,@p1 int',@p0=101,@p1=203
> or, formatted:
> SELECT mybo0_.id AS id0_,
> mybo0_.name AS name0_,
> mybo0_.bo1key AS bo3_0_,
> mybo0_.otherbo1key AS otherbo4_0_,
> mybo0_.bo2key AS bo5_0_
> FROM mybo mybo0_,
> myref1 myref1x1_,
> myref2 myref2x2_
> WHERE mybo0_.bo1key = myref1x1_.id -- inner (table) join
> AND mybo0_.bo2key = myref2x2_.id -- also inner (table) join
> AND ( ( mybo0_.bo1key IS NOT NULL )
> AND myref1x1_.i2 = @p0
> OR ( mybo0_.bo2key IS NOT NULL )
> AND myref2x2_.j2 = @p1 )
> This is wrong if the reference to ref1 or ref2 is null. The attachment contains a small project that shows the problem.
> AFAIK, the problem has been always present when using HQL navigations (a.b.c....); it is - somewhat implicitly - documented in seciotn 13.7.:
> Compound path expressions make the where clause extremely powerful. Consider:
> from Eg.Cat cat where cat.Mate.Name is not null
> This query translates to an SQL query with a table (inner) join.
> In HQL, one can claim that this is "a feature" (and the workaround is to use manual JOINs). With Linq, this is no longer possible - the semantics of the || operator should be correctly implemented, shouldn't it? Therefore, I dared to mark this bug as "Critical".
> (... or - please - show me that I'm wrong and that || + navigation work as expected!).
> Remark: In our project, which started with NHib 0.99 six years ago, we(I) wrote a complete expression framework akin to Linq (but only with conditions, not with expressions). When we found this problem, I rewrote the HQL builder so that it then created (and still creates) correct INNER or OUTER JOINs, depending on the expression tree (one has to consider Is-Null/Is-Not-Null operators and Not nodes above these, as far as I remember ... I'd have to look into our code).
--
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-04-26 17:55:02
|
[ http://216.121.112.228/browse/NH-2583?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=20971#action_20971 ]
Fabio Maulo commented on NH-2583:
---------------------------------
Now is 25% instead 50%. ;)
> Query with || operator and navigations (many-to-one) creates wrong joins
> ------------------------------------------------------------------------
>
> Key: NH-2583
> URL: http://216.121.112.228/browse/NH-2583
> Project: NHibernate
> Issue Type: Bug
> Components: Linq Provider
> Affects Versions: 3.1.0
> Reporter: Harald M. Müller
> Assignee: Patrick Earl
> Priority: Critical
> Attachments: JoinOptimizationForPureOuterJoinSemantics.txt, LogicalOperatorsInNHibernateLinq.txt, NH-2583a.diff, NH-2583b.diff, NH-2583c.diff, NH-2583d.diff, NH-2583e.diff, NH-2583f.diff, NH-2583g.diff, NH-2583g2ndTry.patch, NH2583.7z, NHib3.0.0Or.zip
>
>
> The following query
> var result = session.Query<MyBO>()
> .Where(bo =>
> (bo.BO1 != null && bo.BO1.I2 == 101) ||
> (bo.BO2 != null && bo.BO2.J2 == 203)
> )
> ;
> result.ToList();
> creates the following SQL on SQL Server 2008 (with .Net 4.0, MsSql2008Dialect):
> exec sp_executesql N'select mybo0_.Id as Id0_, mybo0_.Name as Name0_, mybo0_.BO1Key as BO3_0_, mybo0_.OtherBO1Key as OtherBO4_0_, mybo0_.BO2Key as BO5_0_ from MyBO mybo0_, MyRef1 myref1x1_, MyRef2 myref2x2_ where mybo0_.BO1Key=myref1x1_.Id and mybo0_.BO2Key=myref2x2_.Id and ((mybo0_.BO1Key is not null) and myref1x1_.I2=@p0 or (mybo0_.BO2Key is not null) and myref2x2_.J2=@p1)',N'@p0 int,@p1 int',@p0=101,@p1=203
> or, formatted:
> SELECT mybo0_.id AS id0_,
> mybo0_.name AS name0_,
> mybo0_.bo1key AS bo3_0_,
> mybo0_.otherbo1key AS otherbo4_0_,
> mybo0_.bo2key AS bo5_0_
> FROM mybo mybo0_,
> myref1 myref1x1_,
> myref2 myref2x2_
> WHERE mybo0_.bo1key = myref1x1_.id -- inner (table) join
> AND mybo0_.bo2key = myref2x2_.id -- also inner (table) join
> AND ( ( mybo0_.bo1key IS NOT NULL )
> AND myref1x1_.i2 = @p0
> OR ( mybo0_.bo2key IS NOT NULL )
> AND myref2x2_.j2 = @p1 )
> This is wrong if the reference to ref1 or ref2 is null. The attachment contains a small project that shows the problem.
> AFAIK, the problem has been always present when using HQL navigations (a.b.c....); it is - somewhat implicitly - documented in seciotn 13.7.:
> Compound path expressions make the where clause extremely powerful. Consider:
> from Eg.Cat cat where cat.Mate.Name is not null
> This query translates to an SQL query with a table (inner) join.
> In HQL, one can claim that this is "a feature" (and the workaround is to use manual JOINs). With Linq, this is no longer possible - the semantics of the || operator should be correctly implemented, shouldn't it? Therefore, I dared to mark this bug as "Critical".
> (... or - please - show me that I'm wrong and that || + navigation work as expected!).
> Remark: In our project, which started with NHib 0.99 six years ago, we(I) wrote a complete expression framework akin to Linq (but only with conditions, not with expressions). When we found this problem, I rewrote the HQL builder so that it then created (and still creates) correct INNER or OUTER JOINs, depending on the expression tree (one has to consider Is-Null/Is-Not-Null operators and Not nodes above these, as far as I remember ... I'd have to look into our code).
--
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: Harald M. M. (J. <nh...@gm...> - 2011-04-26 20:30:25
|
[ http://216.121.112.228/browse/NH-2583?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Harald M. Müller updated NH-2583:
---------------------------------
Attachment: Domain.cs
AbstractMassTestingFixture.cs
The two files added last (AbstractMassTestingFixture and Domain) reduce the number of created objects in the tests from 60680 to 24262 for all NH-2583 tests. This is still a large number, but at least it's smaller ....
The tactics employed is not to create objects repeatedly:
* Earlier, both an assignment of "null" and "0" would lead to the same object graph if the respective property would only accept "0" (i.e., it was an int and not an int?).
* Now, an InvalidOperationException is thrown on the attempted assignment, which hinders the creator to save the obejcts to the test database.
> Query with || operator and navigations (many-to-one) creates wrong joins
> ------------------------------------------------------------------------
>
> Key: NH-2583
> URL: http://216.121.112.228/browse/NH-2583
> Project: NHibernate
> Issue Type: Bug
> Components: Linq Provider
> Affects Versions: 3.1.0
> Reporter: Harald M. Müller
> Assignee: Patrick Earl
> Priority: Critical
> Attachments: AbstractMassTestingFixture.cs, Domain.cs, JoinOptimizationForPureOuterJoinSemantics.txt, LogicalOperatorsInNHibernateLinq.txt, NH-2583a.diff, NH-2583b.diff, NH-2583c.diff, NH-2583d.diff, NH-2583e.diff, NH-2583f.diff, NH-2583g.diff, NH-2583g2ndTry.patch, NH2583.7z, NHib3.0.0Or.zip
>
>
> The following query
> var result = session.Query<MyBO>()
> .Where(bo =>
> (bo.BO1 != null && bo.BO1.I2 == 101) ||
> (bo.BO2 != null && bo.BO2.J2 == 203)
> )
> ;
> result.ToList();
> creates the following SQL on SQL Server 2008 (with .Net 4.0, MsSql2008Dialect):
> exec sp_executesql N'select mybo0_.Id as Id0_, mybo0_.Name as Name0_, mybo0_.BO1Key as BO3_0_, mybo0_.OtherBO1Key as OtherBO4_0_, mybo0_.BO2Key as BO5_0_ from MyBO mybo0_, MyRef1 myref1x1_, MyRef2 myref2x2_ where mybo0_.BO1Key=myref1x1_.Id and mybo0_.BO2Key=myref2x2_.Id and ((mybo0_.BO1Key is not null) and myref1x1_.I2=@p0 or (mybo0_.BO2Key is not null) and myref2x2_.J2=@p1)',N'@p0 int,@p1 int',@p0=101,@p1=203
> or, formatted:
> SELECT mybo0_.id AS id0_,
> mybo0_.name AS name0_,
> mybo0_.bo1key AS bo3_0_,
> mybo0_.otherbo1key AS otherbo4_0_,
> mybo0_.bo2key AS bo5_0_
> FROM mybo mybo0_,
> myref1 myref1x1_,
> myref2 myref2x2_
> WHERE mybo0_.bo1key = myref1x1_.id -- inner (table) join
> AND mybo0_.bo2key = myref2x2_.id -- also inner (table) join
> AND ( ( mybo0_.bo1key IS NOT NULL )
> AND myref1x1_.i2 = @p0
> OR ( mybo0_.bo2key IS NOT NULL )
> AND myref2x2_.j2 = @p1 )
> This is wrong if the reference to ref1 or ref2 is null. The attachment contains a small project that shows the problem.
> AFAIK, the problem has been always present when using HQL navigations (a.b.c....); it is - somewhat implicitly - documented in seciotn 13.7.:
> Compound path expressions make the where clause extremely powerful. Consider:
> from Eg.Cat cat where cat.Mate.Name is not null
> This query translates to an SQL query with a table (inner) join.
> In HQL, one can claim that this is "a feature" (and the workaround is to use manual JOINs). With Linq, this is no longer possible - the semantics of the || operator should be correctly implemented, shouldn't it? Therefore, I dared to mark this bug as "Critical".
> (... or - please - show me that I'm wrong and that || + navigation work as expected!).
> Remark: In our project, which started with NHib 0.99 six years ago, we(I) wrote a complete expression framework akin to Linq (but only with conditions, not with expressions). When we found this problem, I rewrote the HQL builder so that it then created (and still creates) correct INNER or OUTER JOINs, depending on the expression tree (one has to consider Is-Null/Is-Not-Null operators and Not nodes above these, as far as I remember ... I'd have to look into our code).
--
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-04-26 21:13:28
|
[ http://216.121.112.228/browse/NH-2583?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=20976#action_20976 ]
Fabio Maulo commented on NH-2583:
---------------------------------
LOL!!! The Console.WriteLine was removed
> Query with || operator and navigations (many-to-one) creates wrong joins
> ------------------------------------------------------------------------
>
> Key: NH-2583
> URL: http://216.121.112.228/browse/NH-2583
> Project: NHibernate
> Issue Type: Bug
> Components: Linq Provider
> Affects Versions: 3.1.0
> Reporter: Harald M. Müller
> Assignee: Patrick Earl
> Priority: Critical
> Attachments: AbstractMassTestingFixture.cs, Domain.cs, JoinOptimizationForPureOuterJoinSemantics.txt, LogicalOperatorsInNHibernateLinq.txt, NH-2583a.diff, NH-2583b.diff, NH-2583c.diff, NH-2583d.diff, NH-2583e.diff, NH-2583f.diff, NH-2583g.diff, NH-2583g2ndTry.patch, NH2583.7z, NHib3.0.0Or.zip
>
>
> The following query
> var result = session.Query<MyBO>()
> .Where(bo =>
> (bo.BO1 != null && bo.BO1.I2 == 101) ||
> (bo.BO2 != null && bo.BO2.J2 == 203)
> )
> ;
> result.ToList();
> creates the following SQL on SQL Server 2008 (with .Net 4.0, MsSql2008Dialect):
> exec sp_executesql N'select mybo0_.Id as Id0_, mybo0_.Name as Name0_, mybo0_.BO1Key as BO3_0_, mybo0_.OtherBO1Key as OtherBO4_0_, mybo0_.BO2Key as BO5_0_ from MyBO mybo0_, MyRef1 myref1x1_, MyRef2 myref2x2_ where mybo0_.BO1Key=myref1x1_.Id and mybo0_.BO2Key=myref2x2_.Id and ((mybo0_.BO1Key is not null) and myref1x1_.I2=@p0 or (mybo0_.BO2Key is not null) and myref2x2_.J2=@p1)',N'@p0 int,@p1 int',@p0=101,@p1=203
> or, formatted:
> SELECT mybo0_.id AS id0_,
> mybo0_.name AS name0_,
> mybo0_.bo1key AS bo3_0_,
> mybo0_.otherbo1key AS otherbo4_0_,
> mybo0_.bo2key AS bo5_0_
> FROM mybo mybo0_,
> myref1 myref1x1_,
> myref2 myref2x2_
> WHERE mybo0_.bo1key = myref1x1_.id -- inner (table) join
> AND mybo0_.bo2key = myref2x2_.id -- also inner (table) join
> AND ( ( mybo0_.bo1key IS NOT NULL )
> AND myref1x1_.i2 = @p0
> OR ( mybo0_.bo2key IS NOT NULL )
> AND myref2x2_.j2 = @p1 )
> This is wrong if the reference to ref1 or ref2 is null. The attachment contains a small project that shows the problem.
> AFAIK, the problem has been always present when using HQL navigations (a.b.c....); it is - somewhat implicitly - documented in seciotn 13.7.:
> Compound path expressions make the where clause extremely powerful. Consider:
> from Eg.Cat cat where cat.Mate.Name is not null
> This query translates to an SQL query with a table (inner) join.
> In HQL, one can claim that this is "a feature" (and the workaround is to use manual JOINs). With Linq, this is no longer possible - the semantics of the || operator should be correctly implemented, shouldn't it? Therefore, I dared to mark this bug as "Critical".
> (... or - please - show me that I'm wrong and that || + navigation work as expected!).
> Remark: In our project, which started with NHib 0.99 six years ago, we(I) wrote a complete expression framework akin to Linq (but only with conditions, not with expressions). When we found this problem, I rewrote the HQL builder so that it then created (and still creates) correct INNER or OUTER JOINs, depending on the expression tree (one has to consider Is-Null/Is-Not-Null operators and Not nodes above these, as far as I remember ... I'd have to look into our code).
--
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-04-30 14:10:52
|
[ http://216.121.112.228/browse/NH-2583?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=20982#action_20982 ]
Fabio Maulo commented on NH-2583:
---------------------------------
Herald,
The difference storing less entities is not so big.
Probably the problem is in another place.
btw, I would know the situation of this issue.
Which is the pending task to close the issue ?
> Query with || operator and navigations (many-to-one) creates wrong joins
> ------------------------------------------------------------------------
>
> Key: NH-2583
> URL: http://216.121.112.228/browse/NH-2583
> Project: NHibernate
> Issue Type: Bug
> Components: Linq Provider
> Affects Versions: 3.1.0
> Reporter: Harald M. Müller
> Assignee: Patrick Earl
> Priority: Critical
> Attachments: AbstractMassTestingFixture.cs, Domain.cs, JoinOptimizationForPureOuterJoinSemantics.txt, LogicalOperatorsInNHibernateLinq.txt, NH-2583a.diff, NH-2583b.diff, NH-2583c.diff, NH-2583d.diff, NH-2583e.diff, NH-2583f.diff, NH-2583g.diff, NH-2583g2ndTry.patch, NH2583.7z, NHib3.0.0Or.zip
>
>
> The following query
> var result = session.Query<MyBO>()
> .Where(bo =>
> (bo.BO1 != null && bo.BO1.I2 == 101) ||
> (bo.BO2 != null && bo.BO2.J2 == 203)
> )
> ;
> result.ToList();
> creates the following SQL on SQL Server 2008 (with .Net 4.0, MsSql2008Dialect):
> exec sp_executesql N'select mybo0_.Id as Id0_, mybo0_.Name as Name0_, mybo0_.BO1Key as BO3_0_, mybo0_.OtherBO1Key as OtherBO4_0_, mybo0_.BO2Key as BO5_0_ from MyBO mybo0_, MyRef1 myref1x1_, MyRef2 myref2x2_ where mybo0_.BO1Key=myref1x1_.Id and mybo0_.BO2Key=myref2x2_.Id and ((mybo0_.BO1Key is not null) and myref1x1_.I2=@p0 or (mybo0_.BO2Key is not null) and myref2x2_.J2=@p1)',N'@p0 int,@p1 int',@p0=101,@p1=203
> or, formatted:
> SELECT mybo0_.id AS id0_,
> mybo0_.name AS name0_,
> mybo0_.bo1key AS bo3_0_,
> mybo0_.otherbo1key AS otherbo4_0_,
> mybo0_.bo2key AS bo5_0_
> FROM mybo mybo0_,
> myref1 myref1x1_,
> myref2 myref2x2_
> WHERE mybo0_.bo1key = myref1x1_.id -- inner (table) join
> AND mybo0_.bo2key = myref2x2_.id -- also inner (table) join
> AND ( ( mybo0_.bo1key IS NOT NULL )
> AND myref1x1_.i2 = @p0
> OR ( mybo0_.bo2key IS NOT NULL )
> AND myref2x2_.j2 = @p1 )
> This is wrong if the reference to ref1 or ref2 is null. The attachment contains a small project that shows the problem.
> AFAIK, the problem has been always present when using HQL navigations (a.b.c....); it is - somewhat implicitly - documented in seciotn 13.7.:
> Compound path expressions make the where clause extremely powerful. Consider:
> from Eg.Cat cat where cat.Mate.Name is not null
> This query translates to an SQL query with a table (inner) join.
> In HQL, one can claim that this is "a feature" (and the workaround is to use manual JOINs). With Linq, this is no longer possible - the semantics of the || operator should be correctly implemented, shouldn't it? Therefore, I dared to mark this bug as "Critical".
> (... or - please - show me that I'm wrong and that || + navigation work as expected!).
> Remark: In our project, which started with NHib 0.99 six years ago, we(I) wrote a complete expression framework akin to Linq (but only with conditions, not with expressions). When we found this problem, I rewrote the HQL builder so that it then created (and still creates) correct INNER or OUTER JOINs, depending on the expression tree (one has to consider Is-Null/Is-Not-Null operators and Not nodes above these, as far as I remember ... I'd have to look into our code).
--
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: Harald M. M. (J. <nh...@gm...> - 2011-04-30 15:22:13
|
[ http://216.121.112.228/browse/NH-2583?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=20984#action_20984 ]
Harald M. Müller commented on NH-2583:
--------------------------------------
> The difference storing less entities is not so big. Probably the problem is in another place.
Mhm. That would require more precise measurements. Maybe I find to time to fire up a dotTrace.
> btw, I would know the situation of this issue. Which is the pending task to close the issue ?
Patrick should also comment on this. As I see it:
a) We know a solution that creates only outer joins (2 ways: Use "Select...JoinDetector" instead of WhereJoinDetector; or comment out call to MakeInnerIfJoined in WhereJoinDetector). BUT: Neither Patrick nor me have tested this; and for ?? and ?: operators, there are not even test cases.
b) We know that the current solution, which DOES optimize to inner joins, is WRONG when there is a ?? (coalesce) or ?: in the query. I have written an analysis text that explains a quite fine-granular solution for ??, but ?: the analysis is still missing. So there is not yet a solution that works correctly for inner joins with these operators.
Patrick, as I understood him, would like to check in a solution along the lines of a), so that we *have* a solution for || - even if it does not optimize to inner joins even in simple cases.
> Query with || operator and navigations (many-to-one) creates wrong joins
> ------------------------------------------------------------------------
>
> Key: NH-2583
> URL: http://216.121.112.228/browse/NH-2583
> Project: NHibernate
> Issue Type: Bug
> Components: Linq Provider
> Affects Versions: 3.1.0
> Reporter: Harald M. Müller
> Assignee: Patrick Earl
> Priority: Critical
> Attachments: AbstractMassTestingFixture.cs, Domain.cs, JoinOptimizationForPureOuterJoinSemantics.txt, LogicalOperatorsInNHibernateLinq.txt, NH-2583a.diff, NH-2583b.diff, NH-2583c.diff, NH-2583d.diff, NH-2583e.diff, NH-2583f.diff, NH-2583g.diff, NH-2583g2ndTry.patch, NH2583.7z, NHib3.0.0Or.zip
>
>
> The following query
> var result = session.Query<MyBO>()
> .Where(bo =>
> (bo.BO1 != null && bo.BO1.I2 == 101) ||
> (bo.BO2 != null && bo.BO2.J2 == 203)
> )
> ;
> result.ToList();
> creates the following SQL on SQL Server 2008 (with .Net 4.0, MsSql2008Dialect):
> exec sp_executesql N'select mybo0_.Id as Id0_, mybo0_.Name as Name0_, mybo0_.BO1Key as BO3_0_, mybo0_.OtherBO1Key as OtherBO4_0_, mybo0_.BO2Key as BO5_0_ from MyBO mybo0_, MyRef1 myref1x1_, MyRef2 myref2x2_ where mybo0_.BO1Key=myref1x1_.Id and mybo0_.BO2Key=myref2x2_.Id and ((mybo0_.BO1Key is not null) and myref1x1_.I2=@p0 or (mybo0_.BO2Key is not null) and myref2x2_.J2=@p1)',N'@p0 int,@p1 int',@p0=101,@p1=203
> or, formatted:
> SELECT mybo0_.id AS id0_,
> mybo0_.name AS name0_,
> mybo0_.bo1key AS bo3_0_,
> mybo0_.otherbo1key AS otherbo4_0_,
> mybo0_.bo2key AS bo5_0_
> FROM mybo mybo0_,
> myref1 myref1x1_,
> myref2 myref2x2_
> WHERE mybo0_.bo1key = myref1x1_.id -- inner (table) join
> AND mybo0_.bo2key = myref2x2_.id -- also inner (table) join
> AND ( ( mybo0_.bo1key IS NOT NULL )
> AND myref1x1_.i2 = @p0
> OR ( mybo0_.bo2key IS NOT NULL )
> AND myref2x2_.j2 = @p1 )
> This is wrong if the reference to ref1 or ref2 is null. The attachment contains a small project that shows the problem.
> AFAIK, the problem has been always present when using HQL navigations (a.b.c....); it is - somewhat implicitly - documented in seciotn 13.7.:
> Compound path expressions make the where clause extremely powerful. Consider:
> from Eg.Cat cat where cat.Mate.Name is not null
> This query translates to an SQL query with a table (inner) join.
> In HQL, one can claim that this is "a feature" (and the workaround is to use manual JOINs). With Linq, this is no longer possible - the semantics of the || operator should be correctly implemented, shouldn't it? Therefore, I dared to mark this bug as "Critical".
> (... or - please - show me that I'm wrong and that || + navigation work as expected!).
> Remark: In our project, which started with NHib 0.99 six years ago, we(I) wrote a complete expression framework akin to Linq (but only with conditions, not with expressions). When we found this problem, I rewrote the HQL builder so that it then created (and still creates) correct INNER or OUTER JOINs, depending on the expression tree (one has to consider Is-Null/Is-Not-Null operators and Not nodes above these, as far as I remember ... I'd have to look into our code).
--
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-04-30 20:25:57
|
[ http://216.121.112.228/browse/NH-2583?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=20985#action_20985 ]
Fabio Maulo commented on NH-2583:
---------------------------------
FYI
about run-time: a possible cause can be the number of exceptions created but, IMO, we should prevent the creation of those case generating exceptions.
btw, Herald, don't worry about the time, now what is more important is find a solution.
Thanks for your collaboration and the effort you put in this issue.
> Query with || operator and navigations (many-to-one) creates wrong joins
> ------------------------------------------------------------------------
>
> Key: NH-2583
> URL: http://216.121.112.228/browse/NH-2583
> Project: NHibernate
> Issue Type: Bug
> Components: Linq Provider
> Affects Versions: 3.1.0
> Reporter: Harald M. Müller
> Assignee: Patrick Earl
> Priority: Critical
> Attachments: AbstractMassTestingFixture.cs, Domain.cs, JoinOptimizationForPureOuterJoinSemantics.txt, LogicalOperatorsInNHibernateLinq.txt, NH-2583a.diff, NH-2583b.diff, NH-2583c.diff, NH-2583d.diff, NH-2583e.diff, NH-2583f.diff, NH-2583g.diff, NH-2583g2ndTry.patch, NH2583.7z, NHib3.0.0Or.zip
>
>
> The following query
> var result = session.Query<MyBO>()
> .Where(bo =>
> (bo.BO1 != null && bo.BO1.I2 == 101) ||
> (bo.BO2 != null && bo.BO2.J2 == 203)
> )
> ;
> result.ToList();
> creates the following SQL on SQL Server 2008 (with .Net 4.0, MsSql2008Dialect):
> exec sp_executesql N'select mybo0_.Id as Id0_, mybo0_.Name as Name0_, mybo0_.BO1Key as BO3_0_, mybo0_.OtherBO1Key as OtherBO4_0_, mybo0_.BO2Key as BO5_0_ from MyBO mybo0_, MyRef1 myref1x1_, MyRef2 myref2x2_ where mybo0_.BO1Key=myref1x1_.Id and mybo0_.BO2Key=myref2x2_.Id and ((mybo0_.BO1Key is not null) and myref1x1_.I2=@p0 or (mybo0_.BO2Key is not null) and myref2x2_.J2=@p1)',N'@p0 int,@p1 int',@p0=101,@p1=203
> or, formatted:
> SELECT mybo0_.id AS id0_,
> mybo0_.name AS name0_,
> mybo0_.bo1key AS bo3_0_,
> mybo0_.otherbo1key AS otherbo4_0_,
> mybo0_.bo2key AS bo5_0_
> FROM mybo mybo0_,
> myref1 myref1x1_,
> myref2 myref2x2_
> WHERE mybo0_.bo1key = myref1x1_.id -- inner (table) join
> AND mybo0_.bo2key = myref2x2_.id -- also inner (table) join
> AND ( ( mybo0_.bo1key IS NOT NULL )
> AND myref1x1_.i2 = @p0
> OR ( mybo0_.bo2key IS NOT NULL )
> AND myref2x2_.j2 = @p1 )
> This is wrong if the reference to ref1 or ref2 is null. The attachment contains a small project that shows the problem.
> AFAIK, the problem has been always present when using HQL navigations (a.b.c....); it is - somewhat implicitly - documented in seciotn 13.7.:
> Compound path expressions make the where clause extremely powerful. Consider:
> from Eg.Cat cat where cat.Mate.Name is not null
> This query translates to an SQL query with a table (inner) join.
> In HQL, one can claim that this is "a feature" (and the workaround is to use manual JOINs). With Linq, this is no longer possible - the semantics of the || operator should be correctly implemented, shouldn't it? Therefore, I dared to mark this bug as "Critical".
> (... or - please - show me that I'm wrong and that || + navigation work as expected!).
> Remark: In our project, which started with NHib 0.99 six years ago, we(I) wrote a complete expression framework akin to Linq (but only with conditions, not with expressions). When we found this problem, I rewrote the HQL builder so that it then created (and still creates) correct INNER or OUTER JOINs, depending on the expression tree (one has to consider Is-Null/Is-Not-Null operators and Not nodes above these, as far as I remember ... I'd have to look into our code).
--
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: Patrick E. (JIRA) <nh...@gm...> - 2011-06-27 01:10:59
|
[ http://216.121.112.228/browse/NH-2583?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Patrick Earl resolved NH-2583.
------------------------------
Resolution: Fixed
Fix Version/s: 3.2.0CR1
I'm happy to report that based on Harald's very helpful framing of the problem, I was able to find a simple solution where the correctness is easy to understand. This is now implemented and I'm not aware of any deficiencies (aside from not optimizing to inner joins on some occasions).
> Query with || operator and navigations (many-to-one) creates wrong joins
> ------------------------------------------------------------------------
>
> Key: NH-2583
> URL: http://216.121.112.228/browse/NH-2583
> Project: NHibernate
> Issue Type: Bug
> Components: Linq Provider
> Affects Versions: 3.1.0
> Reporter: Harald M. Müller
> Assignee: Patrick Earl
> Priority: Critical
> Fix For: 3.2.0CR1
>
> Attachments: AbstractMassTestingFixture.cs, Domain.cs, JoinOptimizationForPureOuterJoinSemantics.txt, LogicalOperatorsInNHibernateLinq.txt, NH-2583a.diff, NH-2583b.diff, NH-2583c.diff, NH-2583d.diff, NH-2583e.diff, NH-2583f.diff, NH-2583g.diff, NH-2583g2ndTry.patch, NH2583.7z, NHib3.0.0Or.zip
>
>
> The following query
> var result = session.Query<MyBO>()
> .Where(bo =>
> (bo.BO1 != null && bo.BO1.I2 == 101) ||
> (bo.BO2 != null && bo.BO2.J2 == 203)
> )
> ;
> result.ToList();
> creates the following SQL on SQL Server 2008 (with .Net 4.0, MsSql2008Dialect):
> exec sp_executesql N'select mybo0_.Id as Id0_, mybo0_.Name as Name0_, mybo0_.BO1Key as BO3_0_, mybo0_.OtherBO1Key as OtherBO4_0_, mybo0_.BO2Key as BO5_0_ from MyBO mybo0_, MyRef1 myref1x1_, MyRef2 myref2x2_ where mybo0_.BO1Key=myref1x1_.Id and mybo0_.BO2Key=myref2x2_.Id and ((mybo0_.BO1Key is not null) and myref1x1_.I2=@p0 or (mybo0_.BO2Key is not null) and myref2x2_.J2=@p1)',N'@p0 int,@p1 int',@p0=101,@p1=203
> or, formatted:
> SELECT mybo0_.id AS id0_,
> mybo0_.name AS name0_,
> mybo0_.bo1key AS bo3_0_,
> mybo0_.otherbo1key AS otherbo4_0_,
> mybo0_.bo2key AS bo5_0_
> FROM mybo mybo0_,
> myref1 myref1x1_,
> myref2 myref2x2_
> WHERE mybo0_.bo1key = myref1x1_.id -- inner (table) join
> AND mybo0_.bo2key = myref2x2_.id -- also inner (table) join
> AND ( ( mybo0_.bo1key IS NOT NULL )
> AND myref1x1_.i2 = @p0
> OR ( mybo0_.bo2key IS NOT NULL )
> AND myref2x2_.j2 = @p1 )
> This is wrong if the reference to ref1 or ref2 is null. The attachment contains a small project that shows the problem.
> AFAIK, the problem has been always present when using HQL navigations (a.b.c....); it is - somewhat implicitly - documented in seciotn 13.7.:
> Compound path expressions make the where clause extremely powerful. Consider:
> from Eg.Cat cat where cat.Mate.Name is not null
> This query translates to an SQL query with a table (inner) join.
> In HQL, one can claim that this is "a feature" (and the workaround is to use manual JOINs). With Linq, this is no longer possible - the semantics of the || operator should be correctly implemented, shouldn't it? Therefore, I dared to mark this bug as "Critical".
> (... or - please - show me that I'm wrong and that || + navigation work as expected!).
> Remark: In our project, which started with NHib 0.99 six years ago, we(I) wrote a complete expression framework akin to Linq (but only with conditions, not with expressions). When we found this problem, I rewrote the HQL builder so that it then created (and still creates) correct INNER or OUTER JOINs, depending on the expression tree (one has to consider Is-Null/Is-Not-Null operators and Not nodes above these, as far as I remember ... I'd have to look into our code).
--
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