Android Question Float type is wrong!

[LucaMs]

I read some posts on this topic.
I have always read that it is not in the internal representation.

I rather think so.

Create a SQLite table with a field of type:
test 1) Column data type = NUMERIC
test 2) Column data type = TEXT

Insert some values:

Private Query As String
Query = "INSERT INTO TB (Price) VALUES (?)"
Price = 10.1
DB.ExecNonQuery2(Query, Array As Object(Price))

You'll get:

1) NUMERIC - NOT ROUNDING
Listview data (loaded from DB): correct
DB data: WRONG

1a) NUMERIC - ROUNDING INTO THE QUERY
Listview data (loaded from DB): correct
DB data: correct

1b) NUMERIC - ROUNDING THE FLOAT VALUES SETTING THE VARIABLES
Listview data (loaded from DB): correct
DB data: WRONG

-------------------------------------------------------------------

2) TEXT - NOT ROUNDING
Listview data (loaded from DB): WRONG
DB data: WRONG

 

[Erel]

Can you upload a simple project that demonstrates this issue?

 

[LucaMs]

I should post more than one project, because they are tests, one is without rounding, another with it, one with the db field as NUMERIC, another as TEXT. One saves data using array of OBJECT, another with array of STRING and so on.

Anyway...

[Data that the DB "has received"

]

 

[Erel]

There is no issue here. A binary floating point type cannot represent accurately decimal numbers. You should use NumberFormat to convert a floating point number to a string.

 

[LucaMs]

I don't agree, sorry.

I've read many times that answer.

We're not talking about strings.

However, I will have to perform other tests.

Now I can not.

 

[Erel]

The first thing to do in order to show any issue is to remove the database related code.

 

[LucaMs]

The first thing to do in order to show any issue is to remove the database related code.

???

Private flt As Float
    Private dbl As Double

    flt = 1.123456789123456789123456789123456789
    dbl = 1.123456789123456789123456789123456789

    Log(flt) ' 1.1234568357467651 so its precision is 6 decimals
    Log(dbl) ' 1.1234567891234568 so its precision is 15 decimals

    flt = 0
    For i = 1 To 99999
        flt = flt + 0.1234
    Next
    Log(flt) '                    12330.8564453125
                ' rapid debug :   12330.856
                ' Win calculator: 12339.8766

 

[LucaMs]

???

Private flt As Float
    Private dbl As Double

    flt = 1.123456789123456789123456789123456789
    dbl = 1.123456789123456789123456789123456789

    Log(flt) ' 1.1234568357467651 so its precision is 6 decimals
    Log(dbl) ' 1.1234567891234568 so its precision is 15 decimals

    flt = 0
    For i = 1 To 99999
        flt = flt + 0.1234
    Next
    Log(flt) '                    12330.8564453125
                ' rapid debug :   12330.856
                ' Win calculator: 12339.8766

0.1234 * 99999 is too much for float vars, my error!

 

[Mahares]

Going back to your DB example: If you declare price as REAL, the 5 numbers you have on your screenshot will display the same way in the database as on the listview. PRICE REAL in table: 10.1 10.15 2.2 20.2 20.25

 

[LucaMs]

Strange, because there is not REAL type in SQLite.

If you try to use SQLite Database Browser, you don't have that type, as you know.

I'll try, thanks, Mahares

 

[klaus]

If you look at the SQLite documentation you should use REAL:

 

[LucaMs]

Evidently REAL is a type added after the development of SQLite Database Browser; it does not allow you to enter new fields of that type.

From the point of view of the database, however, there would be many possibilities, even without the REAL (I'll try them).

My concern was about the internal calculations in B4A.

I think there is no problem.

 

[Informatix]

There is no issue here. A binary floating point type cannot represent accurately decimal numbers. You should use NumberFormat to convert a floating point number to a string.

I explained in this post that Log can return a wrong value with Float values because these values are converted to double, which is not a neutral operation. NumberFormat has the same problem as it seems to be based also on NumberToString.

In Java:

float F = 0.123456789f;
Log.i("B4A", "F=" + F);
Log.i("B4A", "F*F=" + (F*F));
double dFF = F*F;
Log.i("B4A", "dFF=" + dFF);

-> F=0.12345679
-> F*F=0.015241579
-> dFF=0.01524157915264368
If I compute the square root of 0.01524157915264368 (dFF) with my Windows Calc, I get 0.12345679062993529802282045525767, which is close to the value returned by the Java Log function for F. If I compute the square root of 0.015241579 (F*F), I get 0.12345679001172839501117129625054, which is even closer. So the Java Log seems to return the internal value reliably and we can see that converting to double introduces a very slight difference.

Now in Basic4Android v3.82:

Dim F As Float = 0.123456789
Log("F=" & F)
Log("F*F=" & (F*F))
Dim dFF As Double = F*F
Log("dFF=" & dFF)

-> F=0.12345679104328156
-> F*F=0.01524157915264368
-> dFF=0.01524157915264368
The returned values for F and F*F are different from the previous results. I can clearly see that F*F is converted to double as it returns the same value as dFF.
With F = 0.12345679104328156, as reported by the B4A Log, I should get something close to 0.01524157925470448601602121343603 for F*F but it's not the case. So the Log function does not reflect the internal value reliably.

EDIT for quick readers: this post is not about the accuracy of Float values. It is about the difference between the value stored internally (which is not accurate as you can see above; neither Java nor B4A return 0.123456789 for F) and the value returned by Log or NumberFormat.

For numbers with a few decimals, like 3.1 or 0.123, having Log returning 3.1 or 0.123 is not exact as these numbers are not stored internally with this accuracy. But returning 3.0999999046325684 or 0.12300000339746475 is disturbing as these numbers are obviously not float values (too many decimals). If we round them with the precision of a float value (32 bits, about 7 significant numbers), then the closer results are 3.1 and 0.123. And, as I proved above, converting float numbers with a lot of decimals to double adds the unaccuracy of the float type to the unaccuracy of the double type. So, in all cases, IMHO, values should not be converted.

 

[Erel]

The float to double conversion is not related to the issue LucaMs has described.

I've ran some tests and the conversion actually adds precision in most cases.

You can test it with this code:

public static void main(String[] args ) {
     Random r = new Random();
     int count = 0;
     for (int i = 0;i< 10000;i++) {
       float f = r.nextFloat();//0.123456789f;
       BigDecimal bd = new BigDecimal(String.valueOf(f));
       BigDecimal bdd = bd.multiply(bd); //most accurate
       BigDecimal bddf = new BigDecimal(String.valueOf((double)f * f));
       BigDecimal  bff = new BigDecimal(String.valueOf(f * f));
       //the next line will return -1 if bddf is closer to bdd compared to bff and bdd
       count += bdd.subtract(bddf).abs().compareTo(bdd.subtract(bff).abs());
     }
     System.out.println(count);
   }

Also note that java.text.NumberFormat.format which is the "correct" way to convert a number to a string accepts a double or a long number. So the float number will be converted to double anyway.

 

[LucaMs]

To conclude:

To make internal calculations I can safely use the Float (within the limits of its accuracy) and to save them in the DBs I should use TEXT and NumberFormat (I still have to test the REAL).

Is it right?

 

[LucaMs]

Tested REAL:

If I use that type and save normally

Price = 2.20
DB.ExecNonQuery2(Query, Array As Object(Price))

DB data are still wrong

 

[LucaMs]

To conclude:

To make internal calculations I can safely use the Float (within the limits of its accuracy) and to save them in the DBs I should use TEXT and NumberFormat (I still have to test the REAL).

Is it right?

No, it is not right!

NumberFormat requires the minimum number of integer, prefixing zeros; as a result, reading the data I would be forced to use a function to delete them!

 

[LucaMs]

No, it is not right!

NumberFormat requires the minimum number of integer, prefixing zeros; as a result, reading the data I would be forced to use a function to delete them!

I used -1 for MinimumIntegers and now it works fine !

 

[Informatix]

The float to double conversion is not related to the issue LucaMs has described.

I've ran some tests and the conversion actually adds precision in most cases.

You can test it with this code:

public static void main(String[] args ) {
     Random r = new Random();
     int count = 0;
     for (int i = 0;i< 10000;i++) {
       float f = r.nextFloat();//0.123456789f;
       BigDecimal bd = new BigDecimal(String.valueOf(f));
       BigDecimal bdd = bd.multiply(bd); //most accurate
       BigDecimal bddf = new BigDecimal(String.valueOf((double)f * f));
       BigDecimal  bff = new BigDecimal(String.valueOf(f * f));
       //the next line will return -1 if bddf is closer to bdd compared to bff and bdd
       count += bdd.subtract(bddf).abs().compareTo(bdd.subtract(bff).abs());
     }
     System.out.println(count);
   }

Also note that java.text.NumberFormat.format which is the "correct" way to convert a number to a string accepts a double or a long number. So the float number will be converted to double anyway.

It was very late last night when I published my post and I should have explained things differently and probably thinked twice.
A lot of B4A users judge float values according to what Log returns and that's the case in this thread hence my post. Some of these users are aware that the Float type does not store with accuracy the fractional part of their numbers. But how many know that the value returned by Log is different from what a Java user would get for the same number? Who is right in this case? Does the conversion to double made by B4A brings something useful? Is it free of issues? I'm not mathematician so my approach to understand this problem and find an answer was probably wrong.
In my post, I chose 0.123456789 because it has more decimals than a float value can handle. It's not what your test code above is able to reproduce. But I was maybe lucky with all numbers that I tried and, finally, what a lot of other numbers will show is just the distance between the real internal value and the rounded value used to represent it. As far as you do not exceed the number of significant numbers of the double type, the conversion to double is safe but a very important point that I wanted to say is that returning "0.10000000149011612" for 0.1f gives the false impression that "0.10000000149011612" is the exact internal value, something that you can trust. In fact, it is as wrong as returning "0.1". Do a loop to add this number to itself 1000 times. Logically, you should get something like 100.00000149011612. Of course, it's not what you get (100.09904...) and it's worse with only 100 iterations (10.1000...), so the double representation is useless and gives a false impression of accuracy.
Sorry if my considerations about float and Log "polluted" this thread.

 

[LucaMs]

I should delete all yours posts, Erel & Informatix