EF LINQ to SQL, dividing by zero error, generated query puts parameters in wrong order

Question

EDIT Steps to reproduce this error at bottom of post

My Data Structure for this issue:

    public class StockRequest
    {
        public int StartYear { get; set; }
        public StockInterval StockInterval { get; set; }
    }

    public class StockInterval
    {
        /// 
        ///  Can be 0 = non-recurring, 1 = annual, 2 = once every 2 years, 3 = once every 3 years
        /// 
        public int IntervalInYears { get; set; }
    }

If I want to get all stock requests for say 2021. The following data would meet that criteria:

var nonRecurringRequest = new StockRequest() { StartYear = 2021, StockInterval = new StockInterval() { IntervalInYears = 0 } };
var annualRequest = new StockRequest() { StartYear = 2020, StockInterval = new StockInterval() { IntervalInYears = 1 } };
var everyTwoYearsRequest = new StockRequest() { StartYear = 2019, StockInterval = new StockInterval() { IntervalInYears = 2 } };
var everyThreeYearsRequest = new StockRequest() { StartYear = 2018, StockInterval = new StockInterval() { IntervalInYears = 3 } };

The key where clause in the EF query is:

query.Where(x => 
   x.StartYear <= selectedYear && 
  (
    x.StartYear == selectedYear || 
    (x.StockInterval.IntervalInYears != 0 && selectedYear - x.StartYear % x.StockInterval.IntervalInYears == 0) 
  )
);

The part causing issues is a non-recurring stock request (interval of 0). You can't mod that because then you divide by zero. However, I'm aware of this and in the past have resolved this by first checking if the property (IntervalInYears) is not zero before trying to mod. Since the first part of the WHERE fails the check, it does not continue to the mod part.

For some reason that isn't working this time. And when I check the generated query, it's putting the 0 first:

WHERE 
StockRequests.[StartYear] <= @stockYear
AND 
(
    StockRequests.[StartYear] = @stockYear OR 
    (
        0 <> StockIntervals.[IntervalInYears] AND 
        0 = (@stockYear - StockRequests.[StartYear]) % StockIntervals.[IntervalInYears] 
    )
)

Executing that in SQL Server generates divide by zero error. However, flipping the sides of 0 and StockIntervals.IntervalInYears:

WHERE 
StockRequests.[StartYear] <= @stockYear
AND 
(
    StockRequests.[StartYear] = @stockYear OR 
    (
        StockIntervals.[IntervalInYears] <> 0  AND 
        0 = (@stockYear - StockRequests.[StartYear]) % StockIntervals.[IntervalInYears] 
    )
)

Now it works no problem. Why is EF switching this around and how can I fix it within EF? I didn't put the 0 first in the EF query and I don't recall this happening before, this was the solution I always used to make sure I wasn't trying to divide by zero and it used to work. I'm aware I could manually write the SQL query and execute that, but the projection is over 200 lines.

EDIT To reproduce: Table creation Scripts:

CREATE TABLE [dbo].[StockIntervals](
[Id] [uniqueidentifier] NOT NULL,
[Name] [nvarchar](255) NOT NULL,
[IntervalInYears] [int] NOT NULL,
 CONSTRAINT [PK_dbo.StockIntervals] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[StockIntervals] ADD  DEFAULT ((0)) FOR [IntervalInYears]
GO

CREATE TABLE [dbo].[StockRequests](
[Id] [uniqueidentifier] NOT NULL,
[Count] [int] NOT NULL,
[DateRequested] [datetime] NOT NULL,
[StartYear] [int] NOT NULL,
[StockIntervalId] [uniqueidentifier] NOT NULL,
[EndYear] [int] NULL,


CONSTRAINT [PK_dbo.StockRequests] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[StockRequests]  WITH CHECK ADD  CONSTRAINT [FK_dbo.StockRequests_dbo.StockIntervals_StockIntervalId] FOREIGN KEY([StockIntervalId])
REFERENCES [dbo].[StockIntervals] ([Id])
GO

ALTER TABLE [dbo].[StockRequests] CHECK CONSTRAINT [FK_dbo.StockRequests_dbo.StockIntervals_StockIntervalId]
GO

Populate Tables:

INSERT INTO [dbo].[StockIntervals]
       ([Id]
       ,[Name]
       ,[IntervalInYears])
 VALUES
       ('738A431E-D517-4C17-9ECA-A1A0942E236B', 'Non-recurring one time', 0),
       ('CCB746A7-F644-4C7E-ADBE-AE14DE01B19E', 'Annual', 1),
       ('80C6CAE6-5287-41E6-A5FE-AAA53035EC19', 'Every 2 years', 2),
       ('B34EE256-C40B-4F03-8232-14B681186C7A', 'Every 3 years', 3)

GO

INSERT INTO [dbo].[StockRequests]
       ([Id]
       ,[Count]
       ,[DateRequested]
       ,[StartYear]
       ,[StockIntervalId]
       ,[EndYear])
 VALUES
       ('4a5ae94e-0a85-4195-8e7e-8cc556307b30'
       ,15
       ,'2022-01-11 15:16:41.567'
       ,2021
       ,'738A431E-D517-4C17-9ECA-A1A0942E236B'
       ,null),
       ('f0d83b68-0da1-4824-9eeb-2e52ff369db5'
       ,60
       ,'2022-01-11 15:16:41.567'
       ,2020
       ,'CCB746A7-F644-4C7E-ADBE-AE14DE01B19E'
       ,null),
       ('a49b4b9e-80d6-4fca-ad78-6c8996616c97'
       ,1000
       ,'2022-01-11 15:16:41.567'
       ,2019
       ,'80C6CAE6-5287-41E6-A5FE-AAA53035EC19'
       ,null),
       ('cc21a265-f8df-4d2d-9eae-5f6f97ef9909'
       ,50
       ,'2022-01-11 15:16:41.567'
       ,2018
       ,'B34EE256-C40B-4F03-8232-14B681186C7A'
       ,null)
GO

Run this query:

DECLARE @stockYear int = 2021

SELECT * FROM 
dbo.StockRequests
INNER JOIN dbo.StockIntervals on StockIntervalId = StockIntervals.Id
WHERE 
    StockRequests.[StartYear] <= @stockYear
    AND 
    (
        StockRequests.[StartYear] = @stockYear OR 
        (
            0 <> StockIntervals.[IntervalInYears]  AND 
            0 = (@stockYear - StockRequests.[StartYear]) % StockIntervals.[IntervalInYears] 
        )
    )

No error. Okay now try inserting a new record:

INSERT INTO [dbo].[StockRequests]
VALUES ('FFA820F1-E361-4AC5-AB00-E621BFFEF9B5', 20, '2022-01-11 16:22:11.567', 2020, '738A431E-D517-4C17-9ECA-A1A0942E236B', null)

Run the query again. Divide by zero error happens. After playing with the data, this behavior makes sense. If the @stockYear is greater than or less than the StartYear and the interval of that record is zero, it will error out because if gets to the inner most part of the query, and the interval is zero and it doesn't have boolean expression shortcutting. Okay.

But switch the one line of the query to:

StockIntervals.[IntervalInYears] <> 0

Now it works! Not sure how this is coincidence though, I've run my scripts through many scenarios to trigger the error, but it always is resolved by the above. If there is no short cutting, switching the operands should still cause the error. Yet it does not, consistently. So people are saying the operand order doesn't matter, but I am able to show it appears to.

user_14868997 · Answer

You appear to be labouring under the assumption that AND and OR in T-SQL will always short-circuit in the order specified in the query. This is absolutely not the case.

It is true that it will normally short-circuit a logical expression. After all, why do more work than necessary? But it may not be in the order that was specified in the query. Logical operators are not specified to execute in any particular order, and the optimizer often chooses to switch them around based on things like estimates of short-circuiting likelihood or the amount of work involved in evaluation, as long as the operator precedence rules are followed (AND before OR etc).

Because evaluating the space of all possible execution plans is too vast, the optimizer uses aggressive pruning to remove options based on heuristics. These two predicates:

(
    StockRequests.[StartYear] = @stockYear OR 
    (
        0 <> StockIntervals.[IntervalInYears]  AND 
        0 = (@stockYear - StockRequests.[StartYear]) % StockIntervals.[IntervalInYears] 
    )
)

and

(
    StockRequests.[StartYear] = @stockYear OR 
    (
        StockIntervals.[IntervalInYears] <> 0  AND 
        0 = (@stockYear - StockRequests.[StartYear]) % StockIntervals.[IntervalInYears] 
    )
)

are exactly the same as far as query intention is concerned. The question is what the optimizer will choose to do with them. In your case, it so happens that putting comparators one way around causes certain optimizations to fall in to place (or not) and therefore the AND can get flipped around.

As you can see from this fiddle, which is running on SQL Server 2019, both of your options short-circuited correctly, as did flipping around the AND. I had to flip the OR to get it to fail, and then the order of the AND did not matter. Note that the logic was not changed in any of the queries, and that the order of the AND or = comparators themselves do not force the hand of the optimizer, it just sometimes guides it down a certain path.

So it's very dependent on what the optimizer decides to do, and you cannot guarantee upfront that it will always do it correctly. Yes, you saw it do that a hundred times, but the hundred-and-first could change, perhaps because of statistics changes, or a update to SQL Server, or changing the cardinality estimator version, or the database compatibility level, or any of the many things that can cause a recompile.

The only guaranteed way to ensure short-circuiting in a particular order is to use CASE (or NULLIF which compiles into a CASE). This is documented by Microsoft, it will work as long as you do not use any aggregation functions.

In other words, do not expect something like CASE WHEN x > 0 THEN SUM(1 / x) END to work, because the SUM is often evaluated at an earlier stage. It only works with scalar values. As far as I am aware I would expect the same issue would apply to subqueries and window functions.

You can therefore work around your problem by using NULLIF

(
    StockRequests.[StartYear] = @stockYear OR 
    (
        StockIntervals.[IntervalInYears] <> 0  AND 
        0 = (@stockYear - StockRequests.[StartYear]) % NULLIF(StockIntervals.[IntervalInYears], 0)
    )
)

In Entity Framework you can use something like (value == 0 ? null : value)

query.Where(x => 
   x.StartYear <= selectedYear && 
  (
    x.StartYear == selectedYear || 
    (x.StockInterval.IntervalInYears != 0
     && selectedYear - x.StartYear %
        (x.StockInterval.IntervalInYears == 0 ? null : x.StockInterval.IntervalInYears)
        == 0) 
  )
);

EF LINQ to SQL, dividing by zero error, generated query puts parameters in wrong order

1 Answers