Bug in POWER function?

  • 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?

  • 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

  • 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.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply