T-SQL中使用ORDER BY或INSERT时出现数值转换算术溢出错误

我编写了一个返回float类型的自定义函数CalculateDistance，单独使用SELECT和WHERE语句查询时完全正常，但只要添加ORDER BY排序或INSERT INTO插入操作，就会抛出Arithmetic overflow error converting numeric to data type numeric错误。

函数代码 #

CREATE Function [dbo].[CalculateDistance]
    (@Longitude1 Decimal(8,5),
    @Latitude1   Decimal(8,5),
    @Longitude2  Decimal(8,5),
    @Latitude2   Decimal(8,5))
Returns Float
As
Begin
Declare @Temp Float
 
Set @Temp = sin(@Latitude1/57.2957795130823) * sin(@Latitude2/57.2957795130823) + cos(@Latitude1/57.2957795130823) * cos(@Latitude2/57.2957795130823) * cos(@Longitude2/57.2957795130823 - @Longitude1/57.2957795130823)
 
if @Temp > 1
    Set @Temp = 1
Else If @Temp < -1
    Set @Temp = -1
 
Return (3958.75586574 * acos(@Temp) )
 
End

测试代码 #

DECLARE 
    @startLongitude decimal(8,5) = -96.75841,
    @startLatitude decimal(8,5) = 40.78117

DECLARE @returned TABLE (
    Distance FLOAT
)

--INSERT INTO @returned(Distance) --< 取消注释会触发错误
SELECT dbo.CalculateDistance(@startLongitude, @startLatitude, z.Longitude, z.Latitude) Distance
    FROM Zips z
    WHERE dbo.CalculateDistance(@startLongitude, @startLatitude, z.Longitude, z.Latitude) < 251
--    ORDER BY Distance --< 取消注释会触发错误

Zips表部分数据 #

正常返回的距离示例值 #

168.49747310531987
168.49747310531987
117.96381879830946
117.96381879830946
82.12701472670007
120.86004491231444

已尝试的操作及结果 #

试过将返回的Distance转换为int或numeric类型后再排序/插入，依然触发相同错误，完全不清楚错误发生的原因和转换的位置。

错误根源 #

问题出在函数内部的数值运算精度自动转换：函数参数是Decimal(8,5)，当它和常量57.2957795130823做除法时，SQL Server会默认将该常量识别为numeric(15,13)。根据SQL Server的数值运算规则，Decimal(8,5)与numeric(15,13)运算后，结果的精度和小数位计算会导致中间值的精度溢出。

而当执行ORDER BY或INSERT INTO时，SQL Server的查询优化器会选择不同的执行计划，可能提前计算更多中间值或改变函数调用方式，从而触发了单纯SELECT/WHERE时未暴露的溢出问题。

解决步骤 #

方法一：显式转换参数为float后运算 #

在函数内部先将Decimal类型的经纬度转换为float，再进行三角函数运算，避免数值类型自动转换带来的精度问题：

CREATE Function [dbo].[CalculateDistance]
    (@Longitude1 Decimal(8,5),
    @Latitude1   Decimal(8,5),
    @Longitude2  Decimal(8,5),
    @Latitude2   Decimal(8,5))
Returns Float
As
Begin
Declare @Temp Float
Declare @Lat1 Float = CONVERT(Float, @Latitude1)
Declare @Lat2 Float = CONVERT(Float, @Latitude2)
Declare @Lon1 Float = CONVERT(Float, @Longitude1)
Declare @Lon2 Float = CONVERT(Float, @Longitude2)
Declare @RadFactor Float = 57.2957795130823
 
Set @Temp = sin(@Lat1/@RadFactor) * sin(@Lat2/@RadFactor) + cos(@Lat1/@RadFactor) * cos(@Lat2/@RadFactor) * cos((@Lon2 - @Lon1)/@RadFactor)
 
if @Temp > 1
    Set @Temp = 1
Else If @Temp < -1
    Set @Temp = -1
 
Return (3958.75586574 * acos(@Temp) )
 
End

方法二：修改常量为float类型 #

将运算中的常量57.2957795130823显式声明为float，强制运算时按float类型处理：

Set @Temp = sin(@Latitude1/CONVERT(Float,57.2957795130823)) * sin(@Latitude2/CONVERT(Float,57.2957795130823)) + cos(@Latitude1/CONVERT(Float,57.2957795130823)) * cos(@Latitude2/CONVERT(Float,57.2957795130823)) * cos(@Longitude2/CONVERT(Float,57.2957795130823) - @Longitude1/CONVERT(Float,57.2957795130823))

额外优化：避免重复调用函数 #

测试代码中WHERE和SELECT子句重复调用了CalculateDistance，可以改用CTE或子查询只计算一次，提升性能同时避免重复运算可能带来的问题：

DECLARE 
    @startLongitude decimal(8,5) = -96.75841,
    @startLatitude decimal(8,5) = 40.78117

DECLARE @returned TABLE (
    Distance FLOAT
)

WITH ZipDistances AS (
    SELECT dbo.CalculateDistance(@startLongitude, @startLatitude, z.Longitude, z.Latitude) Distance
    FROM Zips z
)
INSERT INTO @returned(Distance)
SELECT Distance
FROM ZipDistances
WHERE Distance < 251
ORDER BY Distance

内容的提问来源于stack exchange，提问作者user10557899