Left Join not generating correct Null* Types

[fr3fou]

Version

1.11.0

What happened?

After updating to 1.11.0, the code generated when using LEFT JOIN on tables seems to be incorrect.
For the queries and schema given below, I'd expect the following code to be generated:

...
type GetSuggestedUsersByIDRow struct {
	UserID          uuid.UUID
	UserNickname    string
	UserEmail       string
	UserDisplayName string
	UserPassword    sql.NullString
	UserGoogleID    sql.NullString
	UserAppleID     sql.NullString
	UserBio         string
	UserCreatedAt   time.Time
	UserAvatarID    uuid.NullUUID
	MediaID         uuid.NullUUID
	MediaCreatedAt  sql.NullTime
	MediaHash       sql.NullString
	MediaDirectory  sql.NullString
	MediaAuthorID   uuid.NullUUID
	MediaWidth      sql.NullInt32
	MediaHeight     sql.NullInt32
}
...

This is the output when generating queries using 1.10.0.
Instead, with 1.11.0, I get:

...
type GetSuggestedUsersByIDRow struct {
	UserID          uuid.UUID
	UserNickname    string
	UserEmail       string
	UserDisplayName string
	UserPassword    sql.NullString
	UserGoogleID    sql.NullString
	UserAppleID     sql.NullString
	UserBio         string
	UserCreatedAt   time.Time
	UserAvatarID    uuid.NullUUID
	MediaID         uuid.UUID
	MediaCreatedAt  time.Time
	MediaHash       string
	MediaDirectory  string
	MediaAuthorID   uuid.UUID
	MediaWidth      int32
	MediaHeight     int32
}
...

You can see that the joined table (the media), should have Null* types, instead of the plain regular types.

Relevant log output

No response

Database schema

CREATE TABLE "users"
(
    "user_id"           uuid PRIMARY KEY,
    "user_nickname"     VARCHAR(30) UNIQUE NOT NULL,
    "user_email"        TEXT UNIQUE        NOT NULL,
    "user_display_name" TEXT               NOT NULL,
    "user_password"     TEXT               NULL,
    "user_google_id"    TEXT UNIQUE        NULL,
    "user_apple_id"     TEXT UNIQUE        NULL,
    "user_bio"          VARCHAR(160)       NOT NULL DEFAULT '',
    "user_created_at"   TIMESTAMP          NOT NULL DEFAULT (NOW()),
    "user_avatar_id"    uuid UNIQUE        NULL
);

CREATE TABLE "media"
(
    "media_id"         uuid PRIMARY KEY,
    "media_created_at" TIMESTAMP NOT NULL DEFAULT (NOW()),
    "media_hash"       TEXT      NOT NULL,
    "media_directory"  TEXT      NOT NULL,
    "media_author_id"  uuid      NOT NULL,
    "media_width"      INT       NOT NULL,
    "media_height"     INT       NOT NULL
);

ALTER TABLE "users"
    ADD FOREIGN KEY ("user_avatar_id") REFERENCES "media" ("media_id") ON DELETE SET DEFAULT ON UPDATE CASCADE;

SQL queries

CREATE TABLE "users"
(
    "user_id"           uuid PRIMARY KEY,
    "user_nickname"     VARCHAR(30) UNIQUE NOT NULL,
    "user_email"        TEXT UNIQUE        NOT NULL,
    "user_display_name" TEXT               NOT NULL,
    "user_password"     TEXT               NULL,
    "user_google_id"    TEXT UNIQUE        NULL,
    "user_apple_id"     TEXT UNIQUE        NULL,
    "user_bio"          VARCHAR(160)       NOT NULL DEFAULT '',
    "user_created_at"   TIMESTAMP          NOT NULL DEFAULT (NOW()),
    "user_avatar_id"    uuid UNIQUE        NULL
);

CREATE TABLE "media"
(
    "media_id"         uuid PRIMARY KEY,
    "media_created_at" TIMESTAMP NOT NULL DEFAULT (NOW()),
    "media_hash"       TEXT      NOT NULL,
    "media_directory"  TEXT      NOT NULL,
    "media_author_id"  uuid      NOT NULL,
    "media_width"      INT       NOT NULL,
    "media_height"     INT       NOT NULL
);

ALTER TABLE "users"
    ADD FOREIGN KEY ("user_avatar_id") REFERENCES "media" ("media_id") ON DELETE SET DEFAULT ON UPDATE CASCADE;

-- name: GetSuggestedUsersByID :many
SELECT DISTINCT u.*, m.*
FROM users u
         LEFT JOIN media m ON u.user_avatar_id = m.media_id
WHERE u.user_id != @user_id
LIMIT @user_imit;

Configuration

{
  "version": "1",
  "packages": [
    {
      "path": "db",
      "engine": "postgresql",
      "schema": "query.sql",
      "queries": "query.sql"
    }
  ]
}

Playground URL

No response

What operating system are you using?

macOS

What database engines are you using?

PostgreSQL

What type of code are you generating?

Go

[lyubchev]

Any updates on this?

[eriklott]

I'm also having an issue with no NULL types being generated for the left join in the theme_view view below:

-- TABLES

CREATE TABLE theme (
    id uuid PRIMARY KEY,
    name varchar(100) NOT NULL,
    template text NOT NULL,
    status varchar(32) NOT NULL,
    created_at timestamptz NOT NULL,
    updated_at timestamptz NOT NULL
);

CREATE TABLE blob (
    id uuid PRIMARY KEY,
    type varchar(32) NOT NULL,
    status varchar(32) NOT NULL,
    key varchar(128) NOT NULL,
    filename varchar(256) NOT NULL,
    content_type varchar(128) NOT NULL,
    byte_size bigint NOT NULL,
    checksum varchar(128) NOT NULL,
    created_at timestamptz NOT NULL,
    updated_at timestamptz NOT NULL
);

CREATE TABLE theme_thumbnail (
    id uuid PRIMARY KEY,
    theme_id uuid NOT NULL
);

-- VIEW
CREATE VIEW theme_view AS
    SELECT
        t.*,
        -- thumbnail cols
        tt2.id AS thumbnail_id,
        tt2.key AS thumbnail_key,
        tt2.filename AS thumbnail_filename,
        tt2.content_type AS thumbnail_content_type,
        tt2.byte_size AS thumbnail_byte_size,
        tt2.checksum AS thumbnail_checksum,
        tt2.created_at AS thumbnail_created_at,
        tt2.updated_at AS thumbnail_updated_at
    FROM theme t
    LEFT JOIN
        (   SELECT DISTINCT ON (tt1.theme_id) tt1.theme_id, b.*
            FROM theme_thumbnail tt1
            INNER JOIN blob b ON tt1.id = b.id
            WHERE b.status = 'Active'
            ORDER BY tt1.theme_id, b.created_at DESC
        ) tt2 ON t.id = tt2.theme_id;

The following ThemeView struct is generated:

type ThemeView struct {
	ID                   string
	Name                 string
	Template             string
	Status               string
	CreatedAt            time.Time
	UpdatedAt            time.Time
	ThumbnailID          string
	ThumbnailKey         string
	ThumbnailFilename    string
	ThumbnailContentType string
	ThumbnailByteSize    int64
	ThumbnailChecksum    string
	ThumbnailCreatedAt   time.Time
	ThumbnailUpdatedAt   time.Time
}

I expected a ThemeView struct of:

type ThemeView struct {
	ID                   string
	Name                 string
	Template             string
	Status               string
	CreatedAt            time.Time
	UpdatedAt            time.Time
	ThumbnailID          sql.NullString
	ThumbnailKey         sql.NullString
	ThumbnailFilename    sql.NullString
	ThumbnailContentType sql.NullString
	ThumbnailByteSize    sql.NullInt64
	ThumbnailChecksum    sql.NullString
	ThumbnailCreatedAt   sql.NullTime
	ThumbnailUpdatedAt   sql.NullTime
}

Thanks for the hard work on Sqlc! Fantastic package

[lyubchev]

This is such a breaking change for me, any updates?

[sbres]

There are already multiple issues that are related to the same problem this one is the latest one and seems to have more comments, I think we should close the related issues and keep the conversation on this one.

Related issue:
#1349
Related PR:
#1462

[sbres]

I've done some digging the behaviour is breaking on the commit 4663aab.

It's an interesting bug, basically, when we do a select * the selected item does not have an alias like the ones on the join.
From my tests, there are 2 ways to work around while a fix is done.

1- Avoid using select * and put every single field by hand
2- Avoid using table aliases

Here is a playground with all the examples.
https://play.sqlc.dev/p/17ecf791ac89a280999a21b2ef9796eae2e0a7ad4adeed1ac5a9063f31eded6d

@timstudd Do you have some free time to check this ?

[richardbizik]

PR: #1462 is trying to solve a different issue. There is problem with nullable values when you are joining with subquery see this test query:
https://github.com/kyleconroy/sqlc/pull/1462/files#diff-b05b5bbca9afffb93b923638a4b8c9aa5b5d82b7076656e98eab1038bc9ae96cR31

[sbres]

@richardbizik I think you might be right, it did look as the problem could've come from the same place.