sqlc: sqlc.embed() generates code that cannot handle NULL items

Version

1.18.0

What happened?

I was testing to implement sqlc.embed() in order to simplify my conversions routines from sqlc to protobuf.

I have this query:

-- name: GetChannelLayers :many
SELECT sqlc.embed(t)
FROM unnest(sqlc.narg('channel_layer_ids')::TEXT[]) WITH ORDINALITY o(channel_layer_id, ord)
LEFT JOIN channel_layer AS t ON t.channel_layer_id = o.channel_layer_id::UUID AND t.tenant_id = $1;

The input is a list of IDs that i expect to get from the DB. If the ID is not found I expect to get a NULL row. Unfortunately it seems like the code for the new sqlc.embed() feature doesn’t handle this case very well since I get a scan error if I try to query for a non-existent ID.

pgx.ScanArgError: can't scan into dest[0]: cannot scan null into *string

The generated output code:

const getChannelLayers = `-- name: GetChannelLayers :many
SELECT t.tenant_id, t.channel_id, t.channel_layer_id, t.name, t.description, t.created, t.updated, t.removed, t.disabled, t.condition_ref, t.z_index, t.width, t.height, t.pos_x, t.pos_y, t.alignment, t.content_ref
FROM unnest($2::TEXT[]) WITH ORDINALITY o(channel_layer_id, ord)
LEFT JOIN channel_layer AS t ON t.channel_layer_id = o.channel_layer_id::UUID AND t.tenant_id = $1
`

type GetChannelLayersParams struct {
	TenantID        string
	ChannelLayerIds []string
}

type ChannelLayer struct {
	TenantID       string
	ChannelID      uuid.UUID
	ChannelLayerID uuid.UUID
	Name           string
	Description    sql.NullString
	Created        time.Time
	Updated        time.Time
	Removed        sql.NullTime
	Disabled       sql.NullTime
	ConditionRef   sql.NullString
	ZIndex         sql.NullInt32
	Width          sql.NullInt32
	Height         sql.NullInt32
	PosX           sql.NullInt32
	PosY           sql.NullInt32
	Alignment      ChannelLayerAlignment
	ContentRef     sql.NullString
}

type GetChannelLayersRow struct {
	ChannelLayer ChannelLayer
}

func (q *Queries) GetChannelLayers(ctx context.Context, arg GetChannelLayersParams) ([]*GetChannelLayersRow, error) {
	rows, err := q.db.Query(ctx, getChannelLayers, arg.TenantID, arg.ChannelLayerIds)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var items []*GetChannelLayersRow
	for rows.Next() {
		var i GetChannelLayersRow
		if err := rows.Scan(
			&i.ChannelLayer.TenantID,
			&i.ChannelLayer.ChannelID,
			&i.ChannelLayer.ChannelLayerID,
			&i.ChannelLayer.Name,
			&i.ChannelLayer.Description,
			&i.ChannelLayer.Created,
			&i.ChannelLayer.Updated,
			&i.ChannelLayer.Removed,
			&i.ChannelLayer.Disabled,
			&i.ChannelLayer.ConditionRef,
			&i.ChannelLayer.ZIndex,
			&i.ChannelLayer.Width,
			&i.ChannelLayer.Height,
			&i.ChannelLayer.PosX,
			&i.ChannelLayer.PosY,
			&i.ChannelLayer.Alignment,
			&i.ChannelLayer.ContentRef,
		); err != nil {
			return nil, err
		}
		items = append(items, &i)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}

I’m actually not sure how to solve this. I need the LEFT JOIN because reasons… Perhaps it’s impossible to support this case?

Perhaps sqlc can create a temp/hidden type where all fields are NULL-able. Scan the fields using this type and then check if the PRIMARY KEY fields are Valid. If they are valid, convert the type with the NULL-able fields into the normal type. If the fields are not valid then return a nil object pointer for the embedded field.

@nickjackson, any input?

What operating system are you using?

Linux

What database engines are you using?

PostgreSQL

What type of code are you generating?

Go