duckdb / duckdb/duckdb-node-neo

Prefer prepared statement param types over JS-inferred types (was: Inconsistent query result with integer conversion in prepared statement)

Open
#366 9 comments 0 reactions 1 assignee Claimed by @jraymakers View on GitHub
enhancement
Dominant language
TypeScript
Stars
197
Forks
46
Avg merge
15h 38m
Merged PRs (30d)
22

Description

Hi,

I'm experiencing an inconsistent behavior using prepared statements with integers and **inferred type**.

**Node.js** v22.15.0
**@duckdb/node-api** 1.4.4-r.1

I was expecting that binding an int value to a prepared query would have it compared consistently with a bigint column, instead it works only when the value is previously upcast with `BigInt()`.

Reproducer: expected 2 rows, query returns 3

```javascript
import { DuckDBInstance } from '@duckdb/node-api';

// Connect to instance
const instance = await DuckDBInstance.create(':memory:', {
TimeZone: 'UTC',
});
const connection = await instance.connect();

await connection.run(`
CREATE TABLE test (
value BIGINT
);
`);

// Seed values
const valueFrom = 1739408400000;
await connection.run(`
INSERT INTO test VALUES
(${valueFrom - 1}),
(${valueFrom}),
(${valueFrom + 1});
`);

// Prepare query
const query = await connection.prepare(`
SELECT value
FROM test
WHERE value >= $valueFrom;
`);

query.bind({
valueFrom: valueFrom, // works with BigInt(valueFrom)
});

// Perform query
const result = await query.runAndReadAll();

// Check results, 2 rows expected
const records = result.getRowObjects();
console.log(records.length); // prints 3
```

If I prepare the same query at the database level, it behaves as expected

This works: expected 2 rows, query returns 2

```javascript
import { DuckDBInstance } from '@duckdb/node-api';

// Connect to instance
const instance = await DuckDBInstance.create(':memory:', {
TimeZone: 'UTC',
});
const connection = await instance.connect();

await connection.run(`
CREATE TABLE test (
value BIGINT
);
`);

// Seed values
const valueFrom = 1739408400000;
await connection.run(`
INSERT INTO test VALUES
(${valueFrom - 1}),
(${valueFrom}),
(${valueFrom + 1});
`);

// Prepare query
await connection.run(`
PREPARE read_values AS
SELECT value
FROM test
WHERE value >= $valueFrom
`);

// Perform query
const result = await connection.runAndReadAll(`
EXECUTE read_values(valueFrom := ${valueFrom});
`);

// Check results, 2 rows expected
const records = result.getRowObjects();
console.log(records.length); // prints 2

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.