Left Join not generating correct Null* Types

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

Any updates on this?

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

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

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

@timstudd

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 ?

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

@richardbizik

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

Add other test from sqlc-dev#1334

@eriklott

@impzero @eriklott @fr3fou would my proposal for giving the develop control of the generated types (and their nullability) solve this issue for you by chance?

#1525

@skabbes

@skabbes I can still reproduce the issue with v1.13.0

Yes, my proposal was not accepted completely. It only was accepted for inputs and only for inputs begin specified as null.

The part of my proposal that would apply here would be for outputs of the query, so I would not have expected it to have fixed this unfortunately.

I just built sqlc from source using make sqlc-dev and the problem still persists for me.
This problem reproduces not only for timestamp fields, but for int fields as well.

with t1 as (
	select
		id,
		min(col) as min_col
	from
		table1
	group by
		id
)
select
	t1.min_col
from
	t2
left outer join t1 on t2.id = t1.id

min_col ends up being interface{}. More so, when getting query results it's returned as int underneath, so any NULL values will cause a runtime error.

scripts: use sed to work around sqlc LEFT JOIN issue

Navigation Menu

Repository navigation

Left Join not generating correct Null* Types #1334

Version

What happened?

Relevant log output

Database schema

SQL queries

Configuration

Playground URL

What operating system are you using?

What database engines are you using?

What type of code are you generating?

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Left Join not generating correct Null* Types #1334

Description

Version

What happened?

Relevant log output

Database schema

SQL queries

Configuration

Playground URL

What operating system are you using?

What database engines are you using?

What type of code are you generating?

Activity

lyubchev commented on Dec 18, 2021

eriklott commented on Dec 28, 2021

lyubchev commented on Mar 6, 2022

sbres commented on Mar 14, 2022

sbres commented on Mar 14, 2022

richardbizik commented on Mar 16, 2022

sbres commented on Mar 16, 2022

skabbes commented on Apr 2, 2022

fr3fou commented on May 12, 2022

skabbes commented on May 12, 2022

kdubovikov commented on May 19, 2022

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions