0

I am building a data mart using the data pipeline in DBT. All intermediate stages are saved as a view in SQL Server.

There was a strange problem with division by zero at one of the stages ("Divide by zero error encountered.").

Short description:

Stage 1

At Stage 1, parameter_1 is formed through GROUP BY, dividing the sum of one value by parameter_0.

Parameter_0 takes integer values from 1 to 90. Additionally, I write "WHERE parameter_0 > 0"

Code from DBT:

  select
        ...
        , sales.quantity / available_on_stock.available_days as quantity_avg
    ...
    where
        available_on_stock.available_days > 0

Stage 2

Similar story. Parameter_2 is formed through GROUP BY, dividing the sum of another value by parameter_1.

Parameter_1 can be equal to 0, but I write "WHERE parameter_1 > 0".

Code from DBT:

select
    ...
    , stock_fact.quantity / sales_avg.quantity_avg as quantity_grade
    ...
where
    sales_avg.quantity_avg > 0

Stage 3

At the stage, there is an easy conversion through CASE parameter_2. Prerequisite, set condition for parameter_2: "WHERE parameter_2 > 0"

Code from DBT:

select
    ...
    , case
        when quantity_grade between 0 and 30    then '2. 1-30'
        when quantity_grade between 30 and 45   then '3. 31-45'
        when quantity_grade between 45 and 60   then '4. 46-60'
        when quantity_grade between 60 and 90   then '5. 61-90'
        when quantity_grade between 90 and 120  then '6. 91-120'
        when quantity_grade between 120 and 180 then '7. 121-180'
        when quantity_grade > 180               then '8. 181+'
    end quantity_grade
    ...
where quantity_grade >= 0

View in stages 1 and 2 are considered without errors in both DBT and SQL Server.

There are problems when launching the view in step 3. If there are conditions WHERE parameter_2 > 0, then the process crashes with the error

Divide by zero error encountered

If I change the WHERE clause from 0 to any other number (1, 10, 50, 100, etc.), the error persists.

As soon as I remove the WHERE clause, the error disappears and the view is considered successful.

Inside the CASE, there are comparison conditions for parameter_2. This condition does not affect operation: it succeeds without WHERE and fails with an error when WHERE appears.

Brief summary of the essence of the error:

a system of views is formed in which there is a division, but it is checked that all denominators are not zero.

In the final view, when using WHERE with the parameter calculated earlier, the error "Divide by zero error encountered." occurs. When removing WHERE, the error disappears.

Has anyone come across this weird working of SQL Server 2017? Does anyone have any idea how this can be solved?

  • Tried to set division parameters via CAST (decimal, float).
  • Tried changing conditions in WHERE clauses.
  • Tried to transfer WHERE to similar conditions after "JOIN... ON...".

Nothing changed the solution to the problem. When there is a "WHERE Parameter_2 > ..." in the last step, the launched view throws the error

Divide by zero error encountered

If the same condition is transferred to CASE, then the view is considered successful.

If you remove WHERE, then the view is also considered successful.

But it is necessary to save WHERE.

Dmitrii Malygin
  • 144
  • 2
  • 2
  • 12
  • 1
    Rather than trying to describe the problem, why not show us the code you have and provide a [mre]? Though, the "simple" method to fix a divide by zero error is to `NULL` the value of the divisor if it's `0`; `NULLIF` is one method. – Thom A Mar 23 '23 at 10:06
  • 4
    Just because you are filtering on the value `> 0` doesn't mean that the query engine, for some reason, won't process the division in the `SELECT` first. As such `stock_fact.quantity / sales_avg.quantity_avg` *could* be derived ***before*** the data is filtered to `available_on_stock.available_days > 0`. If `available_days` can have a value of `0` and it's a division then, as mentioned, `NULL` it to avoid the error. – Thom A Mar 23 '23 at 10:29
  • If it's more desirable you can use a `CASE` to avoid this: `CASE WHEN quantity_avg > 0 THEN quantity / quantity_avg ELSE 0 END AS quantity_grade` – Patrick Hurst Mar 23 '23 at 15:12
  • This could help: https://stackoverflow.com/questions/861778/how-to-avoid-the-divide-by-zero-error-in-sql – Aleix CC Mar 24 '23 at 07:42
  • Don't describe the problem. Post enough code so people can reproduce it. Post the *actual, full query*, table schema and test data that reproduces the problem. It's quite possible that some *other* division is causing the error. Perhaps a division with a `SUM()`, `COUNT()` or other aggregate that returns 0. Perhaps the query uses a *subquery* in `SELECT`, which isn't affected by the outer `WHERE`. Perhaps you have correlated subqueries that get evaluated before the outer one. – Panagiotis Kanavos May 17 '23 at 12:31
  • `All intermediate stages are saved as a view in SQL Server.` views are just query definitions. Nothing is saved, there are no stages. If you have a query calling 5 nested views you're actually executing one query with the view definitions expanded – Panagiotis Kanavos May 17 '23 at 12:33
  • Does `WHERE parameter_0 > 0"` mean an "optional" query parameter in a catch-all-query? Such queries are almost always a bad idea. The database engine caches and *reuses* execution plans based on the query text. The actual execution plan is generated after eliminating dead code, like an excluded "optional" condition. This may result in query plans that aren't as efficient when that value is included, or force the evaluation of far more rows than needed due to table scans – Panagiotis Kanavos May 17 '23 at 12:38

0 Answers0