duckdb / duckdb/duckdb-node

column ordering in results is lost and `columns()` is not usable with prepared statements

Open
#90 0 comments 0 reactions 0 assignees View on GitHub
Dominant language
C++
Stars
91
Forks
36
PR merge metrics
No merged PRs in 30d

Description

# Problem
I've found that calling `columns()` on a prepared statement doesn't work.

The columns returned are always;

```
[ { name: 'unknown', type: { id: 'UNKNOWN', sql_type: 'UNKNOWN' } } ]
```

## Replicate it

```ts
const token = "....";
const statement = `SELECT ? as "field one";`;
const bindings: any[] = [1];

var db = new duckdb.Database('md:my_db', {
motherduck_token: token,
});
const conn = db.connect();
const st = conn.prepare(statement, ...bindings as any);

await new Promise((resolve, reject) => {
st.all(...bindings, (err, res) => {
if (err) reject(err);
resolve(res);
});
});
console.log('columns', st.columns());
```

## Why we care

We need the columns because this is how order of the resulting columns can be preserved. Currently the results are returned in a map and therefore they loose their ordering.

For example, a statement of

```
SELECT 1, 2, -50, 3, 4, 5, 'hi', 'bye', 'abc', '0123'
```

would then return

```
[
{
'1': 1,
'2': 2,
'3': 3,
'4': 4,
'5': 5,
'-50': -50,
"'hi'": 'hi',
"'bye'": 'bye',
"'abc'": 'abc',
"'0123'": '0123'
}
]
```

note the change of position for `-50`.

For non-prepared statements, using `.columns()` works because it returns the columns ordered correctly;

```
[
{ name: '1', type: { id: 'INTEGER', sql_type: 'INTEGER' } },
{ name: '2', type: { id: 'INTEGER', sql_type: 'INTEGER' } },
{ name: '3', type: { id: 'INTEGER', sql_type: 'INTEGER' } },
{ name: '4', type: { id: 'INTEGER', sql_type: 'INTEGER' } },
{ name: '-50', type: { id: 'INTEGER', sql_type: 'INTEGER' } },
{ name: "'hi'", type: { id: 'VARCHAR', sql_type: 'VARCHAR' } },
{ name: "'bye'", type: { id: 'VARCHAR', sql_type: 'VARCHAR' } },
{ name: "'abc'", type: { id: 'VARCHAR', sql_type: 'VARCHAR' } },
{ name: "'0123'", type: { id: 'VARCHAR', sql_type: 'VARCHAR' } }
]
```

Which we can then use to take the values out in the same order as the statements in the original query.

Contributor guide

No contributing guide indexed for this repository

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.