duckdb / duckdb/duckdb-node-neo
Prefer prepared statement param types over JS-inferred types (was: Inconsistent query result with integer conversion in prepared statement)
- 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.