Jet 4.0 DECIMAL type: sort order bug
Jamie Collins
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
The purpose of this post is to consider whether the following
oft-quoted statement is a correct description of the sort order bug:
"If the [column] is [of] Decimal type and you ask for descending order,
the sorting is wildly inaccurate. This happens in all versions that
have the Decimal field type (Acc. 2000 and later)... Negative values
appear first (wrong), followed by the positive ones. Nulls and zeros
sort unpredictably - at the beginning, middle or end, depending on the
data."
To clarify, the prerequisites for the sort order bug to be exhibited
are as follows:
1) The DECIMAL column in the resultset (as distinct from the set from
which the resultset derives) must include negative values.
2) The sort order must be descending.
3) The sort must be performed by the engine (as distinct from the
middleware/front end e.g. in using the recordset's Sort method, sorting
in the report writer, etc).
Consider the above quote in detail:
1) "Negative values appear first (wrong), followed by the positive
ones."
This is statement is 100% correct and describes the sort order bug in
its entirety.
Consider that a numeric resultset has four mutually exclusive subsets:
positive values; negative values; zeros; null values.
Each subset is sorted correctly: the subset of negative values is
sorted correctly in descending order, as is the subset of positive
values.
The problem is the order of appearance of the subsets. The subset of
negative values appears first and before the subset of positive values,
which is wrong for descending order.
2) "Nulls [..] sort unpredictably - at the beginning, middle or end,
depending on the data."
This would appear to be a misstatement. The sorting of the subset of
null values and its position in the resultset are entirely predictable
and correct.
The subset of null always appears in the correct position in the
resultset, being at the end of the resultset and immediately after the
subset of zeros. There is no evidence I know of to suggest that the
subset null values ever appears anywhere else relative to the other
subsets.
>From "ADO Provider Properties and Settings"
(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/adoproperties.asp):
"NULL Collation Order: A Long value (read-only) that specifies where
Null values are collated (sorted). For the Microsoft Jet provider, the
value is always 4, which indicates that null values are sorted at the
low end of the list."
If there was a sort order bug with *any* Jet data type where the NULL
collation order was violated, it would be far more significant that the
one under discussion that affects only negative DECIMAL values. Such a
bug, should one exist, would almost certainly warrant a MSDN bug
article in itself. However, there is no such article and there is no
evidence I know of to suggest the NULL collation order is violated
under and circumstances.
3) " zeros sort unpredictably - at the beginning, middle or end,
depending on the data."
This would appear to be a misstatement. The sorting of the subset of
zeros and its position in the resultset are entirely predictable and
correct.
The subset of zeros always appears in the correct position in the
resultset, being immediately after the subset of positive values. There
is no evidence I know of to suggest that the subset zeros ever appears
anywhere else relative to the other subsets.
4) "This happens in all versions that have the Decimal field type (Acc.
2000 and later)."
The sort order bug is restricted to the Jet 4.0 engine, the native
engine for Access versions 200, 2001 and 2003. The sort order bug has
been fixed in Access 2007 engine, being a branch of the Jet code base
'private' to the Access team. Bug fixes in the Jet 4.0 main branch
since it was 'deprecated' have been limited to security issues (see:
MDAC 2.8 Overview: Deprecated Components:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/mdacsdk/htm/mdac_deprecated_components.asp).
In other words, a fix in Access 2007 engine is the best we could have
hoped for and we got it.
5) "the sorting is wildly inaccurate"
This is entirely subjective but for me the essence of this bug (that
the correct-ordered subset of negative values appears in the wrong
place relative to the other subsets in the resultset) does make me
think the results are "wildly inaccurate".
In conclusion, I think the above quote (in full) includes misstatements
and/or inaccuracies that contribute to the nature of the sort order bug
being exaggerated.
Feedback on any point made above is encouraged.
Jamie.
--