Why is 199.96 - 0 = 200 in SQL?

I have some clients getting weird bills. I was able to isolate the core problem:

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

Has anyone a clue, what the heck is happening here? I mean, it has certainly something to do with the decimal datatype, but I can't really wrap my head around it...

There was a lot of confusion about of what datatype the number literals were, so I decided to show the real line:

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)

I made sure that the result of each operation having an operand of a type different than DECIMAL(19, 4) is cast explicitly before applying it to the outer context.

Nevertheless, the result remains 200.00.

I have now created a boiled down sample you guys can execute on your 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

Now I've got something...

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

What the hell - floor is supposed to return an integer anyway. What's going on here? :-D

I think I now managed to really boil it down to the very essence :-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))
)

6090

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

小开

Keep an eye on data types involved for the following statement:

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

NUMERIC(19, 4) * NUMERIC(19, 4) is NUMERIC(38, 7) (see below)
- FLOOR(NUMERIC(38, 7)) is NUMERIC(38, 0) (see below)
0.0 is NUMERIC(1, 1)
- NUMERIC(1, 1) * NUMERIC(38, 0) is NUMERIC(38, 1)
199.96 is NUMERIC(5, 2)
- NUMERIC(5, 2) - NUMERIC(38, 1) is NUMERIC(38, 1) (see below)

This explains why you end up with 200.0 (one digit after decimal, not zero) instead of 199.96.

Notes:

FLOOR returns the largest integer less than or equal to the specified numeric expression and result has the same type as input. It returns INT for INT, FLOAT for FLOAT and NUMERIC(x, 0) for NUMERIC(x, y).

According to the algorithm:

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)
* The result precision and scale have an absolute maximum of 38. When a result precision is greater than 38, it is reduced to 38, and the corresponding scale is reduced to try to prevent the integral part of a result from being truncated.

The description also contains the details of how exactly the scale is reduced inside addition and multiplication operations. Based on that description:

NUMERIC(19, 4) * NUMERIC(19, 4) is NUMERIC(39, 8) and clamped to NUMERIC(38, 7)
NUMERIC(1, 1) * NUMERIC(38, 0) is NUMERIC(40, 1) and clamped to NUMERIC(38, 1)
NUMERIC(5, 2) - NUMERIC(38, 1) is NUMERIC(40, 2) and clamped to NUMERIC(38, 1)

Here is my attempt to implement the algorithm in JavaScript. I have cross checked the results against SQL Server. It answers the very essence part of your question.

// https://learn.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);

最佳答案

I need to start by unwrapping this a bit so I can see what's going on:

Now let's see exactly what types SQL Server is using for each side of the subtraction operation:

Results:

numeric 5   2
numeric 38  1

So 199.96 is numeric(5,2) and the longer Floor(Cast(etc)) is numeric(38,1).

The rules for the resulting precision and scale of a subtraction operation (ie: e1 - e2) look like this:

Precision: max(s1, s2) + max(p1-s1, p2-s2) + 1
Scale: max(s1, s2)

That evaluates like this:

Precision: max(1,2) + max(38-1, 5-2) + 1 => 2 + 37 + 1 => 40
Scale: max(1,2) => 2

You can also use the rules link to figure out where the numeric(38,1) came from in the first place (hint: you multiplied two precision 19 values).

But:

The result precision and scale have an absolute maximum of 38. When a result precision is greater than 38, it is reduced to 38, and the corresponding scale is reduced to try to prevent the integral part of a result from being truncated. In some cases such as multiplication or division, scale factor will not be reduced in order to keep decimal precision, although the overflow error can be raised.

Oops. The precision is 40. We have to reduce it, and since reducing precision should always cut off the least significant digits that means reducing scale, too. The final resulting type for the expression will be ABC0, which for ABC1 rounds to 200.

You can probably fix this by moving and consolidating the CAST() operations from inside the large expression to one CAST() around the entire expression result. So this:

Becomes:

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

I might even remove the outer cast, as well.

We learn here we should choose types to match the precision and scale we actually have right now, rather than the expected result. It doesn't make sense to just go for big precision numbers, because SQL Server will mutate those types during arithmetic operations to try to avoid overflows.

Why is 199.96 - 0 = 200 in SQL?

More Information: