SQL Server中的算术溢出问题

Question

我有一个存储过程，它将两个坐标对之间的距离计算为浮点数。我试图使用它来过滤值列表，但是遇到了算术溢出错误。 查询如下：

SELECT * FROM Housing h  WHERE convert(float, dbo.CalculateDistance(35, -94, h.Latitude, h.Longitude)) <= 30.0

出现以下错误：

Msg 8115，Level 16，State 6，Line 1 算术溢出错误，将浮点数转换为数据类型数字。

参考存储过程：

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

我也尝试将结果转换为十进制，但没有效果。

- Echilon

---

当您单独运行存储过程时，它是否有效？ - MyHeadHurts

它说它正在将浮点数转换为数字。除了可能在选择本身格式化结果时，我没有看到任何应该发生这种情况的地方？ - andrewdski

Answer 1

您的输入是DECIMAL(8,5)。这意味着方程式包括，例如，SIN(DECIMAL(8,5) / 57.2957795130823)。其中57.2957795130823不能表示为DECIMAL(8,5)。

这意味着由于不同的数据类型，您有一个隐含的CAST操作。在这种情况下，似乎57.2957795130823被转换为DECIMAL(8,5) [数字]，从而导致溢出。

我建议采用以下任一方法之一：
- 更改函数以将输入作为FLOATS处理。即使使用数字调用函数
- 将57.2957795130823更改为57.29577
- 显式地将DECIMALs强制转换为FLOATs

- MatBailie

---

我思考了一下：57.2957795130823是十进制数(15,13)。除以小数遵循一些神秘的规则：https://dev59.com/T3RC5IYBdhLWcg3wD8xV#424052。也可以尝试SELECT SIN(CAST(-94 As decimal(8,5)) / 57.2957795130823 )。 - gbn

Answer 2

我会尝试转换一些算术，以防万一。

convert(float,(SIN(@Latitude1/57.2957795130823)) * convert(float,(SIN(@Latitude2/57.2957795130823)) + convert(float,(COS(@Latitude1/57.2957795130823)) * convert(float,(COS(@Latitude2/57.2957795130823)) * convert(float,COS(@Longitude2/57.2957795130823 - @Longitude1/57.2957795130823))

你可以使用的另一件事是

IFNULL(convert(float,(SIN(@Latitude1/57.2957795130823)),0.00)

你的结果可能会返回空值

- MyHeadHurts

---

SIN输出已经是浮点型的，输入也被转换为浮点型。 - gbn

Answer 3

这是你与 <= 30.0 的比较。

30.0 是 decimal(3,2)（带小数点的常量在 SQL Server 中为 decimal），而浮点输出无法转换。请参见：

SELECT 30.0 AS What INTO dbo.DataType
Go
SELECT t.name, c.*
FROM sys.columns c JOIN sys.types t ON c.system_type_id = t.system_type_id
WHERE object_id = OBJECT_ID('dbo.DataType')
GO
DROP TABLE dbo.DataType
GO

尝试

... <= CAST(30.0 As float)

- gbn

Answer 4

你正在返回一个浮点数。纬度和经度变量也应该使用浮点数，不是吗？

- Denis de Bernardy