SQL Server bug or feature? Decimal numbers convers

问题:

During development faced up with quite a strange SQL Server behavior. Here we have absolutely the same formula for absolutely the same number. The only difference is how we are getting this number (4.250). From table, temp table, variable table or hardcoded value. Rounding and casting is absolutely the same in all cases.

-- normal table
CREATE TABLE [dbo].[value]
(
[val] [decimal] (5, 3) NOT NULL
) 
INSERT INTO [value] VALUES (4.250 )
SELECT ROUND(CAST(val * 0.01 / 12 AS DECIMAL(15, 9)), 9) AS val FROM [value] AS pr

-- inline query from normal table
SELECT * FROM (SELECT ROUND(CAST(val * 0.01 / 12 AS DECIMAL(15, 9)), 9) AS val FROM [value] AS pr) a

-- record without table
SELECT ROUND(CAST(4.250 * 0.01 / 12 AS DECIMAL(15, 9)), 9) AS val

-- table variable
DECLARE @value AS TABLE (
val  [decimal] (5, 3)
);

INSERT INTO @value VALUES (4.250 )

SELECT ROUND(CAST(val * 0.01 / 12 AS DECIMAL(15, 9)), 9) AS val FROM @value

-- temp table
CREATE TABLE #value
(
    val  [decimal] (5, 3)
)
INSERT INTO #value VALUES (4.250 )
SELECT ROUND(CAST(val * 0.01 / 12 AS DECIMAL(15, 9)), 9) AS val FROM #value AS pr

-- all records together
SELECT ROUND(CAST(val * 0.01 / 12 AS DECIMAL(15, 9)), 9) AS val FROM [value] AS pr
UNION ALL
SELECT ROUND(CAST(4.250 * 0.01 / 12 AS DECIMAL(15, 9)), 9) AS val
UNION ALL
SELECT ROUND(CAST(val * 0.01 / 12 AS DECIMAL(15, 9)), 9) AS val FROM @value
UNION ALL
SELECT ROUND(CAST(val * 0.01 / 12 AS DECIMAL(15, 9)), 9) AS val FROM #value AS pr

DROP TABLE #value;
DROP TABLE [dbo].[value];

And the results are:

回答1:

This appears to be because you haven't specified the data type of 4.250 everywhere you have hard coded that value, along with mixing datatypes decimal(5,3) and decimal(15,9) in your table declarations and cast statements.

Note that specifying the same precision everywhere:

-- normal table
CREATE TABLE [dbo].[value]
  (
     [val] DECIMAL(15, 9) NOT NULL
  )

INSERT INTO [value]
SELECT CAST(4.250 AS DECIMAL(15, 9))

SELECT ROUND(CAST(val * 0.01 / 12 AS DECIMAL(15, 9)), 9) AS val
FROM   [value] AS pr

-- inline query from normal table
SELECT *
FROM   (SELECT ROUND(CAST(val * 0.01 / 12 AS DECIMAL(15, 9)), 9) AS val
        FROM   [value] AS pr) a

-- record without table
SELECT ROUND(CAST(CAST(4.250 AS DECIMAL(15, 9)) * 0.01 / 12 AS DECIMAL(15, 9)), 9) AS val

-- table variable
DECLARE @value AS TABLE
  (
     val [DECIMAL] (15, 9)
  );

INSERT INTO @value
SELECT CAST(4.250 AS DECIMAL(15, 9))

SELECT ROUND(CAST(val * 0.01 / 12 AS DECIMAL(15, 9)), 9) AS val
FROM   @value

-- temp table
CREATE TABLE #value
  (
     val [DECIMAL] (15, 9)
  )

INSERT INTO #value
SELECT CAST(4.250 AS DECIMAL(15, 9))

SELECT ROUND(CAST(val * 0.01 / 12 AS DECIMAL(15, 9)), 9) AS val
FROM   #value AS pr

-- all records together
SELECT ROUND(CAST(val * 0.01 / 12 AS DECIMAL(15, 9)), 9) AS val
FROM   [value] AS pr
UNION ALL
SELECT ROUND(CAST(CAST(4.250 AS DECIMAL(15, 9)) * 0.01 / 12 AS DECIMAL(15, 9)), 9) AS val
UNION ALL
SELECT ROUND(CAST(val * 0.01 / 12 AS DECIMAL(15, 9)), 9) AS val
FROM   @value
UNION ALL
SELECT ROUND(CAST(val * 0.01 / 12 AS DECIMAL(15, 9)), 9) AS val
FROM   #value AS pr

DROP TABLE #value;

DROP TABLE [dbo].[value];

You get the same result for every row:

0.003541667

Further Note:

You can test to see what datatype your hardcoded numeric value is by stuffing it into a variant:

DECLARE @var SQL_VARIANT;

SELECT @var = 4.250

SELECT SQL_VARIANT_PROPERTY(@var, 'BaseType'),
       SQL_VARIANT_PROPERTY(@var, 'Precision'),
       SQL_VARIANT_PROPERTY(@var, 'Scale');

This returns numeric(4,3) on my local SQL Server box. (Numeric and Decimal are the same thing )

Edit #2: Further digging

Taking just the first example:

CREATE TABLE [dbo].[value]
(
[val] [decimal] (5, 3) NOT NULL
) 
INSERT INTO [value] VALUES (4.250 )

SELECT ROUND(CAST(val * 0.01 / 12 AS DECIMAL(15, 9)), 9) AS val FROM [value] AS pr

-- inline query from normal table
SELECT * FROM (SELECT ROUND(CAST(val * 0.01 / 12 AS DECIMAL(15, 9)), 9) AS val FROM [value] AS pr) a

DROP TABLE VALUE

Having dug a little further, the execution plans are different - the first statement is being parameterised, whereas the subquery version is not:

If you look at the properties window:

It doesn't list the datatypes of these parameters, but doing the same trick with stuffing the values 0.01 and 12 into a variant ends up with datatypes numeric(2,2) and int respectively.

If you cast the hardcoded values in the second statement to those datatypes:

SELECT * FROM (SELECT ROUND(CAST(val * CAST(0.01 AS NUMERIC(2,2)) / CAST(12 AS INT) AS DECIMAL(15, 9)), 9) AS val FROM [value] AS pr) a

You get the same result for both statements. Why it has decided to parameterise the select but not the subquery, what the data types of the parameters actually are, and what datatypes the hardcoded values are treated as normally in the second statement...remain a mystery to me. We would probably need to ask someone with internal knowledge of the SQL Server engine.

回答2:

If I run:

SELECT  CAST(pr.val * 0.01 / 12 AS DECIMAL(15, 9)) AS val
,       SQL_VARIANT_PROPERTY(CAST(pr.val * 0.01 / 12 AS DECIMAL(15, 9)), 'BaseType')
FROM    [value] AS pr

Value 0.003541660 is returned.

If I run:

SELECT  CAST(pr.val * 0.01 / 12 AS DECIMAL(15, 9)) AS val
FROM    [value] AS pr

Value 0.003541667 is returned.

Smells very much like a bug to me...

edit

Based on the answer by Bridge, I too decided to have a look at the execution plans. Lo and Behold:

SELECT  CAST(pr.val * 0.01 / 12 AS DECIMAL(15, 9)) AS val
FROM    [value] AS pr
OPTION (RECOMPILE)


-- inline query from normal table
SELECT  a.val
FROM    (
            SELECT  CAST(pr.val * 0.01 / 12 AS DECIMAL(15, 9)) AS val
            FROM    [value] AS pr
        ) AS a
OPTION (RECOMPILE)

Both queries return 0.003541660. So it appears the reuse of the execution plan is where the 'error' originates. (Note: DBCC FREEPROCCACHE doesn't have the same result!)

Extra note: If I save the execution plans as xml, the files are identical both with and without OPTION (RECOMPILE).

edit:

If I set the database to PARAMETERIZATION FORCED, the subquery is still executed without parameters. If I force parameterization by explicitly using 0.01 and 12 as variables, the returned value is again the same. I think SQL Server defines the parameters in a different datatype than expected. I haven't been able to force the result to 0.003541660 though. This also explains why OPTION(RECOMPILE) results in identical values: If RECOMPILE is used, parameterization is turned off.

回答3:

From SQL Server data types page

When you use the +, -, *, /, or % arithmetic operators to perform implicit or explicit conversion of int, smallint, tinyint, or bigint constant values to the float, real, decimal or numeric data types, the rules that SQL Server applies when it calculates the data type and precision of the expression results differ depending on whether the query is autoparameterized or not.

Therefore, similar expressions in queries can sometimes produce different results. When a query is not autoparameterized, the constant value is first converted to numeric, whose precision is just large enough to hold the value of the constant, before converting to the specified data type. For example, the constant value 1 is converted to numeric (1, 0), and the constant value 250 is converted to numeric (3, 0).

When a query is autoparameterized, the constant value is always converted to numeric (10, 0) before converting to the final data type. When the / operator is involved, not only can the result type's precision differ among similar queries, but the result value can differ also. For example, the result value of an autoparameterized query that includes the expression SELECT CAST (1.0 / 7 AS float) will differ from the result value of the same query that is not autoparameterized, because the results of the autoparameterized query will be truncated to fit into the numeric (10, 0) data type.

Note:

numeric (10, 0) is equivalent to INT.

In the example above when both dividend and divisor are whole numbers the type is treated as INT e.g. INT / INT = INT

If on the other hand one of the types is forced to be a "proper" NUMERIC type the expression is treated as NUMERIC( 10, 0 ) / NUMERIC( 10, 0 ) = NUMERIC( 21, 11 ). See: Precision, scale, and Length (Transact-SQL) for explanation of how result types are calculated.

Example:

EXEC sp_describe_first_result_set N'SELECT 1 as a, 7 as b, 1 / 7 AS Result'
EXEC sp_describe_first_result_set N'SELECT 1 as a, CONVERT( NUMERIC( 10, 0 ), 7 ) as b, CONVERT( INT, 1 ) / CONVERT( NUMERIC( 10, 0 ), 7 ) AS a'

Note: NUMERIC data type only has a fixed number of decimal places (scale) to store fractional numbers. This becomes important when the division produces the result with (infinitely) long decimal part e.g. 1 / 3 that has to be truncated to fit the type.

Auto-Parameterization

From Microsoft White Paper:

... only those SQL statements for which parameter values do not affect query plan selection are auto-parameterized.

SQL Server's LPE (Language Processing and Execution) component auto-parameterizes SQL statements. When QP (query processor) component realizes that values of literal constants does not affect query plan choice, it declares LPE's attempt of auto-parameterization "safe" and auto-parameterization proceeds; otherwise, auto-parameterization is declared "unsafe" and is aborted.

If the Query Processor deems the query as "unsafe" the query still executes but the plan that is cached is for that specific query only.

The above article describe in great detail statement types that are ineligible for auto-parametrization.

SQLTeam article provides a good summary though:

• Single Table – No JOINs
• No IN clause
• No UNION
• No SELECT INTO
• No Query Hints
• No DISTINCT or TOP
• No full-text, linked servers or table variables
• No sub-queries
• No GROUP BY
• No <> in WHERE clause
• No functions
• No DELETE or UPDATE with FROM clause
• Parameter values can’t affect plan

OPs case

The difference in results boils down to whether 12 is auto-parameterized and treated as INT/ NUMERIC( 10, 0 ) or not, thus treated as NUMERIC( 2, 0 ). This will directly affect the precision (number of decimal places) of the result before rounding: decimal(19,16) or decimal(11,8).

Input parameters:

-- Note: on my machine "parameterization" option does not have any effect on below example
SELECT CONVERT( decimal (5, 3), 4.250 ) AS a, -- the type is explicitly defined in the table
    0.01 AS b -- always becomes NUMERIC( 2, 2 )
    12 AS c -- will either become NUMERIC( 2, 0 ) or NUMERIC( 10, 0 ) / INT
EXEC sp_describe_first_result_set N'SELECT CONVERT( decimal (5, 3), 4.250 ) AS a, 0.01 AS b, 12 AS c'

In the above case it is treated as INT.

You can "force" it to be treated as NUMERIC( 2, 0 ):

-- Note: on my machine "parameterization" option does not have any effect on below example
SELECT 0.01 AS b, ( 12 * 0.01 ) AS c
EXEC sp_describe_first_result_set N'SELECT ( 12 * 0.01 ) AS c'
-- Result: 0.12 numeric(5,2)

Formula for calculating product data type: p1 + p2 + 1, s1 + s2.

To find out the starting type solve: 5 = x + 2 + 1, 2 = y + 2 to get 2, 0 i.e. NUMERIC( 2, 0 )

Output type of the result will be as follows:

-- 12 is NUMERIC( 10, 0 ) / INT
SELECT CONVERT( decimal (5, 3), 4.250 ) * CONVERT( decimal (2, 2), 0.01 ) / CONVERT( decimal(10, 0), 12 )
EXEC sp_describe_first_result_set N'SELECT CONVERT( decimal (5, 3), 4.250 ) * CONVERT( decimal (2, 2), 0.01 ) / CONVERT( decimal(10, 0), 12 )'
-- Result: 0.0035416666666666 decimal(19,16) -> rounding to 9 decimal places: 0.003541667

-- 12 is NUMERIC( 2, 0 )
SELECT CONVERT( decimal (5, 3), 4.250 ) * CONVERT( decimal (2, 2), 0.01 ) / CONVERT( decimal(2, 0), 12 )
EXEC sp_describe_first_result_set N'SELECT CONVERT( decimal (5, 3), 4.250 ) * CONVERT( decimal (2, 2), 0.01 ) / CONVERT( decimal(2, 0), 12 )'
-- Result: 0.00354166 decimal(11,8) -> rounding to 9 decimal places: 0.003541660

To see how the result types are calculated see Precision, scale, and Length (Transact-SQL).

Following the steps described in Dale Burnett article, query plans for every statement in OPs example were obtained. Query plans were checked for ParameterizedPlanHandle attribute for each statement. Results are below, with possible reasons (see Auto-Parameterization section above) for no Auto-Parameterization:

1. Normal table: Auto-parameterized. Note the following in XML plan: ParameterizedText="(@1 numeric(2,2),@2 int)SELECT round(CONVERT([decimal](15,9),[val]*@1/@2),(9)) [val] FROM [value] [pr]"
2. Inline: Did not auto-parameterize. Reason: sub-query. Note that CTEs do not auto-parameterize either.
3. No table: Did not auto-parameterize. Reason: Not sure. Likely too trivial.
4. Table variable: Did not auto-parameterize. Reason: table variable
5. Tempt Table: Did not auto-parameterize. Reason: Not sure. Temp tables are not explicitly mentioned.
6. "All together": Did not auto-parameterize. Reason: UNION

Solution

Cast your literals and / or intermediate results to the desired type to avoid surprises e.g.

SELECT CONVERT( decimal( 12, 7 ), CONVERT( decimal (5, 3), 4.250 ) * CONVERT( decimal (2, 2), 0.01 )) / CONVERT( decimal(2, 0), 12 )
EXEC sp_describe_first_result_set N'SELECT CONVERT( decimal( 12, 7 ), CONVERT( decimal (5, 3), 4.250 ) * CONVERT( decimal (2, 2), 0.01 )) / CONVERT( decimal(2, 0), 12 )'
-- Result: 0.0035416666 decimal(15,10) -> rounding to 9 decimal places: 0.003541660

Summary

This question is a complex case of: Division of 2 numbers using CAST function in SQL server 2008R2. With the complexity stemming from the fact that SQL Server may use different data types in different scenarios.

Good reads

• How To Tell If Your Query Has Been Auto-Parameterized (and why wasn’t it?)
• Plan Caching in SQL Server 2008