Why is 199.96 - 0 = 200 in SQL?

Solution 1:

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.

More Information:

Precision, Scale, and Length using Sql_Variant_Property()
Operator Precedence
Data type precedence

Solution 2:

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

Why is 199.96 - 0 = 200 in SQL?

Solution 1:

More Information:

Solution 2:

Related

Recent Posts