JavaScript & Oracle Number handling: two connections with differents fetchAsString parameters

[atiertant]

how to create two connections: first with fetchAsString on numbers and second without any ?

[sagiegurari]

you can set those up on every connection.execute call

[dmcghan]

@atiertant See the fetchInfo section of the doc.

[atiertant]

@sagiegurari connection.execute is asynchronous, are you sure this is working if there are many request at the same time?

@dmcghan this is for explicit column not for every one like fetchAsString

[dmcghan]

@atiertant That's true, there's currently no generic solution at the execute level. Could you please explain the use case? Why does fetchAsString not work for you?

[atiertant]

@dmcghan first problem was node-oracledb don't bind float correctly, to avoid this problem, we used fetchAsString but for integrate this in knex we need to write unit test, the fetchAsString test is working, but other test fail because they sould not return string...
the problem is getConnection() doesn't configure fetchAsString in connection, it use the module global array...

[dmcghan]

@atiertant

first problem was node-oracledb don't bind float correctly

Did you log an issue? Do you have a test case? It sounds like this is the real problem you're having so I think it's best we look at that.

[cjbj]

The attribute hierarchy change request is tracked in #361

At a guess, any number 'issue' is an expected artifact of conversion to/from different binary representations.

[atiertant]

@cjbj
console.log(0.2 + 0.7); // gives 0.8999999999999999
but console.log(0.9); //gives 0.9
so this could be represented in javascript number...
where does this binary conversion is done in javascript code?

[bjouhier]

@atiertant See http://floating-point-gui.de/basic/

[atiertant]

@bjouhier java and many other languages have same precision in operations:
console.log(0.2 + 0.7); // gives 0.8999999999999999
but
console.log((2 + 7) /10 ); // gives 0.9

see https://github.com/MikeMcl/bignumber.js/

there are solutions ...

[bjouhier]

Of course but JavaScript only has one number datatype, and it's an IEEE 754 floating point.

[atiertant]

@bjouhier problem is not in javascript Number datatype (this is a 64bits signed number)

var a = 0.9;
console.log(a); // gives 0.9

only mathematical operations loss precision with floating point but you can perform them with integer and divide result after...
console.log((0.2 * 10 + 0.7 * 10) /10 ); // gives 0.9
here + is performed on integers so no problem with precision...

[bjouhier]

@atiertant Sorry to contradict you but the problem is in the JavaScript Number datatype. This datatype is an IEEE 754 floating point. See https://www.ecma-international.org/ecma-262/7.0/index.html section 4.3.21

When you write 0.9 this is not exactly 0.9, this is the floating-point number which is closest to 0.9 (and there is some magic in the number to string conversion to format it as 0.9).

[atiertant]

@bjouhier your right, it's not exactly 0.9 in the value binary representation, that's why we lose precision.
but the stringified value should be correct, this one is used by every javascript mathematical libraries and in every displayed value... so this could be fixed using javascript Number datatype.

[bjouhier]

If you want to do precise arithmetics with decimals you should not use JavaScript numbers. Instead you should use a big decimal library like the one you mentioned above. But you should manipulate the values only as instances of the Big class or as strings. Don't convert them back and forth to javascript numbers.

When reading or writing to Oracle, you should pass the values as strings, not numbers.

Oracle numbers can have up to 38 decimals of precision (and, unlike JS numbers, they represent these numbers exactly). A 64 bit integer has 19.27 decimals of precision but a JS number only has 15.95 decimals of precision. So you're heading for trouble it you try to map high precision database values to JS values, even integer values that would fit into a 64 bit int. Try console.log(+"12345678901234567").

[dmcghan]

@atiertant The problem is the JavaScript Number type. The stringified value is sometimes rounded. We can't depend on that.

A JavaScript decimal type recently came up in a twitter discussion from one of the tc39 folks:
https://twitter.com/code_barbarian/status/850942340873060352

If JavaScript gets a decimal type, then we can look at using that instead.

[atiertant]

@dmcghan if the problem was the JavaScript Number type why must i use a fetchAsString then parsefloat that return a JavaScript Number ???

console.log(Number(0.2 + 0.7) !== Number(0.9)) // gives true
console.log(Number(0.9) !== Number(0.9)) // gives false

maybe the max oracle database precision could not be handled with JavaScript Number type but at now node-oracledb do worst than javascript can...

[bjouhier]

Don't convert to JS number (don't use parseFloat)! Convert from string to Big number (from 3rd party library) and do all the computation with Big numbers.

[dmcghan]

@atiertant fetchAsString with parseFloat is not fixing the problem, it's masking it. If you tried to do arithmetic with the result this would become more obvious.

let x = parseFloat('.1');
let y = parseFloat('.2');

console.log(x); // 0.1
console.log(y); // 0.2
console.log(x + y); // 0.30000000000000004

We can't depend on rounding display values, we need a decimal type.

In the meantime, decimal arithmetic should be done in Oracle and the results brought through as strings to be displayed. As for your library, I'd recommend not trying to fix this on your own.

[bjouhier]

You are fooling yourself into thinking that JavaScript can do a good job with decimal numbers. JS numbers only represent a very small subset of decimal numbers exactly. Even numbers as simple as 0.1 or 0.2 are only approximations (but 0.125 is exact!). If you want to do decimal arithmetics, stay away from JS numbers and use a big decimal library.

The problem has nothing to do with the oracledb driver. It has to do with JavaScript itself.

[atiertant]

@dmcghan i use mathjs for mathematical operation.
i need fetchAsString because node-oracledb can't assign number correctly, javascript number type can't do mathematical operation with precision, it's ok (and out of node-oracldb scope).

but javascript can handle stringify number correctly, and using them with mathjs (stringifing them and using bigNumber internaly) but those caming from node-oracledb not, because the stringified value is wrong!
parseFloat just create the same number with the correct string value...(i only use 2 digits)

if you used integer value in your conversion algorithm and divide it at end, you should have a correct string value.
console.log((0.2 * 10 + 0.7 * 10) /10 ); // gives 0.9

[atiertant]

@bjouhier wouldn't like to do decimal arithmetics, just have a correct value on assignment, this is a fact: number caming from database could be assigned correctly because parseFloat do, sure there are some case where it could not work but 2 digits ...

[bjouhier]

@atiertant Why don't you use fetchAsString and then math.bignumber(str) with the strings that you obtain? The whole point behind fetchAsString is to give you a reliable method to pass decimal values back and forth between Oracle and the decimal library of your choice.

For the fun of it I tried to find out how many numbers in the 0.000000, 0.000001, 0.000002, 0.000003, ... 0.999999 series are exact in JavaScript. It's only 64 out of 1,000,000! With 2 digits, it is only 4 out of 100: 0.00, 0.25, 0.50 and 0.75. All the others numbers are approximations.

[bjouhier]

@atiertant To understand your problem better I have a few questions:

• What is the datatype of your oracle column?
• What is the value inside oracle?
• Which value do you get with and without the fetchAsString option?

[atiertant]

hope this to be more clear:

var oracledb = require('oracledb');

oracledb.getConnection({
  user: "user",
  password: "password",
  connectString: "ip/db"
}, function(err, connection) {
  if (err) {
    console.error(err.message);
    return;
  }
  connection.execute("SELECT 0.235 from DUAL", function(err, result) {
    if (err) {
      console.error(err.message);
      doRelease(connection);
      return;
    }
    console.log('oracledb', result.rows[0].toString()); // oracledb 0.23500000000000001
    var a = parseFloat('0.235');
    console.log('parseFloat', a); // parseFloat 0.235
    console.log('is equal', a.toString() === result.rows[0].toString()); // is equal false
    doRelease(connection);
  });
});

function doRelease(connection) {
  connection.close(function(err) {
    if (err) {
      console.error(err.message);
    }
  });
}

as you can see string value is wrong...but javascript can assign this value

[bjouhier]

I tried some variants and dived a bit into the source of the driver.

Interestingly, SELECT CAST(0.235 as BINARY_DECIMAL) from DUAL gives 0.2349999999999999 instead of 0.23500000000000001.

I did not see any special parsing in the oracledb driver source code. It just transfers the values between OCI and V8 (https://github.com/oracle/node-oracledb/blob/master/src/njs/src/njsConnection.cpp#L4047 for example). The conversion between the oracle number and double must be inside OCI and may differ from what a you get by first converting to string, and then parsing with parseFloat (which would be inefficient).

It is very likely that you will get the same discrepancy if you call OCI from C and retrieve Oracle decimal numbers into C double variables.

Morale: to get reliable results with decimal values, always fetch them as strings.

[atiertant]

so this is an oracle(maybe in OCI) bug
is there any chance to see this fixed a day?

Morale: to get reliable results with decimal values, always fetch them as strings.

so, to get reliable results, do not use oracle methods, rewrite your own...

[bjouhier]

so, to get reliable results, do not use oracle methods, rewrite your own...

Not quite. OCI has a proper API to retrieve numbers with full precision: https://docs.oracle.com/cd/B12037_01/appdev.101/b10779/oci11oty.htm#434061. The problem is that JavaScript does not have any built-in type for decimals, just a floating-point number type. A .NET driver, for example, would be able to do exact conversions to .NET decimals.

An alternative would be to expose the OCI Number class to JavaScript. I know this has been suggested but I don't think it has been implemented so far.

[atiertant]

@bjouhier read the issue

[bjouhier]

@atiertant I understand your issue. You don't like the fact that reading decimals as numbers sometimes gives values that are a bit off. But this is a well known issue; this is covered by the documentation and there is an option (fetchAsString) which is specifically designed to solve this problem. Why don't you use it?

I tried to help you and dig to give you an explanation where this could come from (I'm curious too). I cannot do much more.

[atiertant]

@bjouhier the only thing i don't like is saying "The problem is the JavaScript Number type" in this case, this is wrong! so oracle could reconize it's a bug in their code, they fix or not but stop saying this is not possible in javascript...
would be curious too to know what is contain in *(double *)bind->value

there is an option (fetchAsString) which is specifically designed to solve this problem.

this doesn't solve the problem, this is a trick to avoid the problem.(but haven't got choice)

that said ! for now, i need fetchAsString to get exact number but an other problem is that fetchAsString is a module global parameter so can't have two connections with different parameters.

[bjouhier]

so oracle could reconize it's a bug in their code

That's Oracle's call, not mine. I don't work for Oracle.

[bjouhier]

From the doc:

Individual query columns in execute() or queryStream() calls can override the fetchAsString global setting by using fetchInfo.

Maybe this can help you handle multiple connections. This is how we handle this issue.

Otherwise, I'm pretty sure that the*(double*)bind->value contains the bad value. To check it you can just add a printf and recompile the driver.

[anthony-tuininga]

As stated many times already, the problem goes to the fact that decimal numbers can only be approximated in floating point. Arguing about which approximation is "correct" seems pointless since neither of them are accurate. Both are approximations! The only advantage that the JavaScript approximation has over the Oracle approximation is that it is consistent with itself. If, however, you attempt to perform any arithmetic with those floating point numbers the inaccuracy will become obvious soon enough -- again, as has been shown above.

The real solution is to use decimal numbers in JavaScript as well -- and there are a number of third party libraries that support them. But in order to use those you have to use fetchAsString and then iterate over the rows returned to transform those strings into the decimal numbers -- which incurs a great deal of overhead and is disruptive to the code. One possible solution to this problem that I have considered is the introduction of a "hook" that could be specified at various levels (globally, per connection, per bind variable). This hook would be a callable that would accept one argument (the value that would ordinarily be returned by node-oracledb) and would return the value that should be returned instead. This would allow the use of any decimal number library (or even parseFloat() for those who prefer it!) without having to massage the output from the node-oracledb library. It could of course be used for other purposes! Does this sound like a good idea? Like it might resolve your issue? Thoughts welcome!

[dmcghan]

The only advantage that the JavaScript approximation has over the Oracle approximation is that it is consistent with itself.

I would add one more advantage, which is @atiertant's main use case and a common one for Node.js in general: REST APIs that output JSON. In this case, you're not planning on doing arithmetic, you're just sending the data along for another system to leverage. Sending the number out as a string is often undesirable (though is still needed for really large numbers).

One possible solution to this problem that I have considered is the introduction of a "hook" that could be specified at various levels (globally, per connection, per bind variable).

To better illustrate this concept (though the final API could be different) here's an example:

oracledb.fetchMappings = [
  {
    from: oracledb.NUMBER,
    to: oracledb.STRING,
    converter: parseFloat
  }
]

[bjouhier]

Arguing about which approximation is "correct" seems pointless since neither of them are accurate. Both are approximations!

Right. JavaScript uses the IEEE 754-2008 “round to nearest, ties to even” rounding mode. Libraries written in other languages (like OCI) may use a different rounding rule when they convert decimals to floating-point. This cannot be qualified as an OCI bug because OCI may be called from different languages which may have different rules (or no rule at all, leaving this up to the compiler!).

The oracledb driver could bypass the decimal-to-double conversion done by OCI, to align strictly on the JavaScript rule (easy, just call parseFloat(fetch_as_string_value)). But this is not free (in CPU cycles) and it is a bit of a mirage (as values are approximations anyway). IMO, not worth it.

@anthony-tuininga The current API does the job for us but I can see the benefit of having a hook. What seems to be lacking most is a way to configure the mapping at the connection or pool level.

[anthony-tuininga]

@anthony-tuininga The current API does the job for us but I can see the benefit of having a hook. What seems to be lacking most is a way to configure the mapping at the connection or pool level.

Thanks for the feedback. I agree that being able to configure the mapping at a connection or pool level would be a good thing to have. What sort of behaviour would you like to see? One possibility would be to extend fetchInfo and permit the column name to be a data type as well, as in this:

fetchInfo: { oracledb.NUMBER : { type : oracledb.STRING } }

And if the hook was implemented it could look like this:

fetchInfo: { oracledb.NUMBER : { type : oracledb.STRING, converter = BigNumber } }

This format could then be used globally, at the connection level or at the execute level. At the connection level, if the attribute is not set by the user, the global value would be used. Does that seem reasonable to you?

[bjouhier]

@anthony-tuininga API looks reasonable except for the mix of keys (column names and datatype constants) under fetchInfo. I'd rather introduce a different key for datatypes, something like fetchTypes.

[anthony-tuininga]

@bjouhier Thanks. We'll keep that in mind.

[bjouhier]

@anthony-tuininga One potential issue with hooks might be performance: every call to the hook will have to go through the C++/JavaScript boundary. This may be more costly than returning all the values as strings and transforming the result's rows in JS afterwards.

[anthony-tuininga]

@bjouhier Good point. We'll need to test that. Thanks.

[atiertant]

As stated many times already, the problem goes to the fact that decimal numbers can only be approximated in floating point. Arguing about which approximation is "correct" seems pointless since neither of them are accurate. Both are approximations!

@anthony-tuininga don't confuse binary decimal value used for arithmetic and string returned by .toString() ... the problem here is not how arithmetic could be done with this numbers
but what .toString() return in this case there no approximations...

The only advantage that the JavaScript approximation has over the Oracle approximation is that it is consistent with itself.

there is no advantage to JavaScript approximation or an other but this is a JavaScript driver !
and in JavaScript arithmetic are approximations but .toString() is not...
so you can use any JavaScript arithmetic library using .toString() value.(this is the JavaScript way to do)

this is an OCI bug or a node-oracledb buggy use of it because C++ double value should be exact and then nan will handle convertion correctly for node.js

about hook, i would prefer the fetchAsString at connection level.
the problem with fetchAsString is for integer, i wouldn't like them to be fetchAsString...
@bjouhier the problem with fetchInfo is that you need columnName/type handle before all execute.(not possible in knex because it's a query builder not an orm)

[bjouhier]

@atiertant

JavaScript arithmetic are approximations but .toString() is not.

toString does not print the exact value, it does rounding. The exact value is the value encoded by the binary representation. Some exact values:

0.1: 0.1000000000000000055511151231257827021181583404541015625
0.2: 0.200000000000000011102230246251565404236316680908203125
0.3: 0.299999999999999988897769753748434595763683319091796875
0.4: 0.40000000000000002220446049250313080847263336181640625
0.5: 0.5 (this one is exact!)
0.6: 0.59999999999999997779553950749686919152736663818359375

[bjouhier]

@atiertant One clarification: I don't completely disagree with you.

You have a point: the oracle JS driver could do a better job at converting oracle decimals to JS numbers. But there is a cost and it would not solve all the problems that fetchAsString solves.

The problem is that your phrasing (.toString is not [an approximation], C++ double value should be exact, there is no advantage to JavaScript approximation, ...) is too approximative (when not wrong) and complicates the discussion.

A good article: http://blog.reverberate.org/2016/02/06/floating-point-demystified-part2.html. It explains the advantage of the JavaScript rounding rule (in comparison to Lua).

[atiertant]

@bjouhier one more time you're right ! but ...
javascript maximum number of decimals is 17 so last one is rounded, then 16 digits precision could be handled by JS number and upper should be fetchAsString

$ node
> 0.1234567890123456
0.1234567890123456
> 0.12345678901234567
0.12345678901234566
> 0.123456789012345678
0.12345678901234568
> 0.1234567890123456789
0.12345678901234568

am i right ?

[bjouhier]

Yes, fetchAsString is required if you want more than 16 digits (precision is actually a bit less, 15.95). Below you could do without, especially if you don't compute but just expose the data through a REST service.

[atiertant]

@bjouhier from 2 digits not working now on node-oracledb to 15.95 handled by JS numbers, driver could do better. now questions are:
where this double come from ?
is there an other way to get exact number value from OCI ?

[bjouhier]

The double comes from OCI, which is not open source. No hope to change it (OCI is used in zillions of 3rd party code and I doubt that Oracle wants to disrupt this eco-system - we are part of it and wouldn't want this).

But the problem can be fixed in the oracledb driver. An easy way is to ask OCI to return all decimal values as strings (as if fetchAsString was always set) and then convert them to JavaScript numbers with parseFloat (only when fetchAsString is not set). The only problem is that it will likely be slower than just letting OCI return a double.

If you are confortable with C++ you can attempt a PR but it would be better to discuss it with Oracle staff first and you would probably need to control this with a flag, for backward compat.

[atiertant]

@bjouhier from OCI doc:

Conversions to and from native floating-point types have the precision of the relevant floating-point type, not to exceed 38 decimal digits.

You can use the OCIDescriptorAlloc() call to allocate an OCIParam (with the parent handle being the environment handle). Subsequently, you can call OCIAttrSet() with the following allowed attribute types to set relevant type information:

OCI_ATTR_PRECISION
To set numeric precision. Pass a (ub1 *) attribute value to the buffer holding the precision value.

[bjouhier]

The problem is not one of precision but of rounding mode. As I understand it OCI does not let you control the rounding mode so you have to pass the value as string.

@anthony-tuininga's API proposal would solve your problem:

fetchInfo: { oracledb.NUMBER : { type : oracledb.STRING, converter = parseFloat } }

So maybe you should push for this API to move forwards. It is more general/powerful.

[atiertant]

@bjouhier more general for sure but slower... if precision was set to 17 digits, rounding mode doesn't matter no?

[bjouhier]

Increasing the precision won't help. What you have to do is reproduce exactly the rounding rule used by parseFloat. Consider the following:

$ node
> parseFloat('0.9999999999999999444')
0.9999999999999999
> parseFloat('0.9999999999999999445')
1

Why does the result change between ...444 and ...445? Normally we would expect it to change between ...499 and ...500!

That's because JS imposes a very special rounding rule (one that roundtrips) and it does not help to have more precision on the input.

[atiertant]

@bjouhier look like number could be troncated

[cjbj]

Closing - I think this has run its course.

[FrancoisXavierNoe]

Hello,
How can we manage this issue when calling an Oracle Database object or collection ?
I think that fetchAsString is not working for that ?
The options fetchAsBuffer and fetchAsString do not affect values in objects queried from the database.

[anthony-tuininga]

There is no way, currently. If you would like to see a way developed, please create a new enhancement request detailing what you would like to see. Thanks!