sqlc-dev
emit_pointer_for_null_types results in left join scan error
Version
1.27.0
What happened?
when using emit_pointer_for_null_types my left join is []string instead of []*string
Relevant log output
2024/10/11 18:20:31 can't scan into dest[8]: failed to scan array element 0: cannot scan NULL into *string
### Database schema
```sql
CREATE TABLE iam_user (
uuid uuid NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
name TEXT NOT NULL UNIQUE,
email TEXT NOT NULL UNIQUE,
alias TEXT NULL,
description TEXT NULL,
created_at timestamptz NOT NULL DEFAULT NOW(),
updated_at timestamptz NOT NULL DEFAULT NOW(),
deleted_at timestamptz
);
CREATE TABLE iam_group (
uuid uuid NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
name TEXT NOT NULL UNIQUE,
alias TEXT NULL,
description TEXT NULL,
created_at timestamptz NOT NULL DEFAULT NOW(),
updated_at timestamptz NOT NULL DEFAULT NOW(),
deleted_at timestamptz NULL
);
CREATE TABLE iam_group_member (
uuid uuid NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
group_uuid uuid NOT NULL UNIQUE REFERENCES iam_group(uuid) ON UPDATE NO ACTION ON DELETE CASCADE,
user_uuid uuid NOT NULL UNIQUE REFERENCES iam_user(uuid) ON UPDATE NO ACTION ON DELETE CASCADE
);
SQL queries
go
const GetUser = `-- name: GetUser :one
SELECT
u.uuid, u.name, u.email, u.alias, u.description, u.created_at, u.updated_at, u.deleted_at, COALESCE(array_agg(g.name), '{}')::text[] AS memberof
FROM
iam_user u
LEFT JOIN
iam_group_member gm ON u.uuid = gm.user_uuid
LEFT JOIN
iam_group g ON gm.group_uuid = g.uuid
WHERE
(
(u.uuid = $1 AND $1 IS NOT NULL)
OR
(u.name = $2 AND $2 IS NOT NULL)
)
GROUP BY
u.uuid
LIMIT 1
`
type GetUserParams struct {
Uuid *uuid.UUID
Name *string
}
type GetUserRow struct {
Uuid uuid.UUID
Name string
Email string
Alias *string
Description *string
CreatedAt pgtype.Timestamptz
UpdatedAt pgtype.Timestamptz
DeletedAt pgtype.Timestamptz
Memberof []string
}
// generate:
// package: iam.v1
// message: target: user
func (q *Queries) GetUser(ctx context.Context, db DBTX, arg GetUserParams) (GetUserRow, error) {
row := db.QueryRow(ctx, GetUser, arg.Uuid, arg.Name)
var i GetUserRow
err := row.Scan(
&i.Uuid,
&i.Name,
&i.Email,
&i.Alias,
&i.Description,
&i.CreatedAt,
&i.UpdatedAt,
&i.DeletedAt,
&i.Memberof,
)
return i, err
}
### Configuration
```yaml
json
{
"version": "2",
"plugins": [
{
"name": "proto",
"process": {
"cmd": "sqlc-gen-proto"
}
}
],
"sql": [
{
"schema": "./db/migrations/*/schema",
"queries": "./db/migrations/*/queries",
"engine": "postgresql",
"analyzer": {
"database": true
},
"gen": {
"go": {
"package": "sqlc",
"sql_package": "pgx/v5",
"out": "gen/sqlc",
"emit_methods_with_db_argument": true,
"emit_exported_queries": true,
"emit_pointers_for_null_types": true,
"overrides": [
{
"db_type": "uuid",
"go_type": {
"import": "github.com/google/uuid",
"type": "UUID"
}
},
{
"nullable": true,
"db_type": "uuid",
"go_type": {
"import": "github.com/google/uuid",
"type": "UUID",
"pointer": true
}
}
]
}
}
},
{
"engine": "postgresql",
"schema": "./db/migrations/*/schema",
"queries": "./db/migrations/*/queries",
"codegen": [
{
"out": "gen",
"plugin": "proto",
"options": {
"out_dir": "./gen",
"user_defined_dir": "./user_defined",
"one_of_id": "identifier",
"global_replace_type": {
"uuid": "string"
}
}
}
]
}
]
}
### Playground URL
_No response_
### What operating system are you using?
_No response_
### What database engines are you using?
_No response_
### What type of code are you generating?
_No response_
Not sure this has anything to do with emit_pointer_for_null_types and the fact that Postgresql has null entries, and it works if its []*string.
Anyway for me to tell sqlc to use []*string for my array_aggr in my query, currently I have to cast it to ::text[] for it not to be []interface{}
I was able to handle this to use []string by using the following statement:
COALESCE(array_agg(COALESCE(g.name, ''))::text[], '{}')::text[] AS memberof
I'm still curious though If I wanted ::text[] to be a []*string how I'd accomplish it.
Looking at the pg convert type function it looks like if its an array... it always comes back []string.
@Smithx10 were you able to get a pointer result ? I'm facing the same problem
