sql-server - means - type sql server




Pourquoi 199.96-0=200 en SQL? (2)

J'ai des clients qui reçoivent des factures étranges. J'ai pu isoler le problème 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....

Quelqu'un a-t-il une idée de ce qui se passe ici? Je veux dire, cela a certainement quelque chose à voir avec le type de données décimal, mais je ne peux pas vraiment en comprendre le sens ...

Il y avait beaucoup de confusion à propos de quel type de données étaient les littéraux numériques, alors j'ai décidé de montrer la vraie ligne:

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)

Je me suis assuré que le résultat de chaque opération ayant un opérande d'un type différent de DECIMAL(19, 4) est converti explicitement avant de l'appliquer au contexte externe.

Néanmoins, le résultat reste 200.00 .

J'ai maintenant créé un échantillon condensé que vous pouvez exécuter sur votre ordinateur.

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

Maintenant j'ai quelque chose ...

-- 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)))

Quoi que l'enfer - sol est censé retourner un entier de toute façon. Que se passe t-il ici? :-RÉ

Je pense que j’ai maintenant réussi à réduire le tout à l’essence même :-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))
)

Gardez un œil sur les types de données impliqués pour l'instruction suivante:

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) est NUMERIC(38, 7) (voir ci-dessous).
    • FLOOR(NUMERIC(38, 7)) est NUMERIC(38, 0) (voir ci-dessous).
  2. 0.0 est NUMERIC(1, 1)
    • NUMERIC(1, 1) * NUMERIC(38, 0) est NUMERIC(38, 1)
  3. 199.96 est 199.96 NUMERIC(5, 2)
    • NUMERIC(5, 2) - NUMERIC(38, 1) est NUMERIC(38, 1) (voir ci-dessous).

Cela explique pourquoi vous vous retrouvez avec 200.0 ( un chiffre après le 199.96 décimal, pas zéro ) au lieu de 199.96 .

Remarques:

FLOOR renvoie le plus grand entier inférieur ou égal à l'expression numérique spécifiée et le résultat a le même type que l'entrée. Il renvoie INT pour INT, FLOAT pour FLOAT et NUMERIC (x, 0) pour NUMERIC (x, y).

Selon l'algorithme :

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 précision et l'échelle du résultat ont un maximum absolu de 38. Lorsqu'une précision du résultat est supérieure à 38, elle est réduite à 38 et l'échelle correspondante est réduite pour éviter que la partie intégrale d'un résultat ne soit tronquée.

La description contient également des détails sur la réduction exacte de l'échelle dans les opérations d'addition et de multiplication. Basé sur cette description:

  • NUMERIC(19, 4) * NUMERIC(19, 4) est NUMERIC(39, 8) et est fixé à NUMERIC(38, 7)
  • NUMERIC(1, 1) * NUMERIC(38, 0) est NUMERIC(40, 1) et est fixé à NUMERIC(38, 1)
  • NUMERIC(5, 2) - NUMERIC(38, 1) est NUMERIC(40, 2) et est fixé à NUMERIC(38, 1)

Voici ma tentative d'implémenter l'algorithme en JavaScript. J'ai vérifié les résultats par rapport à SQL Server. Cela répond à l' essence même de votre question.

// 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);


Je dois commencer par déballer cela un peu pour que je puisse voir ce qui se passe:

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

Voyons maintenant exactement quels types SQL Server utilise pour chaque côté de l'opération de soustraction:

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')

Résultats:

numeric 5   2
numeric 38  1

Donc 199.96 est numeric(5,2) et le plus long Floor(Cast(etc)) est numeric(38,1) .

Les règles relatives à la précision et à l'échelle résultantes d'une opération de soustraction (c'est-à-dire: e1 - e2 ) sont les suivantes:

Précision: max (s1, s2) + max (p1-s1, p2-s2) + 1
Échelle: max (s1, s2)

Cela évalue comme ceci:

Précision: max (1,2) + max (38-1, 5-2) + 1 => 2 + 37 + 1 => 40
Échelle: max (1,2) => 2

Vous pouvez également utiliser le lien des règles pour déterminer l'origine du chiffre numeric(38,1) (indice: vous avez multiplié deux 19 valeurs de précision).

Mais:

  • La précision et l’échelle du résultat ont un maximum absolu de 38. Lorsqu'une précision de résultat est supérieure à 38, elle est réduite à 38 et l’échelle correspondante est réduite pour éviter que la partie intégrante d’un résultat ne soit tronquée. Dans certains cas, tels que la multiplication ou la division, le facteur d'échelle ne sera pas réduit afin de conserver la précision décimale, bien que l'erreur de dépassement de capacité puisse être augmentée.

Oops. La précision est de 40. Nous devons la réduire, et puisque réduire la précision doit toujours couper les chiffres les moins significatifs, cela signifie aussi réduire l'échelle. Le type résultant final pour l'expression sera numeric(38,0) , ce qui, pour 199.96 arrondit à 200 .

Vous pouvez probablement résoudre ce problème en déplaçant et en consolidant les opérations CAST() de la grande expression vers un CAST() autour du résultat de l'expression. Donc ça:

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

Devient:

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

Je pourrais même retirer le casting extérieur, aussi.

Nous apprenons ici que nous devrions choisir des types qui correspondent à la précision et à l’échelle que nous avons actuellement , plutôt qu’au résultat attendu. Cela n'a pas de sens de se contenter de chiffres de grande précision, car SQL Server mue ces types lors des opérations arithmétiques pour éviter les débordements.

Plus d'information:







sqldatatypes