Node-sqlite3: Joins of tables with duplicate column names returning only last table's columns

[joshbalfour]

Node version: v0.10.28
node-sqlite3 version: 3.0.5

If you have two tables which have, say, ID and name fields:

thing: ID, name

user: ID, name, thingID

then join them

select thing.id, thing.name, user.id, user.name 
from thing
inner join user on thing.id = user.thingID

The returned rowset contains only one pair of ID and name properties which will be the ID and name of the last table, which in this case is user:

[
{ id: 1,
  name: "foo" }
]

This would be to be expected if the fields in the query were not prefixed with the table name.
When running the same query in a SQLite DB Browser it returns

id, name, id, name 

So it's understandable that if you try to make a JS Object out of that then the later id property would overwrite the first one.

Are we using SQLite wrong? Is it expected for it to strip out table names in returned column names?

We have a workaround, which is to alias each column as tablename_columnname, but this is a performance hit as the result then needs to be processed to remove the tablename_.

[kkaefer]

The recommended way of circumventing this issue is to use aliases.

[joshbalfour]

This is what we're doing, it doesn't fix the issue though.

[kkaefer]

There is no issue to fix. The SQLite API is index based, and you can use sqlite3_column_name to query the name. This is what the node module does, but we can't magically create distinct names.

[cztomsik]

If anyone else is fighting this, it seems dblite fetch array of arrays (and so you can reference columns one by one) - there might be others but this one seems to be working.

UPDATE: sql.js returns array of arrays as well (including array of all columns)

sqlite3 currently does not support this but it could certainly be patched somehow in src/statement.cc

@kkaefer would you be open to such addition?

[kkaefer]

I'm not convinced that this change is necessary to node-sqlite3, especially when it's so easy to define unique aliases.

[cztomsik]

What do you mean by "so easy"? Is this general purpose sqlite driver or rather an opiniated data access layer for obtaining an array of hashes?

Let's say I'd like to implement sqlite shell using this library - so if I type SELECT 1, 1 I would really expect to see two columns filled with ones. MySQL does this, MSSQL does it too. You can't implement SQL shell without being able to get an array of arrays (and it's useful for other low-level stuff too)

BTW: even ActiveRecord.Result allows getting an array of arrays - and that's much higher abstraction.
http://api.rubyonrails.org/classes/ActiveRecord/Result.html

[cyjake]

A pr is proposed :-)
Can we re-open this issue?

[cyjake]

Sorry about the repeated reference. I was struggling with travis.

[rathboma]

Hey everyone,

I run Beekeeper Studio and I'm struggling with this issue.

If a user runs select 1 as total, 2 as total, the result is a single column with 2 as the result. Obviously users expect their sql client to return both values 1, 2.

Is there a suggested way do this now?

===

I don't disagree that the workaround is a fairly simple user change, but I think there's a difference between the user making a mistake, and the user querying two columns with the same name. It might also not be a simple change in a large, complex query with nested selects.

Querying two columns with the same name is a 100% valid query, supported by the database itself, and supported by the command line sqlite3 client. It'd be fantastic to have a simple way to enable the node driver to return the correct results.