Bug in POWER function?

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 General Discussion » Bug in POWER function?

Bug in POWER function?

Rate Topic

Display Mode

Topic Options

Author

Message

jon-230788

Posted Tuesday, February 15, 2011 1:21 PM

Forum Newbie

Group: General Forum Members
Last Login: Yesterday @ 10:48 AM
Points: 6, Visits: 87

I was just trying to load up the largest BIGINT, 9,223,372,036,854,775,807, by using the POWER function (2^63) - 1. When plugging in "2 raised to the 63," the returned value is 9,223,372,036,854,775,800 and not 9,223,372,036,854,775,808 as I would expect (prior subtracting 1 to get the largest allowed BIGINT).

SELECT POWER(2., 63.) - 1.

When obtaining the largest INT value by raising to the power of 31, I get the expected results.

SELECT POWER(2., 31.) - 1.

This a known issue?

Post #1064529

GSquared

Posted Tuesday, February 15, 2011 2:14 PM

SSCoach

Group: General Forum Members
Last Login: Monday, May 06, 2013 1:09 PM
Points: 15,439, Visits: 9,569

It's probably a floating point issue. It looks like you're using floating point numbers (they have decimal places), instead of integers. Shouldn't affect whole-number calculations, but it may in this kind of case.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Post #1064569

Jeff Moden

Posted Tuesday, February 15, 2011 4:08 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:49 AM
Points: 32,889, Visits: 26,758

POWER relies on a FLOAT conversion which, IIRC, has a maximum precision of 15 digits. Here's an article that demonstrates the problem for another function that does a FLOAT conversion...

http://www.sqlservercentral.com/articles/T-SQL/71565/

--Jeff Moden

"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1064616

« Prev Topic | Next Topic »

Permissions