SQL State: 01003 Null values were eliminated

tullyelly commented 5 years ago

When summing a column that includes nulls, an SQL State: 01003 warning is returned. In these instances, the idb-connector is returning neither an error or any data. The query in question runs successfully returning no data when data is expected. All views or queries that include this condition react the same way.

We are running node version 10.15.0.

In ACS SQL scripts the query runs, returns the data as expected, and also the warning mentioned. We've never had this issue with the connector before and this warning seems to be the only difference with this data set/view/query.

Now that we are aware of the issue, we've engineered around this to avoid having nulls in the sum.

abmusse commented 5 years ago

Hello @tullyelly

Can you provide some additional information, like what version of idb-connector are you using?

It might be useful to turn debug mode on with connection.debug(true) and have us take a look at the output.

I tried to replicate the issue you described above found that run sql scripts returns data as well as the 01003 warning. In my case using the idb-connector returns the SUM properly but no 01003 warning. The steps I took are described below.

Create Table and Insert Data/NULL

CREATE TABLE MYSCHEMA.SCORES(team VARCHAR(100) ALLOCATE(20), score INTEGER)

INSERT INTO MYSCHEMA.SCORES VALUES('Lakers', NULL)
INSERT INTO MYSCHEMA.SCORES VALUES('Bulls', 100)
INSERT INTO MYSCHEMA.SCORES VALUES('Suns', NULL)
INSERT INTO MYSCHEMA.SCORES VALUES('Knicks', 95)
INSERT INTO MYSCHEMA.SCORES(team) VALUES('Raptors')

View the data

Sum up the Scores

In run sql scripts data is returned as well the warning like you mentioned.

SELECT SUM(score) FROM MYSCHEMA.SCORES

Lets now sum up the scores using node v10.15.3 and idb-connector v1.1.10

    const { dbconn, dbstmt } = require('idb-connector');

    const sql = 'SELECT SUM(score) FROM MYSCHEMA.SCORES';
    const connection = new dbconn();
    connection.debug(true);
    connection.conn('*LOCAL');
    const statement = new dbstmt(connection);

    statement.exec(sql, (result, error) => {
      if (error) {
        throw error;
      }
      console.log('SUM: ', result);
      statement.close();
      connection.disconn();
      connection.close();
    });

SQLExecDirect(0): SELECT SUM(score) FROM MYSCHEMA.SCORES
SQLNUMRESULTSCOLS(0) Column Count = 1
SQLDescribeCol(0)       index[0]        sqlType[4]      colScale[0]     colPrecise[4]
SUM:  [ { '00001': '195' } ]
SQLFreeStmt: stmth 3 [SQL_DROP]
SQLFreeStmt(0)
SQLDisconnect: conn obj [1803464b0] handler [2]
SQLFreeConnect: conn obj [1803464b0] handler [2]
SQLFreeConnect[0]

tullyelly commented 5 years ago

Hello @abmusse,

I will get with the lead developer to have him work through the issue as you described and see if we are able to replicate our issue using your method. We've been able to engineer around the issue now that we know (a guess) the cause by eliminating nulls from our summary.

Expect follow-up from us no later than early next week.

Your prompt response is appreciated.

jasonwardell commented 5 years ago

@abmusse We were using idb-connector 1.1.8 and I used your test data and the results were the same. When there is a warning no error or data comes back in nodejs. I just updated to 1.1.10 and now the results are as expected and we are able to get data back even though there is a warning.

Thank you.

abmusse commented 5 years ago

Glad to hear that!

Yes previous versions of idb-connector only returned data when SQL_SUCCESS was returned.

Returning data even when we have warnings (SQL_SUCCESS_WITH_INFO) was fixed after https://github.com/IBM/nodejs-idb-connector/issues/62#issuecomment-472681109 was reported.

IBM / nodejs-idb-connector