Prepared statements don't accept list arguments

[tronis470]

I can't pass a list/array as an argument into prepared statements with duckdb-node 0.9.2. Try this script:

const { Database } = require("duckdb");

const db = new Database(":memory:");
const conn = db.connect();

const stmt1 = conn.prepare("select ?::INTEGER as fortytwo");
stmt1.all(42, (err, res) => {
  if (err) {
    throw err;
  }
  console.log(res);
});

// throws error
const stmt2 = conn.prepare("select unnest(?::VARCHAR[]) as x");
stmt2.all(["a", "b", "c"], (err, res) => {
  if (err) {
    throw err;
  }
  console.log(res);
});

// throws error
const stmt3 = conn.prepare("select unnest(?) as x");
stmt3.all(["a", "b", "c"], (err, res) => {
  if (err) {
    throw err;
  }
  console.log(res);
});

// throws error
const stmt4 = conn.prepare("select unnest(?) as x");
stmt4.all([["a", "b", "c"]], (err, res) => {
  if (err) {
    throw err;
  }
  console.log(res);
});

Depending on which version you try, you get errors like:

Error: Type VARCHAR with value 'a,b,c' can't be cast to the destination type LIST

or

Error: Binder Error: UNNEST() can only be applied to lists, structs and NULL

[lubert]

I'm encountering the same issue with duckdb 1.0.0.

Are there any suggested workarounds?

[kisaragi-hiu]

This is caused by Utils::BindParameter not doing anything to handle arrays. JS arrays fall into the IsObject case, then get converted ToString().Utf8Value() before being passing onto DuckDB itself, then causing DuckDB to complain that it's receiving a VARCHAR for a column that wants a LIST.

This explains why passing ["a", "b", "c"] from JS would end up with the error "Type VARCHAR with value 'a,b,c' can't be cast to the destination type LIST". It's doing the same thing as ["a", "b", "c"].toString().

[uwemaurer]

try with this query select unnest(json(?)::text[]) as x and pass JSON.stringify(["a", "b", "c"]) as parameter