Invalid SQL generated for bool expressions

6 posts, 0 answers
  1. Greg
    Greg avatar
    44 posts
    Member since:
    Jun 2012

    Posted 14 Nov 2013 Link to this post

    Using mssql when casting a bool field to bool? in where expressions 'field <> 0' is generated which is a conditional expression and not a nullable bit field. This is especially annoying when the field comes from a left-joined table and I need to treat it as nullable. E.g.



    from a in db.Table1
    join b in db.Table2 on ... equals ... into bg
    from b in bg.DefaultIfEmpty()
    join c in db.Table3 on ... equals ... into cg
    from c in cg.DefaultIfEmpty()
    where (bool?)b.Flag ?? (bool?)c.Flag ?? false
    select a




    generates ISNULL(b.[Flag] <> 0,ISNULL(c.[Flag] <> 0,(1=0))). This works properly for Nullable<int>, but not for bool. The ORM should always ignore the cast from T to Nullable<T> when generating the query.



    If I write (b ?? c).Allow the generated code is ISNULL(b.[Flag], c.[ID]) -- didn't ever get the column right! -- which is not a conditional expression, syntax error again.



    Also note the constant 'false' is translated to (1=0) and 'true' to (1=1) so if I use ((bool?)b.Flag ?? (bool?)c.Flag) == true the generated code is ISNULL(b.[Flag]<>0, c.[Flag]<>0)=(1=1) which is again malformed.



    You should only create a predicate expression from a bit expression when required (e.g. when combining with logical operators), the pattern would be "(case when [expression]=1 then 1 else 0 end)=1" or "isnull([expression], 0)=1"



    A possible workaround is this: ((b ?? c) != null ? (b ?? c).Allow : false) or (b != null ? b.Allow : c.Allow), the latter generates quite complicated code if the PK is long.
  2. Thomas
    Admin
    Thomas avatar
    590 posts

    Posted 15 Nov 2013 Link to this post

    Hello Greg,

    thanks for bringing this to our attention! I filed a bug entry for this. Based on my experience with past issues in the bool area, I expect this will take a bit of time; Booleans are not that easy as they seem to be when you need to consider bit,bool,int,string columns that could back them. Therefore I would not like to make a promise when this issue will be fixed.

    I've updated your Telerik points for this.
    Thomas
    Telerik
    OpenAccess ORM Q3 2013 simplifies your model operations even further providing you with greater flexibility. Check out the list of new features shipped with our latest release!
  3. DevCraft banner
  4. Doroteya
    Admin
    Doroteya avatar
    502 posts

    Posted 28 Feb 2014 Link to this post

    Hello Greg,

    The LINQ query you need to execute can be composed as demonstrated in the next code snippet (it is based on the Northwind sample database). The SQL statement generated by Telerik Data Access, in this case, will return the correct result on condition that the boolean properties used in the where clause are set as Nullable in the model.
    using (EntitiesModel dbContext = new EntitiesModel())
    {
      var products = from p in dbContext.Products
                join od in dbContext.OrderDetails on p.ProductID equals od.ProductID into pod
                from od in pod.DefaultIfEmpty()
                join o in dbContext.Orders on od.OrderID equals o.OrderID into podo
                from o in podo.DefaultIfEmpty()
                where (od.Confirmed ?? o.Shipped) ?? false
                select p;
    }
    The generated SQL is as follows:
    SELECT *
    FROM [Products] a
    LEFT JOIN [Order Details] AS b ON (a.[ProductID] = b.[ProductID])
    LEFT JOIN [Orders] AS c ON (b.[OrderID] = c.[OrderID])
    WHERE ISNULL(b.[Confirmed],c.[Shipped]) = 1
    ORDER BY a.[ProductID]

    Regarding the bug discovered through the tests of the query posted by you, I will make sure you are properly updated in this thread once the fix is available.

    Thank you for your patience and cooperation.  



    Regards,
    Doroteya
    Telerik
    OpenAccess ORM is now Telerik Data Access. For more information on the new names, please, check out the Telerik Product Map.
  5. Greg
    Greg avatar
    44 posts
    Member since:
    Jun 2012

    Posted 03 Mar 2014 in reply to Doroteya Link to this post

    Just an addition to your workaround:
    This works:
        where (od.Confirmed ?? o.Shipped) ?? false
    These however (which is semantically the same since ?? is associative) does not:
        where od.Confirmed ?? (o.Shipped ?? false)
        where od.Confirmed ?? o.Shipped ?? false
    The latter two are syntactically the same due to operator precedence.
    Bool has too many issues right now. I'm going to stick with mapping stuff as int until this is fixed. I can map that as non-nullable as well.
  6. Greg
    Greg avatar
    44 posts
    Member since:
    Jun 2012

    Posted 03 Mar 2014 in reply to Greg Link to this post

    And one more addition, the following pattern also generates an invalid query regardless of parentheses and associativity:
    from d in db.MainTable
    where
    (
        (
            db.LookupTable.Where({pk match based on MainTable values}).Select({bool? field}).FirstOrDefault()
            ??
            db.LookupTable.Where({other pk match based on MainTable values}).Select({bool? field}).FirstOrDefault()
        )
        ??
        false
    )
    select d

    Again, OA detects correctly that the lookup subqueries will produce a single value, but the bool expression has a syntax error. It either does not add '=1' to the end or adds =(1=1).
  7. Doroteya
    Admin
    Doroteya avatar
    502 posts

    Posted 06 Mar 2014 Link to this post

    Hi Greg,

    Thank you for your feedback.

    Indeed, your observation about syntactically the same where clauses, and the different results they produce is correct. This effect is caused by a bug on our side, which was logged after your initial report.

    Regarding the last LINQ query, I confirm that we successfully reproduced the behaviour you describe. Unfortunately, I was unable to define a workaround about it.

    Let me assure you that at the time present all the scenarios you experienced issues with were logged on our side, and would be taken into consideration when we design any fixes in this direction. Additionally, I will make sure to update this thread as soon as I have any suggestions for you, or as soon as the fix is available for download.

    Thank you in advance for your understanding and cooperation.


    Regards,
    Doroteya
    Telerik
    OpenAccess ORM is now Telerik Data Access. For more information on the new names, please, check out the Telerik Product Map.
Back to Top
DevCraft banner