Post reply

CAST as numeric rounding issue

  • July 5, 2016 at 4:15 pm

    #400358

    Hi,

    I have two servers, one running Microsoft SQL Server 2014 (SP1-CU5) and another running SQL Server 2008 R2 Service Pack 3 (SP3).

    I have a linked server setup to Oracle 11g. Both servers are running the OraOLEDB.Oracle Provider with the same options and driver version (11.02.00.01).

    I setup a test database in ORACLE:

    CREATE TABLE [MySchema].NUMBER_TEST

    (

    UNDEFINED NUMBER

    )

    ...

    I threw some test values in there:

    select * from [MySchema].NUMBER_TEST

    order by 1

    -98.786

    -98.785

    -98.784

    98.784

    98.785

    98.786

    Here's where things get weird on me:

    SSMS 2014:

    Select UNDEFINED, CAST(UNDEFINED AS numeric(20,2))

    from openquery([MyLinkedServer],

    'select * from [MySchema].NUMBER_TEST') Numbers

    UNDEFINED (No column name)

    -98.786 -98.79

    [highlight="#ffff11"]-98.785 -98.78[/highlight] <-- Rounded Up

    -98.784 -98.78

    98.784 98.78

    [highlight="#ffff11"]98.785 98.78[/highlight] <-- Rounded Down

    98.786 98.79

    SSMS 2008 R2:

    (same query)

    UNDEFINED (No column name)

    -98.786 -98.79

    [highlight="#ffff11"]-98.785 -98.79[/highlight] <-- Rounded Down

    -98.784 -98.78

    98.784 98.78

    [highlight="#ffff11"]98.785 98.79[/highlight] <-- Rounded Up

    98.786 98.79

    Does anyone have any idea why the rounding is occurring differently?

  • July 6, 2016 at 5:11 pm

    #1888821

    Despite what your arrows say, 2014 rounded .785 down and 2008 rounded up consistently.

    I'm not familiar with the Oracle NUMBER datatype, but the documentation I glanced at suggests that in the absence of scale and precision specifications, your data is being stored as floating, which is an approximation. Evidently 2008 and 2014 are interpreting the stored values differently in making rounding decisions. Hopefully someone else can give you a more precise explanation.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • July 6, 2016 at 7:11 pm

    #1888828

    For these reasons, I don't use implicit rounding caused by datatype scale reduction. I always use the ROUND() function to do the rounding and then stuff it into the datatype I want for display purposes.

    --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)
    Intro to Tally Tables and Functions

  • July 7, 2016 at 10:14 am

    #1889016

    Thanks for the replies. I put together a blog post here:

    CAST as numeric rounding issue (Oracle Number to SQL Server numeric)

    One thing I've learned from all of this is that the mathematical definition rounding is ambiguous!

  • July 7, 2016 at 4:56 pm

    #1889073

    It may be not entirely SQL2014 fault

    I've set up a quick test in 2014 instance:CREATE TABLE #NUMBER_TEST

    (

    UNDEFINED NUMERIC (20,4)

    )

    INSERT INTO #NUMBER_TEST

    SELECT -98.786 UNION

    SELECT -98.785 UNION

    SELECT -98.784 UNION

    SELECT 98.784 UNION

    SELECT 98.785 UNION

    SELECT 98.786

    SELECT nt.UNDEFINED, CAST(UNDEFINED AS numeric(20,2)) FROM #NUMBER_TEST nt

    and the result exactly the same as for SQL2008R2:

    UNDEFINED (No column name)

    -98.7860 -98.79

    -98.7850 -98.79

    -98.7840 -98.78

    98.7840 98.78

    98.7850 98.79

    98.7860 98.79

    There must be something with OPENQUERY.

    Look at the drivers used to connect to Oracle in both cases.

    _____________
    Code for TallyGenerator

Viewing 5 posts - 1 through 4 (of 4 total)

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