Pertanyaan Mengapa 199,96 - 0 = 200 dalam SQL?


Saya punya beberapa klien yang mendapatkan tagihan aneh. Saya bisa mengisolasi masalah inti:

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

Adakah yang tahu, apa yang terjadi di sini? Maksudku, itu pasti ada hubungannya dengan datatype desimal, tapi aku tidak bisa benar-benar membungkus kepalaku ...


Ada banyak kebingungan tentang apa datatype nomor literalnya, jadi saya memutuskan untuk menunjukkan garis nyata:

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)

Saya memastikan bahwa hasil dari setiap operasi memiliki operan tipe yang berbeda dari DECIMAL(19, 4) ditampilkan secara eksplisit sebelum menerapkannya pada konteks luar.

Namun demikian, hasilnya tetap 200.00.


Saya sekarang telah membuat sampel yang direbus yang dapat Anda jalankan di komputer Anda.

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

Sekarang saya punya sesuatu ...

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

Apa-apaan ini - lantai seharusnya mengembalikan bilangan bulat. Apa yang terjadi di sini? :-D


Saya pikir saya sekarang berhasil benar-benar merebusnya sampai ke intinya :-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


asal


Jawaban:


Saya harus mulai dengan membukanya sedikit sehingga saya dapat melihat apa yang terjadi:

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

Sekarang mari kita lihat apa jenis SQL Server menggunakan untuk setiap sisi operasi pengurangan:

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

Hasil:

numerik 5 2
numerik 38 1

Begitu 199.96 aku s numeric(5,2) dan semakin lama Floor(Cast(etc)) aku s numeric(38,1).

Itu aturan untuk ketepatan dan skala yang dihasilkan operasi pengurangan (yaitu: e1 - e2) terlihat seperti ini:

Presisi: max (s1, s2) + max (p1-s1, p2-s2) + 1
Skala:  max (s1, s2)

Itu mengevaluasi seperti ini:

Presisi: max (1,2) + max (38-1, 5-2) + 1 => 2 + 37 + 1 => 40
Skala: max (1,2) => 2

Anda juga dapat menggunakan tautan aturan untuk mencari tahu di mana numeric(38,1) berasal dari tempat pertama (petunjuk: Anda melipatgandakan dua ketepatan 19 nilai).

Tapi:

  • Ketepatan hasil dan skala memiliki maksimum absolut 38. Ketika presisi hasil lebih besar dari 38, itu dikurangi menjadi 38, dan   skala yang sesuai dikurangi untuk mencoba mencegah bagian integral dari   hasil dari terpotong. Dalam beberapa kasus seperti perkalian atau   pembagian, faktor skala tidak akan berkurang untuk menjaga desimal   presisi, meskipun kesalahan melimpah dapat ditingkatkan.

Ups. Ketepatannya adalah 40. Kita harus menguranginya, dan karena mengurangi ketepatan harus selalu memotong angka yang paling tidak signifikan yang berarti mengurangi skala juga. Jenis hasil akhir untuk ekspresi akan numeric(38,0), yang untuk 199.96 putaran ke 200.

Anda mungkin dapat memperbaikinya dengan memindahkan dan mengkonsolidasikan CAST() operasi dari dalam ekspresi besar satu  CAST() sekitar seluruh hasil ekspresi. Jadi ini:

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

Menjadi:

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

Saya bahkan mungkin menghapus pemain luar juga.

Kita belajar di sini kita harus memilih jenis yang sesuai dengan ketepatan dan skala yang sebenarnya kita miliki sekarang juga, daripada hasil yang diharapkan. Tidak masuk akal untuk hanya pergi untuk nomor presisi besar, karena SQL Server akan bermutasi jenis-jenis tersebut selama operasi aritmatika untuk mencoba menghindari overflows.


Informasi Lebih Lanjut:


76
2017-07-20 13:05



Mengawasi jenis data yang terlibat untuk pernyataan berikut:

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) aku s NUMERIC(38, 7) (Lihat di bawah)
    • FLOOR(NUMERIC(38, 7)) aku s NUMERIC(38, 0) (Lihat di bawah)
  2. 0.0 aku s NUMERIC(1, 1)
    • NUMERIC(1, 1) * NUMERIC(38, 0) aku s NUMERIC(38, 1)
  3. 199.96 aku s NUMERIC(5, 2)
    • NUMERIC(5, 2) - NUMERIC(38, 1) aku s NUMERIC(38, 1) (Lihat di bawah)

Ini menjelaskan mengapa Anda berakhir dengan 200.0 (satu digit setelah desimal, bukan nol) dari pada 199.96.

Catatan:

FLOOR mengembalikan bilangan bulat terbesar kurang dari atau sama dengan ekspresi numerik yang ditentukan dan hasilnya memiliki tipe yang sama dengan input. Ini mengembalikan INT untuk INT, FLOAT untuk FLOAT dan NUMERIC (x, 0) untuk NUMERIC (x, y).

Menurut algoritma:

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)

* Hasil presisi dan skala memiliki nilai absolut maksimum 38. Kapan   presisi hasil lebih besar dari 38, itu dikurangi menjadi 38, dan   skala yang sesuai dikurangi untuk mencoba mencegah bagian integral dari   hasil dari terpotong.

Deskripsi juga berisi rincian tentang bagaimana tepatnya skala dikurangi di samping dan operasi perkalian. Berdasarkan uraian itu:

  • NUMERIC(19, 4) * NUMERIC(19, 4) aku s NUMERIC(39, 8) dan dijepit NUMERIC(38, 7)
  • NUMERIC(1, 1) * NUMERIC(38, 0) aku s NUMERIC(40, 1) dan dijepit NUMERIC(38, 1)
  • NUMERIC(5, 2) - NUMERIC(38, 1) aku s NUMERIC(40, 2) dan dijepit NUMERIC(38, 1)

Berikut ini adalah upaya saya untuk mengimplementasikan algoritma dalam JavaScript. Saya telah memeriksa hasil silang terhadap SQL Server. Itu menjawab intinya bagian dari pertanyaanmu.

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