`SELECT COALESCE()` results are always assumed non-null

[mpyw]

Version

1.13.0

What happened?

Possibly related to:

• #937
• #1096

SELECT COALESCE(x, y) results are always assumed non-null. If all fields are nullable, result typing should not be string but NullString.

Database schema

CREATE TABLE examples(
  id BIGSERIAL PRIMARY KEY,
  nullable text,
  nonnull text NOT NULL
);

SQL queries

-- name: GetExample :one
SELECT
  nullable,
  nonnull,
  COALESCE(nonnull) AS coalesce_nonnull,
  COALESCE(nullable) AS coalesce_nullable,
  COALESCE(nonnull, nonnull) AS coalesce_nonnull_nonnull,
  COALESCE(nonnull, nullable) AS coalesce_nonnull_nullable,
  COALESCE(nullable, nonnull) AS coalesce_nullable_nonnull,
  COALESCE(nullable, nullable) AS coalesce_nullable_nullable
FROM examples WHERE id = $1 LIMIT 1;

Configuration

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

Outputs

Expected:

type GetExampleRow struct {
	Nullable                 sql.NullString
	Nonnull                  string
	CoalesceNonnull          string
	CoalesceNullable         sql.NullString
	CoalesceNonnullNonnull   string
	CoalesceNonnullNullable  string
	CoalesceNullableNonnull  string
	CoalesceNullableNullable sql.NullString
}

Actual:

type GetExampleRow struct {
	Nullable                 sql.NullString
	Nonnull                  string
	CoalesceNonnull          string
	CoalesceNullable         string
	CoalesceNonnullNonnull   string
	CoalesceNonnullNullable  string
	CoalesceNullableNonnull  string
	CoalesceNullableNullable string
}

Playground URL

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

What operating system are you using?

macOS

What database engines are you using?

PostgreSQL

What type of code are you generating?

Go

[mpyw]

Probably we should fix here:

https://github.com/kyleconroy/sqlc/blob/582ba6e79b354683c363da5f7c352fce50fd3fc7/internal/compiler/output_columns.go#L146-L171

[mpyw]

@kyleconroy This fix is essential for my company's products, which make heavy use of COALESCE. And they don't willing to use forked packages. I would appreciate if you could review the PR if you have time.