When batchErrors=true and dmlRowCounts=true the dmlRowCounts property returns values of 0's in a executeMany of a MERGE statement after first error is hit

[eopio]

1. What versions are you using?

   process.platform: win32 process.version: v20.14.0 process.arch: x64 require('oracledb').versionString: 6.5.1 (also in 5.2.0) require('oracledb').oracleClientVersionString: 21.3.0.0.0

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

   Error.

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

   When bulk executing (executeMany) a MERGE and an error is hit on a specific data element then dmlRowCounts are zero (0) for subsequent data elements even if they hit no error and affected some rows.

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

const oracledb = require("oracledb");
const { getDsn, getPassword, getUser } = require("./helpers"); //change with your database credentials

(async ()=>{

    const con = await oracledb.getConnection({
        user: getUser(), //change with your database credentials
        password: getPassword(), //change with your database credentials
        connectString: getDsn(), //change with your database credentials
    });

    let sqlSelAllRows="select * from TEST_TABLE_NODE_DML_COUNTS"

    try{

        ////////////////////////////////////////////////////////////////////////////////////////
        ////////////////////////////////////////EXAMPLE INITIALIZATION//////////////////////////
        ////////////////////////////////////////////////////////////////////////////////////////

        try{
            let sqlDropTable=`drop table TEST_TABLE_NODE_DML_COUNTS`
            await con.execute(sqlDropTable)
            console.log(`Existing EXAMPLE table was deleted`)
        } catch (err) {
            let errorNum=err.errorNum
            console.log(`errorNum=${errorNum}`)
            if (errorNum===942){ //ORA-00942: table or view does not exist
                console.log(`Main EXAMPLE table did not exist`)
            } else{
                throw new Error('Unexpected case when deleting TABLE EXAMPLE')
            }
        }

        try{
            let sqlDropTableAux=`drop table TEST_TABLE_AUX_NODE_DML_COUNTS`
            await con.execute(sqlDropTableAux)
            console.log(`Existing EXAMPLE AUXILIARY table was deleted`)
        } catch (err) { //ORA-00942: table or view does not exist
            let errorNum=err.errorNum
            console.log(`errorNum=${errorNum}`)
            if (errorNum===942){
                console.log(`Main EXAMPLE AUXILIARY table did not exist`)
            } else{
                throw new Error('Unexpected case when deleting TABLE AUX EXAMPLE')
            }
        }

        //CREATION OF AUXILIARY EXAMPLE TABLE: TEST_TABLE_AUX_NODE_DML_COUNTS
        let sqlCreateTableAux=`
        create table TEST_TABLE_AUX_NODE_DML_COUNTS(
            VAR_ID NUMBER(4),
            CONSTRAINT TEST_PK_TABLE_AUX PRIMARY KEY (VAR_ID)
        )`
        await con.execute(sqlCreateTableAux)

        //CREATION OF EXAMPLE TABLE: TEST_TABLE_NODE_DML_COUNTS (IT NEEDS AUXILIARY TABLE ALSO)
        let sqlCreateTable=`
        CREATE TABLE TEST_TABLE_NODE_DML_COUNTS (
            VAR_ID NUMBER(4) NOT NULL,
            VAR_DETAIL VARCHAR2(200),
            CONSTRAINT TEST_FK_TABLE FOREIGN KEY (VAR_ID) REFERENCES TEST_TABLE_AUX_NODE_DML_COUNTS(VAR_ID)
        )`
        await con.execute(sqlCreateTable)

        //INITIAL DATA GENERATION (IMPORTANT TO REPRODUCE THE EXAMPLE)
        await con.execute('INSERT INTO TEST_TABLE_AUX_NODE_DML_COUNTS(VAR_ID) VALUES (1)')
        await con.execute("INSERT INTO TEST_TABLE_NODE_DML_COUNTS(VAR_ID,VAR_DETAIL) VALUES (1,null)")  //sic, missing value of VAR_DETAIL
        await con.execute('INSERT INTO TEST_TABLE_AUX_NODE_DML_COUNTS(VAR_ID) VALUES (3)')        
        // --IMPORTANT: NOTICE NO RECORD WITH 'VAR_ID=2' IS PRESENT IN AUXILIARY TABLE (TEST_TABLE_AUX_NODE_DML_COUNTS), TABLE     

        let resSelectPreMerge=await con.execute(sqlSelAllRows,{},{outFormat: oracledb.OBJECT})
        let rowsSelectPreMerge=resSelectPreMerge.rows
        console.log(`rowsSelectPreMerge=${JSON.stringify(rowsSelectPreMerge)}`)
        /*
            (BEFORE MERGE) EXAMPLE ROWS IN EXAMPLE TABLETEST_TABLE_AUX_NODE_DML_COUNTS:
                [
                    {"VAR_ID":1,"VAR_DETAIL":null}
                ]
            1 ROW ONLY ONLY WITH VAR_ID=1 AND NULL VAR_DETAIL
        */

        ////////////////////////////////////////////////////////////////////////////////////////
        ////////////////////////////////////////////////////////////////////////////////////////                
        ////////////////////////////////////////////////////////////////////////////////////////

        /////////////////////////////EXAMPLE////////////////////////////////////////////////////

        const optionsBulk={
            batchErrors:true, //needed in this example to continue executing after first error (if any)
            dmlRowCounts:true, //problematic parameter
            bindDefs:{
                VAR_ID:{
                    dir: oracledb.BIND_IN,
                    type: oracledb.NUMBER
                },
                VAR_DETAIL:{
                    dir: oracledb.BIND_IN,
                    type: oracledb.STRING,
                    maxSize:200
                }
            }
        }

        let bulkData=[
            {VAR_ID:1,VAR_DETAIL:'TO BE UPDATED IN EXAMPLE TABLE BECAUSE VAR_ID=1 EXISTED IN EXAMPLE TABLE AND VAR_ID=1 EXISTED IN SECONDARY TABLE'},
            {VAR_ID:2,VAR_DETAIL:'TO BE REJECTED OF EXAMPLE TABLE BECAUSE THERE IS NO PARENT RECORD WITH VAR_ID=2 IN AUX TABLE'},
            {VAR_ID:3,VAR_DETAIL:'TO BE INSERTED IN EXAMPLE TABLE BECAUSE VAR_ID=3 DID NOT EXIST IN EXAMPLE TABLE AND VAR_ID=3 EXISTED IN SECONDARY TABLE'},
        ]

        //THIS MERGE WILL INSERT NEW RECORD IF IT DID NOT EXIST BY "VAR_ID" IN AUX TABLE OR UPDATE "VAR_DETAIL" IF IT "VAR_ID" EXISTED IN AUX TABLE
        sqlBulk=`
        MERGE INTO TEST_TABLE_NODE_DML_COUNTS tab
        USING
        (
            select
            :VAR_ID VAR_ID,
            :VAR_DETAIL VAR_DETAIL
            from dual
        ) query
        ON (tab.VAR_ID=query.VAR_ID)
        WHEN MATCHED THEN
            UPDATE SET
            tab.VAR_DETAIL=query.VAR_DETAIL
        WHEN NOT MATCHED THEN
            INSERT(tab.VAR_ID,tab.VAR_DETAIL) VALUES (query.VAR_ID,query.VAR_DETAIL)
        `
        let resBulk=await con.executeMany(sqlBulk,bulkData,optionsBulk)

        let dmlRowCounts=resBulk.dmlRowCounts
        console.log(`dmlRowCounts=${JSON.stringify(dmlRowCounts)}`) //IMPORTANT: OBTAINING dmlRowCounts=[1,0,0] but dmlRowCounts=[1,0,1] expected!!!!!!!!!!!!!!!!!!!!!!

        // in resBulk.batchErrors you could see error associated to second data element of bulkData because of expected 'ORA-02291: integrity constraint (CDI_ADMON.TEST_FK_TABLE) violated - parent key not found' given INITIAL DATA GENERATION

    } catch (err) {
        console.log(`error=${JSON.stringify(err)}`)
        console.log(`error.message=${err.message}`)
    } finally {
        if (typeof con !== 'undefined') {
            await con.commit()

            /*INFO ONLY: just to print table state after al steps*/

            let resSelectPostMerge=await con.execute(sqlSelAllRows,{},{outFormat: oracledb.OBJECT})
            let rowsSelPostMerge=resSelectPostMerge.rows
            console.log(`rowsSelPostMerge=${JSON.stringify(rowsSelPostMerge)}`)
            /*
            (AFTER MERGE) EXAMPLE ROWS IN EXAMPLE TABLETEST_TABLE_AUX_NODE_DML_COUNTS:
                [
                    {"VAR_ID":1,"VAR_DETAIL":"TO BE UPDATED IN EXAMPLE TABLE BECAUSE VAR_ID=1 EXISTED IN EXAMPLE TABLE AND VAR_ID=1 EXISTED IN SECONDARY TABLE"},
                    {"VAR_ID":3,"VAR_DETAIL":"TO BE INSERTED IN EXAMPLE TABLE BECAUSE VAR_ID=3 DID NOT EXIST IN EXAMPLE TABLE AND VAR_ID=3 EXISTED IN SECONDARY TABLE"}
                ]
            It can be seen that in EXAMPLE THAT THAT THIRD (3rd) ELEMENT IN BULK DATA (the one with VAR_ID=3) RESULTING OF CREATING A NEW RECORD (the one with VAR_ID=3) IN EXAMPLE TABLE BUT THIRD POSITION OF dmlRowCounts (ie associated with index=2) is dmlRowCounts[2]=0 and not 1 as expected which is why this potential bug has been opened
            */

            await con.close()
        }
    }

})()

[sudarshan12s]

Thanks @eopio for reporting the issue. We are checking the cause and it looks this has similar cause as the one reported here.

We will update.