NULL values for embedded structs and row() with pgx.RowToStructByNameLax

[taai]

Hi,

In example of how to use pgx.RowToStructByNameLax the row() is being used in SQL query:

pgx/rows_test.go

Lines 764 to 797 in eab316e

	func TestRowToStructByNameLaxRowValue(t *testing.T) {
	type AnotherTable struct{}
	type User struct {
	UserID int `json:"userId" db:"user_id"`
	Name string `json:"name" db:"name"`
	}
	type UserAPIKey struct {
	UserAPIKeyID int `json:"userApiKeyId" db:"user_api_key_id"`
	UserID int `json:"userId" db:"user_id"`
	User *User `json:"user" db:"user"`
	AnotherTable *AnotherTable `json:"anotherTable" db:"another_table"`
	}
	defaultConnTestRunner.RunTest(context.Background(), t, func(ctx context.Context, t testing.TB, conn *pgx.Conn) {
	pgxtest.SkipCockroachDB(t, conn, "")
	rows, _ := conn.Query(ctx, `
	WITH user_api_keys AS (
	SELECT 1 AS user_id, 101 AS user_api_key_id, 'abc123' AS api_key
	), users AS (
	SELECT 1 AS user_id, 'John Doe' AS name
	)
	SELECT user_api_keys.user_api_key_id, user_api_keys.user_id, row(users.*) AS user
	FROM user_api_keys
	LEFT JOIN users ON users.user_id = user_api_keys.user_id
	WHERE user_api_keys.api_key = 'abc123';
	`)
	slice, err := pgx.CollectRows(rows, pgx.RowToStructByNameLax[UserAPIKey])
	assert.NoError(t, err)
	assert.ElementsMatch(t, slice, []UserAPIKey{{UserAPIKeyID: 101, UserID: 1, User: &User{UserID: 1, Name: "John Doe"}, AnotherTable: nil}})
	})
	}

This is the query:

WITH user_api_keys AS (
	SELECT 1 AS user_id, 101 AS user_api_key_id, 'abc123' AS api_key
), users AS (
	SELECT 1 AS user_id, 'John Doe' AS name
)
SELECT
	user_api_keys.user_api_key_id,
	user_api_keys.user_id,
	row(users.*) AS user
FROM user_api_keys
LEFT JOIN users ON users.user_id = user_api_keys.user_id
WHERE user_api_keys.api_key = 'abc123';

But if there is no matching relation (the users.* is NULL), the row(users.*) AS user part is going to return an empty row like this: (,) which leads error in pgx.RowToStructByNameLax - something about not being able to set null values (*int) to not-null (int) variables in the embedded struct.

A solution is to do this:

(CASE WHEN users.id IS NOT NULL THEN row(users.*) END) AS user

This will return a non-empty row() or a NULL and pgx.RowToStructByNameLax works ok with that.

Is there a more elegant way? Or can this (not taking empty row() ((,)) as nil) be assumed to be a bug in pgx.RowToStructByNameLax that should be fixed?

[jackc]

I do not know of a more elegant way, and I also do not think it is a bug.

The problem is that row(null, null) is being scanned into User. But the UserID and Name fields of User cannot be NULL.

Also, this is not specific to pgx.RowToStructByNameLax, the code in question is the general case composite to struct scanning system. You would have the same issue if you called Scan directly.

[geeeeeeeeek]

@jackc Have you considered adopting something similar to this sqlx pull request? Setting the pointer to users to null instead of scanning the nils into an empty struct.

jmoiron/sqlx#847

Nested structs are now only instantiated when one of the database columns in that nested struct is not nil. This allows objects scanned in left/outer joins to keep their natural types (instead of setting everything to NullableX).

[jackc]

TBH, I haven't thought much about it.

It might be a good feature, but it would be a breaking change. There would need to be some way to opt-in, presumably on a per-call basis.