Skip to content

Strange Unwanted and Odd precision

Forums Forums SIMPOL Programming Strange Unwanted and Odd precision

Viewing 8 posts - 1 through 8 (of 8 total)
  • Author
    Posts
  • #3511
    JD Kromkowski
    Participant

    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

    #3514
    JD Kromkowski
    Participant

    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

    #3515
    JD Kromkowski
    Participant

    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?

    #3516
    JD Kromkowski
    Participant

    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.

    #3517
    Michael
    Keymaster

    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.

    #3519
    JD Kromkowski
    Participant

    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.

    #3520
    JD Kromkowski
    Participant

    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

    #3521
    Michael
    Keymaster

    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).

Viewing 8 posts - 1 through 8 (of 8 total)
  • You must be logged in to reply to this topic.