Strange Unwanted and Odd precision

[JD Kromkowski]

I have a long used database. In SB the field looked like this:

Amount;NUM ;$999999.00 ;8 ;0 ;15 ;1 ;10 ;

The figures were not calculated. they were just manually entered.

It was imported a long time ago into SIMPOL.

It is a NUM field. The display is 99999.00

Everything works fine. Except when I started using arrays to hold the data (in order to calculate averages, medians, mins and maxs, etc.)

In the array, some (not all) of the data exhibit bizarre precision whose origin is a mystery.

For example, I get this for one value.

20513.24000000000160071067512035369873046875

Why? Where did all of the extra digits come from?

If I export the database to a csv, I don’t get these values. I get 20413.24 which is what was entered.

If I use filter (“=” button) (or run a quick report) and

MYDATA.”Amount” = 20513.24 I get no results

But if I make the filter

MYDATA.”Amount” > 20513.24 AND MYDATA.”Amount” < 20513.25

I will get result.

Other than having put data into array and looked at it, there is no way that I could have found this problem. (Except now I have found that if I make display scientific notation, I can find)
There is no way to easily fix this in Personal, because of limitations to Update (correct?)

Thoughts

• This topic was modified 7 years, 3 months ago by JD Kromkowski.

[JD Kromkowski]

is this floating math which I always forget and then remember every 4 years. https://docs.python.org/release/2.5.1/tut/node16.html

If so there is still a problem when working with databases. If I search or set a filter for mydata.myfield == 20513.24 I need to find that record! even if 24/100 in binary is some other number (invisibly really close).

JDK

[JD Kromkowski]

This a version of the axiom: never store things in float unless you are a physist looking for the Higgs Boson.

So what is the solution? Again?

[JD Kromkowski]

sorry for careless use of word “float”, when maybe I meant double or long double.

We don’t really have a type like DECIMAL(n,2) or DECIMAL (n,4). Do we? SB may have stored in float or double or long double, but when we search (do comparisions) we never had the problem of not finding the correct record. So what am I supposed to do? To do it correctly in SBNG.

[Michael]

Hi John,

PPCS stores values in the same way that Superbase Classic does (100% compatibility), which means that NUM values are a C-language floating point double. Superbase NG stores number types as fractions, ensuring 100% precision. The problems arise when you try and use NUM fields as indexes, since the values get converted back and forth from exact precision to IEEE floating point.

Essentially, using indexes on number fields is probably a mistake. We are planning a new multi-user database engine that will use TCP/IP and which will support all of the new Superbase NG datatypes, but until that is available, I would suggest avoiding the use of number data types for indexes (especially if you are using PPCS). There are workarounds, such as fixed length text fields, but they have their own issues.

[JD Kromkowski]

I understand issues with indexes on number field. But how to a search for or do reports involving (like most businesses) currency.

Say there is an invoice for 99.68 (< this is what I entered)
What is actually saved is 99.68000000000000682121026329696178436279296875

If I run a report (or filter) BILLS.”BillTotal” = 99.68, I will get no records!
This didn’t happen in SB, did it. Because I never remembered this problem.

So what do I do?

I’ve started manually trying to fix records by setting display to scientific notation so I can find and then changing 99.68000000000000682121026329696178436279296875 to 98.68.

I can’t use round() or .fix to update or calculate columns in a report or filter.

Even multiplying by a 100, doesn’t correct the problem.

[JD Kromkowski]

Here is how I fixed the problem (who knows what I did so many years ago to create the issue in SB and or what happened when data was converted):

sbme =@ sbme1.new(“C:\Users\Public\Documents\SIMPOL Office\mysbme.sbm”, “O”, error=e)
if sbme =@= .nul
s = “Error number ” + .tostr(e, 10) + ” opening the SBME file”
else
tablename =@ sbme.opentable(“MYTABLE”, error=e)
if e
s = “Error number ” + .tostr(e, 10) + ” opening the table{d}{a}”
else
sbindex =@ tablename.firstindex
sfieldnameX = “MyField”
sbfieldX =@ getfield(tablename,sfieldnameX)
fieldtype = sbfieldX.datatype
r =@ tablename.select(lastrecord=.false, locktype = “exclusive”, error = e)
recount = tablename.recordcount()
while count < recount
data[count] = r.get(sbfieldX)
s = .tostr(data[count],10)
if .len(s) > 10
testnumber = .fix(data[count],100)
r.put(sbfieldX,testnumber)
r.save(“”, error=e)
sbme.commit(error)
data[count] = r.get(sbfieldX)
end if
r =@ r.select(previousrecord=.false, locktype = “exclusive”, error = e)
count = count + 1
end while (e == 64)
end if
end if
end function s

[Michael]

One thing to try is to use .fix() when assigning currency values and to limit it to 2 (or even 4) decimal places. When dealing with currency another approach is to use an integer field and then always multiply the currency values by 100 when saving and divide by 100 when retrieving (plus using .fix() after dividing).

[Author]

Posts