[nhibernate-issues] [NHJIRA] Commented: (NH-2583) Query with || operator and navigations (many-to-one) creates wrong joins

[Harald M. M. (J. <nh...@gm...>]

    [ 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