Null sub-struct fields

[leoquack]

Hello, thank you for making this library, it has helped me a lot!

However, I've stumbled across an edge case I believe. Either that or my design is off.

When I scan into a struct that has a pointer sub-struct, it gives an error if the fields of the sub-struct are null. Just to clarify, the reason I have assigned the sub-struct to be a pointer value, is to be able to accept null values. So its <nil> when any of its values cannot be scanned

I haven't looked into it to figure out why, but JSON unmarshal works this way?

Take the following example:

err = pgxscan.Select(ctx, db, &users, `
    SELECT
        a."ignore_field",
        a."address_id",
        b."ignore_field" as "address.ignore_field" -- specifically this line.
    FROM "user" a
    LEFT JOIN "address" b ON b."address_id" = a."address_id"
    `)

See, "address" in this case is pointer to struct (Address). But when it's fields are NULL, this query returns an error: eg `cannot scan null into string`.

I found a workaround using json objects, but it's not particularly tasteful:

err = pgxscan.Select(ctx, db, &users, `
    SELECT
        a."ignore_field",
        a."address_id",
        -- using JSON works fine but needs json tag for each struct field.
        json_build_object(
            'ignore_field', b."ignore_field"
        ) as "address"
    FROM "user" a
    LEFT JOIN "address" b ON b."address_id" = a."address_id"
    `)

Is there something I missed, or maybe a better design where I can keep the normal select fields without using JSON?

See a full code example

```go package main import ( "context" "fmt" "log" "github.com/jackc/pgx/v4/pgxpool" "github.com/georgysavva/scany/pgxscan" ) type User struct { IgnoreField string `db:"ignore_field"` AddressID *uint64 `db:"address_id"` Address *Address `db:"address"` } type Address struct { IgnoreField string `db:"ignore_field" json:"ignore_field"` } func main() { var err error ctx := context.Background() db, err := pgxpool.Connect(ctx, "connection-string") if err != nil { log.Fatalf("couldn't connect: %s", err) } _, err = db.Exec(ctx, ` create table if not exists "address" ( "address_id" integer generated always as identity primary key, "ignore_field" text not null );`) if err != nil { log.Fatalf("couldn't create address: %s", err) } _, err = db.Exec(ctx, ` create table if not exists "user" ( "user_id" integer generated always as identity primary key, "ignore_field" text not null, "address_id" integer constraint user_address_address_id_fk references "address" );`) if err != nil { log.Fatalf("couldn't create user: %s", err) } insertData(ctx, db) users := make([]User, 0) // Select 1: not working. err = pgxscan.Select(ctx, db, &users, ` SELECT a."ignore_field", a."address_id", b."ignore_field" as "address.ignore_field" FROM "user" a LEFT JOIN "address" b ON b."address_id" = a."address_id" `) if err != nil { log.Fatalf("couldn't select 1: %s", err) } // Select 2: working but with json object (plus needs json tags on each field). err = pgxscan.Select(ctx, db, &users, ` SELECT a."ignore_field", a."address_id", json_build_object( 'ignore_field', b."ignore_field" ) as "address" FROM "user" a LEFT JOIN "address" b ON b."address_id" = a."address_id" `) if err != nil { log.Fatalf("couldn't select 2: %s", err) } for _, user := range users { fmt.Printf("result %+v\n", user.Address) } } func insertData(ctx context.Context, db *pgxpool.Pool) { _, err := db.Exec(ctx, ` insert into "address" ("ignore_field") values ('test address 1');`) if err != nil { log.Fatalf("couldn't insert address: %s", err) } _, err = db.Exec(ctx, ` insert into "user" ("ignore_field", "address_id") values ('test 1', 1), ('test 2', NULL);`) if err != nil { log.Fatalf("couldn't insert user: %s", err) } } ```

[georgysavva]

Hi there! Your intention to use a pointer to support NULL value was current, but you did it in the wrong place. The NULL value belongs to the IgnoreField field, and hence, the pointer should be there:

type User struct {
    IgnoreField string   `db:"ignore_field"`
    AddressID   *uint64  `db:"address_id"`
    Address     Address `db:"address"` // Here the pointer is not needed. But you can still use it, it doesn't affect the scanning results.
}

type Address struct {
    IgnoreField *string `db:"ignore_field"`
}

Note that, the Address struct is just a container/envelope for scany, and it doesn't matter whether you specify it by pointer or by value. I hope that helps, let me know if you have more questions.

[georgysavva]

Closing due to inactivity. Feel free to reopen.