LlblGen ignores second part of OR

Posts   
 
    
AlexanderM
User
Posts: 41
Joined: 18-May-2012
# Posted on: 18-Sep-2014 14:54:12   

Hello,

I have the following statement and to my surprise PoolEmployeeContract was sometimes null. This is functionally impossible (data consistency), and I would say technically also because of the use of First instead of FirstOrDefault. Anyway I proceeded to investigate the generated SQL and noticed the second part of my OR statement (the second one on pec) was missing. Simple test was then to revert the OR statement and to notice the changes in result and generated SQL.


var actualWeek = new DateTime(2014,9,15);
var poolId = 1783;

var poolEmps = (from pe in PoolEmployee 
        where pe.PoolId == poolId && (pe.MaxDateOut == null || pe.MaxDateOut > actualWeek)
        select new {PoolEmployee = pe,
        PoolEmployeeContract=pe.PoolEmployeeContracts.First(pec => pec.DateIn < actualWeek && (pec.DateOut == null || pec.DateOut > actualWeek))
        }).ToList();
poolEmps.Dump();

Original SQL: SELECT * FROM "IP_OWNER"."POOL_EMPLOYEE_CONTRACT" "LPA_L1" WHERE ( ( ( ( "LPA_L1"."DATE_IN" < p1) AND ( ( "LPA_L1"."DATE_OUT" IS NULL)))) AND (
EXISTS (SELECT "LPLA_1"."ID" AS "Id" FROM "IP_OWNER"."POOL_EMPLOYEE" "LPLA_1" WHERE ( ( ( ( ( "LPLA_1"."POOL_ID" = p2) AND ( ( "LPLA_1"."MAX_DATE_OUT" IS NULL) OR ( "LPLA_1"."MAX_DATE_OUT" > p3))))) AND "LPLA_1"."ID" = "LPA_L1"."POOL_EMPLOYEE_ID"))))

SQL with reverted OR (pec.DateOut > actualWeek || pec.DateOut == null) SELECT * FROM "IP_OWNER"."POOL_EMPLOYEE_CONTRACT" "LPA_L1" WHERE ( ( ( ( "LPA_L1"."DATE_IN" < p1) AND ( (** "LPA_L1"."DATE_OUT" > p2**)))) AND ( EXISTS (SELECT "LPLA_1"."ID" AS "Id" FROM "IP_OWNER"."POOL_EMPLOYEE" "LPLA_1" WHERE ( ( ( ( ( "LPLA_1"."POOL_ID" = p3) AND ( ( "LPLA_1"."MAX_DATE_OUT" IS NULL) OR ( "LPLA_1"."MAX_DATE_OUT" > p4))))) AND "LPLA_1"."ID" = "LPA_L1"."POOL_EMPLOYEE_ID"))))

We're using version 4.2.14.811

Kind regards, Alexander.

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 18-Sep-2014 17:52:42   

Is the relation between Emp and EmpContract 1:1 or 1:m?

(Edit) Reproduced on Northwind, for the Customer - Order relation.

Code used:

            var actualWeek = new DateTime(1914, 9, 15);

            using (var adapter = new DataAccessAdapter())
            {
                var metaData = new LinqMetaData(adapter);

                var poolEmps = (from pe in metaData.Customer
                                where pe.Country == "UK" && (pe.Region == null || pe.Region == "Isle of Wight")
                                select new
                                {
                                    PoolEmployee = pe,
                                    PoolEmployeeContract = pe.Orders.First(pec => pec.OrderDate < actualWeek && (pec.ShippedDate == null || pec.ShippedDate > actualWeek))
                                }).ToList();
            }

Query: SELECT [LPLA_1].[Address], [LPLA_1].[City], [LPLA_1].[CompanyName], [LPLA_1].[ContactName], [LPLA_1].[ContactTitle], [LPLA_1].[Country], [LPLA_1].[CustomerID] AS [CustomerId], [LPLA_1].[Fax], [LPLA_1].[Phone], [LPLA_1].[PostalCode], [LPLA_1].[Region], 1 AS [LPFA_3] FROM [Northwind].[dbo].[Customers]  [LPLA_1]   WHERE ( ( ( ( ( ( [LPLA_1].[Country] = @p1) AND ( ( [LPLA_1].[Region] IS NULL) OR ( [LPLA_1].[Region] = @p2)))))))
    Parameter: @p1 : String. Length: 15. Precision: 0. Scale: 0. Direction: Input. Value: "UK".
    Parameter: @p2 : String. Length: 15. Precision: 0. Scale: 0. Direction: Input. Value: "Isle of Wight".
Query: SELECT [LPA_L1].[CustomerID] AS [CustomerId], [LPA_L1].[EmployeeID] AS [EmployeeId], [LPA_L1].[Freight], [LPA_L1].[OrderDate], [LPA_L1].[OrderID] AS [OrderId], [LPA_L1].[RequiredDate], [LPA_L1].[ShipAddress], [LPA_L1].[ShipCity], [LPA_L1].[ShipCountry], [LPA_L1].[ShipName], [LPA_L1].[ShippedDate], [LPA_L1].[ShipPostalCode], [LPA_L1].[ShipRegion], [LPA_L1].[ShipVia] FROM [Northwind].[dbo].[Orders]  [LPA_L1]   WHERE ( ( ( ( [LPA_L1].[OrderDate] < @p1) AND ( ( [LPA_L1].[ShippedDate] IS NULL)))) AND ( [LPA_L1].[CustomerID] IN (@p2, @p3, @p4, @p5, @p6, @p7, @p8)))
    Parameter: @p1 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1914-09-15T00:00:00.0000000.
    Parameter: @p2 : StringFixedLength. Length: 5. Precision: 0. Scale: 0. Direction: Input. Value: "AROUT".
    Parameter: @p3 : StringFixedLength. Length: 5. Precision: 0. Scale: 0. Direction: Input. Value: "BSBEV".
    Parameter: @p4 : StringFixedLength. Length: 5. Precision: 0. Scale: 0. Direction: Input. Value: "CONSH".
    Parameter: @p5 : StringFixedLength. Length: 5. Precision: 0. Scale: 0. Direction: Input. Value: "EASTC".
    Parameter: @p6 : StringFixedLength. Length: 5. Precision: 0. Scale: 0. Direction: Input. Value: "ISLAT".
    Parameter: @p7 : StringFixedLength. Length: 5. Precision: 0. Scale: 0. Direction: Input. Value: "NORTS".
    Parameter: @p8 : StringFixedLength. Length: 5. Precision: 0. Scale: 0. Direction: Input. Value: "SEVES".
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 18-Sep-2014 18:40:26   

Please specify more information, this is clearly due to inheritance but you didn't specify any inheritance hierarchy info.

We'll look into it. It does sound familiar though...

Frans Bouma | Lead developer LLBLGen Pro
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 19-Sep-2014 10:03:00   

The predicate for the First() query can become false:

First(pec => pec.DateIn < actualWeek && (pec.DateOut == null || pec.DateOut > actualWeek))

The '&&' after pec.DateIn < actualWeek only results in true if pec.DateIn < actualWeek and pec.DateOut is null. The '|| pec.DateOut > actualWeek)' is always false, if pec.DateIn < actualWeek is true (a date can't be both smaller and larger than actualWeek wink )

Nevertheless, the missing expression is a bit odd. It might be due to a compiler optimization (i.e. the predicate is missing from the expression tree as it doesn't have an influence over the result) so I'll check that first. It's not removed when I run the First() query separately:

var q = metaData.Order.First(o => o.OrderDate < actualWeek && (o.ShippedDate == null || o.ShippedDate > actualWeek));

results in:

SELECT TOP 1 [LPA_L1].[CustomerID] AS [CustomerId],
             [LPA_L1].[EmployeeID] AS [EmployeeId],
             [LPA_L1].[Freight],
             [LPA_L1].[OrderDate],
             [LPA_L1].[OrderID] AS [OrderId],
             [LPA_L1].[RequiredDate],
             [LPA_L1].[ShipAddress],
             [LPA_L1].[ShipCity],
             [LPA_L1].[ShipCountry],
             [LPA_L1].[ShipName],
             [LPA_L1].[ShippedDate],
             [LPA_L1].[ShipPostalCode],
             [LPA_L1].[ShipRegion],
             [LPA_L1].[ShipVia]
FROM   [Northwind].[dbo].[Orders] [LPA_L1]
WHERE  (((([LPA_L1].[OrderDate] < '1914-09-15T00:00:00' /* @p1 */)
      AND (([LPA_L1].[ShippedDate] IS NULL)
            OR ([LPA_L1].[ShippedDate] > '1914-09-15T00:00:00' /* @p2 */)))))

Which did result in a NULL as result, which was a bit surprising till I saw the predicate can't be true if var actualWeek = new DateTime(1914, 9, 15).

Digging deeper simple_smile

(edit) Full expression tree of working repro query:

value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource2`1[NW26.Adapter.EntityClasses.CustomerEntity])
    .Where(c => ((c.Country == "UK") 
                AndAlso ((c.Region == null) 
                            OrElse (c.Region == "Isle of Wight"))))
    .Select(c => new <>f__AnonymousType84`2(
                    Customer = c, 
                    Order = c.Orders.FirstOrDefault(o => 
                                    ((o.OrderDate < Convert(value(LinqTester.AdapterTests.DynamicListFetches+<>c__DisplayClass0).actualWeek)) 
                                    AndAlso 
                                    ((o.ShippedDate == Convert(null)) 
                                        OrElse 
                                     (o.ShippedDate > Convert(value(LinqTester.AdapterTests.DynamicListFetches+<>c__DisplayClass0).actualWeek)))))))

So we can rule out the compiler doing tricks, we have a bug somewhere.

Frans Bouma | Lead developer LLBLGen Pro
AlexanderM
User
Posts: 41
Joined: 18-May-2012
# Posted on: 19-Sep-2014 10:40:21   

Good to see, you're working on it. We have a workaround, rewriting the query in dot/fluent syntax and placing an .AsEnumerable() in between.

var poolEmps = _metaData.PoolEmployee.Where(pe => pe.PoolId == poolId && (pe.MaxDateOut == null || pe.MaxDateOut > actualDate)) .AsEnumerable() .Select(pe => new PoolEmpExt { PoolEmployee = pe, PoolEmployeeContract = pe.PoolEmployeeContracts.FirstOrDefault(pec => pec.DateIn < actualDate && (pec.DateOut > actualDate || pec.DateOut == null)), }).ToList();

So we can continue but we will wait for the fix because I would like to remove AsEnumerable()

Happy hunting sunglasses

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 19-Sep-2014 10:43:39   

Also do realize what I said about the predicate, that the orred expression is unnecessary wink

Digging...

Frans Bouma | Lead developer LLBLGen Pro
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 19-Sep-2014 11:44:36   

Found it. And because it's so utterly silly, I'm going to post it here so you all have a good laugh about it simple_smile


/// <summary>
/// Removes the passed in predicates from the filter to clean.
/// </summary>
/// <param name="predicatesToRemove">The predicates to remove.</param>
/// <param name="filterToClean">The filter to clean.</param>
/// <returns></returns>
public static IPredicateExpression RemovePredicatesFromFilter(List<IPredicate> predicatesToRemove, IPredicateExpression filterToClean)
{
    if(filterToClean.IsEmpty())
    {
        return filterToClean;
    }

    IPredicateExpression toReturn = new PredicateExpression();
    PredicateExpressionOperator lastSeenOperator = PredicateExpressionOperator.And;
    foreach(IPredicateExpressionElement element in filterToClean)
    {
        switch(element.Type)
        {
            case PredicateExpressionElementType.Empty:
                continue;
            case PredicateExpressionElementType.Operator:
                lastSeenOperator = (PredicateExpressionOperator)element.Contents;
                break;
            case PredicateExpressionElementType.Predicate:
                IPredicate elementToAdd;
                IPredicateExpression elementAsExpression = element.Contents as IPredicateExpression;
                if(elementAsExpression == null)
                {
                    // IPredicate
                    if(predicatesToRemove.Contains((IPredicate)element.Contents))
                    {
                        // skip, as it's one to remove
                        continue;
                    }
                    // keep this element
                    elementToAdd = (IPredicate)element.Contents;
                }
                else
                {
                    var negateBeforeCleanup = elementAsExpression.Negate;
                    elementAsExpression = RemovePredicatesFromFilter(predicatesToRemove, elementAsExpression);
                    if(elementAsExpression.IsEmpty())
                    {
                        continue;
                    }
                    elementToAdd = elementAsExpression;
                    // negate could have been set on the expression, so we've to set it on the new expression instance we got back.
                    elementToAdd.Negate = negateBeforeCleanup;
                }
                if(lastSeenOperator == PredicateExpressionOperator.And)
                {
                    toReturn.AddWithAnd(elementToAdd);
                }
                break;
        }
    }

    return toReturn;
}

If you spot it, it's so silly.... hint: PredicateExpressionOperator is an enum which also can be... 'Or' wink

Frans Bouma | Lead developer LLBLGen Pro
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 19-Sep-2014 11:55:39   

Fixed in attached runtime. Final build with installer will be released later today.

Frans Bouma | Lead developer LLBLGen Pro
AlexanderM
User
Posts: 41
Joined: 18-May-2012
# Posted on: 19-Sep-2014 13:40:32   

Indeed I laughed, but with respect.

BTW the second part of the OR isn't alway true. That's because DateIn and DateOut are totally different, start day of a contract and end date of a contract. So if an employee has a contract from 1-1-2014 to 31-12-2014 and actualWeek is this monday 15-9-2014, the second part of the OR is true. These employees didn't have a contract while being active employees and crashed my code. And that started my search for my errors, I admit I also make bugs stuck_out_tongue_winking_eye , or data problems. Thanks for the fix, I have tested it and it works.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 19-Sep-2014 13:57:57   

Yeah, my bad, the second predicate isn't like I thought it was simple_smile Glad it's solved.

Frans Bouma | Lead developer LLBLGen Pro