Desbordando AVG y SUM: Cuando SQL Server se queda sin dedos para sumar

Bug en SQL Server: CASE ejecuta funciones en cláusulas que no cumplen la condición

Hola a todos: En esta ocasión, vamos a ocupar esta nueva entrada en el blog para hablaros de un comportamiento inusual de SQL Server, que ejecuta ciertas funciones en cláusulas de sentencias CASE que no se evalúan a verdadero.

El operador CASE
CASE, tal y como nos lo decribe la MSDN "evalúa una lista de condiciones y devuelve una de las varias expresiones de resultado posibles".

Es decir, nos permite retornar un valor distinto en función de cada uno de los valores posibles que pueda tener el argumento o condición que estemos evaluando. Podemos decirle que nos devuelva un resultado diferente en función del valor de una variable o de expresiones lógicas.


El problema a resolver

Supongamos que queremos obtener el mínimo de aplicar una fórmula matemática a una columna de una tabla, siempre que no existiese ningún valor nulo en ella. Si hubiese algún NULL, éste debía ser el valor devuelto:
SELECT CASE WHEN EXISTS (SELECT * FROM SomeTable WHERE SomeValue IS NULL) THEN NULL ELSE MIN(Utils.SomeMathFunction(SomeValue)) END AS SomeValue FROM SomeTable
Esta query debería comportarse de la siguiente forma:
  • Si existe un sólo valor nulo en la columna SomeValue de la tabla SomeTable, debe devolver un NULL, sin realizar ningún tipo de evaluación más.
  • En caso contrario, deberá devolvernos el mínimo valor tras aplicar la función Utils.SomeMathFunction a todos los valores de la columna.
Hacemos la prueba y ejecutamos una traza con el SQL Server Profiler para comprobar qué hace SQL Server. Primero probamos sin valores nulos: funciona. Pero la sorpresa llega cuando al haber valores nulos, vemos en la traza que igualmente existen tantas llamadas a la función Utils.SomeMathFunction como registros en la tabla ¡¿Cómo es posible?!

El bug de SQL Server
Sin ser capaces de encontrar una explicación al comportamiento del servidor de bases de datos por nosotros mismos, nos ponemos en contacto con una auténtica autoridad en lo que a T-SQL se refiere: Itzik Ben-Gan, autor de numerosos libros y columnas sobre transact, MVP de Microsoft y uno de los gurús mundiales en lo que se refiere al motor de bases de datos de Microsoft.

Ben-Gan nos confirma que, efectivamente, SQL Server no se comporta correctamente ante la combinación de la instrucción CASE con una función de agregado (MIN), y que ejecuta esta última pese a que la condición que debía llevar a su ejecución no se evalúe a verdadero.

Y nos ofrece un caso más simple de reproducción del problema:
DECLARE @i AS INT = 1;SELECT CASE @i WHEN 1 THEN NULL ELSE MIN(1/0) END;
Ejecutar esa consulta produce el siguiente mensaje de error, cuando el CASE debería haber determinado que @i vale 1 y, por tanto, el valor a devolver es 1, y nunca MIN(1/0), que no debería ni siquiera haberse ejecutado:

Msg 8134, Level 16, State 1, Line 2
Divide by zero error encountered.
Como para casi todo, existe un workaround
Ben-Gan también nos ofreció un workaround para salir del paso, que evita que SQL Server realice la función de agregado en estas situaciones:

DECLARE @i AS INT = 1;SELECT CASE @i WHEN 1 THEN NULL ELSE (SELECT MIN(1/0)) END;

Simplemente añadiendo una cláusula SELECT delante de la operación de agregado, la operación MIN(1/0) ya no se ejecutará, lo que en nuestro caso original quedaría como sigue:
SELECT CASE WHEN EXISTS (SELECT * FROM SomeTable WHERE SomeValue IS NULL) THEN NULL ELSE (SELECT MIN(Utils.SomeMathFunction(SomeValue) FROM SomeTable) END AS SomeValue
Conclusión

Así pues, debemos estar alerta cuando combinemos CASE con el uso de funciones de tipo agregado como MIN, MAX, COUNT... porque éstas se van a ejecutar sea cual sea la evaluación de la expresión que lleva a ellas: tanto si es verdadera, como si es falsa.

Si, además, dentro de estas funciones llamamos a funciones definidas por el usuario (UDFs) o insertamos cualquier otra lógica que sólo debería ejecutarse en determinadas ocasiones, debemos saber que no será así, y que esa lógica se ejecutará todas las veces para todos los registros implicados en la consulta. Siempre. Evidentemente, el rendimiento de nuestra query se verá empobrecido de manera totalmente innecesaria y prescindible.

Mientras no haya solución por parte de Microsoft, podemos hacer uso del workaround aquí expuesto o, mejor, intentar replantear nuestras consultas de otra forma, para evitar este bug, siempre que sea posible.

Agradecimientos a Itzik Ben-Gan por su rápida respuesta e interés en la consulta.

Comentarios

  1. Excelente articulo, muchas gracias por la información, a este punto tengo una pregunta:
    tengo un query: select Precio_Venta/Cantidad as Valor_Unidad from Xtabla
    la consulta resultante son casi 400,000 registros. Curiosamente da el error de divide by zero, (obviamente ya revise el campo cantidad y no existe 0 o nulos) al ejecutar el mismo query dando como resultado los primeros 200 mil no da error, si lo ejecuto para los segundos 200 mil tampoco da error. ¿alguna sugerencia?. De antemano muchas gracias por la atención a la presente.

    ResponderEliminar
    Respuestas
    1. Hola Benedicto:

      Lamento no haberte contestado antes. Agradezco enormemente tu comentario y me gustaría haberte podido ayudar con tu duda, pero no vi tu texto hasta ahora. Si aún sigues teniendo problemas de este tipo, coméntamelo e intentaré ayudarte.

      Eliminar
  2. Interesante...
    Yo tengo un problema al usar Update con Case
    como puedo hacer para que Else, no deje todo como Null, sino el mismo valor?

    ResponderEliminar
    Respuestas
    1. Hola, Osvaldo:
      ¿Podrías poner un ejemplo de tu consulta?
      Gracias

      Eliminar
    2. Hola

      Soy estudiante y tengo duda de lo siguiente:

      Me pidieron que esto

      DECLARE @A INT
      DECLARE @B INT
      DECLARE @C VARCHAR
      SET @A=20
      SET @B=12
      SET @C= ' '

      IF (@C='+')
      BEGIN
      SELECT @A+@B
      END

      IF (@C='-')
      BEGIN
      SELECT @A-@B
      END

      IF (@C='*')
      BEGIN
      SELECT @A*@B
      END
      IF (@C='/')
      BEGIN
      SELECT @A+/@B
      END

      Ahora lo realice con CASE usando las variables.

      Espero tener respuesta de su parte.

      Saludos..

      Eliminar
    3. Hola:

      Imagino que tu profesor prefiere que la respuesta sea de tu parte, y no de la mía :)

      Puedes consultar la ayuda online, y comprobar la sintaxis de CASE.
      https://docs.microsoft.com/es-es/sql/t-sql/language-elements/case-transact-sql.

      De las dos variantes que hay, a ti te interesa la sencilla, evaluando @C con cada uno de los cuatro posibles valores.

      Por ejemplo:

      SELECT CASE @C WHEN ‘+’ THEN...

      Espero haberte ayudado a resolver el problema, pensando tú la solución.

      Saludos y gracias por leer.

      Eliminar

Publicar un comentario