[nhibernate-issues] [NHJIRA] Updated: (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: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