Welcome to SPN

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

Sign Up Now!
  1. Guest ji, please consider donating today!
      Become a Supporter    ::   Make a Contribution   
    Monthly Recurring Target: $300 :: Achieved: $95

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.
    >
    > --
    >
    >
     
Since you're here... we have a small favor to ask...

More people are visiting & reading SPN than ever but far fewer are paying to sustain it. Advertising revenues across the online media have fallen fast. So you can see why we need to ask for your help. Donating to SPN's is vote for free speech, for diversity of opinions, for the right of the people to stand up to religious bigotry. Without any affiliation to any organization, this constant struggle takes a lot of hard work to sustain as we entirely depend on the contributions of our esteemed writers/readers. We do it because we believe our perspective matters – because it might well be your perspective, too... Fund our efforts and together we can keep the world informed about the real Sikh Sikhi Sikhism. If everyone who writes or reads our content, who likes it, helps us to pay for it, our future would be much more secure. Every Contribution Matters, Contribute Generously!

    Become a Supporter      ::     Make a Contribution     



Share This Page