Some decimal numbers are read from the database with extra decimal numbers

[pablopen]

Some decimal numbers are retrieved from the database with extra decimals.

If we define a column as Number(18,6), and insert this values:

235.772
235.944
236.644

and then we query the database, we get the following values:

235.77200000000002
235.94400000000002
236.644

Seems that oracledb is messing with the decimal parts of some numbers, when parsing then into a javascript number (the integer part looks irrelevant, 20.772 is also affected by the same issue)

Setup:

1. What is your version of Node.js? v4.6.2

2. What version of node-oracledb are you using? 1.13.1

3. What is the version of your Oracle client (e.g. Instant Client)? 12.2.0.1.0

4. What is the version of Oracle Database? Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

5. What is your OS and version? ubuntu 16.04 LTS

[anthony-tuininga]

Take a look at issue #665. The short answer is that Oracle numbers are decimal numbers and JavaScript is using floating point numbers. It is impossible for some decimal numbers to be represented in floating point numbers, and not all methods of generating these representations are the same. Your only option is to use fetchAsString and use a decimal number library in JavaScript if you want to avoid this. :-)

[cjbj]

Also check the manual:
https://github.com/oracle/node-oracledb/blob/master/doc/api.md#numberhandling
and
https://github.com/oracle/node-oracledb/blob/master/doc/api.md#fetchasstringhandling

[viniciustg]

Thank you!

[andriusphoto]

Hi,
our problem is a little different. We pass to the DB function float as string "2.3" but DB gets 2.3000000000000003. What a solution to the best in such a situation?

[anthony-tuininga]

Can you provide a simple test that demonstrates the problem? If you are actually passing the value to the database as a string that shouldn't happen. Are you checking the result in SQL*Plus or are you fetching that value back again in node-oracledb? If you are fetching, make sure you do to_char(number_value) in your query to avoid the issue that we have been discussing!

[andriusphoto]

Problem exists only when passing '2.3' to database PL/SQL function (which inserts/update data).
We passing string '2.3' to PL/SQL function's parameter with type number.
For example we are passing to parameter p_shp_work_hours => '2.3'

  function save_shift_plans(p_shp_id in number,
                            p_shp_record_version in number default 1,
                            p_shp_date in date,
                            p_shp_work_hours in number);

When data getting from database results is OK, if in database table value is 2.3 we get 2.3, if in database value is 2.3000000000000003 then we get 2.3000000000000003. There are no problems here.

[cjbj]

@andriusphoto can you open a new issue? And please give us some running Node.js code and the SQL to create the DB schema. And explain how you are checking the result. Thanks!

[edfbarker]

We have cases where columns intended to represent monetary values have been defined as Number, and decimal values have been inserted into these fields that include high precision. This causes problems for code that retrieve these later. Asking the driver to treat these as strings does not work for us because the large volume of calling code expects these values to be numbers and to be able to perform additions and subtractions with them. We would have to adjust all of this code to convert the strings to numbers.

I was hoping to find a way to get the oracledb driver to consider the scale of a number when extracting it from the DB, and adjust the number accordingly to eliminate any excess caused by the inherent issues with Javascript number representation. I understand that Javascript has limitations on how precisely it can represent decimal numbers. My desire here is to use the stated scale in the column definition to approximate the numeric value at that specific precision.

For example, if we had inserted a value of 2.30 (two dollars and 30 cents), and that was stored in the DB as 2.3000000000000003, upon extraction we would get 2.3 assuming the column was defined as Number(10,2).

I have tried to instrument the wrapper that we have around oracledb to look at the result set, and for the fields in our schema that are dollar values, perform a conversion:

value = Number(row[dollarFIeld].toFixed(2))

so converting it to a string with a fixed scale of 2, and then converting it back to a number. This works perfectly, but the performance impact is not manageable when our results sets are large.

I see that in SQLDeveloper, it appears to use the definition to adjust what is shown. A value in the DB such as 2.3000000000000003 is shown as 2.3000000000000003 if the column is defined as just Number, but is shown as 2.3 if the column is defined as Number(10,2). When I pull these same values via the Oracle driver, they both show as 2.3000000000000003, so presumably SQLDeveloper is considering the scale but the Oracle driver is not.

My guess is that Javascript, when assigning a floating point number to a variable, or converting a string to a number via the Number function (not constructor) is able to preserve the scale that was indicated by the literal number or string, but when any arithmetic is done, the intended scale is no longer clear so the result of that operation now has a large scale.

For example:

let x = 0.1
let y = 0.2
console.log(x + y)
0.30000000000000004
let z = Number((x+y).toFixed(2));
console.log(z);
0.3

Similar to the example above, the Oracle driver could use the scale in the column definition to call the Number function on columnValue.toFixed(columnScale)

[cjbj]

I opened an issue for you.