Incorrect SUM() type inference (missing NULL possibility) #3028
Description
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