SQLSERVER2005_ROUND関数のバグ? - by はに

Status : 

  By Design<br /><br />
		The product team believes this item works according to its intended design.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 341762 Comments
Status Closed Workarounds
Type Bug Repros 0
Opened 5/2/2008 11:44:48 PM
Access Restriction Public


Management Studioより 


(3)CAST(ROUND(0.5,0) AS NUMERIC)としても発生しない。('1'が返却される。) 


Sign in to post a comment.
Posted by はに on 5/7/2008 at 8:15 AM
Posted by Microsoft on 5/6/2008 at 11:02 AM
Thanks for reporting the issue.

The problem is related to the strong typing in TSQL. The return type of an expression is determined based on the input type, not the actual input values. For function ROUND, if the input is numeric(p, s), the return type is also numeric(p, s).

In the case of 0.5, it has type numeric(1, 1), so the result is also numeric(1, 1). The value 1.0 is out of range of this type. In SQL Server 2000, we did return this value with the type numeric(1, 1), which is invalid. Various client tools and appliations may be broken with such return values, since it is out of range of the type.

Whiling fixing the problem, we could consider change the return type to numeric(p+1, s), but that would have a significant impact on persisted computed columns and indexed views. So we had to preserve the type. And we did add the detection that if the result is out of range of the type, we throw an overflow error.

To workaround the issue, you could:

1) set the database compatibility level to 90 or lower. This would get the old behavior. But note that some client tools or apps might fail with such type/value.

sp_dbcmptlevel 'testdb', 90

2) cast the numeric value to a higher precision.

select ROUND(CAST(0.5 AS numeric(10, 1)), 0)


declare @v numeric(10, 1)
set @v = 0.5
select ROUND(@v, 0)

Best regards,
Jun Fang