Bug: Does not correctly return a result with duplicate keys

[rathboma]

Hey folks,

First - thank you for maintaining this library. It is excellent.

I'm the maintainer of Beekeeper Studio, an open source desktop SQL client.

One problem that keeps coming up is that our users will run a query with duplicate column names and get super confused at the result

For example:

select 1 as foo, 2 as foo

The result of this query is [ { foo: 2 } ], which is incorrect.

Is this a bug?

When using the sqlite command line client you get 2 results, 1, 2, as expected. This is also true in many database client apps like DBeaver, etc.

I understand that renaming the columns is the sql-way to fix this, but I don't get to control that, instead I get upset or angry users who are confused :-).

I can also see how this could lead to some major, if subtle, bugs in applications that use the library for their production database.

Proposed solution

I've recently fixed this bug for both PSQL and MySQL2 by using an option that forces results as an array rather than an object, in our example it would return something like below:

{
  "fields": ["foo", "foo"],
  "data": [ [1, 2] ]
}

Is something like this possible? I know currently the driver doesn't return a separate fields key, so it would be a different return signature.

If so -- I'm super happy to work on this feature if you can provide me with some direction as to where it should be implemented.

Thanks again,

Matthew

[kewde]

Hi Matthew,

Thank you for reporting this issue, it's very much appreciated. I don't know what exactly causes this issue to arise on the top of my head, so it will require a bit of debugging and investigation on my end.
Is this using the get function? It would be helpful to have an initial piece of code to replicate this behavior and I'll take a look and see if there is a workaround if any.

PS: BeeKeeper studio seems like a very interesting application, I will give it a try!

[MasterOdin]

Running this in the REPL, so please ignore the callback timing issues here:

var sqlite3 = require('sqlite3').verbose();
var db = new sqlite3.Database(':memory:');

db.each('SELECT 1 AS foo, 2 AS foo, 3 AS foo', (err, row) => {
  console.log(row);
});
// outputs: { foo: 3 }
// desired, something like: [ ['foo', 'foo', 'foo'], [1, 2, 3] ]

var stmt = db.prepare('SELECT 1 AS foo, 2 AS foo, 3 AS foo');
stmt.get((err, row) => {
  console.log(row);
});
// outputs: { foo: 3 }
// desired, something like: [ ['foo', 'foo', 'foo'], [1, 2, 3] ]

stmt.finalize();
db.close();

This is in contrast to running a similar query against sqlite directly:

$ sqlite3 :memory:
SQLite version 3.32.3 2020-06-18 14:16:19
Enter ".help" for usage hints.
sqlite> .headers on
sqlite> SELECT 1 AS foo, 2 AS foo, 3 AS foo;
foo|foo|foo
1|2|3

I'm guessing under the hood this library (or how it binds to sqlite?) is doing what pg, mysql2, etc. do which is given a resultset from the DB, parses each returned row into an object, so you have an array of objects for a given query. They introduced a new flag to instead return an array of arrays to represent the data, with a secondary object that gives the fields, which the caller is then responsible for in lining them up to the given row values.