named parameters do not work with sqlite

[jreut]

Version

Other

What happened?

Howdy, I've really enjoyed using this software so far. Thank you for writing it! I think I found a problem in SQLite, and I think I have a vague understanding of the cause.

When I write a test like this on top of the latest main:

diff --git a/internal/endtoend/testdata/sqlc_arg/sqlite/catalog.sql b/internal/endtoend/testdata/sqlc_arg/sqlite/catalog.sql
new file mode 100644
index 00000000..2d02dc78
--- /dev/null
+++ b/internal/endtoend/testdata/sqlc_arg/sqlite/catalog.sql
@@ -0,0 +1 @@
+CREATE TABLE t (a INTEGER);
diff --git a/internal/endtoend/testdata/sqlc_arg/sqlite/query.sql b/internal/endtoend/testdata/sqlc_arg/sqlite/query.sql
new file mode 100644
index 00000000..386bf906
--- /dev/null
+++ b/internal/endtoend/testdata/sqlc_arg/sqlite/query.sql
@@ -0,0 +1,5 @@
+-- name: FuncParamIdent :many
+SELECT a FROM t WHERE a = sqlc.arg(b);
+
+-- name: FuncParamString :many
+SELECT a FROM t WHERE a = sqlc.arg('b');
diff --git a/internal/endtoend/testdata/sqlc_arg/sqlite/sqlc.yaml b/internal/endtoend/testdata/sqlc_arg/sqlite/sqlc.yaml
new file mode 100644
index 00000000..f16dc833
--- /dev/null
+++ b/internal/endtoend/testdata/sqlc_arg/sqlite/sqlc.yaml
@@ -0,0 +1,8 @@
+version: "2"
+sql:
+- schema: catalog.sql
+  queries: query.sql
+  engine: sqlite
+  gen:
+    go:
+      out: db
-- 
2.37.1

I get an error like this:

$ go test -run=TestReplay/testdata/sqlc_arg/sqlite ./internal/endtoend
line 2:34 extraneous input '(' expecting {<EOF>, ';', ALTER_, ANALYZE_, ATTACH_, BEGIN_, COMMIT_, CREATE_, DEFAULT_, DELETE_, DETACH_, DROP_, END_, EXPLAIN_, INSERT_, PRAGMA_, REINDEX_, RELEASE_, REPLACE_, ROLLBACK_, SAVEPOINT_, SELECT_, UPDATE_, VACUUM_, VALUES_, WITH_}
line 5:34 extraneous input '(' expecting {<EOF>, ';', ALTER_, ANALYZE_, ATTACH_, BEGIN_, COMMIT_, CREATE_, DEFAULT_, DELETE_, DETACH_, DROP_, END_, EXPLAIN_, INSERT_, PRAGMA_, REINDEX_, RELEASE_, REPLACE_, ROLLBACK_, SAVEPOINT_, SELECT_, UPDATE_, VACUUM_, VALUES_, WITH_}
line 6:0 extraneous input '<EOF>' expecting {';', ALTER_, ANALYZE_, ATTACH_, BEGIN_, COMMIT_, CREATE_, DEFAULT_, DELETE_, DETACH_, DROP_, END_, EXPLAIN_, INSERT_, PRAGMA_, REINDEX_, RELEASE_, REPLACE_, ROLLBACK_, SAVEPOINT_, SELECT_, UPDATE_, VACUUM_, VALUES_, WITH_}
--- FAIL: TestReplay (0.05s)
    --- FAIL: TestReplay/testdata/sqlc_arg/sqlite (0.01s)
        endtoend_test.go:100: sqlc generate failed: # package db
            query.sql:1:1: extraneous input '<EOF>' expecting {';', ALTER_, ANALYZE_, ATTACH_, BEGIN_, COMMIT_, CREATE_, DEFAULT_, DELETE_, DETACH_, DROP_, END_, EXPLAIN_, INSERT_, PRAGMA_, REINDEX_, RELEASE_, REPLACE_, ROLLBACK_, SAVEPOINT_, SELECT_, UPDATE_, VACUUM_, VALUES_, WITH_}
FAIL
FAIL    github.com/kyleconroy/sqlc/internal/endtoend    0.469s
FAIL

I believe this error comes from ./internal/engine/sqlite.Parser.Parse(), which make sense. SQLite doesn't have namespaces, so it cannot parse sqlc.arg(ident). It can parse sqlc_arg(ident), but then of course the rewrite rule doesn't do its job.

What should we do? Perhaps move the rewriting before the engine parse step? Or extend the syntax of named parameters? I'm more than happy to write some code, ask more questions, or hear more opinions.

Relevant log output

$ go test -run=TestReplay/testdata/sqlc_arg/sqlite ./internal/endtoend
line 2:34 extraneous input '(' expecting {<EOF>, ';', ALTER_, ANALYZE_, ATTACH_, BEGIN_, COMMIT_, CREATE_, DEFAULT_, DELETE_, DETACH_, DROP_, END_, EXPLAIN_, INSERT_, PRAGMA_, REINDEX_, RELEASE_, REPLACE_, ROLLBACK_, SAVEPOINT_, SELECT_, UPDATE_, VACUUM_, VALUES_, WITH_}
line 5:34 extraneous input '(' expecting {<EOF>, ';', ALTER_, ANALYZE_, ATTACH_, BEGIN_, COMMIT_, CREATE_, DEFAULT_, DELETE_, DETACH_, DROP_, END_, EXPLAIN_, INSERT_, PRAGMA_, REINDEX_, RELEASE_, REPLACE_, ROLLBACK_, SAVEPOINT_, SELECT_, UPDATE_, VACUUM_, VALUES_, WITH_}
line 6:0 extraneous input '<EOF>' expecting {';', ALTER_, ANALYZE_, ATTACH_, BEGIN_, COMMIT_, CREATE_, DEFAULT_, DELETE_, DETACH_, DROP_, END_, EXPLAIN_, INSERT_, PRAGMA_, REINDEX_, RELEASE_, REPLACE_, ROLLBACK_, SAVEPOINT_, SELECT_, UPDATE_, VACUUM_, VALUES_, WITH_}
--- FAIL: TestReplay (0.05s)
    --- FAIL: TestReplay/testdata/sqlc_arg/sqlite (0.01s)
        endtoend_test.go:100: sqlc generate failed: # package db
            query.sql:1:1: extraneous input '<EOF>' expecting {';', ALTER_, ANALYZE_, ATTACH_, BEGIN_, COMMIT_, CREATE_, DEFAULT_, DELETE_, DETACH_, DROP_, END_, EXPLAIN_, INSERT_, PRAGMA_, REINDEX_, RELEASE_, REPLACE_, ROLLBACK_, SAVEPOINT_, SELECT_, UPDATE_, VACUUM_, VALUES_, WITH_}
FAIL
FAIL    github.com/kyleconroy/sqlc/internal/endtoend    0.469s
FAIL

Database schema

CREATE TABLE t (a INTEGER);

SQL queries

-- name: FuncParamIdent :many
SELECT a FROM t WHERE a = sqlc.arg(b);

-- name: FuncParamString :many
SELECT a FROM t WHERE a = sqlc.arg('b');

Configuration

version: "2"
sql:
- schema: catalog.sql
  queries: query.sql
  engine: sqlite
  gen:
    go:
      out: db

Playground URL

https://play.sqlc.dev/p/cb336b688e15e85a40b86bcda950315188279233546f4e0e3996cf7dfc84b315

What operating system are you using?

macOS

What database engines are you using?

No response

What type of code are you generating?

Go

[jreut]

After some very quick searching, I found that this issue duplicates #1881 and #1954.

[andrewmbenton]

I believe this issue is resolved on latest main after merging #2274.

I am going to close this issue for now presuming the above is accurate, but if an problem persists please feel free to reopen.