sqlc
sqlc copied to clipboard
sqlc-dev
Incorrect SUM() type inference (missing NULL possibility)
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
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
