Обнаружена ошибка деление на ноль sql

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.

Время прочтения: 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, можно быть уверенным в том, что этот специфический, с точки зрения арифметики, код, являющийся частью большей задачи, не даст сбой в результате ее решения.

29.07.09 — 14:29

5 — 29.07.09 — 14:39

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

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

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

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

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

Будьте внимательны во время деления целых чисел

В PostgreSQL деление целого числа на целое число в результате дает целое число. Не делайте так:

db=# (
  SELECT tax / price AS tax_ratio
  FROM sale
);
 tax_ratio
----------
    0

Чтобы получить ожидаемый результат деления, вам нужно привести одно из значений к плавающему типу:

 db=# (
  SELECT tax / price::float AS tax_ratio
  FROM sale
);
 tax_ratio
----------
 0.17

Неспособность распознать эту ошибку может привести к крайне неточным результатам.

Защита от ошибок деления на ноль

Деление на ноль — известная ошибка:

db=# SELECT 1 / 0
ERROR: division by zero

Деление на ноль является логической ошибкой и ее нужно не просто «обойти», а исправить так, чтобы на первом месте у вас не было делителя, равного нулю. Однако бывают ситуации, когда возможен нулевой делитель. Один из простых способов защиты от ошибок деления на ноль — это присвоить всему выражению неопределенное значение, установив неопределенное значение делителю, если он равен нулю:

db=# SELECT 1 / NULLIF(0, 0);
 ?column?
----------
   -

Функция NULLIF возвращает неопределенное значение null, если первый аргумент равен второму. В этом случае, если знаменатель равен нулю.

При делении любого числа на NULL результатом будет NULL. Чтобы получить некоторое значение, вы можете свернуть все выражение с COALESCE и предоставить дефолтное значение:

db=# SELECT COALESCE(1 / NULLIF(0, 0), 1);
 ?column?
----------
    1

Функция COALESCE очень полезна. Она допускает любое количество аргументов и возвращает первое значение, которое не является неопределенным.

Знайте разницу между UNION и UNION ALL

Классический вопрос на собеседовании начального уровня для разработчиков и администраторов баз данных: «В чем разница между функциями UNION и UNION ALL?».

UNION ALL объединяет результаты одного или нескольких запросов. UNION делает то же самое, и к тому же исключает повторяющиеся строки. Не делайте так:

 SELECT created_by_id FROM sale
  UNION
  SELECT created_by_id FROM past_sale
);
QUERY PLAN
-----------
Unique  (cost=2654611.00..2723233.86 rows=13724572 width=4)
  ->  Sort  (cost=2654611.00..2688922.43 rows=13724572 width=4)
        Sort Key: sale.created_by_id
        ->  Append  (cost=0.00..652261.30 rows=13724572 width=4)
              ->  Seq Scan on sale  (cost=0.00..442374.57 rows=13570157 width=4)
              ->  Seq Scan on past_sale  (cost=0.00..4018.15 rows=154415 width=4)

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

Если вам не нужно удалять повторяющиеся строки, лучше использовать функцию UNION ALL:

 db=# (
  SELECT created_by_id FROM sale
  UNION ALL
  SELECT created_by_id FROM past_sale
);
QUERY PLAN
-----------
 Append  (cost=0.00..515015.58 rows=13724572 width=4)
   ->  Seq Scan on sale  (cost=0.00..442374.57 rows=13570157 width=4)
   ->  Seq Scan on past_sale  (cost=0.00..4018.15 rows=154415 width=4)

Выполняется намного проще. Результаты получены, сортировка не требуется.

Будьте внимательны при подсчете столбцов, допускающих неопределенное значение

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

db=# pset null NULL
Null display is "NULL".
db=# WITH tb AS (
  SELECT 1 AS id
  UNION ALL
  SELECT null AS id
)
SELECT *
FROM tb;
  id
------
    1
 NULL

Столбец id содержит значение null. Посчитаем столбец id:

 db=# WITH tb AS (
  SELECT 1 AS id
  UNION ALL
  SELECT null AS id
)
SELECT COUNT(id)
FROM tb;
 count
-------
     1

В таблице две строки, но функция COUNT возвращает 1. Это произошло, потому что функция COUNT игнорирует неопределенные значения.

Чтобы посчитать строки, используйте функцию COUNT(*):

 db=# WITH tb AS (
  SELECT 1 AS id
  UNION ALL
  SELECT null AS id
)
SELECT COUNT(*)
FROM tb;
 count
-------
  2

Эта особенность также может быть полезна. Например, если поле с именем modified содержит неопределенное значение в том случае, если строка не была изменена, вы можете рассчитать процент измененных строк следующим образом:

db=# (
  SELECT COUNT(modified) / COUNT(*)::float AS modified_pct
  FROM sale
);
 modified_pct
---------------
  0.98

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

db=# WITH tb AS (
  SELECT null AS id
  UNION ALL
  SELECT null AS id
)
SELECT SUM(id::int)
FROM tb;
 sum
-------
 NULL

Это все документированные операции, так что будьте внимательны!

Обратите внимание на часовые пояса

Часовые пояса всегда являются источником путаницы и ошибок. PostgreSQL отлично справляется с часовыми поясами, но вам все равно придется обратить внимание на некоторые вещи.

Распространенной ошибкой, которую я вижу бесчисленное количество раз, является сокращение временных меток без уточнения часового пояса. Скажем, мы хотим узнать, сколько продаж было сделано каждый день:

SELECT created_at::date, COUNT(*)
FROM sale
GROUP BY 1

Без точной настройки часового пояса вы можете получить разные результаты в зависимости от часового пояса, установленного приложением:

 now
------------
 2019-11-08
db=# SET TIME ZONE 'australia/perth';
SET
db=# SELECT now()::date;
now
------------
2019-11-09

Если вы не уверены, с каким часовым поясом работаете, вы можете делать это неправильно.

При обращении к временной метке сначала приведите ее к нужному часовому поясу:

SELECT (timestamp at time zone 'asia/tel_aviv')::date, COUNT(*)
FROM sale
GROUP BY 1;

За установку часового пояса обычно отвечает приложение. Например, чтобы получить часовой пояс, используемый psql:

db=# SHOW timezone;
TimeZone
----------
Israel
db=# SELECT now();
now
-------------------------------
2019-11-09 11:41:45.233529+02

А чтобы установить часовой пояс в PSQL:

 db=# SET timezone TO 'UTC';
SET
db=# SELECT now();
now
-------------------------------
2019-11-09 09:41:55.904474+00

Также важно помнить, что часовой пояс вашего сервера может отличаться от часового пояса вашего локального компьютера. Поэтому если вы выполняете запросы на локальном компьютере, они могут привести к разным результатам в готовом продукте. Чтобы избежать ошибок, всегда точно устанавливайте часовой пояс.

Избегайте преобразований в индексированных полях

Использование функций в индексированном поле может помешать базе данных использовать индекс в этом поле:

SELECT * FROM sale
  WHERE created at time ZONE 'asia/tel_aviv' > '2019-10-01'
);
QUERY PLAN
----------
Seq Scan on sale (cost=0.00..510225.35 rows=4523386 width=276)
Filter: timezone('asia/tel_aviv', created) > '2019-10-01 00:00:00'::timestamp without time zone

Поле created индексируется, но поскольку мы преобразовали его с помощью часового пояса, индекс не использовался.

Один из способов использования индекса в этом случае — применить преобразование в правой части:

SELECT * FROM sale WHERE created > '2019-10-01' AT TIME ZONE 'asia/tel_aviv' );
QUERY PLAN
----------
Index Scan using sale_created_ix on sale  (cost=0.43..4.51 rows=1 width=276)
Index Cond: (created > '2019-10-01 00:00:00'::timestamp with time zone)

Другим примером использования дат является фильтрация определенного периода:

 db=# (
 SELECT * FROM sale WHERE created + INTERVAL '1 day' > '2019-10-01'
);
QUERY PLAN
----------
 Seq Scan on sale  (cost=0.00..510225.35 rows=4523386 width=276)
   Filter: ((created + '1 day'::interval) > '2019-10-01 00:00:00+03'::timestamp with time zone)

Как и до этого, интервальная функция в поле created препятствовала базе данных использовать индекс. Чтобы база данных использовала индекс, примените преобразование в правой части выражения вместо всего поля:

 SELECT *
  FROM sale
  WHERE created > '2019-10-01'::date - INTERVAL '1 day'
);
QUERY PLAN
----------
 Index Scan using sale_created_ix on sale  (cost=0.43..4.51 rows=1 width=276)
   Index Cond: (created > '2019-10-01 00:00:00'::timestamp without time zone)

Заключение

Применение приведенных выше советов в повседневной жизни помогает нам поддерживать работоспособную базу данных с минимальными потерями. Мы обнаружили, что обучение разработчиков и не разработчиков тому, как лучше писать на SQL, может иметь большое значение. Если у вас есть какие-то советы по SQL, которые мы могли пропустить, дайте нам знать, и мы добавим их сюда!

В этой статье мы рассмотрим, как избежать ошибки «делить на ноль» в SQL. Если мы разделим любое число на ноль, это приведет к бесконечности, и мы получим сообщение об ошибке. Мы можем избежать этого сообщения об ошибке, используя следующие три метода:

• Использование функции NULLIF()
• Использование оператора CASE
• Использование ВЫКЛ. ARITHABORT

Сначала мы создадим базу данных для выполнения операций SQL.

Запрос:

CREATE DATABASE Test;

Выход:

Успешно выполненные команды показывают, что база данных «Тест» создана.

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

DECLARE @Num1 INT;
DECLARE @Num2 INT;

После объявления переменной мы должны установить значения. Установите значение второй переменной равным нулю.

SET @Num1=12;
SET @Num2=0;

Способ 1: использование функции NULLIF()

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

Синтаксис:

NULLIF(exp1, exp2);

Теперь мы используем функцию NULLIF() в знаменателе с нулевым значением второго аргумента.

SELECT @Num1/NULLIF(@Num2,0) AS Division;

• На сервере SQL, если мы разделим любое число на значение NULL, его вывод будет NULL.
• Если первый аргумент равен нулю, это означает, что если значение Num2 равно нулю, то функция NULLIF() возвращает значение NULL.
• Если первый аргумент не равен нулю, то функция NULLIF() возвращает значение этого аргумента. И деление происходит как обычно.

Вот полный запрос.

Запрос:

DECLARE @Num1 INT;
DECLARE @Num2 INT;
SET @Num1=12;
SET @Num2=0;
SELECT @Num1/NULLIF(@Num2,0) AS Division;

Выход:

Способ 2: использование оператора CASE

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

Мы должны проверить значение знаменателя, то есть значение переменной Num2. Если он равен нулю, верните NULL, в противном случае верните обычное деление.

SELECT CASE
WHEN @Num2=0
THEN NULL
ELSE @Num1/@Num2
END AS Division;

Вот полный запрос:

Запрос:

DECLARE @Num1 INT;
DECLARE @Num2 INT;
SET @Num1=12;
SET @Num2=0;
SELECT CASE
    WHEN @Num2=0
    THEN NULL
    ELSE @Num1/@Num2
END AS Division;

Выход:

Способ 3: ВЫКЛЮЧИТЕ ARITHABORT

Чтобы контролировать поведение запросов, мы можем использовать методы SET. По умолчанию ARITHABORT включен. Он завершает запрос и возвращает сообщение об ошибке. Если мы установим его в OFF, он завершится и вернет значение NULL.

Как и в случае с ARITHBORT, мы должны установить ANSI_WARNINGS в положение OFF, чтобы избежать появления сообщения об ошибке.

SET ARITHABORT OFF;
SET ANSI_WARNINGS OFF;

Вот полный запрос:

Запрос:

SET ARITHABORT OFF;
SET ANSI_WARNINGS OFF;
DECLARE @Num1 INT;
DECLARE @Num2 INT;
SET @Num1=12;
SET @Num2=0;
Select @num1/@Num2;

Выход: