Why does TSQL return the wrong value for POWER(2.,64.)?

select POWER(2.,64.) returns 18446744073709552000 instead of 18446744073709551616. It seems to have only 16 digits of precision (rounding the 17th).

Even making the precision explicit select power(cast(2 as numeric(38,0)),cast(64 as numeric(38,0))) it still returns the rounded result.

This seems like a pretty basic operation for it to be flaking out arbitrarily at 16 digits of precision like this. The highest it can calculate correctly is only POWER(2.,56.), failing for POWER(2.,57.). What is going on here?

What's really terrible is that select 2.2.2.2.2.2.2.2.2.2.2.2.2.2.2.2.2.2.2.2.2.2.2.2.2.2.2.2.2.2.2.2.2.2.2.2.2.2.2.2.2.2.2.2.2.2.2.2.2.2.2.2.2.2.2.2.2.2.2.2.2.2.2.*2.; actually returns the right value. So much for terseness.

From the online documentation:

POWER ( float_expression , y )

Arguments


float_expression
Is an expression of type float or of a type that can be implicitly converted to float

The implication is that whatever you pass as the first parameter is going to be implicitly cast to a float(53) before the function is executed. However, this is not (always?) the case.

If it were the case, it would explain the loss of precision:


Conversion of float values that use scientific notation to decimal or numeric is restricted to values of precision 17 digits only. Any value with precision higher than 17 rounds to zero.

On the other hand, the literal 2. is type numeric…:

DECLARE @foo sql_variant;
SELECT @foo = 2.;
SELECT SQL_VARIANT_PROPERTY(@foo, 'BaseType');
GO

| (No column name) |
| :--------------- |
| numeric |

dbfiddle here

…and the multiply operator returns the data type of the argument with the higher precedence.

It appears that on 2016 (SP1), all the precision is retained:

SELECT @@version;
GO

| (No column name) |
| :---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64)
Oct 28 2016 18:17:30
Copyright (c) Microsoft Corporation
Express Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 (Build 9600: ) (Hypervisor)
|

SELECT POWER(2.,64.);
GO

| (No column name) |
| :------------------- |
| 18446744073709551616 |

dbfiddle here

…but on 2014 (SP2), they are not:

SELECT @@version;
GO

| (No column name) |
| :------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Microsoft SQL Server 2014 (SP2) (KB3171021) - 12.0.5000.0 (X64)
Jun 17 2016 19:14:09
Copyright (c) Microsoft Corporation
Express Edition (64-bit) on Windows NT 6.3 (Build 9600: ) (Hypervisor)
|

SELECT POWER(2.,64.);
GO

| (No column name) |
| :------------------- |
| 18446744073709552000 |

dbfiddle here

StackExchange Database Administrators Q#168815, answer score: 17