Arithmetic Overflow Error T-SQL - how to debug

Question

Hi,

Would appreciate any help with this!

I have generated an automated SQL script in Synapse to score a ML model:
-- Create a stored procedure for storing the scoring script.
CREATE PROCEDURE dbo.view_NYC
AS
BEGIN
-- Select input scoring data and assign aliases.
WITH InputData AS
(
SELECT
[tipped],
[paymentType],
[passengerCount],
CAST([tripDistance] AS [real]) AS [tripDistance],
[tripTimeSecs],
CAST([pickupTimeBin] AS [varchar]) AS [pickupTimeBin]
FROM [dbo].[NYC_manual]
)
-- Using T-SQL Predict command to score machine learning models.
SELECT *
FROM PREDICT (MODEL = (SELECT [model] FROM dbo.aml_nyc WHERE [ID] = 'gr-synapse-nyc_taxi_train-20220508121353-Best:1'),
DATA = InputData,
RUNTIME = ONNX) WITH ([variable_out1] [float])
END
GO

-- Execute the above stored procedure.
EXEC dbo.view_NYC

However, I get the following error:
11:06:45
Started executing query at Line 1
Arithmetic Overflow.
Total execution time: 00:00:05.930

How do I debug this error?
From searching for an answer, this type of error is caused when converting one data type to another. But how do I even start to debug where this is happening?

Answer 1

Hello @AeroG and welcome to Microsoft Q&A.

As I understand, you wish for help in the process of debugging an arithmetic overflow error.

An arithmetic overflow occurs when the output of an operation is a value bigger than can be stored in the destination data type. This means the data type must be numerically bound -- Integers, Floats, (maybe decimal). Strings and chars have different error message. This error message may also apply to date / datetime.

Given this, the most suspect part of your code is

CAST([tripDistance] AS [real]) AS [tripDistance],  

According to (doc link), Real is equivalent to Float(24). There is a bigger version of float, called double, as it is double the size of a standard float. Try float(53) or double precision instead of real, and see if the error message goes away.

Please let me know if this helps.

Answer 2

You may be casting into too small an item.

E.G.

CAST ( (ITEMA *1.0 /ITEMB) AS VARCHAR(4) )

A case statement checks that the value is between 100 and 1000, and it was assumed (wrongly) that the value would fit into a four character container.

ITEMA and ITEMB are type INT.

The code errored (Arithmetic overflow) when trying to put numeric values like

nnn.0000000 into the 4 character container.