Using % with ILIKE operator

[lucasl84]

Hi,

Just wanted to ask something I'm not fully getting. I'm having trouble on how to correctly use the ILIKE operator with the % wildcard. I have a table orders with a TEXT field named item_id. This works:

await db.all("SELECT * FROM orders WHERE item_id ILIKE ?;", item)

Adding the % wildcard in any of the following ways will fail:

await db.all("SELECT * FROM orders WHERE item_id ILIKE %?;", item)

await db.all("SELECT * FROM orders WHERE item_id ILIKE ?%;", item)

await db.all("SELECT * FROM orders WHERE item_id ILIKE %?%;", item)

The error is the same for the 3 examples:

[Error: Parser Error: syntax error at or near "%"] {
  errno: -1,
  code: 'DUCKDB_NODEJS_ERROR',
  errorType: 'Parser'
}

Adding single quotes for any of the % expressions above does not solve the issue.

Another similat issue that I'm getting is when I try to perform a SELECT with the IN operator. Given an array of ids, e.g: [1, 2, 3] How should I correctly write/pass arguments to db.all?:

let orders = [1, 2, 3];
await db.all("SELECT * FROM batches WHERE order_id IN (?);", orders);

The above snippet produces the following error:

[Error: Conversion Error: Could not convert string '1,3,6' to INT64] {
  errno: -1,
  code: 'DUCKDB_NODEJS_ERROR',
  errorType: 'Conversion'
}

Is there any documentation on how to write these types of queries?

Thanks in advance,
Lucas.

[lucasl84]

A couple of updates, still confused about how to correctly write queries:

This (as from the above example) does not work:

await db.all("SELECT * FROM orders WHERE item_id ILIKE %?%;", item);

But this does:

await db.all("SELECT * FROM orders WHERE item_id ILIKE ?;", `%${item}%`);

For the IN example I've also tried without luck using ANY instead of IN:

await db.all("SELECT * FROM batches WHERE order_id = ANY([?]);", orderIds.toString());  // orderIs is an array of integers

However the same above query does work from the duckdb CLI:

D SELECT * FROM batches WHERE order_id = ANY([2, 22]);
┌───────┬──────────┬───────────────────┬───────────────────┬─────────┬────────┐
│  id   │ order_id │ required_quantity │ produced_quantity │ cart_id │ status │
│ int64 │  int64   │       int32       │       int32       │  int32  │ int32  │
├───────┼──────────┼───────────────────┼───────────────────┼─────────┼────────┤
│     1 │       22 │                22 │                22 │      22 │     22 │
└───────┴──────────┴───────────────────┴───────────────────┴─────────┴────────┘

Any help/guidelines/references on how to correctly treat the ? placeholder for diverse operators/use cases is much appreciated as at least for me this has been (still is) a time waster.

Thanks in advance,
Lucas.