Frage Warum ist 199.96 - 0 = 200 in SQL?


Ich habe einige Kunden, die seltsame Rechnungen bekommen. Ich konnte das Kernproblem isolieren:

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

Hat jemand eine Ahnung, was zum Teufel passiert hier? Ich meine, es hat sicherlich etwas mit dem Dezimaldatentyp zu tun, aber ich kann mich nicht wirklich darum kümmern ...


Es gab viel Verwirrung darüber, welcher Datentyp die Zahlenliterale waren, also entschied ich mich, die echte Zeile zu zeigen:

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)

Ich stellte sicher, dass das Ergebnis jeder Operation einen Operanden eines anderen Typs als DECIMAL(19, 4) wird explizit vor dem Anwenden auf den äußeren Kontext umgewandelt.

Trotzdem bleibt das Ergebnis erhalten 200.00.


Ich habe jetzt eine gekochte Probe erstellt, die ihr auf eurem Computer ausführen könnt.

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

Jetzt habe ich etwas ...

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

Was zur Hölle soll eigentlich eine ganze Zahl zurückgeben. Was ist denn hier los? :-D


Ich glaube, es ist mir jetzt gelungen, es auf das Wesentliche zu reduzieren: -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


Ursprung


Antworten:


Ich muss damit beginnen, es etwas auszupacken, damit ich sehen kann, was vor sich geht:

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

Jetzt sehen wir uns genau an, welche Typen SQL Server für jede Seite der Subtraktionsoperation verwendet:

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

Ergebnisse:

numerisch 5 2
numerisch 38 1

Damit 199.96 ist numeric(5,2) und desto länger Floor(Cast(etc)) ist numeric(38,1).

Das Regeln für die resultierende Präzision und Skalierung einer Subtraktionsoperation (dh e1 - e2) sieht aus wie das:

Präzision: max (s1, s2) + max (p1-s1, p2-s2) + 1
Rahmen:  max (s1, s2)

Das sieht so aus:

Präzision: max (1,2) + max (38-1, 5-2) + 1 => 2 + 37 + 1 => 40
Rahmen: max (1,2) => 2

Sie können auch den Link Regeln verwenden, um herauszufinden, wo numeric(38,1) kam von an erster Stelle (Hinweis: Sie multipliziert zwei Genauigkeit 19 Werte).

Aber:

  • Die Ergebnisgenauigkeit und die Skalierung haben ein absolutes Maximum von 38. Wenn eine Ergebnisgenauigkeit größer als 38 ist, wird sie auf 38 reduziert, und die   entsprechende Skala wird reduziert, um zu versuchen, den integralen Teil davon zu verhindern   ein Ergebnis davon, dass es abgeschnitten wurde. In einigen Fällen wie Multiplikation oder   Division, Skalierungsfaktor wird nicht reduziert, um Dezimal zu halten   Genauigkeit, obwohl der Überlauffehler erhöht werden kann.

Hoppla. Die Genauigkeit ist 40. Wir müssen sie reduzieren, und da die Genauigkeit reduziert werden sollte, sollten immer die niedrigstwertigen Stellen abgeschnitten werden, was auch die Skalierung reduziert. Der endgültige resultierende Typ für den Ausdruck wird sein numeric(38,0), die für 199.96 Runden nach 200.

Sie können dies wahrscheinlich durch Verschieben und Konsolidieren der CAST() Operationen von innerhalb des großen Ausdrucks zu ein  CAST() um das gesamte Ausdrucksergebnis. Also das:

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

Wird:

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

Ich könnte sogar die äußere Besetzung entfernen.

Wir lernen hier, dass wir Typen wählen sollten, die der Präzision und Größe entsprechen, die wir tatsächlich haben jetzt soforteher als das erwartete Ergebnis. Es macht keinen Sinn, nur nach großen Präzisionszahlen zu suchen, da SQL Server diese Typen während arithmetischer Operationen mutiert, um Überläufe zu vermeiden.


Mehr Informationen:


76
2017-07-20 13:05



Behalten Sie die beteiligten Datentypen für die folgende Anweisung im Auge:

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) ist NUMERIC(38, 7) (siehe unten)
    • FLOOR(NUMERIC(38, 7)) ist NUMERIC(38, 0) (siehe unten)
  2. 0.0 ist NUMERIC(1, 1)
    • NUMERIC(1, 1) * NUMERIC(38, 0) ist NUMERIC(38, 1)
  3. 199.96 ist NUMERIC(5, 2)
    • NUMERIC(5, 2) - NUMERIC(38, 1) ist NUMERIC(38, 1) (siehe unten)

Dies erklärt, warum Sie am Ende sind 200.0 (eine Nachkommastelle, nicht Null) Anstatt von 199.96.

Anmerkungen:

FLOOR gibt die größte ganze Zahl zurück, die kleiner oder gleich dem angegebenen numerischen Ausdruck ist, und das Ergebnis hat denselben Typ wie die Eingabe. Es gibt INT für INT, FLOAT für FLOAT und NUMERIC (x, 0) für NUMERIC (x, y) zurück.

Gemäß der Algorithmus:

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)

* Das Ergebnis Präzision und Maßstab haben ein absolutes Maximum von 38. Wann   eine Ergebnisgenauigkeit ist größer als 38, ist auf 38 reduziert, und die   entsprechende Skala wird reduziert, um zu versuchen, den integralen Teil davon zu verhindern   ein Ergebnis davon, dass es abgeschnitten wurde.

Die Beschreibung enthält auch die Details, wie genau die Skalierung innerhalb von Additions- und Multiplikationsoperationen reduziert wird. Basierend auf dieser Beschreibung:

  • NUMERIC(19, 4) * NUMERIC(19, 4) ist NUMERIC(39, 8) und geklammert an NUMERIC(38, 7)
  • NUMERIC(1, 1) * NUMERIC(38, 0) ist NUMERIC(40, 1) und geklammert an NUMERIC(38, 1)
  • NUMERIC(5, 2) - NUMERIC(38, 1) ist NUMERIC(40, 2) und geklammert an NUMERIC(38, 1)

Hier ist mein Versuch, den Algorithmus in JavaScript zu implementieren. Ich habe die Ergebnisse gegen SQL Server überprüft. Es beantwortet die sehr Essenz Teil deiner Frage.

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