`sqlc.slice()` breaks SQLite queries that contain other parameters.

[SoMuchForSubtlety]

Version

1.18.0

What happened?

sqlc generates queries using numbered parameters, but if a sqlc.slice comes before other parameters, the numbers will no longer match.

Relevant log output

No response

Database schema

CREATE TABLE authors (
  id   integer PRIMARY KEY,
  name text      NOT NULL,
  age  ingeger
);

SQL queries

-- name: SomeQuery :many
select * from authors
where name in (sqlc.slice(names)) 
  and age = $age;

Configuration

{
  "version": "1",
  "packages": [
    {
      "path": "db",
      "engine": "sqlite",
      "schema": "query.sql",
      "queries": "query.sql"
    }
  ]
}

Playground URL

https://play.sqlc.dev/p/009df3b554022fd08ed62f1940e1ec63303e165e7e95182eb702d4b9351a56c4

What operating system are you using?

Linux

What database engines are you using?

SQLite

What type of code are you generating?

Go

[ciarand]

Just ran into this. Is there a reason the SQLite engine can't use named parameters? If I were to submit a PR to switch to using named params for both non-slice params and generated names for slice params ((sqlc.slice('my_slice')) => (@my_slice__1, @my_slice__2, @my_slice__3)) would that be considered for acceptance?

[bengesoff]

I've run into the same issue. I have this SQL code:

-- name: GetRuns :many
SELECT runs.*, scenarios.name AS scenario_name
FROM runs
JOIN scenarios
ON scenarios.id = runs.scenario_id
WHERE
    (timestamp > sqlc.narg('run_date_after') OR @run_date_after IS NULL)
    AND (timestamp < sqlc.narg('run_date_before') OR @run_date_before IS NULL)
    AND (status IN (sqlc.slice('filter_run_status')))
    AND (scenarios.name = sqlc.narg('filter_scenario_name') OR @filter_scenario_name IS NULL);

And it gets compiled into this query:

-- name: GetRuns :many
SELECT runs.id, runs.timestamp, runs.status, scenarios.name AS scenario_name
FROM runs
JOIN scenarios
ON scenarios.id = runs.scenario_id
WHERE
    (timestamp > ?1 OR ?1 IS NULL)
    AND (timestamp < ?2 OR ?2 IS NULL)
    AND (status IN (?,?,?,?))
    AND (scenarios.name = ?4 OR ?4 IS NULL)

The whole ?2, ?, ?4 causes the query to not match anything when the parameters are substituted, and no error is thrown. However if I rearrange the WHERE clause so that the sqlc.slice comes at the end, things seem to work as expected.

[sybrenstuvel]

I'm running into the same thing. IMO this should at least be included in the documentation as something to be cautious about.