Repository navigation
sqlc.slice() breaks SQLite queries that contain other parameters. #2452
Description
Activity
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?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,?,?4causes the query to not match anything when the parameters are substituted, and no error is thrown. However if I rearrange theWHEREclause so that thesqlc.slicecomes at the end, things seem to work as expected.I'm running into the same thing. IMO this should at least be included in the documentation as something to be cautious about.
I'm hitting the same issue. This looks like a major bug! I hope it will get resolved.
I'd love to look into patching this. @kyleconroy any pointers on where to look first? So far, the only solution I see is to replace numbered placeholders on query execution dynamically, adding the size of the slice (for example, if the slice is the first placeholder, replacing
?2to?(1 + length of the slice)Reacted by SybrenRan into the same issue with SQLite
☹️ Reacted by Ciaran Downey and Michael DebétazSame here with SQLite. I'll try a fix in the generated code to see if a solution can be applied.
EDIT: I think this should be handled during the generation. For example, if the
/*SLICE:col*/?, then the numbered parameters should be assigned at runtime (at least, if it's not the last parameter). It would be an extra cost for the query, but there is no way to know how many parameters there will be before having the actual slice.For the time being, I just copy/paste the generated code in a
internals/database/users.raw.goand modify it for my use.
Version
1.18.0
What happened?
sqlc generates queries using numbered parameters, but if a
sqlc.slicecomes before other parameters, the numbers will no longer match.Relevant log output
No response
Database schema
SQL queries
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