postgres style $1/$2 are treated as named arguments about go-sqlite

[zombiezen]

This looks like a difference between PostgreSQL and SQLite. According the official SQLite docs:

A dollar-sign followed by an identifier name also holds a spot for a named parameter (emphasis mine) with the name $AAAA.

So I would expect that this would work (untested):

const sqlStatement = `SELECT EXISTS( SELECT 1 FROM x WHERE z = $2 AND y = $1);`
err := sqlitex.Execute(conn, sqlStatement, &sqlitex.ExecOptions{
  ResultFunc: func(stmt *sqlite.Stmt) error { /* ... */ },
  Named: map[string]any{
    "$1": 8,
    "$2": 4,
  },
})

If you come from a other sqlite Go library that respect this "postgres style", this can create issues that are hard to trace back.

Can you help point me to which Go SQLite library you are referring to? If emulating the positional parameter style is behavior another library has, it would help me weigh whether we should shim this in.

[Perusae]

Might have been Matts go-Sqlite3 version. Been ages when we rewrote the codebase to sqlite and our zombiezen/go-sqlite update is 5+ months old.

Maybe it never supported it and we never ran into this issue as our order just matched all the time. It was only recently that data issues in our database (see ref on first post) triggered several day investigation, and this "behavior" issue come to light.

The solution was simply to replace all instances of $1 with ?1...

Our issue is simply how easy it is to creates a silent bug if your using $1 and expect ?1 behavior. Maybe a warning is better to be put in place then changing the default behavior of go-sqlite?

[zombiezen]

I'm closing this as "won't fix", since the general position of this library is to be largely the same as SQLite. I understand this design decision has caused some frustration on your end, but I can't see a way to improve this situation. Thanks for the feedback, and I'll keep it in mind if I see the same sorts of issues arise in the future.