Divide by zero error encountered when the condition is explicitly excluded

Question

I am creating some test data, that requires me to calculate a percentage.

In my predicate, I am excluding any records that would cause a divide by zero error, and when I run my SQL query on that data set, everything runs fine.

Total number of records generated (all combinations): 92,345,408

Total number of records excluding a divide by zero instance: 92,141,104

When I add the "qualify for Use Case 1," condition, the query still executes without errors. However, when I also add "Use Case 2" to my predicate, I encounter a divide by zero error. I do not understand how this is happening as I am excluding that condition:

WHERE CAST(m1.MoneyValue1 AS FLOAT) - CAST(m2.MoneyValue2 AS FLOAT) != 0

Below is my code to create 3 different dollar value columns (DECIMAL(18,2)), and then I am using a CROSS APPLY to get all possible combinations.

DECLARE @Money1 TABLE
    (
    ID INT IDENTITY (1,1) NOT NULL,
    MoneyValue1 DECIMAL (18,2) NOT NULL
    )

DECLARE @Money2 TABLE
    (
    ID INT IDENTITY (1,1) NOT NULL,
    MoneyValue2 DECIMAL (18,2) NOT NULL
    )

DECLARE @Money3 TABLE
    (
    ID INT IDENTITY (1,1) NOT NULL,
    MoneyValue3 DECIMAL (18,2) NOT NULL
    )


DECLARE @stop DECIMAL(18,2) = 2000.00   --  capping the maximum test value at $2000.00
DECLARE @interval FLOAT = 4.43  --  adding a random dollar amount to create variability and several test values

DECLARE @MoneyValue DECIMAL (18,2) = 0  --  for my test, I don't care about negative dollar amounts

WHILE @MoneyValue < @stop
    BEGIN
        INSERT INTO @Money1
            (
            MoneyValue1
            )
            SELECT CAST(@MoneyValue AS DECIMAL(18,2))

            SET @MoneyValue = CAST(@MoneyValue AS FLOAT) + CAST(@interval AS FLOAT)

    END

    INSERT INTO @Money2 --  use the same values generated by the statement above for my second Money column
        (
        MoneyValue2
        )
        SELECT
            CAST(MoneyValue1 AS DECIMAL(18,2))
        FROM @Money1

    INSERT INTO @Money3 --  use the same values generated by the statement above for my second Money column
        (
        MoneyValue3
        )
        SELECT
            CAST(MoneyValue1 AS DECIMAL(18,2))
        FROM @Money1

Next, I want to create 10 randomized samples of data; the Calc column is to show the value of Use Case 1 (see predicate in predicate example causing error below).

SELECT TOP 10
    m1.MoneyValue1 AS TotalPmt,
    m2.MoneyValue2 AS TotalPmtChange,
    m3.MoneyValue3 AS PmtChangeAmount
    ,CAST(m2.MoneyValue2 AS FLOAT) / (CAST(m1.MoneyValue1 - m2.MoneyValue2 AS FLOAT)) AS Calc
FROM @Money1 AS m1
CROSS APPLY @Money2 AS m2
CROSS APPLY @Money3 AS m3
WHERE CAST(m1.MoneyValue1 AS FLOAT) - CAST(m2.MoneyValue2 AS FLOAT) != 0    --  exclude the possibility of a divide by zero error
ORDER BY NEWID()

If I change the predicate to now also include only Use Case 1, again- the query executes without errors.

WHERE CAST(m1.MoneyValue1 AS FLOAT) - CAST(m2.MoneyValue2 AS FLOAT) != 0    --  exclude the possibility of a divide by zero error
    AND CAST(m2.MoneyValue2 AS FLOAT) / (CAST(m1.MoneyValue1 - m2.MoneyValue2 AS FLOAT)) > .1   --  qualify for Use Case 1
ORDER BY NEWID()

However, if I change the predicate to also include both Use Case 1 and Use Case 2 conditions, I will now get a divide by zero error!

WHERE CAST(m1.MoneyValue1 AS FLOAT) - CAST(m2.MoneyValue2 AS FLOAT) != 0    --  exclude the possibility of a divide by zero error
    AND CAST(m2.MoneyValue2 AS FLOAT) / (CAST(m1.MoneyValue1 - m2.MoneyValue2 AS FLOAT)) > .1   --  qualify for Use Case 1
    AND CAST(m3.MoneyValue3 AS FLOAT) / (CAST(m1.MoneyValue1 - m2.MoneyValue2 AS FLOAT)) > .1   --  qualify for Use Case 2

Messages from SSMS:

(452 row(s) affected)
Msg 8134, Level 16, State 1, Line 58
Divide by zero error encountered.

Answer 1

While I can't necessarily point to the mechanics of the failure, I can say that once I moved the data to a physical table, the divide by zero error stopped occurring.

One post that supports using a table variable as the cause: When should I use a table variable vs temporary table in sql server?

Perhaps it is the inability to create and run statistics on a table variable that causes the engine to collide with a divide by zero record. One other possibility is SQL Server's inability to see a table variable's cardinality correctly, i.e. estimating one record to output from a table variable.

One thing I found to be interesting from the link sited above, which holds a reference to this link:What's the difference between a temp table and table variable in SQL Server?

No column statistics

Having a more accurate table cardinality doesn't mean the estimated row count will be any more accurate however (unless doing an operation on all rows in the table). SQL Server does not maintain column statistics for table variables at all so will fall back on guesses based upon the comparison predicate (e.g. that 10% of the table will be returned for an = against a non unique column or 30% for a > comparison). In contrast column statistics are maintained for #temp tables.

Regardless of the reason, the solution I have found points back to the cause of my original issue (encountering a divide by zero error when my predicate explicitly excluded the possibility of a divide by zero) as a by-product of using a table variable containing several million records.