Skip to content

Fields not nullable when INNER JOINing to a LEFT JOIN #604

Closed
@maxhawkins

Description

@maxhawkins

This contrived example:

CREATE TABLE users (
  user_id    INT PRIMARY KEY,
  city_id    INT -- nullable
);
CREATE TABLE cities (
  city_id    INT PRIMARY KEY,
  mayor_id   INT NOT NULL
);
CREATE TABLE mayors (
  mayor_id   INT PRIMARY KEY,
  full_name  TEXT NOT NULL
);

-- name: GetMayors :many
SELECT
    user_id,
    mayors.full_name
FROM users
LEFT JOIN cities USING (city_id)
INNER JOIN mayors USING (mayor_id);

Produces this results struct:

type GetMayorsRow struct {
	UserID   int32
	FullName string
}

Running this query will fail if city_id is NULL. Because of the left join, the correct type for FullName is sql.NullString.

Metadata

Metadata

Assignees

No one assigned

    Labels

    bugSomething isn't working

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions