Node-oracledb: Column names overwritten when joining tables with same column names

[gentunian]

Version.js:

Node.js version: v8.11.3 (linux x64)
Node-oracledb version: 2.3.0
Oracle Client library version: 12.1.0.1.0
Oracle Database version: 11.2.0.3.0

Joining tables with same columns overrides columns names. I think that this could be solved prepended table name in row metadata or at least having metadata like this:

[{
   name: 'column1',
   table: 'table1'
},
...
]

When using outFormat.OBJECT row data is merged/overwritten. When using outFormat.ARRAY you can't identify which element correspond to which field.

Is there any approach we could use trying to avoid select each field? Selecting each field could be cumbersome in mostly all cases.

[anthony-tuininga]

Can you provide some code that shows what problem you are experiencing? I'm not sure exactly what you are trying to get at. Are you referring to duplicate column names?

[dmcghan]

I ran a quick test...

Given the following:

create table t (
  c number
);

create table t2 (
 c number
);

insert into t values (1);
insert into t2 values (2);

commit;

If you run this code:

const oracledb = require('oracledb');
const config = require('./dbConfig.js');

async function runTest() {
  let conn;

  try {
    conn = await oracledb.getConnection(config);

    let result = await conn.execute(
     `select *
      from t
      join t2
        on 1 = 1`,
      [], // no binds
      {
        outFormat: oracledb.OBJECT
      }
    );

    console.log(result);
  } catch (err) {
    console.error(err);
  } finally {
    if (conn) {
      try {
        await conn.close();
      } catch (err) {
        console.error(err);
      }
    }
  }
}

runTest();

You get this result:

{ outBinds: undefined,
  rowsAffected: undefined,
  metaData: [ { name: 'C' }, { name: 'C' } ],
  rows: [ { C: 2 } ] }

I'd never tested this before. I'm not happy with the current behavior, but I don't know if randomly appending characters (as SQL Developer does) to the column names is much help either. For ad-hoc query tools that makes sense, but the driver is more programatic. Maybe I'm wrong...

Perhaps an error should be raised instead of overwriting.

[dmcghan]

@gentunian The best solution will be to use aliases for your column names. If this isn't possible, perhaps you could do something like this:

const oracledb = require('oracledb');
const config = require('./dbConfig.js');

async function runTest() {
  let conn;

  try {
    conn = await oracledb.getConnection(config);

    let query = 
     `select *
      from t
      join t2
        on 1 = 1`;

    let stmtInfo = await conn.getStatementInfo(query);

    let result = await conn.execute(query);

    for (let x = 0; x < result.rows.length; x += 1) {
      for (let y = 0; y < result.rows[0].length; y += 1) {
        console.log(stmtInfo.metaData[y].name + ': ', result.rows[x][y]);
      }
    }
  } catch (err) {
    console.error(err);
  } finally {
    if (conn) {
      try {
        await conn.close();
      } catch (err) {
        console.error(err);
      }
    }
  }
}

runTest();

getStatementInfo will return the duplicate column names in an array. Then you can fetch the data as an array to get it all. Using this technique, you could implement any kind of duplicate column name handling you like.

[gentunian]

@anthony-tuininga this is an example:

Tables:

 CREATE TABLE "TABLE1" 
   (    "ID" NUMBER(*,0), 
    "COLUMN1" VARCHAR2(16 BYTE), 
    "COLUMN2" VARCHAR2(16 BYTE)
   )
  CREATE TABLE "TABLE2" 
   (    "ID" NUMBER(*,0), 
    "COLUMN1" VARCHAR2(16 BYTE)
   )
Insert into TABLE1 (ID,COLUMN1,COLUMN2) values (1,'hello','world');
Insert into TABLE2 (ID,COLUMN1) values (1,'goodbye');

Sample code:

oracledb.getConnection(connConfig, function (err, connection) {
    if (err) {
        logger.error(`Error connecting to ${connConfig.connectString}`);
        return logger.error(err.message); // one liner
    }

    logger.info(`Connected to ${connConfig.connectString}`);
    const query = "select * from table1 t1 join table2 t2 on t1.id = t2.id";
    const stream = connection.queryStream(query, [], { outFormat: oracledb.OBJECT });
    stream.on('error', function (error) {
        console.error(error);
        connection.close();
    });

    stream.on('data', function (data) {
        console.log(data);
    });

    stream.on('end', function () {
        console.log(`End of stream reached.`)
        connection.close();
    });

    stream.on('metadata', function (metadata) {
        console.log(metadata);
    });
});

Output:

[2018-06-18T16:13:56.304Z] INFO [
  {
    "name": "ID"
  },
  {
    "name": "COLUMN1"
  },
  {
    "name": "COLUMN2"
  },
  {
    "name": "ID"
  },
  {
    "name": "COLUMN1"
  }
]
[2018-06-18T16:13:56.305Z] INFO {
  "ID": 1,
  "COLUMN1": "goodbye",
  "COLUMN2": "world"
}

I would expect something more similar to:

Using outFormat.ARRAY won't overwrite the values but again you don't know which column is from which table:

With outFormat.ARRAY:

[2018-06-18T16:25:17.690Z] INFO [
  1,
  "hello",
  "world",
  1,
  "goodbye"
]

[dmcghan]

@gentunian Please see my last comment.

[gentunian]

@dmcghan Ok, I will try to map it to stream if possible.

[dmcghan]

@gentunian Have a look at this comment: https://github.com/oracle/node-oracledb/issues/908#issuecomment-390006986 There's a ToJSONArray class that you could modify if you need JSON output.

[cjbj]

This is a 'known issue' with associative arrays that hasn't yet been added to the node-oracledb doc. (It is in PHP doc, e.g. see _Duplicate Column Names and Associative Arrays_ on p 152 of http://www.oracle.com/technetwork/topics/php/underground-php-oracle-manual-098250.html)

Fundamentally the lower Oracle layers don't return the query table names in column metadata, so we can't prefix the array key. I've proposed this as an Oracle Call Interface API enhancement in the past, but bigger projects have taken priority. Also, for a derived column e.g. select t1.c1 + t2.c1 as mycol from t1, t2, the table name couldn't be set.

The solutions are to fetch as ARRAY not OBJECT, or to use column aliases as mentioned.

Database experts recommend against using SELECT * in apps since that can lead to bigger issues when schemas evolve. Given this, the column alias method is the way to go.

The duplicate name clash is an occasional WTF, but has a simple user space solution and no perfect lower level solution except to throw an error. And in terms of scheduling there are bigger features users want from us - it's slightly humorous to use the same excuse as OCI!

PRs are welcome, of course.