TSQL arithmetic overflow using BIGINT

Question

Can someone clarify for me why I get an error when trying to set the @a variable in the example below?

DECLARE @a BIGINT SET @a = 7*11*13*17*19*23*29*31 /* ERROR: Msg 8115, Level 16, State 2, Line 1 Arithmetic overflow error converting expression to data type int. */ 

Now I could understand that, internaly, SQL starts to do the math, calculating the multiplication and putting the temporary result in INT, then it passes it to BIGINT.

However, if I add 1.0 * to the list of numbers, there are no errors, so I believe that during this time SQL uses float as a temporary result, and then sends it to BIGINT

 DECLARE @b BIGINT SET @b = 1.0 * 7*11*13*17*19*23*29*31 /* NO ERROR */ 

Honestly, I see nothing wrong with the code ... it's that simple ...

[I am using SQL 2008]

[EDIT]

Thanks to Nathan for the link. This is good information that I didn’t know about, but I still don’t understand why I get the error and why I have “tricks” to get a simple script of how it works.

Is this what I need to know how to deal with a programmer?

Or, this is a mistake, and if so, I will consider this issue closed.

Answer 1

When you perform such calculations, individual numbers are stored large enough to hold that number, i.e. numeric (1.0). Check this:

Attention
When you use +, -, *, /, or% arithmetic operators to perform implicit or explicit conversions of int, smallint, tinyint, or bigint for values ​​of floating, real, decimal or numeric data types, the rules that SQL Server applies when it evaluates data type and accuracy of the expression results differ depending on whether the request is auto-parameterized or not.

Therefore, similar expressions in the query may sometimes result in different results. When the query is not auto-parameterized, the constant value is first converted to a numeric number, whose accuracy is high enough to hold the constant value, before converting to the specified data type. For example, a constant value of 1 is converted to numeric (1, 0) and a constant value of 250 is converted to numeric (3, 0).

When a query is auto-aromatized, a constant value is always converted to a numeric (10, 0) before being converted to a final data type. When / operator, not only the accuracy of the result of similar queries, but the value of the result may vary. For example, the value of the result of an auto-parameterized query that includes the SELECT CAST expression (1.0 / 7 AS float) will differ from the result of the same query that is not auto-parameterized, because the results of the auto-parameterized query will be truncated to fit in numeric (10, 0). For more information about parameterized queries, see Simple Parameterization.

http://msdn.microsoft.com/en-us/library/ms187745.aspx

---

Edit

This is not a bug in SQL Server. On the same page is indicated:

The int data type is the primary integer data type in SQL Server.

and

SQL Server does not automatically support other integer data types (tinyint, smallint, and int) for bigint.

This is a defined behavior. As a programmer, if you have reason to believe that your data is overflowing with a data type, you need to take precautions to avoid this situation. In this case, simply converting one of these numbers to BIGINT will solve the problem.

 DECLARE @a BIGINT SET @a = 7*11*13*17*19*23*29*CONVERT(BIGINT, 31) 

Answer 2

In the first example, SQL Server combines an INT list together and detects that the result is too large to be INT and an error is generated. In the second example, he notices a float there, so he first converts all INT to float, and then performs the multiplication.

Similarly, you can do this:

 DECLARE @a BIGINT, @b BIGINT set @b = 1 SET @a = @b*7*11*13*17*19*23*29*31 

This works fine because it notices BIGINT there, so it converts all INTs to BIGINT, and then performs the multiplication.