Arithmetic Overflow Error T-SQL - how to debug

Question

question

aerog asked May 9, '22 MartinJaffer-MSFT commented May 23, '22

Arithmetic Overflow Error T-SQL - how to debug

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?

azure-synapse-analytics

· 1

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MartinJaffer-MSFT · May 23 at 08:01 PM

Hello @aerog,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution, please do share that same with the community as it can be helpful to others. Otherwise, will respond back with the more details and we will try to help.

Thanks
Martin

0 ·

Answer 1 · 2022-05-10T18:47:26.537Z

MartinJaffer-MSFT answered May 10, '22

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.

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

question