Division by zero ошибка sql

Время прочтения: 4 мин.

В процессе работы c данными в SQL Server, мы столкнулись с такой ситуацией. Одним из промежуточных шагов в нашей задаче было выполнение простого арифметического действия, из данных в виде целых чисел, загружаемых в таблицу SQL, значения которых   участвовали в делении. В какой-то момент времени выполнение всего кода могло быть прервано сообщением об ошибке из-за того, что знаменатель принимал значение 0. Как этого избежать подобной ситуации, я расскажу в этой статье.

Обычно при делении не возникает проблем, и мы получаем соотношение двух величин, если знаменатель не равен нулю:

declare @vol_1 int;
declare @vol_2 int;
set @vol_1 = 10;
set @vol_2 = 5;
select @vol_1/@vol_2 ratio_vol;

Соотношение значений вычисляется корректно:

При значении @vol_2 = 0 получаем сообщение об ошибке:

declare @vol_1 int;
declare @vol_2 int;
set @vol_1 = 10;
set @vol_2 = 0;
select @vol_1/@vol_2 ratio_vol;

Для того, чтобы ошибка не возникала при выполнении запроса предлагается предусмотреть механизм, позволяющий справляться с условием, когда значение @vol_2 станет равным нулю

1. Применение функции NULLIF.

Синтаксис NULLIF следующий:

NULLIF(expr1, expr2)

При равенстве значений двух аргументов, возвращается значение NULL.

Например:

select NULLIF (55, 55) result;

Результат запроса:

Если значения аргументов не равны, возвращается значение первого аргумента (expr1).

select NULLIF (12, 55) result;

Изменим этот запрос, добавив в него NULLIF, для обхода ошибки деления на ноль.

Логика использования функции NULLIF для задачи деления на ноль следующая:

• используем в знаменателе функцию NULLIF с нулевым значением ее второго аргумента
• если значение первого аргумента функции NULLIF также равно нулю, то возвращается значение NULL, и тогда в SQL Server, если мы разделим число на значение NULL, на выходе получим NULL.
• если значение первого аргумента не равно нулю, возвращается значение первого аргумента функции NULLIF, и деление выполняется как стандартная операция деления.

declare @vol_1 int;
declare @vol_2 int;
set @vol_1 = 10;
set @vol_2 = 0;
select @vol_1/NULLIF (@vol_2, 0) ratio_vol;

Ниже, результат работы такого кода (в знаменателе – значение NULL):

Добавим в код функцию ISNULL, для того, чтобы вместо значения NULL в выводе результата вычисления получать 0.

Эта функция заменяет NULL значение в expr1 и возвращает значение expr2 в качестве вывода.

Логика запроса с функциями ISNULL и NULLIF такая:

• первый аргумент ((@vol_1/ NULLIF (@vol_2,0)) вернет значение NULL;
• для функции ISNULL указываем нулевое значение второго аргумента;
• так как первый аргумент — NULL, то вывод всего запроса равен нулю, т.е. значению второго аргумента.

Пример кода с функциями ISNULL и NULLIF:

declare @vol_1 int;
declare @vol_2 int;
set @vol_1 = 10;
set @vol_2 = 0;
select ISNULL (@vol_1/NULLIF (@vol_2, 0),0) ratio_vol;

Вывод результата успешного выполнения запроса:

2. Использование оператора CASE.

Посмотрим, как использовать для нашей задачи оператор CASE для возврата значений на основе определенных условий.

Оператор CASE проверит значение параметра @vol_2:

• если значение @vol_2 равно нулю, возвращается значение NULL;
• если это условие не выполняется, то производится арифметическая операция деления (@vol_1/@vol_2) и возвращается ее результат.

declare @vol_1 int;
declare @vol_2 int;
set @vol_1 = 10;
set @vol_2 = 0;
select CASE
	    when @vol_2 = 0
	    then NULL
	    else @vol_1/@vol_2
       end as ratio_vol;

Успешный результат запроса:

Применяя рассмотренные методы, позволяющие избежать ошибки деления на ноль в SQL Server, можно быть уверенным в том, что этот специфический, с точки зрения арифметики, код, являющийся частью большей задачи, не даст сбой в результате ее решения.

EDIT:
I’m getting a lot of downvotes on this recently…so I thought I’d just add a note that this answer was written before the question underwent it’s most recent edit, where returning null was highlighted as an option…which seems very acceptable. Some of my answer was addressed to concerns like that of Edwardo, in the comments, who seemed to be advocating returning a 0. This is the case I was railing against.

ANSWER:
I think there’s an underlying issue here, which is that division by 0 is not legal. It’s an indication that something is fundementally wrong. If you’re dividing by zero, you’re trying to do something that doesn’t make sense mathematically, so no numeric answer you can get will be valid. (Use of null in this case is reasonable, as it is not a value that will be used in later mathematical calculations).

So Edwardo asks in the comments «what if the user puts in a 0?», and he advocates that it should be okay to get a 0 in return. If the user puts zero in the amount, and you want 0 returned when they do that, then you should put in code at the business rules level to catch that value and return 0…not have some special case where division by 0 = 0.

That’s a subtle difference, but it’s important…because the next time someone calls your function and expects it to do the right thing, and it does something funky that isn’t mathematically correct, but just handles the particular edge case it’s got a good chance of biting someone later. You’re not really dividing by 0…you’re just returning an bad answer to a bad question.

Imagine I’m coding something, and I screw it up. I should be reading in a radiation measurement scaling value, but in a strange edge case I didn’t anticipate, I read in 0. I then drop my value into your function…you return me a 0! Hurray, no radiation! Except it’s really there and it’s just that I was passing in a bad value…but I have no idea. I want division to throw the error because it’s the flag that something is wrong.

EDIT:
I’m getting a lot of downvotes on this recently…so I thought I’d just add a note that this answer was written before the question underwent it’s most recent edit, where returning null was highlighted as an option…which seems very acceptable. Some of my answer was addressed to concerns like that of Edwardo, in the comments, who seemed to be advocating returning a 0. This is the case I was railing against.

ANSWER:
I think there’s an underlying issue here, which is that division by 0 is not legal. It’s an indication that something is fundementally wrong. If you’re dividing by zero, you’re trying to do something that doesn’t make sense mathematically, so no numeric answer you can get will be valid. (Use of null in this case is reasonable, as it is not a value that will be used in later mathematical calculations).

So Edwardo asks in the comments «what if the user puts in a 0?», and he advocates that it should be okay to get a 0 in return. If the user puts zero in the amount, and you want 0 returned when they do that, then you should put in code at the business rules level to catch that value and return 0…not have some special case where division by 0 = 0.

That’s a subtle difference, but it’s important…because the next time someone calls your function and expects it to do the right thing, and it does something funky that isn’t mathematically correct, but just handles the particular edge case it’s got a good chance of biting someone later. You’re not really dividing by 0…you’re just returning an bad answer to a bad question.

Imagine I’m coding something, and I screw it up. I should be reading in a radiation measurement scaling value, but in a strange edge case I didn’t anticipate, I read in 0. I then drop my value into your function…you return me a 0! Hurray, no radiation! Except it’s really there and it’s just that I was passing in a bad value…but I have no idea. I want division to throw the error because it’s the flag that something is wrong.

SQL Server 2017 Developer on Windows SQL Server 2017 Enterprise on Windows SQL Server 2017 Enterprise Core on Windows SQL Server 2017 Standard on Windows Еще…Меньше

Проблемы

Предположим, что у вас есть большое количество параллельных запросов, которые работают в работающей системе SQL Server 2017. При выполнении параллельного запроса вы можете заметить, что параллельный запрос принудительно запускается в последовательном режиме из-за нехватки параллельных рабочих потоков. В этом случае ошибка «выделять память – предупреждение» может вызвать ошибку деления на ноль.

Решение

Эта проблема устранена в следующем накопительном обновлении SQL Server:

       Накопительное обновление 1 для SQL Server 2017

Все новые накопительные обновления для SQL Server содержат все исправления и все исправления для системы безопасности, которые были включены в предыдущий накопительный пакет обновления. Ознакомьтесь с самыми последними накопительными обновлениями для SQL Server.

Последнее накопительное обновление для SQL Server 2017

Статус

Корпорация Майкрософт подтверждает наличие этой проблемы в своих продуктах, которые перечислены в разделе «Применяется к».

Ссылки

Ознакомьтесь с терминологией, которую корпорация Майкрософт использует для описания обновлений программного обеспечения.

EDIT:
I’m getting a lot of downvotes on this recently…so I thought I’d just add a note that this answer was written before the question underwent it’s most recent edit, where returning null was highlighted as an option…which seems very acceptable. Some of my answer was addressed to concerns like that of Edwardo, in the comments, who seemed to be advocating returning a 0. This is the case I was railing against.

ANSWER:
I think there’s an underlying issue here, which is that division by 0 is not legal. It’s an indication that something is fundementally wrong. If you’re dividing by zero, you’re trying to do something that doesn’t make sense mathematically, so no numeric answer you can get will be valid. (Use of null in this case is reasonable, as it is not a value that will be used in later mathematical calculations).

So Edwardo asks in the comments «what if the user puts in a 0?», and he advocates that it should be okay to get a 0 in return. If the user puts zero in the amount, and you want 0 returned when they do that, then you should put in code at the business rules level to catch that value and return 0…not have some special case where division by 0 = 0.

That’s a subtle difference, but it’s important…because the next time someone calls your function and expects it to do the right thing, and it does something funky that isn’t mathematically correct, but just handles the particular edge case it’s got a good chance of biting someone later. You’re not really dividing by 0…you’re just returning an bad answer to a bad question.

Imagine I’m coding something, and I screw it up. I should be reading in a radiation measurement scaling value, but in a strange edge case I didn’t anticipate, I read in 0. I then drop my value into your function…you return me a 0! Hurray, no radiation! Except it’s really there and it’s just that I was passing in a bad value…but I have no idea. I want division to throw the error because it’s the flag that something is wrong.

EDIT:
I’m getting a lot of downvotes on this recently…so I thought I’d just add a note that this answer was written before the question underwent it’s most recent edit, where returning null was highlighted as an option…which seems very acceptable. Some of my answer was addressed to concerns like that of Edwardo, in the comments, who seemed to be advocating returning a 0. This is the case I was railing against.

ANSWER:
I think there’s an underlying issue here, which is that division by 0 is not legal. It’s an indication that something is fundementally wrong. If you’re dividing by zero, you’re trying to do something that doesn’t make sense mathematically, so no numeric answer you can get will be valid. (Use of null in this case is reasonable, as it is not a value that will be used in later mathematical calculations).

So Edwardo asks in the comments «what if the user puts in a 0?», and he advocates that it should be okay to get a 0 in return. If the user puts zero in the amount, and you want 0 returned when they do that, then you should put in code at the business rules level to catch that value and return 0…not have some special case where division by 0 = 0.

That’s a subtle difference, but it’s important…because the next time someone calls your function and expects it to do the right thing, and it does something funky that isn’t mathematically correct, but just handles the particular edge case it’s got a good chance of biting someone later. You’re not really dividing by 0…you’re just returning an bad answer to a bad question.

Imagine I’m coding something, and I screw it up. I should be reading in a radiation measurement scaling value, but in a strange edge case I didn’t anticipate, I read in 0. I then drop my value into your function…you return me a 0! Hurray, no radiation! Except it’s really there and it’s just that I was passing in a bad value…but I have no idea. I want division to throw the error because it’s the flag that something is wrong.

Here are five options for dealing with error Msg 8134 “Divide by zero error encountered” in SQL Server.

First, here’s an example of code that produces the error we’re talking about:

SELECT 1 / 0;

Result:

Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.

We get the error because we’re trying to divide a number by zero. Mathematically, this does not make any sense. You can’t divide a number by zero and expect a meaningful result.

To deal with this error, we need to decide what should be returned when we try to divide by zero. For example, we might want a null value to be returned. Or we might want zero to be returned. Or some other value.

Below are some options for dealing with this error.

Option 1: The NULLIF() Expression

A quick and easy way to deal with this error is to use the NULLIF() expression:

SELECT 1 / NULLIF( 0, 0 );

Result:

NULL

NULLIF() returns NULL if the two specified expressions are the same value. It returns the first expression if the two expressions are different. Therefore, if we use zero as the second expression, we will get a null value whenever the first expression is zero. Dividing a number by NULL results in NULL.

Actually, SQL Server already returns NULL on a divide-by-zero error, but in most cases we don’t see this, due to our ARITHABORT and ANSI_WARNINGS settings (more on this later).

Option 2: Add the ISNULL() Function

In some cases, you might prefer to return a value other than NULL.

In such cases, you can pass the previous example to the ISNULL() function:

SELECT ISNULL(1 / NULLIF( 0, 0 ), 0);

Result:

0

Here I specified that zero should be returned whenever the result is NULL.

Be careful though. In some cases, returning zero might be inappropriate. For example, if you’re dealing with inventory supplies, specifying zero might imply that there are zero products, which might not be the case.

Option 3: Use a CASE Statement

Another way to do it is to use a CASE statement:

DECLARE @n1 INT = 20;
DECLARE @n2 INT = 0;

SELECT CASE
    WHEN @n2 = 0
    THEN NULL
    ELSE @n1 / @n2
END

Result:

NULL

Option 4: The SET ARITHABORT Statement

The SET ARITHABORT statement ends a query when an overflow or divide-by-zero error occurs during query execution. We can use it in conjunction with SET ANSI WARNINGS to return NULL whenever the divide-by-zero error might occur:

SET ARITHABORT OFF;
SET ANSI_WARNINGS OFF;
SELECT 20 / 0;

Result:

NULL

Microsoft recommends that you always set ARITHABORT to ON in your logon sessions, and that setting it to OFF can negatively impact query optimisation, leading to performance issues.

Some clients (such as SQL Server Management Studio) set ARITHABORT to ON by default. This is why you probably don’t see the NULL value being returned when you divide by zero. You can use SET ARITHIGNORE to change this behaviour if you prefer.

Option 5: The SET ARITHIGNORE Statement

The SET ARITHIGNORE statement controls whether error messages are returned from overflow or divide-by-zero errors during a query:

SET ARITHABORT OFF;
SET ANSI_WARNINGS OFF;

SET ARITHIGNORE ON;
SELECT 1 / 0 AS Result_1;

SET ARITHIGNORE OFF;
SELECT 1 / 0 AS Result_2;

Result:

Commands completed successfully.
Commands completed successfully.
Commands completed successfully.
+------------+
| Result_1   |
|------------|
| NULL       |
+------------+
(1 row affected)
Commands completed successfully.
+------------+
| Result_2   |
|------------|
| NULL       |
+------------+
Division by zero occurred.

Here, I set ARITHABORT and ANSI_WARNINGS to OFF so that the statement wasn’t aborted due to the error, and NULL is returned whenever there’s a divide-by-zero error.

Note that the SET ARITHIGNORE setting only controls whether an error message is returned. SQL Server returns a NULL in a calculation involving an overflow or divide-by-zero error, regardless of this setting.

In the above example we can see that when ARITHIGNORE is ON, the division by zero error is not returned. When it’s OFF, the division by zero error message is returned.

  

sooo_ez

23.06.16 — 17:01

  

MrStomak

1 — 23.06.16 — 17:05

Главное — ни в коем случае не пости сюда текст своего запроса, скрывай до последнего!

  

sooo_ez

2 — 23.06.16 — 17:13

  

MrStomak

3 — 23.06.16 — 17:15

В запросе нет деления, значит деление в выражении ресурсов СКД, либо в пользовательских полях, либо в вычисляемых полях, либо в выражении для параметра макета.

  

MrStomak

4 — 23.06.16 — 17:16

  

aleks_default

5 — 23.06.16 — 17:19

в процедуре ПриКомпоновкеРезультата в Макете после инициализации можно посмотреть запрос.

  

sooo_ez

6 — 23.06.16 — 17:20

(5) Сейчас гляну

  

sooo_ez

7 — 23.06.16 — 17:22

  

sooo_ez

8 — 23.06.16 — 17:24

А можно в консоли не переписывать все настройки а просто перенести уже имеющийся отчет?

  

MrStomak

9 — 23.06.16 — 17:43

  

hhhh

10 — 23.06.16 — 17:47

(4) может в СРЕДНЕЕ() делние?

  

sooo_ez

11 — 23.06.16 — 17:53

  

sooo_ez

12 — 23.06.16 — 18:15

  

sooo_ez

13 — 23.06.16 — 18:22

  

sooo_ez

14 — 23.06.16 — 18:30

не, фигня какаято

  

MrStomak

15 — 23.06.16 — 18:33

Процедура ПриКомпоновкеРезультата(ДокументРезультат, ДанныеРасшифровки, СтандартнаяОбработка)

  

mehfk

16 — 23.06.16 — 18:41

  

sooo_ez

17 — 23.06.16 — 18:42

  

MrStomak

18 — 23.06.16 — 18:48

(17) Отладка на сервере выключена наверное, используй консоль тогда (либо запускай отчет не в УФ, а в ОФ)

  

sooo_ez

19 — 23.06.16 — 19:01

  

sooo_ez

20 — 23.06.16 — 19:19

  

MrStomak

21 — 23.06.16 — 19:33

  

sooo_ez

22 — 28.06.16 — 09:13

Не, не решилось, пришлось работать в другой базе

РЕДАКТИРОВАТЬ: Я получаю много отрицательных голосов по этому поводу в последнее время … поэтому я подумал, что просто добавлю примечание, что этот ответ был написан до того, как вопрос подвергся самому последнему редактированию, где возвращение null было выделено как вариант .. ., что кажется очень приемлемым. Часть моего ответа была адресована таким опасениям, как Эдвардо в комментариях, который, казалось, выступал за возврат 0. Это тот случай, против которого я выступал.

ОТВЕТ: Я думаю, здесь есть основная проблема: деление на 0 незаконно. Это признак того, что что-то не так. Если вы делите на ноль, вы пытаетесь сделать что-то, что не имеет математического смысла, поэтому никакой числовой ответ, который вы можете получить, не будет действительным. (Использование null в этом случае является разумным, поскольку это значение не будет использоваться в более поздних математических вычислениях).

Итак, Эдвардо спрашивает в комментариях: «А что, если пользователь поставит 0?», И выступает за то, чтобы получить 0 взамен было нормально. Если пользователь ставит ноль в сумму, и вы хотите, чтобы 0 возвращался, когда они это делают, тогда вам следует ввести код на уровне бизнес-правил, чтобы поймать это значение и вернуть 0 … нет какого-то особого случая, когда деление на 0 = 0.

Это тонкая разница, но она важна … потому что в следующий раз, когда кто-то вызовет вашу функцию и ожидает, что она будет делать правильные вещи, и она сделает что-то необычное, что не является математически правильным, а просто обработает конкретный крайний случай, он получит хороший шанс укусить кого-нибудь позже. На самом деле вы не делите на 0 … вы просто возвращаете плохой ответ на плохой вопрос.

Представьте, что я что-то кодирую, и я облажался. Я должен был прочитать масштабное значение измерения радиации, но в странном крайнем случае, которого я не ожидал, я прочитал 0. Затем я опускаю свое значение в вашу функцию … вы возвращаете мне 0! Ура, без радиации! За исключением того, что это действительно так, и я просто передавал плохую ценность … но я понятия не имею. Я хочу, чтобы деление выдавало ошибку, потому что это признак того, что что-то не так.