Unable to scan into an array of struct

[egonelbre]

Scanning array of spanner structs into a slice of Go structs doesn't seem to be working. Smallest reproducer:

func structTypes(projectId, instanceId, databaseId string) error {
	ctx := context.Background()
	db, err := sql.Open("spanner", fmt.Sprintf("projects/%s/instances/%s/databases/%s", projectId, instanceId, databaseId))
	if err != nil {
		return fmt.Errorf("failed to open database connection: %v\n", err)
	}
	defer db.Close()

	type Entry struct {
		ID   int64
		Name string
	}

	entries := []Entry{
		{ID: 0, Name: "Hello"},
		{ID: 1, Name: "World"},
	}

	rows, err := db.QueryContext(ctx, "SELECT ARRAY(SELECT AS STRUCT * FROM UNNEST(@entries)) AS entries", entries)
	if err != nil {
		return fmt.Errorf("failed to execute query: %v", err)
	}
	defer rows.Close()

	for rows.Next() {
		var allEntries []Entry
		if err := rows.Scan(&allEntries); err != nil {
			return fmt.Errorf("failed to scan row values: %v", err)
		}
		fmt.Printf("%#v\n", allEntries)
	}
	if err := rows.Err(); err != nil {
		return fmt.Errorf("failed to execute query: %v", err)
	}
	return nil
}

This fails with:

2024/11/06 14:40:44 failed to scan row values: sql: Scan error on column index 0, name "entries": unsupported Scan, storing driver.Value type <nil> into type *[]main.Entry
exit status 1

If the code is changed to use TO_JSON and spanner.NullJSON it is able to return the correct values.

It's unclear whether the issue is in go-sql-spanner or upstream.

[egonelbre]

Looks like upstream code does work:

client := must(spanner.NewClient(ctx, "projects/"+ProjectID+"/instances/"+InstanceID+"/databases/"+DatabaseName))
defer client.Close()

type Entry struct {
	Name  string
	Value int64
}

input := []Entry{
	{Name: "Hello", Value: 1000},
	{Name: "World", Value: 2000},
}

rows := client.ReadOnlyTransaction().Query(ctx, spanner.Statement{
	SQL: `SELECT ARRAY(SELECT AS STRUCT Name, Value FROM UNNEST(@input)) AS entries`,
	Params: map[string]any{
		"input": input,
	},
})
for {
	row, err := rows.Next()
	if err != nil {
		fmt.Fprintln(os.Stderr, err)
		break
	}

	var output []*Entry // NOTE: []Entry does not work
	if err := row.Columns(&output); err != nil {
		fmt.Fprintln(os.Stderr, err)
	}
	for _, entry := range output {
		fmt.Println(entry)
	}
}

[egonelbre]

It does look like that supporting that behavior is difficult with the current database/sql implementation. golang/go#67546 would probably allow to fix that.

An alternative would be to instead pass spanner.GenericColumnValue back to the user, which would allow at least to get the values out.

var generic spanner.GenericColumnValue
if err := rows.Scan(&generic); err != nil {
	return fmt.Errorf("failed to scan row values: %v", err)
}
var allEntries []*Entry
err := generic.Decode(&allEntries)

The

go-sql-spanner/rows.go

Line 268 in c39f57f

}

default cases would then read:

	default:
		dest[i] = col
	}
default:
	dest[i] = col
}

The drawback for this approach is that when you use GenericColumnValue, then when adding more specific implementations then they might break; because they won't be able to scan into it anymore. However, since the database/sql feature seems to be on track for 1.24, it can be fixed then.