risposta-alla-domanda-sullo-sviluppo-web-bd.com

Perché 199,96 - 0 = 200 in SQL?

Ho alcuni clienti che ottengono strane fatture. Sono stato in grado di isolare il problema principale:

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

Qualcuno ha idea di cosa diavolo sta succedendo qui? Voglio dire, ha sicuramente qualcosa a che fare con il tipo di dati decimale, ma non riesco davvero a capirlo ...


C'era molta confusione su quale tipo di dati fossero i numeri letterali, quindi ho deciso di mostrare la linea reale:

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)

Mi sono assicurato che il risultato di ogni operazione avente un operando di un tipo diverso da DECIMAL(19, 4) venisse eseguito il cast esplicito prima di applicarlo al contesto esterno.

Tuttavia, il risultato rimane 200.00.


Ora ho creato un campione ridotto che voi ragazzi potete eseguire sul vostro computer.

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

Ora ho qualcosa ...

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

Che diavolo, il piano dovrebbe restituire comunque un numero intero. Cosa sta succedendo qui? :-D


Penso di essere riuscito a ridurlo davvero all'essenza :-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))
)
83
Silverdust

Ho bisogno di iniziare scartando questo un po 'così posso vedere cosa sta succedendo:

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

Ora vediamo esattamente quali tipi di SQL Server sta utilizzando per ciascun lato dell'operazione di sottrazione:

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

Risultati:

 numerico 5 2 
 numerico 38 1 

Quindi 199.96 è numeric(5,2) e il Floor(Cast(etc)) più lungo numeric(38,1).

Le regole per la precisione e la scala risultante di un'operazione di sottrazione (es .: e1 - e2) hanno il seguente aspetto:

Precisione: max (s1, s2) + max (p1-s1, p2-s2) + 1
Scala: max (s1, s2)

Quello valuta così:

Precisione: max (1,2) + max (38-1, 5-2) + 1 => 2 + 37 + 1 => 40
Scala: max (1,2) => 2

Puoi anche usare il link delle regole per capire da dove proviene numeric(38,1) in primo luogo (suggerimento: hai moltiplicato due valori di precisione 19).

Ma:

  • La precisione e la scala dei risultati hanno un massimo assoluto di 38. Quando la precisione di un risultato è maggiore di 38, viene ridotta a 38 e la scala corrispondente viene ridotta per cercare di impedire che la parte integrale di un risultato venga troncata. In alcuni casi, come la moltiplicazione o la divisione, il fattore di scala non verrà ridotto per mantenere la precisione decimale, sebbene sia possibile sollevare l'errore di overflow.

Ops. La precisione è 40. Dobbiamo ridurlo, e dal momento che ridurre la precisione dovrebbe sempre tagliare le cifre meno significative, il che significa anche ridurre la scala. Il tipo risultante finale per l'espressione sarà numeric(38,0), che per 199.96 arrotonda a 200.

Probabilmente si può risolvere questo problema spostando e consolidando le operazioni CAST() dall'interno dell'espressione grande a una CAST() attorno all'intero risultato dell'espressione. Così questo:

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

Diventa:

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

Potrei anche rimuovere il cast esterno.

Qui apprendiamo che dovremmo scegliere i tipi che corrispondono alla precisione e alla scala effettivamente disponibili adesso , piuttosto che al risultato atteso. Non ha senso cercare numeri di precisione elevati, perché SQL Server modificherà questi tipi durante le operazioni aritmetiche per cercare di evitare gli overflow.


Maggiori informazioni:

77
Stanislav Kundii

Tieni d'occhio i tipi di dati coinvolti per la seguente dichiarazione:

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) è NUMERIC(38, 7) (vedi sotto)
    • FLOOR(NUMERIC(38, 7)) is NUMERIC(38, 0) (vedi sotto)
  2. 0.0 è NUMERIC(1, 1)
    • NUMERIC(1, 1) * NUMERIC(38, 0) is NUMERIC(38, 1)
  3. 199.96 è NUMERIC(5, 2)
    • NUMERIC(5, 2) - NUMERIC(38, 1) is NUMERIC(38, 1) (vedi sotto)

Questo spiega perché si finisce con 200.0 ( una cifra dopo il decimale, non zero ) invece di 199.96.

Gli appunti:

FLOOR restituisce il numero intero più grande minore o uguale all'espressione numerica specificata e il risultato ha lo stesso tipo di input. Restituisce INT per INT, FLOAT per FLOAT e NUMERIC (x, 0) per NUMERIC (x, y).

Secondo l'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 precisione e la scala dei risultati hanno un massimo assoluto di 38. Quando la precisione di un risultato è maggiore di 38, viene ridotta a 38 e la scala corrispondente viene ridotta per cercare di impedire la troncatura della parte integrale di un risultato.

La descrizione contiene anche i dettagli di come esattamente la scala viene ridotta all'interno delle operazioni di addizione e moltiplicazione. Basato su tale descrizione:

  • NUMERIC(19, 4) * NUMERIC(19, 4) è NUMERIC(39, 8) e bloccato a NUMERIC(38, 7)
  • NUMERIC(1, 1) * NUMERIC(38, 0) è NUMERIC(40, 1) e bloccato a NUMERIC(38, 1)
  • NUMERIC(5, 2) - NUMERIC(38, 1) è NUMERIC(40, 2) e bloccato a NUMERIC(38, 1)

Ecco il mio tentativo di implementare l'algoritmo in JavaScript. Ho controllato i risultati incrociati con SQL Server. Risponde alla parte molto essence della tua domanda.

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