vacio - Se ha encontrado una división de TSQL por cero a pesar de que no hay columnas que contengan 0

reemplazar null por cero sql

Question

He estado tratando de entender por qué obtengo una "división por cero encontrado" (Msg 8134) con mi consulta SQL, pero me falta algo. Me gustaría saber por qué para el caso específico a continuación, no estoy buscando NULLIF , CASE WHEN... o similar porque ya los conozco (y, por supuesto, puedo usarlos en una situación como la que se muestra a continuación).

Tengo una declaración de SQL con una columna calculada similar a

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

Al ejecutar estos errores de declaración con los mensajes de error mencionados anteriormente, que, en sí mismo, no es el problema, obviamente, TotalSize bien podría ser 0 y, por lo tanto, causar el error.

Ahora lo que no entiendo es que no tengo ninguna fila donde la columna TotalSize sea ​​0 cuando comento la columna calculada, comprobé dos veces que este no es el caso.

Entonces pensé que, por alguna razón, el cálculo de la columna se realizaría en todo el conjunto de resultados antes de filtrar realmente con las condiciones de la cláusula where, pero esto a) no tendría sentido imho yb) al intentar reproducir el error con un configuración de prueba todo funciona bien (ver a continuación):

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

Estoy bastante perplejo y me gustaría saber cuál es el motivo.

Cualquier idea o indicaciones en la dirección correcta son bienvenidas, gracias de antemano

Actualizar

Gracias por su contribución, pero desafortunadamente no me estoy acercando a la raíz del problema. Me las arreglé para quitar la declaración un poco y ahora tengo el caso de que puedo ejecutarlo sin errores si se elimina una JOIN (la necesitaría para columnas adicionales en la salida que eliminé temporalmente).

No entiendo por qué el uso de JOIN conduce al error, ¿no debería un INNER JOIN estándar devolver el mismo número de filas o menos , pero nunca más ?

Código de trabajo

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

Error al causar el código

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

También probé suerte con un cursor y recorriendo el resultado fila por fila, pero en ese caso no se produjo ningún error (no importa cuál de las dos afirmaciones anteriores intenté).

Perdón por la sangría del código desordenado, de alguna manera parece que no se aplica el formato correcto.

GRAMO.

Answer 1

Qué filas se devuelven cuando se ejecuta:

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

Esto podría darte una pista sobre cómo SQL Serve ris evaluando (TotalSize * 100) es cero.

Otra idea, ¿hay algo en su declaración where que también podría ser el problema?
Estás asumiendo que es el TotalSize, pero podría ser en otro lugar.

Answer 2

Los pasos básicos que SQL Server usa para procesar una sola instrucción SELECT incluyen los siguientes

1. El analizador escanea la instrucción SELECT y la divide en unidades lógicas como palabras clave, expresiones, operadores e identificadores.
2. Se construye un árbol de consulta, a veces denominado árbol de secuencia, que describe los pasos lógicos necesarios para transformar los datos de origen en el formato requerido por el conjunto de resultados.
3. El optimizador de consultas analiza diferentes formas en que se puede acceder a las tablas fuente. A continuación, selecciona la serie de pasos que devuelve los resultados más rápido mientras usa menos recursos. El árbol de consulta se actualiza para registrar esta serie de pasos exacta. La versión final optimizada del árbol de consultas se llama plan de ejecución.
4. El motor relacional comienza a ejecutar el plan de ejecución. A medida que se procesan los pasos que requieren datos de las tablas base, el motor relacional solicita que el motor de almacenamiento pase datos de los conjuntos de filas solicitados al motor relacional.
5. El motor relacional procesa los datos devueltos por el motor de almacenamiento en el formato definido para el conjunto de resultados y devuelve el conjunto de resultados al cliente.

Mi interpretación de las cosas es que no hay garantía de que su cláusula where sea evaluada antes de evaluar la columna calculada para todas las filas.

Puede verificar esa suposición cambiando su consulta como a continuación y forzando la cláusula where para evaluar antes del cálculo.

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

Answer 3

SQL es un lenguaje declarativo; usted escribe una consulta que describe lógicamente el resultado que desea, pero depende del optimizador producir un plan físico. Este plan físico puede no tener mucha relación con la forma escrita de la consulta, porque el optimizador no simplemente reordena los "pasos" derivados de la forma textual de la consulta, sino que puede aplicar más de 300 transformaciones diferentes para encontrar una estrategia de ejecución eficiente.

El optimizador tiene una libertad considerable para reordenar expresiones, uniones y otras construcciones de consulta lógica. Esto significa que, en general, no puede confiar en ningún formulario de consulta escrito para forzar una cosa a evaluar antes que otra. En particular, la reescritura dada por Lieven no obliga al predicado de la cláusula WHERE a evaluarse antes de la expresión. El optimizador puede, dependiendo de las estimaciones de costos, decidir evaluar la expresión donde sea más eficiente para hacerlo. Esto puede incluso significar, en algunos casos, que la expresión se evalúa más de una vez.

La pregunta original consideró esta posibilidad, pero la rechazó diciendo que "no tiene mucho sentido". Sin embargo, esta es la forma en que funciona el producto: si SQL Server estima que una unión reducirá el tamaño del conjunto lo suficiente como para que sea más barato calcular la expresión en el resultado de la unión, es libre de hacerlo.

La regla general es nunca depender de una orden de evaluación particular para evitar errores como el desbordamiento o la división por cero. En este ejemplo, uno emplearía una declaración CASE para verificar la existencia de un divisor cero, un ejemplo de programación defensiva.

La libertad del optimizador para reordenar las cosas es un principio fundamental de su diseño. Puede encontrar casos en los que conduce a comportamientos contraintuitivos, pero en general los beneficios superan con creces las desventajas.

Pablo

Answer 4

Me encontré con el mismo problema. En mi caso, los NULL eran aceptables, así que pude solucionarlo de esta manera:

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

Si necesita otro manejo, puede envolver toda la expresión en una función ISNULL como esta:

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