COALESCE in INSERT results in missing arguments

[andrei-dascalu]

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 works

Database 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

https://play.sqlc.dev/p/8471d8c7965409b41c01510cf680b8026ae5268dc83ae2591e891c432aef0432

What operating system are you using?

macOS

What database engines are you using?

MySQL

What type of code are you generating?

Go

[andrewmbenton]

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 COALESCE in INSERT statements, even without sqlc.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/0d614a07b58fbacdea078b51d5e6d338414dc6558059249294c29f74212f9104

And here's a playground link demonstrating the bug using PostgreSQL as engine and without sqlc.narg(): https://play.sqlc.dev/p/28b5372fd509a34792e30e7eb62bbbe74d400ab7e7cb1652d58bc9f7ee1ec5e7

[andrei-dascalu]

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]

The problem occurs because COALESCE is treated as CoalesceExpr instead of FuncCall on ast.

[kyleconroy]

Fix has been merged, will be included in v1.23.0