Numeric overflow incorrectly handled in thick mode

[mrfitz42]

1. What versions are you using?

   database version: Oracle 19c Enterprise edition version 19.21.0.0.0

   process.platform: 'linux' process.version: 'v20.11.0' process.arch: 'x64' require('oracledb').versionString: '6.4.0' require('oracledb').oracleClientVersionString: undefined

2. Is it an error or a hang or a crash? No

3. What error(s) or behavior you are seeing?

   Do not get expected ORA-01426 errors in thick mode. Do see NJS-115 errors in thin mode. CREATE TABLE no_example (id NUMBER, float64_1 NUMBER, float64_2 NUMBER)

   
   Running in thick mode
   binds: [ 101, 1e+25, -1e+25 ]  ,result: 1
   binds: [ 102, 1.7976931348623157e+308, -1.7976931348623157e+308 ]  ,result: 1
   binds: [ 103, -1.7976931348623157e+308, 1.7976931348623157e+308 ]  ,result: 1
   Query results: 
   [
   [ 101, 9.999999999999999e+24, -9.999999999999999e+24 ],
   [ 102, 1.0000000000000001e+126, 0 ],
   [ 103, 0, 1.0000000000000001e+126 ]
   ]

Running in thin mode binds: [ 101, 1e+25, -1e+25 ] ,result: 1 binds: [ 102, 1.7976931348623157e+308, -1.7976931348623157e+308 ] ,result: Error: NJS-115: value cannot be represented as an Oracle Database number binds: [ 103, -1.7976931348623157e+308, 1.7976931348623157e+308 ] ,result: Error: NJS-115: value cannot be represented as an Oracle Database number Query results: [ [ 101, 1e+25, -1e+25 ] ]

4. Include a runnable Node.js script that shows the problem.

// based on examples/example.js const oracledb = require('oracledb');

// Optionally run in node-oracledb Thick mode if (process.env.NODE_ORACLEDB_DRIVER_MODE === 'thick') { oracledb.initOracleClient(); // enable node-oracledb Thick mode }

console.log(oracledb.thin ? 'Running in thin mode' : 'Running in thick mode');

async function run() { var self = this; let connection; try {

let sql, binds, options, result;

connection = await oracledb.getConnection({
  user: process.env.NODE_ORACLEDB_USER,
  password: process.env.NODE_ORACLEDB_PASSWORD,
  connectString: process.env.NODE_ORACLEDB_CONNECTIONSTRING,
});

const stmts = [
  `DROP TABLE no_example`,
  `CREATE TABLE no_example (id NUMBER, float64_1 NUMBER, float64_2 NUMBER)`
];

for (const s of stmts) {
  try {
    await connection.execute(s);
  } catch (e) {
    if (e.errorNum != 942) {
      console.error(e);
    }
  }
}

sql = `INSERT INTO no_example VALUES (:1, :2, :3)`;
binds = [
  [101, 1.0e25, -1.0e25 ],
  [102, Number.MAX_VALUE, -Number.MAX_VALUE ],
  [103, -Number.MAX_VALUE, Number.MAX_VALUE ]
];

for (const b of binds) {
  try {
    result = await connection.execute(sql, b, {autoCommit: true});
    console.log("binds:", b, " ,result:", result.rowsAffected);
  } catch (e) {
    console.log("binds:", b, " ,result:", e.toString());
  }
}

sql = `SELECT * FROM no_example`;
result = await connection.execute(sql, {}, {});

console.log("Query results: ");
console.log(result.rows);

} catch (err) { console.error(err); } finally { if (connection) { try { await connection.close(); } catch (err) { console.error(err); } } } }

run();

[sharadraju]

@mrfitz42 Thank you for reporting this issue. Can you let us know if you had observed this issue in the pre-Thin mode version of node-oracledb (version 5.5 or earlier).

[mrfitz42]

Yes, version 5.0.0 exhibited the same behavior.
If I examine the table contents with SQL Developer, thick mode values for the bad rows are:

102    Infinity     -Infinity
103    -Infinity    Infinity

Thin mode exhibits odd behavior at the Oracle numeric limit:

Running in thin mode
binds: [ 101, 1e+126, -1e+126 ]  ,result: 1
binds: [ 102, 1.7976931348623157e+308, -1.7976931348623157e+308 ]  ,result: Error: NJS-115: value cannot be represented as an Oracle Database number
binds: [ 103, -1.7976931348623157e+308, 1.7976931348623157e+308 ]  ,result: Error: NJS-115: value cannot be represented as an Oracle Database number
Query results: 
[ [ 101, -9.9e+125, 9.9101e+126 ] ]

Even stranger, SQL Developer then shows the float64 columns as (null).

[sharadraju]

Thanks @mrfitz42. We had identified a similar issue internally and it will be fixed in the upcoming release.

[sharadraju]

This is fixed in the 6.5.0 release. @mrfitz42 Please check.

[mrfitz42]

This did fix thick mode, thank you. It now results in two error codes: NJS-115 and DPI-1044.

Running in thick mode
binds: [ 101, 1e+126, -1e+126 ]  ,result: Error: NJS-115: value cannot be represented as an Oracle Database number
DPI-1044: value cannot be represented as an Oracle number
Query results: 
[]

Thin mode still has an issue with values just past the Oracle limits:

Running in thin mode
binds: [ 101, 1e+126, -1e+126 ]  ,result: 1
Query results: 
[ [ 101, -9.9e+125, 9.9101e+126 ] ]

[sharadraju]

@mrfitz42 We will fix the Thin mode issue in the next release.

[sharadraju]

@mrfitz42 Here is the patch for the Thin mode fix:

diff --git a/lib/impl/datahandlers/buffer.js b/lib/impl/datahandlers/buffer.js
index xxxxxx   yyyyy
--- a/lib/impl/datahandlers/buffer.js
+++ b/lib/impl/datahandlers/buffer.js
@@ -843,8 +843,8 @@ class BaseBuffer {
     }

     // throw exception if number cannot be represented as an Oracle Number
-    if (value.length > constants.NUMBER_MAX_DIGITS || exponent > 126 ||
-        exponent < -129) {
+    if (value.length > constants.NUMBER_MAX_DIGITS || exponent >= 126 ||
+        exponent <= -131) {
       errors.throwErr(errors.ERR_ORACLE_NUMBER_NO_REPR);
     }

[sharadraju]

@mrfitz42 We have fixed the Thin mode issue in 6.5.1. Please verify and thank you for contributing!

[mrfitz42]

Fixed in thin mode too. Thank you. ` Running in thin mode binds: [ 100, 9.99999999999999e+125, -9.99999999999999e+125 ] ,result: 1 binds: [ 101, 1e+126, -1e+126 ] ,result: Error: NJS-115: value cannot be represented as an Oracle Database number Query results: [ [ 100, 9.99999999999999e+125, -9.99999999999999e+125 ] ]

Running in thick mode binds: [ 100, 9.99999999999999e+125, -9.99999999999999e+125 ] ,result: 1 binds: [ 101, 1e+126, -1e+126 ] ,result: Error: NJS-115: value cannot be represented as an Oracle Database number DPI-1044: value cannot be represented as an Oracle number Query results: [ [ 100, 9.99999999999999e+125, -9.99999999999999e+125 ] ] `