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.


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

Description

SQLSERVER2005のROUND関数についてなのですが、 
Management Studioより 

SELECT ROUND(0.5,0) 

と実行すると、なぜかオーバーフローしてエラーになってしまいます。 
現在わかっている状況をまとめると以下のようになります。 
(1)0.5以上1未満で発生するようです。 
(2)数値型の変数に入れてから渡した場合には発生しない。('1'が返却される。) 
(3)CAST(ROUND(0.5,0) AS NUMERIC)としても発生しない。('1'が返却される。) 
(4)ROUND(0.5,0,0)としてもやはり発生するが、 
   ROUND(0.5,0,1)として切り捨てを指定すると発生しない。 
(5)SQLSERVER2000にて実行するとエラーにはならずに'1'が返却される。 

これはやはりSQLSERVER2005のバグなんでしょうか? 

参考URL:http://www.atmarkit.co.jp/bbs/phpBB/viewtopic.php?topic=29574&forum=26&4
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)

or

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

Best regards,
Jun Fang
Microsoft