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 ] ]
`