Rounding question

  • charlie-514368

    SSC Journeyman

    Points: 94

    Comments posted to this topic are about the item Rounding question

  • Ron McCullough

    SSC Guru

    Points: 63877

    Unfortunately the cited reference for the correct answers does NOT or I could not find the "tie breaking rule" in that reference. Any one have better luck ?

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • mickyT

    SSChampion

    Points: 10360

    bitbucket-25253 (11/18/2012)


    Unfortunately the cited reference for the correct answers does NOT or I could not find the "tie breaking rule" in that reference. Any one have better luck ?

    Nope ... I suspect the difference in the results is rather with the floating point for x being slightly less than 3.65. Needless to say, I got it wrong ... should have looked for the trick.

    I would say without running this, there would be no way to accurately predict the answer

  • Koen Verbeeck

    SSC Guru

    Points: 258985

    It would be great to know more about this "tie-breaking rule", otherwise this question is just guess work.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • bochambers

    Say Hey Kid

    Points: 700

    I initially thought that this was an issue of the approximate storage of the FLOAT data type such that the 3.65 was actually being stored as 3.649999999... for example but when using an exact numeric the exact same outcome occurs, e.g.

    declare @x DECIMAL(3,2), @y DECIMAL(3,2)

    set @x = 3.65

    set @y = 3.75

    select 'x = ' + str(@x,10,1)

    select 'y = ' + str(@y,10,1)

    Similarly if the datatype of @x is set too DECIMAL(2,1) then @x is stored as 3.6 not 3.7 whilst @y is stored as 3.8 as expected.

    I agree that more information should be provided on this rounding behaviour as this is not an expected behaviour and differs from the behaviour of ROUND(@x,1) which correctly rounds up to 3.7 as expected.

  • mickyT

    SSChampion

    Points: 10360

    bochambers (11/18/2012)


    I initially thought that this was an issue of the approximate storage of the FLOAT data type such that the 3.65 was actually being stored as 3.649999999... for example but when using an exact numeric the exact same outcome occurs, e.g.

    declare @x DECIMAL(3,2), @y DECIMAL(3,2)

    set @x = 3.65

    set @y = 3.75

    select 'x = ' + str(@x,10,1)

    select 'y = ' + str(@y,10,1)

    Similarly if the datatype of @x is set too DECIMAL(2,1) then @x is stored as 3.6 not 3.7 whilst @y is stored as 3.8 as expected.

    I agree that more information should be provided on this rounding behaviour as this is not an expected behaviour and differs from the behaviour of ROUND(@x,1) which correctly rounds up to 3.7 as expected.

    STR() may be changing the data type to float prior to conversion:crazy:

  • kapil_kk

    SSC-Insane

    Points: 21316

    I think this question is more about guessing rather than tricky one...

    select STR(4.65,10,1) -- it will gives 4.7

    select STR(3.65,10,1) -- it will gives 3.6

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • This was removed by the editor as SPAM

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    I'm not really happy with the question, and the explanation is just wrong.

    When I saw the question, I knew it was going to be related to inaccuracy in the floating point represtentation. So when I saw the answers, I immediately knew to choose the one that made the least logical sense - that's always the case with these kind of questions. Luckily, there was only one illogical answer. (If "3.7/3.7" had been offered as well, I would have had no choice but to run the query, as these answers are impossible to predict unless you are willing to manually convert it to a binary floating point representation in 53 (I think) bits.

    I like the answer even less. There are no tie-breaking rules used. If there were, then the answer should have included a reference to a page where these rules are actually documented, and this is defintely not the case. The specific answers returned by this single query may seem to suggest that STR() uses "bankers rounding", but that really is a mere coincidence. Many other values will round differently, as several posters in this topic already have explained.

    (By the way, I did some research and found that there has been a discussion on perceived bankers rounding by STR on this very forum - check http://www.sqlservercentral.com/Forums/Topic246556-8-1.aspx).


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • martin.whitton

    SSCrazy

    Points: 2577

    Just out of interest, I tested the rounding of every float value in the sequence 0.65, 1.65, 2.65, etc up to 99.65 using the following code:

    declare

    @a float

    ,@x tinyint;

    declare

    @tbl table

    (

    col1 tinyint

    ,col2 decimal(4,1)

    );

    select

    @x=0;

    while @x<=100

    begin

    select

    @a=@x+0.65;

    insert into

    @tbl

    select

    @a

    ,round(@a,1);

    select

    @x=@x+1;

    end;

    select

    col1

    ,col2

    from

    @tbl;

    I found that 51 rounded down and 49 rounded up, which sounds fairly random except that every number from 4.65 to 15.65 rounds up, as does every number from 64.65 to 99.65.

    There must be some rule that governs this, but I think the best lesson is to always treat float values with caution.

  • Carlo Romagnano

    SSC-Insane

    Points: 22024

    I think it depends on "inaccuracy in the floating point represtentation" as said Hugo.

    STR convert any number to float.

    Here the script to force the right round (a double cast to decimal):

    declare @x float, @y float

    set @x = '3.65'

    set @y = '3.75'

    --What values are returned by the following statements?

    select str(CAST(CAST(@x as decimal(10,4)) as decimal(10,1)),10,1),@x

    select str(CAST(CAST(@y as decimal(10,4)) as decimal(10,1)),10,1),@y

  • Sean of the Lynchmob

    Ten Centuries

    Points: 1038

    I got this question right simply because I have seen it before, but it is an illogical answer and there is no explanation for it. I agree with others in this discussion who have said that seeing the so-called "tie-breaking rule" would help clarify and - more importantly - help people understand this behaviour.

    Nick

  • Jeff Stratford

    Say Hey Kid

    Points: 690

    This reference may help:

  • derek.colley

    SSCrazy Eights

    Points: 8040

    I agree with other posters on this thread that this is a badly-executed question. Picking two arbitrary values with inconsistent results without explaining the rules governing the rounding by STR seems like an incomplete QoTD.

    That said, I wasn't aware of this inconsistent behaviour by STR so it's something new learned, I suppose.

    ---

    Note to developers:
    CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
    So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

    Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
    My blog: http://uksqldba.blogspot.com
    Visit http://www.DerekColley.co.uk to find out more about me.

  • martin.whitton

    SSCrazy

    Points: 2577

    Jeff Stratford (11/19/2012)


    This reference may help:

    Quoting from the link Jeff cited:

    Because the binary representation of a floating-point number may use one of many legal rounding schemes, it is impossible to reliably quantify a floating-point value.

    Note the "impossible"!

Viewing 15 posts - 1 through 15 (of 46 total)

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