TSQL Arithmetic overflow using BIGINT

问题:

Can someone clarify for me why do I get an error when I try to set the variable @a 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.
*/

What I could figure out til now is that, internaly, SQL starts doing the math evaluating the multiplication and placing the temporary result into a INT then it casts it to a BIGINT.

However, if I add a 1.0 * to my list of numbers, there is no error, hence I believe that for this time SQL uses float as a temporary result, then cast it to BIGINT

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

Frankly, I don't see anything wrong with the code... it's so simple...

[ I am using SQL 2008 ]

[EDIT]

Thanks Nathan for the link. That's good information I didn't know about, but I still don't understand why do I get the error and why do I have do "tricks" to get a simple script like this working.

Is it something that I should know how to deal with as a programmer?

Or, this a bug and, if so, I will consider this question closed.

回答1:

When you're doing calculations like this, the individual numbers are stored just large enough to hold that number, ie: numeric(1,0). Check this out:

Caution
When you use the +, -, *, /, or % arithmetic operators to perform implicit or explicit conversion of int, smallint, tinyint, or bigint constant values to the float, real, decimal or numeric data types, the rules that SQL Server applies when it calculates the data type and precision of the expression results differ depending on whether the query is autoparameterized or not.

Therefore, similar expressions in queries can sometimes produce different results. When a query is not autoparameterized, the constant value is first converted to numeric, whose precision is just large enough to hold the value of the constant, before converting to the specified data type. For example, the constant value 1 is converted to numeric (1, 0), and the constant value 250 is converted to numeric (3, 0).

When a query is autoparameterized, the constant value is always converted to numeric (10, 0) before converting to the final data type. When the / operator is involved, not only can the result type's precision differ among similar queries, but the result value can differ also. For example, the result value of an autoparameterized query that includes the expression SELECT CAST (1.0 / 7 AS float) will differ from the result value of the same query that is not autoparameterized, because the results of the autoparameterized query will be truncated to fit into the numeric (10, 0) data type. For more information about parameterized queries, see Simple Parameterization.

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

---

Edit

This isn't a bug in SQL Server. From that same page, it states:

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

and

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

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

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

回答2:

In the first example SQL Server multiplies a list of INTs together, and discovers the result is too big to be an INT and the error is generated. In the second example, it notices there's a float so it converts all the INTs to floats first and then does 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 there's a BIGINT, so it converts all the INTs to BIGINTs and then does the multiplication.