391 ObjectPAL: Decimal digitsVERSIONS: All. Written for Paradox 5
SUBJECT: handling of decimal digits
BY: MARK PAUKER
QUESTION:
I have a file with a single-field primary key. The field is defined as a number. When I do a qlocate from a form, or a ctrl-z from a tableview, the lookup fails on certain numbers. For instance, there are five related records in the file with account numbers ranging from 1912.00 thru 1912.40. When I do a qlocate or ctrl-z on records 1912.00 and 1912.10 pdox finds them with no problem. But when I try to locate 1912.20 or 1912.30 or 1912.40, the system gives me an error saying that the number wasn't found although I know the records are there with those account numbers.
This is not a bug. It is simply what happens when trying to store an 80-bit floating point number as a 64-bit number. Floating point numbers in tables are stored in 64-bits. This has always been the case since the earliest DOS version of Paradox. A 64-bit number has precision of 15 decimal digits. Numbers in PdoxWin are stored in 80-bits, which in turn carry a precision of 18 decimal digits. Thus when placing a number into a table, there must be a loss of precision.
To avoid a loss of precision when storing a value in a table, you could use the currency data type (instead of the number type), which calculates in 18-digit precision, and then immediately rounds to 6 decimal places. Of course, in doing this you would only have 6-digit precision as opposed to 15-digit precision, but the effect of the rounding would be to greatly reduce (if not completely eliminate) the type of problem that Gary is running into. My guess is that most of the products Gary has used in the past have artificially reduced their precision in this way in order to minimize the effect of floating point rounding errors. This approach is neither better nor worse; it is simply another alternative.
I agree that all of this floating point stuff can be pretty annoying, but the fact is that it HAS been around forever (in computer terms), and it is NOT simply a Paradox problem. Paradox simply uses the floating point routines supplied by Intel. IMO, the real problem is one of educating users to understand what "precision" means. The fact that Paradox doesn't support infinite precision means that we cannot simply use equivalence and assume it will work. Instead, we must check to see if the difference between 2 numbers is less than the desired precision (1e-14, for example). We must also realize that the actual precision of our numbers is only as great as the lowest precision of any of the steps. (That is, the 18-digit precision became 15-digit precision when the value was stored in a table.)
What complicates this is that "precision" applies not only to the internal representation of a number, but also to how the number is displayed. The fact is that in the conversion between base 2 and base 10, there is rounding in the _display_ of a number as well. Thus the number "1.1" may actually be stored as "1.10000000000000001", but you wouldn't know it to look at it. Thus we end up with 2 numbers that look identical, but are in fact different.
To solve this type of problem, people have used BCD (binary coded decimal) to store numbers. The problem with this is that Paradox doesn't support BCD math. (By the way, I don't think any other mainstream microcomputer database product supports BCD either.)
I wish people would stop blaming these problems on Paradox. I agree that it would be nice for Paradox to fully support BCD, but it already DOES provide solutions to almost all of these types of problems by supplying a longInt type and a currency type. (Even the number type is "trouble-free" when used as a key field as long as no fractions are used.) It has been suggested that these are "new" problems, or "specific to Paradox." This is simply not true. Paradox uses the microprocessor's floating point routines (defined by IEEE) just like everyone else.
I mean no disrespect, but IMO, anyone who uses fractions as part of their primary key should take a good look at their design. There are times when it may be necessary (I have done it on occasion), but you're really asking for trouble if you don't fully grasp the ramifications of what it means to do so.
Date: 11 April 1996
QUESTION:
Value retrieved from a Number field is not the same as that which was stored. Why is this?
var n1, n2 Number endVar
n1 = 3641448.81554664459
tc.edit()
tc.Password = n1
tc.endEdit()
n2 = tc.Password
n1.view() ; 3641448.81554664459
n2.view() ; 3641448.81554664439
< Help files says >
The range of values possible for a number field is from -10 307 to 10 308 with 15 significant digits.
< /Help files says >
Numbers in ObjectPAL have a 64-bit mantissa and a 15-bit exponent. A mantissa of this size supports about 18 decimal digits of precision.
Numbers stored in tables have a 52-bit mantissa (acting more like a 53-bit mantissa) and an 11-bit exponent. A mantissa of this size supports about 15 decimal digits of precision.
It is important to understand that in this context, "decimal digits" does not refer to digits after the decimal point, but rather to the total number of digits in the number. The number 3641448.81554664459 has 18 decimal digits. When you store it in a table it loses approximately 3 digits of precision, and thus it comes back slightly different. This is pretty much the exact problem that I described in my last message, and more fuel for the argument that the internal format for numbers in ObjectPAL should be the same as the internal format for numbers stored in tables.
Meanwhile, if you really need this precision, you might consider using an alpha field rather than a number.