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

[Fabio M. (JIRA) <nh...@gm...>]

    [ 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