Pourquoi 199,96 - 0 = 200 en SQL?

Question

J'ai quelques clients d'obtenir bizarre factures. J'ai été en mesure d'isoler le problème de base:

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 une idée, ce que le diable se passe ici? Je veux dire, il y a certainement quelque chose à voir avec le type de données décimal, mais je ne peux pas vraiment envelopper ma tête autour de lui...

---

Il y avait beaucoup de confusion à propos de ce type de données, le nombre de littéraux été, j'ai donc 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)

J'ai fait en sorte que le résultat de chaque opération ayant un opérande de type différent que d' DECIMAL(19, 4) est jeté explicitement avant de l'appliquer à l'extérieur du contexte.

Néanmoins, le résultat reste 200.00.

---

J'ai maintenant créé un bouilli vers le bas de l'échantillon les gars, 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)))

Ce que l'enfer - de-chaussée est censé renvoyer un nombre entier de toute façon. Ce qui se passe ici? :-D

---

Je pense que j'ai maintenant réussi à vraiment réduire à 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))
)

Answer 1

J'ai besoin de commencer par ôter ce un peu pour que je puisse voir ce qu'il se passe:

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

Maintenant, nous allons voir exactement quels types de SQL Server à l'aide de chaque côté de la soustraction de l'opération:

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:

numérique 5 2
numérique 38 1

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

Les règles pour la suite de la précision et l'échelle d'une opération de soustraction (ie: e1 - e2) ressembler à ceci:

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

Qui é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 vers les règles de figure où l' numeric(38,1) provenaient de la première place (indice: il est multiplié à deux de précision 19 valeurs).

Mais:

• La précision du résultat et de l'échelle ont un maximum absolu de 38. Si la précision du résultat est supérieur à 38 ans, il est réduit à 38, et la échelle correspondante est réduit à tenter d'empêcher la partie intégrante de raison d'être tronqué. Dans certains cas, tels que la multiplication ou la la division, le facteur d'échelle ne sera pas réduite afin de garder décimal précision, bien que l'erreur de dépassement de capacité peut être augmentée.

Oups. La précision est de 40. Nous avons à le réduire, et étant donné que la réduction de la précision doit toujours couper les chiffres les moins significatifs de réduire l'échelle, trop. Au final le type de l'expression sera numeric(38,0), ce qui pour l' 199.96 rondes 200.

Vous pouvez probablement résoudre ce problème en se déplaçant et de la consolidation de l' CAST() des opérations à partir de l'intérieur de la grande expression à un CAST() autour de la totalité du résultat de l'expression. Donc ceci:

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 pourrait même supprimer l'extérieur en fonte, ainsi.

Nous apprenons ici que nous devrions choisir les types de correspondre à la précision et l'échelle en réalité, nous avons droit maintenant, plutôt que le résultat attendu. Ça n'a pas de sens d'aller juste pour les grands nombres en précision, car SQL Server va muter ces types au cours des opérations arithmétiques pour essayer d'éviter les débordements.

---

Plus D'Informations:

• Précision, l'Échelle et la Longueur à l'aide d' Sql_Variant_Property()
• La Priorité De L'Opérateur
• Priorité des types de données

Answer 2

Gardez un œil sur les types de données concernées par la déclaration 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 NUMERIC(5, 2)
   • NUMERIC(5, 2) - NUMERIC(38, 1) est NUMERIC(38, 1) (voir ci-dessous)

C'est ce qui explique pourquoi vous vous retrouvez avec 200.0 (un chiffre après la virgule, pas zéro) au lieu de 199.96.

Notes:

FLOOR retourne le plus grand entier inférieur ou égal à l'expression numérique spécifiée et le résultat a le même type d'entrée. Il retourne INT INT, FLOAT pour le FLOTTEUR et le NUMÉRIQUE(x, 0) pour le NUMÉRIQUE(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 du résultat et de l'échelle ont un maximum absolu de 38. Lorsque la précision du résultat est supérieur à 38 ans, il est réduit à 38, et la échelle correspondante est réduit à tenter d'empêcher la partie intégrante de raison d'être tronqué.

La description contient également les détails de la façon dont exactement l'échelle est réduite à l'intérieur de l'addition et de la multiplication des opérations. En fonction de cette description:

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

Voici ma tentative de mettre en œuvre l'algorithme en JavaScript. J'ai de la croix vérifié les résultats par rapport à SQL Server. Il répond à l' essence même partie 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);