Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Bug in POWER function? Expand / Collapse
Author
Message
Posted Tuesday, February 15, 2011 1:21 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 26, 2013 9:55 AM
Points: 6, Visits: 96
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
Posted Tuesday, February 15, 2011 2:14 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Posted Tuesday, February 15, 2011 4:08 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 6:46 PM
Points: 36,944, Visits: 31,446
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1064616
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse