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))
)
I need to start by unwrapping this a bit so I can see what's going on:
SELECT 199.96 -
(
0.0 *
FLOOR(
CAST(1.0 AS DECIMAL(19, 4)) *
CAST(199.96 AS DECIMAL(19, 4))
)
)
Now let's see exactly what types SQL Server is using for each side of the subtraction operation:
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')
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 numeric(38,0), which for 199.96 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:
SELECT 199.96 -
(
0.0 *
FLOOR(
CAST(1.0 AS DECIMAL(19, 4)) *
CAST(199.96 AS DECIMAL(19, 4))
)
)
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.