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

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

    [ 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