[sqlite] Positional numbered question mark parameters are incorrectly bound as named parameters

[WillAvudim]

Version

v24.5.0

Platform

Linux dev 6.1.0-37-amd64 #1 SMP PREEMPT_DYNAMIC Debian 6.1.140-1 (2025-05-22) x86_64 GNU/Linux

Subsystem

sqlite

What steps will reproduce the bug?

test("numbered question mark parameters", () => {
  const db = new DatabaseSync(":memory:")
  db.exec(`CREATE TABLE TestInsertTable (arg1, arg2);`)
  const statement = db.prepare(`
    INSERT INTO TestInsertTable (arg1) VALUES (?1);`)

  statement.run("asdf")  // This line fails.

  statement.run({ "?1": "asdf" })   // This line succeeds.
})

How often does it reproduce? Is there a required condition?

It's always reproducible.

What is the expected behavior? Why is that the expected behavior?

Numbered question mark parameters are positional parameters and they should be bound left-to-right from ?1 to SQLITE_MAX_VARIABLE_NUMBER.
Please refer to https://www.sqlite.org/lang_expr.html#varparam
In Node.js' implementation, however, they are bound as named parameters.
I verified that in other implementations (e.g. Python), they are correctly bound as positional parameters (as a tuple or array) and do not require passing the {"?1": value, ...} object, which defeats the purpose of the numbered question mark parameters.

What do you see instead?

Sqlite exception: Error: column index out of range

Additional information

https://www.sqlite.org/lang_expr.html#varparam

[geeksilva97]

Just for a reference, would you mind linking to the Python example you mentioned?

[geeksilva97]

I've made a simple experiment (in the PR) that I think will make it work, but now I'm wondering about it. Since ?NNN are not anonymous parameters like ?

I mean, it makes sense for anonymous params to be positional, but I'm not sure about the others. In the classification, ?NNN, @AAA, :AAA, and $AAA have similar behavior, so they should be treated alike. Maybe?

[WillAvudim]

Sure, here's an example in Python:

import sqlite3

conn = sqlite3.connect('example.db')
cursor = conn.cursor()

cursor.execute('''
    CREATE TABLE IF NOT EXISTS employees (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        department TEXT NOT NULL,
        salary REAL,
        hire_date TEXT
    )
''')

cursor.execute('''
    INSERT INTO employees (name, department, salary, hire_date)
    VALUES (?1, ?2, ?3, ?4)
''', ("Alice Johnson", "Engineering", 75000.0, "2023-01-15"))

cursor.execute('''
    INSERT INTO employees (name, department, salary, hire_date)
    VALUES (?1, ?2, ?3, ?4)
''', ("Bob Smith", "Marketing", 65000.0, "2023-02-20"))

(I just executed it successfully on Python 3.12.4)

The unit test above (in JavaScript) can be updated to look like this:

const statement = db.prepare(`
    INSERT INTO TestInsertTable (arg1, arg2) VALUES (?1, ?1);`)

  statement.run("asdf")  // This line fails while it should succeed.

[WillAvudim]

The documentation clearly identifies ?NNN as ordinal, while $, @, and : are "named" - https://www.sqlite.org/lang_expr.html#varparam

[geeksilva97]

I was asking in the sense that sqlite3_bind_parameter_name returns a name for all the named params.

sqlite3_bind_parameter_name(stmt, 1); // returns "?1" for ?NN params 

[geeksilva97]

I still agree that it would make much more sense to be positional in the interface. The PR is in place. Thanks for reporting.