DECIMAL oddity

J

Jamie Collins

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.

--
 
G

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 said:
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.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Double oddity 6
Decimal places in charts 4
SQL Server SP equivalent 5
Crosstab query oddity 5
Some advice? 3
Download the JAVA , .NET and SQL Server interview with answers 2

Top