NULLIF returns bool instead of correct type

[kyleconroy]

Version

1.14.0

What happened?

Actually, NULLIF also smells bad. When using it in a query, the Go struct is generated with a field with a bool type, instead of a NullInt64 type.

Database schema

CREATE TABLE author (
  id bigserial NOT NULL
);

SQL queries

-- name: GetRestrictedId :one
SELECT
  NULLIF(id, $1) restricted_id
FROM
  author;

Configuration

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

Playground URL

https://play.sqlc.dev/p/321eb83b945edfab5a41d8c91feb596bcab9860ce1780639d1f9863207a20644

What operating system are you using?

macOS

What database engines are you using?

PostgreSQL

What type of code are you generating?

Go

Originally posted by @euller88 in https://github.com/kyleconroy/sqlc/issues/92#issuecomment-536583273

[skabbes]

I think this is an endless game of whack-a-mole. I think in general type-checking is impossible, take for example:

select
  sqlc.arg('string_or_float')::int as x

how can sqlc know what type the first arg is? It can be anything castable to int - including a string or floating point number, numeric(10, 2), etc. I (personally) believe you need to give the developer control over this.

I think my proposal: https://github.com/kyleconroy/sqlc/issues/1525 would at least give the developer control of this behavior, which would allow sqlc to still be usable (but less magic), and also would leave room for "adding magic" in the future. (because theoretically this individual case should be type-checkable).

-- name: GetRestrictedId :one
SELECT
  sqlc.output(
    NULLIF(id, sqlc.arg('ignore_id', 'type', 'bigint', 'nullable', false)),
   'nullable', true) as restricted_id
FROM
  author;

[kyleconroy]

This is fixed in v1.23.0 by enabling the database-backed query analyzer. We added a test case for this issue so it won’t break in the future.

You can play around with the working example on the playground