DB query reader float to double mapping modifies approximation

KNIME Extensions
1

Hello everyone,

I am using mysql (in particular Ver 15.1 Distrib 10.1.28-MariaDB, for Win32 (AMD64)) and trying to read from a float type column (a very simple query " select * from ‘name_table’ ") using the DB query reader node.

Since float are not defined in knime, they must be converted to double. And here the problem: all numbers defined under a resolution of 0.25 are wrongly approximated, even if they were already rounded! It took me a while to figure it out, since the standard output visualization render rounds the number to the second decimal and so everything looks fine…

I am attaching a txt file with the compared results:
mapping issues.txt (52.4 KB)

e.g.:
original float —> mapped double
0.010000000 —> 0.0099999998
0.020000000 —> 0.0199999996
…
0.050000000 —> 0.0500000007
…
0.230000000 —> 0.2300000042
0.240000000 —> 0.2399999946
0.250000000 —> 0.2500000000 (correct!)
0.260000000 —> 0.2599999905
0.270000000 —> 0.2700000107
…
0.490000000 —> 0.4900000095
0.500000000 —> 0.5000000000 (correct!)
0.510000000 —> 0.5099999905
…
2.080000000 —> 2.0799999237
3.290000000 —> 3.2899999619
…
13.980000000 —> 13.9799995422
13.990000000 —> 13.9899997711
14.000000000 —> 14.0000000000 (correct!)

I can somehow solve the issue by rounding again the number after the node output, but still it fells wrong…

Thanks

A

2

Thank you for reporting this, I’ve made an internal ticket to have this investigated.

3

Hello @Suvrec ,
the problem will be fixed with the 4.6 release of KNIME Analytics Platform which will be released in summer.
The problem is that Java in general has problems representing some floating point numbers. and float has even more problems doing so than double which leads to the different numbers. For more details on the problem see this article.

Please note that this problem is only related to the JDBC type REAL which should be mapped to Java float. In MySQL the data type FLOAT is mapped to the JDBC type REAL:
image

The database framework allows users to specify how database types should be mapped to KNIME types. With the next KNIME version we add a new rule for the JDBC type REAL, which is the FLOAT type in MySQL, to map to Double cells using float as intermediate format which will prevent the problem of different accuracies.
The mapping via Float will be the default for all newly created database connector nodes. All existing connector nodes will use the alternative mapping via Double as before to maintain backward compatibility. So once 4.6 is out either use a new MySQL Connector node or go to the Input Type Mapping tab of an existing node and change the mapping as shown below:

image
image663×574 18.3 KB

Bye
Tobias

5 Likes