Pregunta ¿Por qué 199.96 - 0 = 200 en SQL?


Tengo algunos clientes que reciben facturas extrañas. Pude aislar el problema central:

SELECT 199.96 - (0.0 * FLOOR(CAST(1.0 AS DECIMAL(19, 4)) * CAST(199.96 AS DECIMAL(19, 4)))) -- 200 what the?
SELECT 199.96 - (0.0 * FLOOR(1.0 * CAST(199.96 AS DECIMAL(19, 4)))) -- 199.96
SELECT 199.96 - (0.0 * FLOOR(CAST(1.0 AS DECIMAL(19, 4)) * 199.96)) -- 199.96

SELECT 199.96 - (CAST(0.0 AS DECIMAL(19, 4)) * FLOOR(CAST(1.0 AS DECIMAL(19, 4)) * CAST(199.96 AS DECIMAL(19, 4)))) -- 199.96
SELECT 199.96 - (CAST(0.0 AS DECIMAL(19, 4)) * FLOOR(1.0 * CAST(199.96 AS DECIMAL(19, 4))))                         -- 199.96
SELECT 199.96 - (CAST(0.0 AS DECIMAL(19, 4)) * FLOOR(CAST(1.0 AS DECIMAL(19, 4)) * 199.96))                         -- 199.96

-- It gets weirder...
SELECT (0 * FLOOR(CAST(1.0 AS DECIMAL(19, 4)) * CAST(199.96 AS DECIMAL(19, 4)))) -- 0
SELECT (0 * FLOOR(1.0 * CAST(199.96 AS DECIMAL(19, 4))))                         -- 0
SELECT (0 * FLOOR(CAST(1.0 AS DECIMAL(19, 4)) * 199.96))                         -- 0

-- so... ... 199.06 - 0 equals 200... ... right???
SELECT 199.96 - 0 -- 199.96 ...NO....

¿Alguien tiene una pista, qué diablos está pasando aquí? Quiero decir, ciertamente tiene algo que ver con el tipo de datos decimales, pero realmente no puedo entenderlo ...


Hubo mucha confusión acerca de qué tipo de datos eran los literales numéricos, así que decidí mostrar la línea real:

PS.SharePrice - (CAST((@InstallmentCount - 1) AS DECIMAL(19, 4)) * CAST(FLOOR(@InstallmentPercent * PS.SharePrice) AS DECIMAL(19, 4))))

PS.SharePrice DECIMAL(19, 4)

@InstallmentCount INT

@InstallmentPercent DECIMAL(19, 4)

Me aseguré de que el resultado de cada operación tenga un operando de un tipo diferente a DECIMAL(19, 4) se emite explícitamente antes de aplicarlo al contexto externo.

Sin embargo, el resultado permanece 200.00.


Ahora he creado una muestra reducida que ustedes pueden ejecutar en su computadora.

DECLARE @InstallmentIndex INT = 1
DECLARE @InstallmentCount INT = 1
DECLARE @InstallmentPercent DECIMAL(19, 4) = 1.0
DECLARE @PS TABLE (SharePrice DECIMAL(19, 4))
INSERT INTO @PS (SharePrice) VALUES (599.96)

-- 2000
SELECT
  IIF(@InstallmentIndex < @InstallmentCount,
  FLOOR(@InstallmentPercent * PS.SharePrice),
  1999.96)
FROM @PS PS

-- 2000
SELECT
  IIF(@InstallmentIndex < @InstallmentCount,
  FLOOR(@InstallmentPercent * CAST(599.96 AS DECIMAL(19, 4))),
  1999.96)
FROM @PS PS

-- 1996.96
SELECT
  IIF(@InstallmentIndex < @InstallmentCount,
  FLOOR(@InstallmentPercent * 599.96),
  1999.96)
FROM @PS PS

-- Funny enough - with this sample explicitly converting EVERYTHING to DECIMAL(19, 4) - it still doesn't work...
-- 2000
SELECT
  IIF(@InstallmentIndex < @InstallmentCount,
  FLOOR(@InstallmentPercent * CAST(199.96 AS DECIMAL(19, 4))),
  CAST(1999.96 AS DECIMAL(19, 4)))
FROM @PS PS

Ahora tengo algo ...

-- 2000
SELECT
  IIF(1 = 2,
  FLOOR(CAST(1.0 AS decimal(19, 4)) * CAST(199.96 AS DECIMAL(19, 4))),
  CAST(1999.96 AS DECIMAL(19, 4)))

-- 1999.9600
SELECT
  IIF(1 = 2,
  CAST(FLOOR(CAST(1.0 AS decimal(19, 4)) * CAST(199.96 AS DECIMAL(19, 4))) AS INT),
  CAST(1999.96 AS DECIMAL(19, 4)))

Qué diablos - se supone que el piso debe devolver un número entero de todos modos. ¿Que está pasando aqui? :-RE


Creo que ahora logré reducirlo a la esencia misma :-D

-- 1.96
SELECT IIF(1 = 2,
  CAST(1.0 AS DECIMAL (36, 0)),
  CAST(1.96 AS DECIMAL(19, 4))
)

-- 2.0
SELECT IIF(1 = 2,
  CAST(1.0 AS DECIMAL (37, 0)),
  CAST(1.96 AS DECIMAL(19, 4))
)

-- 2
SELECT IIF(1 = 2,
  CAST(1.0 AS DECIMAL (38, 0)),
  CAST(1.96 AS DECIMAL(19, 4))
)

75
2017-07-20 12:32


origen


Respuestas:


Necesito comenzar por desenvolver esto un poco para poder ver lo que está pasando:

SELECT 199.96 - 
    (
        0.0 * 
        FLOOR(
            CAST(1.0 AS DECIMAL(19, 4)) * 
            CAST(199.96 AS DECIMAL(19, 4))
        )
    ) 

Ahora veamos exactamente qué tipos de SQL Server está utilizando para cada lado de la operación de resta:

SELECT  SQL_VARIANT_PROPERTY (199.96     ,'BaseType'),
    SQL_VARIANT_PROPERTY (199.96     ,'Precision'),
    SQL_VARIANT_PROPERTY (199.96     ,'Scale')

SELECT  SQL_VARIANT_PROPERTY (0.0 * FLOOR(CAST(1.0 AS DECIMAL(19, 4)) * CAST(199.96 AS DECIMAL(19, 4)))  ,'BaseType'),
    SQL_VARIANT_PROPERTY (0.0 * FLOOR(CAST(1.0 AS DECIMAL(19, 4)) * CAST(199.96 AS DECIMAL(19, 4)))  ,'Precision'),
    SQL_VARIANT_PROPERTY (0.0 * FLOOR(CAST(1.0 AS DECIMAL(19, 4)) * CAST(199.96 AS DECIMAL(19, 4)))  ,'Scale')

Resultados:

numérico 5 2
numérico 38 1

Asi que 199.96 es numeric(5,2) y el más largo Floor(Cast(etc)) es numeric(38,1).

los reglas para la precisión y escala resultantes de una operación de resta (es decir: e1 - e2) se parece a esto:

Precisión: max (s1, s2) + max (p1-s1, p2-s2) + 1
Escala:  max (s1, s2)

Eso se evalúa así:

Precisión: max (1,2) + max (38-1, 5-2) + 1 => 2 + 37 + 1 => 40
Escala: max (1,2) => 2

También puede usar el enlace de reglas para averiguar dónde numeric(38,1) vino en primer lugar (pista: multiplicó dos valores de precisión 19).

Pero:

  • La precisión y la escala del resultado tienen un máximo absoluto de 38. Cuando la precisión de un resultado es mayor que 38, se reduce a 38, y la   escala correspondiente se reduce para tratar de evitar la parte integral de   un resultado de ser truncado. En algunos casos, como la multiplicación o   división, el factor de escala no se reducirá para mantener el decimal   precisión, aunque el error de desbordamiento puede elevarse.

Oops. La precisión es 40. Tenemos que reducirla, y dado que la reducción de la precisión siempre debe cortar los dígitos menos significativos, también significa reducir la escala. El tipo resultante final para la expresión será numeric(38,0), que para 199.96 rondas para 200.

Probablemente pueda solucionar esto moviendo y consolidando el CAST() operaciones desde el interior de la expresión grande a uno  CAST() alrededor del resultado de la expresión completa. Así que esto:

SELECT 199.96 - 
    (
        0.0 * 
        FLOOR(
            CAST(1.0 AS DECIMAL(19, 4)) * 
            CAST(199.96 AS DECIMAL(19, 4))
        )
    ) 

Se convierte en:

SELECT CAST( 199.96 - ( 0.0 * FLOOR(1.0 * 199.96) ) AS decimial(19,4))

Incluso podría eliminar el yeso externo, también.

Aprendemos que debemos elegir los tipos para que coincidan con la precisión y la escala que realmente tenemos ahora mismo, en lugar del resultado esperado. No tiene sentido simplemente buscar números de gran precisión, ya que SQL Server mutará esos tipos durante operaciones aritméticas para tratar de evitar desbordamientos.


Más información:


76
2017-07-20 13:05



Esté atento a los tipos de datos involucrados en la siguiente declaración:

SELECT 199.96 - (0.0 * FLOOR(CAST(1.0 AS DECIMAL(19, 4)) * CAST(199.96 AS DECIMAL(19, 4))))
  1. NUMERIC(19, 4) * NUMERIC(19, 4) es NUMERIC(38, 7) (vea abajo)
    • FLOOR(NUMERIC(38, 7)) es NUMERIC(38, 0) (vea abajo)
  2. 0.0 es NUMERIC(1, 1)
    • NUMERIC(1, 1) * NUMERIC(38, 0) es NUMERIC(38, 1)
  3. 199.96 es NUMERIC(5, 2)
    • NUMERIC(5, 2) - NUMERIC(38, 1) es NUMERIC(38, 1) (vea abajo)

Esto explica por qué terminas con 200.0 (un dígito después del decimal, no cero) en lugar de 199.96.

Notas:

FLOOR devuelve el entero más grande menor o igual a la expresión numérica especificada y el resultado tiene el mismo tipo que la entrada. Devuelve INT para INT, FLOAT para FLOAT y NUMERIC (x, 0) para NUMERIC (x, y).

De acuerdo a el algoritmo:

Operation | Result precision                    | Result scale*
e1 * e2   | p1 + p2 + 1                         | s1 + s2
e1 - e2   | max(s1, s2) + max(p1-s1, p2-s2) + 1 | max(s1, s2)

* La precisión y la escala del resultado tienen un máximo absoluto de 38. Cuando   una precisión del resultado es mayor que 38, se reduce a 38, y el   escala correspondiente se reduce para tratar de evitar la parte integral de   un resultado de ser truncado.

La descripción también contiene los detalles de cómo se reduce exactamente la escala dentro de las operaciones de suma y multiplicación. En base a esa descripción:

  • NUMERIC(19, 4) * NUMERIC(19, 4) es NUMERIC(39, 8) y sujetado a NUMERIC(38, 7)
  • NUMERIC(1, 1) * NUMERIC(38, 0) es NUMERIC(40, 1) y sujetado a NUMERIC(38, 1)
  • NUMERIC(5, 2) - NUMERIC(38, 1) es NUMERIC(40, 2) y sujetado a NUMERIC(38, 1)

Aquí está mi intento de implementar el algoritmo en JavaScript. He verificado los resultados contra SQL Server. Responde el muy esencia parte de tu pregunta.

// https://docs.microsoft.com/en-us/sql/t-sql/data-types/precision-scale-and-length-transact-sql?view=sql-server-2017

function numericTest_mul(p1, s1, p2, s2) {
  // e1 * e2
  var precision = p1 + p2 + 1;
  var scale = s1 + s2;

  // see notes in the linked article about multiplication operations
  var newscale;
  if (precision - scale < 32) {
    newscale = Math.min(scale, 38 - (precision - scale));
  } else if (scale < 6 && precision - scale > 32) {
    newscale = scale;
  } else if (scale > 6 && precision - scale > 32) {
    newscale = 6;
  }

  console.log("NUMERIC(%d, %d) + NUMERIC(%d, %d) yields NUMERIC(%d, %d) clamped to NUMERIC(%d, %d)", p1, s1, p2, s2, precision, scale, Math.min(precision, 38), newscale);
}

function numericTest_add(p1, s1, p2, s2) {
  // e1 + e2
  var precision = Math.max(s1, s2) + Math.max(p1 - s1, p2 - s2) + 1;
  var scale = Math.max(s1, s2);

  // see notes in the linked article about addition operations
  var newscale;
  if (Math.max(p1 - s1, p2 - s2) > Math.min(38, precision) - scale) {
    newscale = Math.min(precision, 38) - Math.max(p1 - s1, p2 - s2);
  } else {
    newscale = scale;
  }

  console.log("NUMERIC(%d, %d) + NUMERIC(%d, %d) yields NUMERIC(%d, %d) clamped to NUMERIC(%d, %d)", p1, s1, p2, s2, precision, scale, Math.min(precision, 38), newscale);
}

function numericTest_union(p1, s1, p2, s2) {
  // e1 UNION e2
  var precision = Math.max(s1, s2) + Math.max(p1 - s1, p2 - s2);
  var scale = Math.max(s1, s2);

  // my idea of how newscale should be calculated, not official
  var newscale;
  if (precision > 38) {
    newscale = scale - (precision - 38);
  } else {
    newscale = scale;
  }

  console.log("NUMERIC(%d, %d) + NUMERIC(%d, %d) yields NUMERIC(%d, %d) clamped to NUMERIC(%d, %d)", p1, s1, p2, s2, precision, scale, Math.min(precision, 38), newscale);
}

/*
 * first example in question
 */

// CAST(1.0 AS DECIMAL(19, 4)) * CAST(199.96 AS DECIMAL(19, 4))
numericTest_mul(19, 4, 19, 4);

// 0.0 * FLOOR(...)
numericTest_mul(1, 1, 38, 0);

// 199.96 * ...
numericTest_add(5, 2, 38, 1);

/*
 * IIF examples in question
 * the logic used to determine result data type of IIF / CASE statement
 * is same as the logic used inside UNION operations
 */

// FLOOR(DECIMAL(38, 7)) UNION CAST(1999.96 AS DECIMAL(19, 4)))
numericTest_union(38, 0, 19, 4);

// CAST(1.0 AS DECIMAL (36, 0)) UNION CAST(1.96 AS DECIMAL(19, 4))
numericTest_union(36, 0, 19, 4);

// CAST(1.0 AS DECIMAL (37, 0)) UNION CAST(1.96 AS DECIMAL(19, 4))
numericTest_union(37, 0, 19, 4);

// CAST(1.0 AS DECIMAL (38, 0)) UNION CAST(1.96 AS DECIMAL(19, 4))
numericTest_union(38, 0, 19, 4);


20
2017-07-20 15:30