executeMany() offsets index of dbl returning variables on index

BoxenOfDonuts commented 2 weeks ago

What versions are you using?

platform: darwin version: v20.11.1 arch: x64 oracledb: 5.5.0 (reproducible in latest 6.x) clientVersion: 19.3.0.0.0

Is it an error or a hang or a crash?

Error

What error(s) or behavior you are seeing?

When encountering batchErrors, the index / values of dbms returning can become offset incorrectly

// results.batchErrors
[
  [Error: ORA-12899: value too large for column "REPRODUCTION_CONTRACT_TABLE"."CURRENCY_CODE" (actual: 46, maximum: 20)] {
    errorNum: 12899,
    offset: 1
  }
]

// results.outBidns
[
  {
    contractLineIdOut: [ 10000000050 ],
    contractIdOut: [ 100 ],
    creationDateOut: [ 2021-01-01T05:00:00.000Z ],
    lineItemOut: [ 'Line Item 1' ]
  },
  {
    contractLineIdOut: [],
    contractIdOut: [],
    creationDateOut: [],
    lineItemOut: []
  },
  {
    contractLineIdOut: [ 4.089930721500069e-28 ],
    contractIdOut: [ 10000000059 ],
    creationDateOut: [ 9309-01-01T12:00:00.000Z ],
    lineItemOut: [ 'xy\x01\t\x17\x01\x01' ]
  }
]

Expected: Third item in array has the correct lineItemOut, correct number of contractIdOut

[
  {
    contractLineIdOut: [ 10000000050 ],
    contractIdOut: [ 100 ],
    creationDateOut: [ 2021-01-01T05:00:00.000Z ],
    lineItemOut: [ 'Line Item 1' ]
  },
  {
    contractLineIdOut: [],
    contractIdOut: [],
    creationDateOut: [],
    lineItemOut: []
  },
  {
    contractLineIdOut: [ 10000000059 ],
    contractIdOut: [ 800 ],
    creationDateOut: [ 2021-01-10T05:00:00.000Z ],
    lineItemOut: [ 'Line Item 8' ]
  }
]

if the currency code was valid it would also update multiple rows

  {
    contractLineIdOut: [ 10000000055, 10000000056 ],
    contractIdOut: [ 600, 600 ],
    creationDateOut: [ 2021-01-06T05:00:00.000Z, 2021-01-07T05:00:00.000Z ],
    lineItemOut: [ 'Line Item 6', 'Line Item 6' ]
  },

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

  let connection;
  const createTableAndSeed = async () => {
    try {
      connection = await oracledb.getConnection({
        user: oracleConnectionInfo.getKnexConnectionObject().user,
        password: oracleConnectionInfo.getKnexConnectionObject().password,
        connectString: oracleConnectionInfo.getConnectionString(),
      });

      const creationTable = `
          CREATE TABLE REPRODUCTION_CONTRACT_TABLE (
                    CONTRACT_LINE_ID         NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY INCREMENT BY 1 START WITH 10000000000 MAXVALUE 999999999999999999999999999 CACHE 1000
                    , CONTRACT_ID            NUMBER NOT NULL
                    , CREATION_DATE          DATE
                    , CURRENCY_CODE          VARCHAR2(20)
                    , LINE_ITEM              VARCHAR2(500)
                    , CONSTRAINT CONTRACT_LINE_PK PRIMARY KEY (CONTRACT_LINE_ID) ENABLE
          );
      `;

      await connection.execute(creationTable);

      const sql = `
          INSERT INTO REPRODUCTION_CONTRACT_TABLE
          (CONTRACT_ID, CREATION_DATE, CURRENCY_CODE, LINE_ITEM) 
          VALUES (:contract_id, TO_DATE(:creation_date, 'YYYY-MM-DD'), :currency_code, :line_item)
        `;

      const binds = [
        { contract_id: 100, creation_date: '2021-01-01', currency_code: 'USD', line_item: 'Line Item 1' },
        { contract_id: 200, creation_date: '2021-01-02', currency_code: 'USD', line_item: 'Line Item 2' },
        { contract_id: 300, creation_date: '2021-01-03', currency_code: 'USD', line_item: 'Line Item 3' },
        { contract_id: 400, creation_date: '2021-01-04', currency_code: 'USD', line_item: 'Line Item 4' },
        { contract_id: 500, creation_date: '2021-01-05', currency_code: 'USD', line_item: 'Line Item 5' },
        { contract_id: 600, creation_date: '2021-01-06', currency_code: 'USD', line_item: 'Line Item 6' },
        { contract_id: 600, creation_date: '2021-01-07', currency_code: 'USD', line_item: 'Line Item 6' },
        { contract_id: 700, creation_date: '2021-01-08', currency_code: 'USD', line_item: 'Line Item 7' },
        { contract_id: 700, creation_date: '2021-01-09', currency_code: 'USD', line_item: 'Line Item 7' },
        { contract_id: 800, creation_date: '2021-01-10', currency_code: 'USD', line_item: 'Line Item 8' },
      ];

      const options = {
        autoCommit: true, // Automatically commit after each insert
        bindDefs: {
          contract_id: { type: oracledb.NUMBER },
          creation_date: { type: oracledb.STRING, maxSize: 10 },
          currency_code: { type: oracledb.STRING, maxSize: 3 },
          line_item: { type: oracledb.STRING, maxSize: 50 },
        },
      };

      const result = await connection.executeMany(sql, binds, options);
      console.log('Rows inserted:', result.rowsAffected);
    } catch (err) {
      console.log(err);
    } finally {
      connection.close();
    }
  };

  const update = async () => {
    const update = `
    update REPRODUCTION_CONTRACT_TABLE
    SET
    CONTRACT_ID         =:contractId,
    CURRENCY_CODE       =:currencyCode
    WHERE LINE_ITEM     =:lineItem
    RETURNING CONTRACT_LINE_ID, CONTRACT_ID, CREATION_DATE, LINE_ITEM INTO :contractLineIdOut, :contractIdOut, :creationDateOut, :lineItemOut
    `;

    const binds = [
      {
        contractId: 100,
        currencyCode: 'USD',
        lineItem: 'Line Item 1',
      },
      {
        contractId: 600,
        currencyCode: 'USD Values Is Way To Long To Fit In The Column',
        lineItem: 'Line Item 6',
      },
      {
        contractId: 800,
        currencyCode: 'USD',
        lineItem: 'Line Item 8',
      },
    ];

    const options = {
      autoCommit: true,
      batchErrors: true,
      bindDefs: {
        contractId: { type: oracledb.NUMBER },
        currencyCode: { maxSize: 512, type: oracledb.STRING },
        lineItem: { type: oracledb.STRING, maxSize: 512 },
        contractLineIdOut: { type: oracledb.NUMBER, dir: oracledb.BIND_OUT },
        contractIdOut: { type: oracledb.NUMBER, dir: oracledb.BIND_OUT },
        creationDateOut: { type: oracledb.DATE, dir: oracledb.BIND_OUT },
        lineItemOut: { type: oracledb.STRING, dir: oracledb.BIND_OUT, maxSize: 512 },
      },
    };
    let result;
    let connection;
    try {
      connection = await oracledb.getConnection({
        user: oracleConnectionInfo.getKnexConnectionObject().user,
        password: oracleConnectionInfo.getKnexConnectionObject().password,
        connectString: oracleConnectionInfo.getConnectionString(),
      });

      result = await connection.executeMany(update, binds, options);
    } catch (err) {
      console.log(err);
    } finally {
      connection.commit();
      connection.close();
    }

    console.log(result?.batchErrors);
    console.log(result?.outBinds);
  };

  await createTableAndSeed();
  await update();

cjbj commented 2 weeks ago

We appreciate you opening the issue, but the table and column names in the snippets don't match, and you haven't given the original data. Since details can matter when reproducing a bug, can you update the issue with a running script, and include the necessary INSERT statements?

BoxenOfDonuts commented 2 weeks ago

@cjbj I updated the issue with a script and inserts

BoxenOfDonuts commented 2 weeks ago

I tried changing the binds to so that error would happen when updating a single row and had the same results

const binds = [
   ... original binds
  {
    contractId: 200,
    currencyCode: 'USD Values Is Way To Long To Fit In The Column',
    lineItem: 'Line Item 2',
  }
];

 // output
[
  {
    contractLineIdOut: [ 10000000050 ],
    contractIdOut: [ 100 ],
    creationDateOut: [ 2021-01-01T05:00:00.000Z ],
    lineItemOut: [ 'Line Item 1' ]
  },
  {
    contractLineIdOut: [],
    contractIdOut: [],
    creationDateOut: [],
    lineItemOut: []
  },
  {
    contractLineIdOut: [ 4.089930721500069e-28 ],
    contractIdOut: [ 10000000059 ],
    creationDateOut: [ 9309-01-01T12:00:00.000Z ],
    lineItemOut: [ 'xy\x01\t\x17\x01\x01' ]
  }
]

sudarshan12s commented 1 week ago

@BoxenOfDonuts , I think its because awaits are missing here. can you add await here in finally block.

await connection.commit();
await connection.close();

you can also add dmlRowCounts too to verify the updated rows and i could see the updated rows in my test.

const options = {
    autoCommit: true,
    batchErrors: true,
    dmlRowCounts: true,

...
 console.log(result.rowsAffected);
console.log(result?.dmlRowCounts);

just pasting the complete program of yours with minor modifications...


const createTableAndSeed = async () => {
  try {

    connection = await oracledb.getConnection(dbConfig);

    const creationTable = `
          CREATE TABLE REPRODUCTION_CONTRACT_TABLE (
                    CONTRACT_LINE_ID         NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY INCREMENT BY 1 START WITH 10000000000 MAXVALUE 999999999999999999999999999 CACHE 1000
                    , CONTRACT_ID            NUMBER NOT NULL
                    , CREATION_DATE          DATE
                    , CURRENCY_CODE          VARCHAR2(20)
                    , LINE_ITEM              VARCHAR2(500)
                    , CONSTRAINT CONTRACT_LINE_PK PRIMARY KEY (CONTRACT_LINE_ID) ENABLE
          )
      `;

    await connection.execute(`drop table REPRODUCTION_CONTRACT_TABLE `);
    await connection.execute(creationTable);

    const sql = `
          INSERT INTO REPRODUCTION_CONTRACT_TABLE
          (CONTRACT_ID, CREATION_DATE, CURRENCY_CODE, LINE_ITEM) 
          VALUES (:contract_id, TO_DATE(:creation_date, 'YYYY-MM-DD'), :currency_code, :line_item)
        `;

    const binds = [
      { contract_id: 100, creation_date: '2021-01-01', currency_code: 'USD', line_item: 'Line Item 1' },
      { contract_id: 200, creation_date: '2021-01-02', currency_code: 'USD', line_item: 'Line Item 2' },
      { contract_id: 300, creation_date: '2021-01-03', currency_code: 'USD', line_item: 'Line Item 3' },
      { contract_id: 400, creation_date: '2021-01-04', currency_code: 'USD', line_item: 'Line Item 4' },
      { contract_id: 500, creation_date: '2021-01-05', currency_code: 'USD', line_item: 'Line Item 5' },
      { contract_id: 600, creation_date: '2021-01-06', currency_code: 'USD', line_item: 'Line Item 6' },
      { contract_id: 600, creation_date: '2021-01-07', currency_code: 'USD', line_item: 'Line Item 6' },
      { contract_id: 700, creation_date: '2021-01-08', currency_code: 'USD', line_item: 'Line Item 7' },
      { contract_id: 700, creation_date: '2021-01-09', currency_code: 'USD', line_item: 'Line Item 7' },
      { contract_id: 800, creation_date: '2021-01-10', currency_code: 'USD', line_item: 'Line Item 8' },
    ];

    const options = {
      autoCommit: true, // Automatically commit after each insert
      bindDefs: {
        contract_id: { type: oracledb.NUMBER },
        creation_date: { type: oracledb.STRING, maxSize: 10 },
        currency_code: { type: oracledb.STRING, maxSize: 3 },
        line_item: { type: oracledb.STRING, maxSize: 50 },
      },
    };

    const result = await connection.executeMany(sql, binds, options);
    console.log('Rows inserted:', result.rowsAffected);
  } catch (err) {
    console.log(err);
  } finally {
    await connection.close();
  }
};

const update = async () => {
  const update = `
    update REPRODUCTION_CONTRACT_TABLE
    SET
    CONTRACT_ID         =:contractId,
    CURRENCY_CODE       =:currencyCode
    WHERE LINE_ITEM     =:lineItem
    RETURNING CONTRACT_LINE_ID, CONTRACT_ID, CREATION_DATE, LINE_ITEM INTO :contractLineIdOut, :contractIdOut, :creationDateOut, :lineItemOut
    `;

  const binds = [
    {
      contractId: 600,
      currencyCode: 'USD Values Is Way To Long To Fit In The Column',
      lineItem: 'Line Item 6',
    },
    {
      contractId: 1000,
      currencyCode: 'USD',
      lineItem: 'Line Item 1',
    },
    {
      contractId: 8000,
      currencyCode: 'USD',
      lineItem: 'Line Item 8',
    },
  ];

  const options = {
    autoCommit: true,
    batchErrors: true,
    dmlRowCounts: true,
    bindDefs: {
      contractId: { type: oracledb.NUMBER },
      currencyCode: { maxSize: 512, type: oracledb.STRING },
      lineItem: { type: oracledb.STRING, maxSize: 512 },
      contractLineIdOut: { type: oracledb.NUMBER, dir: oracledb.BIND_OUT },
      contractIdOut: { type: oracledb.NUMBER, dir: oracledb.BIND_OUT },
      creationDateOut: { type: oracledb.DATE, dir: oracledb.BIND_OUT },
      lineItemOut: { type: oracledb.STRING, dir: oracledb.BIND_OUT, maxSize: 512 },
    },
  };
  let result;
  let connection;
  try {
    connection = await oracledb.getConnection(dbConfig);

    result = await connection.executeMany(update, binds, options);
  } catch (err) {
    console.log(err);
  } finally {
    await connection.commit();
  }

  console.log(result?.batchErrors);
  console.log(result?.dmlRowCounts);
  console.log(result.rowsAffected);
  console.log(result?.outBinds);

  result = await connection.execute('select * from REPRODUCTION_CONTRACT_TABLE');
  console.log(result.rows);
  await connection.close();

};

async function run() {
  await createTableAndSeed();
  await update();
}

run();

BoxenOfDonuts commented 1 week ago

@BoxenOfDonuts , I think its because awaits are missing here. can you add await here in finally block.

@sudarshan12s that was just a small error in my reproduction code, the real code has the correct awaits. We have not had issues with the data actually being updated, just the bindings being offset on the returns. I did re-run the reproduction script and still see the wrong binding

// console.log(result?.batchErrors) ;
[
  [Error: ORA-12899: value too large for column REPRODUCTION_CONTRACT_TABLE"."CURRENCY_CODE" (actual: 46, maximum: 20)] {
    errorNum: 12899,
    offset: 1
  }
]

// console.log(result?.outBinds);
[
  {
    contractLineIdOut: [ 10000000050 ],
    contractIdOut: [ 100 ],
    creationDateOut: [ 2021-01-01T05:00:00.000Z ],
    lineItemOut: [ 'Line Item 1' ]
  },
  {
    contractLineIdOut: [],
    contractIdOut: [],
    creationDateOut: [],
    lineItemOut: []
  },
  {
    contractLineIdOut: [ 4.089930721500069e-28 ],
    contractIdOut: [ 10000000059 ],
    creationDateOut: [ 9309-01-01T12:00:00.000Z ],
    lineItemOut: [ 'xy\x01\t\x17\x01\x01' ]
  }
]

// console.log(result?.dmlRowCounts);
[ 1, 0, 1 ]

sudarshan12s commented 1 week ago

Thanks . We are checking the invalid outbinds returned and will update.

oracle / node-oracledb

executeMany() offsets index of dbl returning variables on index #1683