Incorrect translation of query to SQL with CASE WHEN ... END

[giuliohome]

Description

I'm trying to fix an issue in my project and I need to avoid duplicate records with a more advanced join condition.

 query {
     for c in context.OilPhysical.EndurCost do
     join n in context.OilPhysical.EndurNominationValid
         on ( (c.CargoId, c.DeliveryId, c.DealNumber) = 
             (n.CargoId, n.DeliveryId, if (n.DeliveryDealNumber>0) then n.DeliveryDealNumber else n.ReceiptDealNumber) )
     where (c.BookingCompany = book &&
         c.FeeStatus <> Cost.ClosedFeeStatus &&
         c.FeeType <> delete_type &&
             ( c.CounterpartyId = i_str ||
               c.FeeId = i || c.CargoId = i )
         )
     take 90
     select c
 }

Repro steps

I've activated the sql logging (and I'm testing my library via f# interactive) against the effective db where the issue has been originated.

#if INTERACTIVE
FSharp.Data.Sql.Common.QueryEvents.SqlQueryEvent 
|> Event.add (fun e -> 
    printfn  "Executing SQL: %s" (e.ToRawSql()))
#endif

The problem is that there are 3 errors in the generated SQL

SELECT TOP 90 [c].[CalculationVolume] as '[c].[CalculationVolume]',[c].[CargoID] as '[c].[CargoID]',[c].[CounterpartyID] as '[c].[CounterpartyID]',
[c].[CounterpartyLongName] as '[c].[CounterpartyLongName]',[c].[CounterpartyShortName] as '[c].[CounterpartyShortName]',[c].[Currency] as '[c].[Currency]',
[c].[DealNumber] as '[c].[DealNumber]',[c].[DeliveryID] as '[c].[DeliveryID]',[c].[Fee] as '[c].[Fee]',[c].[FeeID] as '[c].[FeeID]',[c].[FeePV] as '[c].[FeePV]',
[c].[FeeStatus] as '[c].[FeeStatus]',[c].[FeeType] as '[c].[FeeType]',[c].[LastUpdate] as '[c].[LastUpdate]',[c].[OneTimePaymentDate] as '[c].[OneTimePaymentDate]',
[c].[OperationType] as '[c].[OperationType]',[c].[ParcelID] as '[c].[ParcelID]',[c].[PayRec] as '[c].[PayRec]',[c].[PriceUnit] as '[c].[PriceUnit]',
[c].[TransactionNumber] as '[c].[TransactionNumber]',[c].[UserID] as '[c].[UserID]',[c].[VolumeCalculationType] as '[c].[VolumeCalculationType]',
[c].[booking_company] as '[c].[booking_company]' 
FROM [OilPhysical].[EndurCost] as [c] 
INNER JOIN  [OilPhysical].[EndurNominationValid] as [n] on [c].[CargoID] = [n].[CargoID] AND [c].[DeliveryID] = [n].[DeliveryID] 
AND [c].[DealNumber] = CASE WHEN ([c].[DeliveryDealNumber] > 0) THEN [n].[DeliveryDealNumber] ELSE [c].[ReceiptDealNumber] ENDWHERE ((([c].[FeeType] <> 'Deleted' AND ([c].[booking_company] = 'US_ETS INC - LE' AND [c].[FeeStatus] <> '2-CLOSED')) 
AND ([c].[CargoID] = 20052 OR ([c].[CounterpartyID] = '20052' OR [c].[FeeID] = 20052))))

1. the key END is attached to the key WHERE

2. ([c].[DeliveryDealNumber] > 0) should be ([n].[DeliveryDealNumber] > 0)

3. ELSE [c].[ReceiptDealNumber] sohuld be ELSE [n].[ReceiptDealNumber]

Expected behavior

Correct sql and no exception.

Actual behavior

There is an exception

Error System.AggregateException: One or more errors occurred. ---> System.Data.SqlClient.SqlException: Incorrect syntax near 'ENDWHERE'.
Incorrect syntax near 'c'.
   at System.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__180_0(Task`1 result)
   at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
   at System.Threading.Tasks.Task.Execute()
   --- End of inner exception stack trace ---
---> (Inner Exception #0) System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near 'ENDWHERE'.
Incorrect syntax near 'c'.
   at System.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__180_0(Task`1 result)
   at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
   at System.Threading.Tasks.Task.Execute()
ClientConnectionId:3915fd7a-2a64-4d97-860f-3d98587c5d3f
Error Number:102,State:1,Class:15<---

Known workarounds

At the moment I'm not aware of any known workarounds and this is urgent.

Related information

• Used database: SQL Server 2016
• Operating system Windows Server 2016
• .NET Framework 4.6

[giuliohome]

Found a workaround for the error 1 of 3 (i.e.: key END is attached to the key WHERE).

 query {
     for c in context.OilPhysical.EndurCost do
     join n in context.OilPhysical.EndurNominationValid
         on ( (c.DealNumber, c.CargoId, c.DeliveryId) = 
             ((if (n.DeliveryDealNumber>0) then 
                 n.DeliveryDealNumber 
                 else n.ReceiptDealNumber), 
               n.CargoId, n.DeliveryId) )
     where (c.BookingCompany = book &&
         c.FeeStatus <> Cost.ClosedFeeStatus &&
         c.FeeType <> delete_type &&
             ( c.CounterpartyId = i_str ||
               c.FeeId = i || c.CargoId = i )
         )
     take 90
     select c
 }

but errors 2 and 3 (i.e.: [c].[ReceiptDealNumber] sohuld be [n].[ReceiptDealNumber]) do persist

[giuliohome]

Found a working workaround also for erros 2 and 3! I've moved the difficult part of the join on condition into the where condition.

query {
    for c in context.OilPhysical.EndurCost do
    join n in context.OilPhysical.EndurNominationValid
        on ( (c.CargoId, c.DeliveryId) = 
            (n.CargoId, n.DeliveryId) )
    where (c.BookingCompany = book &&
        (n.DeliveryDealNumber = c.DealNumber || n.ReceiptDealNumber = c.DealNumber) &&
        c.FeeStatus <> Cost.ClosedFeeStatus &&
        c.FeeType <> delete_type &&
            ( c.CounterpartyId = i_str ||
              c.FeeId = i || c.CargoId = i )
        )
    take 90
    select c

Now the issue is normal priority, no longer super urgent ;-)

[Thorium]

Failing SQL wouldn't be so bad, but generating incorrect SQL is always quite urgent priority, even if it's a bit of an edge-case. The PR should fix it, I have to do bit more testing to not break backward compatibility though.

[Thorium]

If you have the environment in place, it would be nice if you can verify the initial query also works with the latest version (1.1.68).

[giuliohome]

Great! Your support is really appreciated, thank you very much, will test the latest version soon in my environment.

[giuliohome]

Awesome! Issue resolved! Thank so much again!