sql server - separar - ¿Por qué 199.96-0=200 en SQL?
sql substring hasta un caracter (2)
Debo empezar 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 está usando SQL Server 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:
numeric 5 2 numeric 38 1
Por lo tanto,
199.96
es
numeric(5,2)
y el
Floor(Cast(etc))
más largo
Floor(Cast(etc))
es
numeric(38,1)
.
Las
reglas para la precisión y la escala resultantes
de una operación de resta (es decir:
e1 - e2
) se ven así:
Precisión: máx (s1, s2) + máx (p1-s1, p2-s2) + 1
Escala: max (s1, s2)
Eso se evalúa así:
Precisión: máx (1,2) + máx (38-1, 5-2) + 1 => 2 + 37 + 1 => 40
Escala: max (1,2) => 2
También puede usar el enlace de reglas para averiguar de dónde vino el número
numeric(38,1)
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 del resultado es mayor que 38, se reduce a 38 y la escala correspondiente se reduce para intentar evitar que se trunque la parte integral de un resultado. En algunos casos, como la multiplicación o la división, el factor de escala no se reducirá para mantener la precisión decimal, aunque se puede aumentar el error de desbordamiento.
Ups.
La precisión es 40. Tenemos que reducirla, y dado que reducir la precisión siempre debe cortar los dígitos menos significativos, lo que significa reducir la escala también.
El tipo resultante final para la expresión será
numeric(38,0)
, que para
199.96
redondea a
200
.
Probablemente pueda solucionar esto moviendo y consolidando las operaciones
CAST()
desde el interior de la expresión grande a
un
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 molde exterior, también.
Aquí aprendemos que debemos elegir tipos que coincidan con la precisión y la escala que tenemos en este momento , en lugar del resultado esperado. No tiene sentido ir solo por grandes números de precisión, porque SQL Server mutará esos tipos durante las operaciones aritméticas para tratar de evitar desbordamientos.
Más información:
Tengo algunos clientes recibiendo 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 decimal, pero realmente no puedo rodearlo con la cabeza ...
Hubo mucha confusión acerca de qué tipo de datos eran los números literales, 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 con un operando de un tipo diferente a
DECIMAL(19, 4)
se emita explícitamente antes de aplicarlo al contexto externo.
Sin embargo, el resultado sigue siendo
200.00
.
Ahora he creado una muestra resumida 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 piso devuelve un entero de todos modos. ¿Que está pasando aqui? :-RE
Creo que ahora logré realmente 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))
)
Mantenga un ojo en los tipos de datos involucrados para la siguiente declaración:
SELECT 199.96 - (0.0 * FLOOR(CAST(1.0 AS DECIMAL(19, 4)) * CAST(199.96 AS DECIMAL(19, 4))))
-
NUMERIC(19, 4) * NUMERIC(19, 4)esNUMERIC(38, 7)(ver más abajo)-
FLOOR(NUMERIC(38, 7))esNUMERIC(38, 0)(ver más abajo)
-
-
0.0esNUMERIC(1, 1)-
NUMERIC(1, 1) * NUMERIC(38, 0)esNUMERIC(38, 1)
-
-
199.96esNUMERIC(5, 2)-
NUMERIC(5, 2) - NUMERIC(38, 1)esNUMERIC(38, 1)(ver más abajo)
-
Esto explica por qué terminas con
200.0
(
un dígito después de decimal, no cero
) en lugar de
199.96
.
Notas:
FLOOR
devuelve el mayor entero menor o igual que 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).
Según 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 la precisión del resultado es mayor que 38, se reduce a 38, y la escala correspondiente se reduce para intentar evitar que se trunque la parte integral de un resultado.
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. Basado en esa descripción:
-
NUMERIC(19, 4) * NUMERIC(19, 4)esNUMERIC(39, 8)y sujeto aNUMERIC(38, 7) -
NUMERIC(1, 1) * NUMERIC(38, 0)esNUMERIC(40, 1)y sujeto aNUMERIC(38, 1) -
NUMERIC(5, 2) - NUMERIC(38, 1)esNUMERIC(40, 2)y se fija aNUMERIC(38, 1)
Aquí está mi intento de implementar el algoritmo en JavaScript. He cruzado los resultados contra SQL Server. Responde a la esencia misma 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);