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

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

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