Разделение TSQL на ноль встречается, несмотря на отсутствие столбцов, содержащих 0

Я пытался понять, почему я получаю «разделение на ноль столкнувшись» (Msg 8134) с моим SQL-запросом, но я должен что-то упустить. Я хотел бы знать, почему для конкретного случая ниже, я не ищу NULLIF , CASE WHEN... или аналогичный, как я уже знаю о них (и, конечно, может использовать их в ситуации, как показано ниже).

У меня есть оператор SQL с вычисленным столбцом, похожим на

 SELECT TotalSize, FreeSpace, (FreeSpace / TotalSize * 100) FROM tblComputer ...[ couple of joins ]... WHERE SomeCondition = SomeValue 

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

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

Затем я подумал, что по какой-то причине вычисление столбцов будет выполняться во всем наборе результатов до фактической фильтрации с условиями предложения where, но это: а) не имеет смысла imho и b) при попытке воспроизвести ошибку с помощью тестовая настройка все работает нормально (см. ниже):

 INSERT INTO tblComputer (ComputerName, IsServer) VALUES ('PC0001',1) INSERT INTO tblComputer (ComputerName, IsServer) VALUES ('PC0002',1) INSERT INTO tblComputer (ComputerName, IsServer) VALUES ('PC0003',1) INSERT INTO tblComputer (ComputerName, IsServer) VALUES ('PC0004',0) INSERT INTO tblComputer (ComputerName, IsServer) VALUES ('PC0005',1) INSERT INTO tblComputer (ComputerName, IsServer) VALUES ('PC0006',0) INSERT INTO tblComputer (ComputerName, IsServer) VALUES ('PC0007',1) INSERT INTO tblHDD (ComputerID, TotalSize, FreeSpace) VALUES (1,100,21) INSERT INTO tblHDD (ComputerID, TotalSize, FreeSpace) VALUES (2,100,10) INSERT INTO tblHDD (ComputerID, TotalSize, FreeSpace) VALUES (3,100,55) INSERT INTO tblHDD (ComputerID, TotalSize, FreeSpace) VALUES (4,0,10) INSERT INTO tblHDD (ComputerID, TotalSize, FreeSpace) VALUES (5,100,23) INSERT INTO tblHDD (ComputerID, TotalSize, FreeSpace) VALUES (6,100,18) INSERT INTO tblHDD (ComputerID, TotalSize, FreeSpace) VALUES (7,100,11) -- This statement does not throw an error as apparently the row for ComputerID 4 -- is filtered out before computing the (FreeSpace / TotalSize * 100) SELECT TotalSize, FreeSpace, (FreeSpace / TotalSize * 100) FROM tblComputer JOIN tblHDD ON tblComputer.ID = tblHDD.ComputerID WHERE IsServer = 1 

Я совершенно тупой и хотел бы знать, в чем причина.

Любые идеи или указатели в правильном направлении приветствуются, спасибо заранее

Обновить

Благодарим вас за предоставленную информацию, но, к сожалению, я, похоже, не приближаюсь к корню проблемы. Мне удалось немного отменить утверждение, и теперь я могу выполнить его без ошибок, если один JOIN будет удален (мне понадобится он для дополнительных столбцов на выходе, который я временно удалил).

Я не понимаю, почему использование JOIN приводит к ошибке, не должен ли стандартный INNER JOIN всегда возвращать одинаковое количество строк или меньше , но не больше ?

Рабочий код

 SELECT TotalSize, FreeSpace ((FreeSpace / TotalSize) * 100) FROM MyTable1 INNER JOIN MyTable2 ON MyTable1.ID = MyTable2.Table1ID WHERE SomeCondition 

Ошибка при создании кода

 SELECT TotalSize, FreeSpace ((FreeSpace / TotalSize) * 100) FROM MyTable1 INNER JOIN MyTable2 ON MyTable1.ID = MyTable2.Table1ID -- This JOIN causes "divide by zero encountered" error INNER JOIN MyTable3 ON MyTable2.ID = MyTable3.Table2ID WHERE SomeCondition 

Я также попробовал свою удачу с помощью курсора и зацикливания на результат по строкам, но в этом случае ошибка не возникала (независимо от того, какое из двух вышеприведенных утверждений я попытался).

Извините за вмятый отступ кода, так как правильное форматирование, похоже, не применяется.

Г.

SQL – декларативный язык; вы пишете запрос, который логически описывает желаемый результат, но оптимизатор должен подготовить физический план. Этот физический план может не иметь большого отношения к письменной форме запроса, поскольку оптимизатор не просто переупорядочивает «шаги», полученные из текстовой формы запроса, он может применять более 300 различных преобразований, чтобы найти эффективную страtagsю исполнения.

Оптимизатор имеет значительную свободу в изменении порядка выражений, объединений и других конструкций логических запросов. Это означает, что вы не можете, в общем, полагаться на любую письменную форму запроса, чтобы заставить одну вещь оценивать другую. В частности, переписывание, данное Ливеном, не заставляет предикат предложения WHERE оцениваться перед выражением. Оптимизатор может, в зависимости от оценки стоимости, принять решение оценить выражение, где это кажется наиболее эффективным для этого. Это может даже означать, в некоторых случаях, что выражение оценивается более одного раза.

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

Общее правило состоит в том, чтобы никогда не зависеть от определенного порядка оценки, чтобы избежать таких ошибок, как переполнение или деление на нуль. В этом примере можно использовать оператор CASE для проверки нулевого делителя – пример защитного программирования.

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

Павел

Основные шаги, которые SQL Server использует для обработки одного оператора SELECT, include следующие

  1. Парсер сканирует оператор SELECT и разбивает его на логические единицы, такие как ключевые слова, выражения, операторы и идентификаторы.
  2. Создано дерево запросов, иногда называемое деревом последовательностей, описывающее логические шаги, необходимые для преобразования исходных данных в формат, необходимый для набора результатов.
  3. Оптимизатор запросов анализирует различные способы доступа к исходным таблицам. Затем он выбирает последовательность шагов, которая быстрее возвращает результаты при использовании меньшего количества ресурсов. Дерево запросов обновляется для записи этой последовательности шагов. Окончательная оптимизированная версия дерева запросов называется планом выполнения.
  4. Реляционный движок начинает выполнение плана выполнения. Когда обрабатываются шаги, требующие данных из базовых таблиц, реляционный движок запрашивает, чтобы механизм хранения данных обрабатывал данные из наборов строк, запрошенных из реляционного движка.
  5. Механизм реляционной обработки обрабатывает данные, возвращенные из механизма хранения, в формат, определенный для набора результатов, и возвращает набор результатов клиенту.

Моя интерпретация вещей заключается в том, что нет никакой гарантии, что ваше предложение where get оценивается перед оценкой вычисленного столбца для всех строк.

Вы можете проверить это предположение, изменив свой запрос, как показано ниже, и принудительно оценивать предложение where перед вычислением.

 SELECT TotalSize, FreeSpace, (FreeSpace / TotalSize * 100) FROM ( SELECT TotalSize, FreeSpace, FROM tblComputer ...[ couple of joins ]... WHERE SomeCondition = SomeValue ) t 

Какие строки возвращаются при запуске:

 SELECT TotalSize FROM tblComputer ...[ couple of joins ]... WHERE SomeCondition = SomeValue and ((TotalSize * 100) = 0) 

Это может дать вам представление о том, как оценка SQL Serve для оценки (TotalSize * 100) равна нулю.

Другая идея, есть ли что-нибудь в вашем заявлении, которое также может быть проблемой?
Вы предполагаете, что это TotalSize, но это может быть где-то еще.

Я столкнулся с тем же вопросом. В моем случае NULL были приемлемыми, поэтому я смог исправить это так:

 Select Expression1 / Expression2 -- Caused Division By 0 Select Expression1 / NULLIF(Expression2,0) -- Causes result to be NULL 

Если вам нужна другая обработка, вы можете обернуть все выражение в функции ISNULL следующим образом:

 Select ISNULL(Expression1 / NULLIF(Expression2,0)-5) -- Returns -5 instead of null or divide by 0 
Interesting Posts

Преобразование файла дампа SQLITE SQL в POSTGRESQL

Как заставить Firefox 47 загружать все мои вкладки при запуске

Явное построение типа объекта ‘###’ в запросе недопустимо.

Java: конвертировать список в строку

Несколько версий Java, работающих одновременно под Windows

гранулы разного размера, пропорциональные оси x на ggplot 2 r

pow (), кажется, отсутствует здесь

Центрирование текста в JTextArea или JTextPane – горизонтальное выравнивание текста

Как удалить поле из каждого элемента, который является последним элементом в строке?

Могу ли я загрузить сборку .NET во время выполнения и создать экземпляр типа, зная только имя?

Asp.Net MVC: Как включить тире в моих URL-адресах?

Лучшая практика для вызова ConfigureAwait для всего кода на стороне сервера

Почему рестарт-менеджер Windows 7 не восстанавливает перезагрузки?

Панель задач Windows. Предотвращение группировки некоторых приложений?

ASP.NET MVC RequireHttps только в производстве

Давайте будем гением компьютера.