Incorrect SUM() type inference (missing NULL possibility)

[danthegoodman1]

Version

1.24.0

What happened?

When you do a query like select sum(amount) from my_table where user_id = $1 the return of the generated go function will be of type int64. However, when something such as a user doesn't exist, it returns a single NULL row rather than no rows:

-- name: CountActiveSubscriptions :one
SELECT SUM(quantity)
FROM subscriptions s
WHERE s.creator_id = $1
  AND s.active = true
  AND s.feature = $2
;

A solution is to wrap with COALESCE(..., 0)::INT8

Relevant log output

No response

Database schema

CREATE TABLE "public"."subscriptions" (
    "id" text NOT NULL,
    "creator_id" text NOT NULL,
    "processor_id" text NOT NULL,
    "processor_sub_id" text NOT NULL,
    "renew_interval_months" int8 NOT NULL,
    "price" int8 NOT NULL,
    "quantity" int8 NOT NULL,
    "started_at" timestamptz NOT NULL DEFAULT now(),
    "renews_at" timestamptz NOT NULL,
    "canceled_at" timestamptz,
    "ended_at" timestamptz,
    "active" bool NOT NULL DEFAULT false,
    "created_at" timestamptz NOT NULL DEFAULT now(),
    "updated_at" timestamptz NOT NULL DEFAULT now(),
    "feature" text NOT NULL DEFAULT 'tts-slot',
    "is_trialing" bool NOT NULL DEFAULT false,
    PRIMARY KEY ("creator_id","id")
);

### SQL queries

_No response_

### Configuration

```yaml
version: 2
sql:
  - engine: "postgresql"
    schema: "schema.sql"
    queries: "./queries/"
    gen:
      go:
        sql_package: "pgx/v5"
        out: "query"
        package: "query"
        overrides:
          # for some reason we sometimes need the catalog prefix and sometimes not although docs say we should always use it
          - db_type: "pg_catalog.bool"
            go_type: "database/sql.NullBool"
            nullable: true
          - db_type: "bool"
            go_type: "database/sql.NullBool"
            nullable: true

          - db_type: "int8"
            go_type: "database/sql.NullInt64"
            nullable: true
          - db_type: "pg_catalog.int8"
            go_type: "database/sql.NullInt64"
            nullable: true

          - db_type: "int2"
            go_type: "database/sql.NullInt16"
            nullable: true
          - db_type: "pg_catalog.int2"
            go_type: "database/sql.NullInt16"
            nullable: true

          - db_type: "float4"
            go_type: "database/sql.NullFloat64"
            nullable: true

          - db_type: "float8"
            go_type: "database/sql.NullFloat64"
            nullable: true

          - db_type: "text"
            go_type: "database/sql.NullString"
            nullable: true

          - db_type: "timestamptz"
            go_type: "database/sql.NullTime"
            nullable: true

          - db_type: "timestamptz"
            go_type: "time.Time"
            nullable: false

          - db_type: "timestamptz"
            go_type: "database/sql.NullTime"
            nullable: false

Playground URL

No response

What operating system are you using?

macOS

What database engines are you using?

PostgreSQL

What type of code are you generating?

Go

[danthegoodman1]

this should return either a *int64 or sql.NullInt64 depending on the configuration, rather than an int64 since it can be a single null row

[ryu-ichiroh]

I agree. I've made the PR. #2275

[Jille]

Related: #3223