Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

DECIMAL oddity

Discussion in 'Information Technology' started by Jamie Collins, Jul 28, 2006.

Tags:
  1. Jamie Collins

    Jamie Collins
    Expand Collapse
    Guest

    This follows my 'Double oddity' post yesterday (thanks again, Gunny).

    Jet seems a little reluctant to promote an INTEGER (Long) to another
    type, preferring to overflow e.g.

    SELECT 2147483647 + 1 FROM DropMe;

    Values outside the INTEGER range (from -2,147,483,648 to 2,147,483,647
    inclusive) in Jet are natively DECIMAL in nature:

    SELECT 2147483648 AS data_value,
    TYPENAME(2147483648) AS data_type
    FROM DropMe;

    Therefore, as Gunny points out, coercing the values to DECIMAL, by
    operating using a native DECIMAL value at any stage, means the INTEGER
    can successfully be promoted to DECIMAL:

    SELECT (2147483647 + 1) + (0.1 - 0.1) AS data_value,
    TYPENAME((2147483647 + 1) + (0.1 - 0.1)) AS data_type
    FROM DropMe;

    One would hope the parser is smart enough to determine that (0.1 - 0.1)
    doesn't actually need to be evaluated <g>.

    OK, on to the oddity. Unlike a certain MVP I could mention, I recently
    revisited this PIA bug:

    BUG: You may notice an incorrect sorted order when you sort the
    negative decimal values in descending order in Access
    http://support.microsoft.com/default.aspx?scid=kb;en-us;837148

    [I love the way they downplay the severity of the bug with the casual
    title].

    The suggested workaround, 'create an index on the Number field before
    you sort the records' does not work for me :(

    I decided to test with values that are natively DECIMAL due to the fact
    they are out of range for INTEGER i.e. instead of 20, -10, 5, -5, 10
    and -20 use 3,000,000,020, -3,000,000,010, 3,000,000,005,
    -3,000,000,005, 3,000,000,010, -3,000,000,020 respectively:

    SELECT BadSort.dec_data
    FROM
    (
    SELECT 3000000020 AS dec_data FROM DropMe
    UNION ALL
    SELECT -3000000010 FROM DropMe
    UNION ALL
    SELECT 3000000005 FROM DropMe
    UNION ALL
    SELECT -3000000005 FROM DropMe
    UNION ALL
    SELECT 3000000010 FROM DropMe
    UNION ALL
    SELECT -3000000020 FROM DropMe
    ) AS BadSort
    ORDER BY BadSort.dec_data DESC;

    No such luck: the order is entirely predicable yet wrong :(

    I then (inadvertently) applied the trick of coercing the already
    DECIMAL values using + (0.1 - 0.1):

    SELECT GoodSort.dec_data
    FROM
    (
    SELECT 3000000020 + (0.1 - 0.1) AS dec_data FROM DropMe
    UNION ALL
    SELECT -3000000010 + (0.1 - 0.1) FROM DropMe
    UNION ALL
    SELECT 3000000005 + (0.1 - 0.1) FROM DropMe
    UNION ALL
    SELECT -3000000005 + (0.1 - 0.1) FROM DropMe
    UNION ALL
    SELECT 3000000010 + (0.1 - 0.1) FROM DropMe
    UNION ALL
    SELECT -3000000020 + (0.1 - 0.1) FROM DropMe
    ) AS GoodSort
    ORDER BY GoodSort.dec_data DESC;

    Surprisingly, the sort order is now correct!

    My joy was short-lived, though. I discovered the reason: the values had
    been coerced to FLOAT (Double). In essence it seems to be this:

    SELECT 3000000020 + (0.1 - 0.1) AS dec_data_value,
    TYPENAME(3000000020 + (0.1 - 0.1)) AS dec_data_type,
    TYPENAME(dec_data_value) AS derived_data_type
    FROM DropMe;

    Why is this DECIMAL value being coerced to FLOAT (Double)?

    Perhaps a more practical question would be: did MS ever publish a
    specification for Jet with a level of detail to include topics such as
    data type precedence and implicit conversion between types? e.g. the
    equivalent of this:

    http://msdn2.microsoft.com/en-us/library/ms187928.aspx

    TIA,
    Jamie.

    --
     
  2. datAdrenaline

    datAdrenaline
    Expand Collapse
    Guest

    While doing research (which was encouraged by another user <g>) on the
    DECIMAL datatype, I came accross this post ... Interesting and informative
    .... actually both of your "oddity" posts were excellent! It seems that
    DECIMAL data type can cause some ambiguity within the MSAccess/VBA/JET realm
    .... maybe it will be clearly defined in JET 4.1 (probably not!) <g>
    --
    Regards,
    Brent Spaulding
    datAdrenaline


    "Jamie Collins" wrote:

    > This follows my 'Double oddity' post yesterday (thanks again, Gunny).
    >
    > Jet seems a little reluctant to promote an INTEGER (Long) to another
    > type, preferring to overflow e.g.
    >
    > SELECT 2147483647 + 1 FROM DropMe;
    >
    > Values outside the INTEGER range (from -2,147,483,648 to 2,147,483,647
    > inclusive) in Jet are natively DECIMAL in nature:
    >
    > SELECT 2147483648 AS data_value,
    > TYPENAME(2147483648) AS data_type
    > FROM DropMe;
    >
    > Therefore, as Gunny points out, coercing the values to DECIMAL, by
    > operating using a native DECIMAL value at any stage, means the INTEGER
    > can successfully be promoted to DECIMAL:
    >
    > SELECT (2147483647 + 1) + (0.1 - 0.1) AS data_value,
    > TYPENAME((2147483647 + 1) + (0.1 - 0.1)) AS data_type
    > FROM DropMe;
    >
    > One would hope the parser is smart enough to determine that (0.1 - 0.1)
    > doesn't actually need to be evaluated <g>.
    >
    > OK, on to the oddity. Unlike a certain MVP I could mention, I recently
    > revisited this PIA bug:
    >
    > BUG: You may notice an incorrect sorted order when you sort the
    > negative decimal values in descending order in Access
    > http://support.microsoft.com/default.aspx?scid=kb;en-us;837148
    >
    > [I love the way they downplay the severity of the bug with the casual
    > title].
    >
    > The suggested workaround, 'create an index on the Number field before
    > you sort the records' does not work for me :(
    >
    > I decided to test with values that are natively DECIMAL due to the fact
    > they are out of range for INTEGER i.e. instead of 20, -10, 5, -5, 10
    > and -20 use 3,000,000,020, -3,000,000,010, 3,000,000,005,
    > -3,000,000,005, 3,000,000,010, -3,000,000,020 respectively:
    >
    > SELECT BadSort.dec_data
    > FROM
    > (
    > SELECT 3000000020 AS dec_data FROM DropMe
    > UNION ALL
    > SELECT -3000000010 FROM DropMe
    > UNION ALL
    > SELECT 3000000005 FROM DropMe
    > UNION ALL
    > SELECT -3000000005 FROM DropMe
    > UNION ALL
    > SELECT 3000000010 FROM DropMe
    > UNION ALL
    > SELECT -3000000020 FROM DropMe
    > ) AS BadSort
    > ORDER BY BadSort.dec_data DESC;
    >
    > No such luck: the order is entirely predicable yet wrong :(
    >
    > I then (inadvertently) applied the trick of coercing the already
    > DECIMAL values using + (0.1 - 0.1):
    >
    > SELECT GoodSort.dec_data
    > FROM
    > (
    > SELECT 3000000020 + (0.1 - 0.1) AS dec_data FROM DropMe
    > UNION ALL
    > SELECT -3000000010 + (0.1 - 0.1) FROM DropMe
    > UNION ALL
    > SELECT 3000000005 + (0.1 - 0.1) FROM DropMe
    > UNION ALL
    > SELECT -3000000005 + (0.1 - 0.1) FROM DropMe
    > UNION ALL
    > SELECT 3000000010 + (0.1 - 0.1) FROM DropMe
    > UNION ALL
    > SELECT -3000000020 + (0.1 - 0.1) FROM DropMe
    > ) AS GoodSort
    > ORDER BY GoodSort.dec_data DESC;
    >
    > Surprisingly, the sort order is now correct!
    >
    > My joy was short-lived, though. I discovered the reason: the values had
    > been coerced to FLOAT (Double). In essence it seems to be this:
    >
    > SELECT 3000000020 + (0.1 - 0.1) AS dec_data_value,
    > TYPENAME(3000000020 + (0.1 - 0.1)) AS dec_data_type,
    > TYPENAME(dec_data_value) AS derived_data_type
    > FROM DropMe;
    >
    > Why is this DECIMAL value being coerced to FLOAT (Double)?
    >
    > Perhaps a more practical question would be: did MS ever publish a
    > specification for Jet with a level of detail to include topics such as
    > data type precedence and implicit conversion between types? e.g. the
    > equivalent of this:
    >
    > http://msdn2.microsoft.com/en-us/library/ms187928.aspx
    >
    > TIA,
    > Jamie.
    >
    > --
    >
    >
     

Share This Page