Repository navigation
COALESCE in INSERT results in missing arguments #2748
Description
Version
1.21.0
What happened?
When writing a query like
INSERT INTO authors (
address,
name,
bio
) VALUES (
?,
COALESCE(sqlc.narg("authName"), ""),
COALESCE(sqlc.narg("authBio"), "")
);
Only the ? is recognised and add as a parameter to the insert query
Relevant log output
none - generate worksDatabase schema
CREATE TABLE authors (
id BIGINT AUTO_INCREMENT NOT NULL,
address VARCHAR(200) NOT NULL,
name VARCHAR(20) NOT NULL,
bio LONGTEXT NOT NULL
);SQL queries
-- name: AddAuthor :execlastid
INSERT INTO authors (
address,
name,
bio
) VALUES (
?,
COALESCE(sqlc.narg("calName"), ""),
COALESCE(sqlc.narg("calDescription"), "")
);Configuration
{
"version": "1",
"packages": [
{
"path": "db",
"engine": "mysql",
"schema": "query.sql",
"queries": "query.sql"
}
]
}Playground URL
What operating system are you using?
macOS
What database engines are you using?
MySQL
What type of code are you generating?
Go
Activity
andrewmbenton commentedon Sep 21, 2023
I think the bug is sadly worse than your description, and I'll probably update the title. It appears that sqlc fails to generate input parameters for values passed to
COALESCEinINSERTstatements, even withoutsqlc.narg(). And I also see this behavior with PostgreSQL.This playground link updates the one you provided to demonstrate that things work fine in the context of
UPDATE: https://play.sqlc.dev/p/0d614a07b58fbacdea078b51d5e6d338414dc6558059249294c29f74212f9104And here's a playground link demonstrating the bug using PostgreSQL as engine and without
sqlc.narg(): https://play.sqlc.dev/p/28b5372fd509a34792e30e7eb62bbbe74d400ab7e7cb1652d58bc9f7ee1ec5e7[-]COALESCE and narg results in missing arguments[/-][+]COALESCE in INSERT results in missing arguments[/+]andrei-dascalu commentedon Sep 21, 2023
Interestingly this does indeed seem particular to COALESCE. I went back and tried a couple of other functions like substrings (both for postgresql and the mysql variant) but they seem ok.
orisano commentedon Sep 21, 2023
The problem occurs because COALESCE is treated as CoalesceExpr instead of FuncCall on ast.
kyleconroy commentedon Oct 12, 2023
Fix has been merged, will be included in v1.23.0