René's URL Explorer Experiment

{"@context":"https://schema.org","@type":"DiscussionForumPosting","headline":"Left Join not generating correct Null* Types","articleBody":"### Version\n\n1.11.0\n\n### What happened?\n\nAfter updating to 1.11.0, the code generated when using `LEFT JOIN` on tables seems to be incorrect.\r\nFor the queries and schema given below, I'd expect the following code to be generated:\r\n```go\r\n...\r\ntype GetSuggestedUsersByIDRow struct {\r\n\tUserID          uuid.UUID\r\n\tUserNickname    string\r\n\tUserEmail       string\r\n\tUserDisplayName string\r\n\tUserPassword    sql.NullString\r\n\tUserGoogleID    sql.NullString\r\n\tUserAppleID     sql.NullString\r\n\tUserBio         string\r\n\tUserCreatedAt   time.Time\r\n\tUserAvatarID    uuid.NullUUID\r\n\tMediaID         uuid.NullUUID\r\n\tMediaCreatedAt  sql.NullTime\r\n\tMediaHash       sql.NullString\r\n\tMediaDirectory  sql.NullString\r\n\tMediaAuthorID   uuid.NullUUID\r\n\tMediaWidth      sql.NullInt32\r\n\tMediaHeight     sql.NullInt32\r\n}\r\n...\r\n```\r\nThis is the output when generating queries using `1.10.0`.\r\nInstead, with `1.11.0`, I get:\r\n```go\r\n...\r\ntype GetSuggestedUsersByIDRow struct {\r\n\tUserID          uuid.UUID\r\n\tUserNickname    string\r\n\tUserEmail       string\r\n\tUserDisplayName string\r\n\tUserPassword    sql.NullString\r\n\tUserGoogleID    sql.NullString\r\n\tUserAppleID     sql.NullString\r\n\tUserBio         string\r\n\tUserCreatedAt   time.Time\r\n\tUserAvatarID    uuid.NullUUID\r\n\tMediaID         uuid.UUID\r\n\tMediaCreatedAt  time.Time\r\n\tMediaHash       string\r\n\tMediaDirectory  string\r\n\tMediaAuthorID   uuid.UUID\r\n\tMediaWidth      int32\r\n\tMediaHeight     int32\r\n}\r\n...\r\n```\r\nYou can see that the joined table (the `media`), should have Null* types, instead of the plain regular types.\n\n### Relevant log output\n\n_No response_\n\n### Database schema\n\n```sql\nCREATE TABLE \"users\"\r\n(\r\n    \"user_id\"           uuid PRIMARY KEY,\r\n    \"user_nickname\"     VARCHAR(30) UNIQUE NOT NULL,\r\n    \"user_email\"        TEXT UNIQUE        NOT NULL,\r\n    \"user_display_name\" TEXT               NOT NULL,\r\n    \"user_password\"     TEXT               NULL,\r\n    \"user_google_id\"    TEXT UNIQUE        NULL,\r\n    \"user_apple_id\"     TEXT UNIQUE        NULL,\r\n    \"user_bio\"          VARCHAR(160)       NOT NULL DEFAULT '',\r\n    \"user_created_at\"   TIMESTAMP          NOT NULL DEFAULT (NOW()),\r\n    \"user_avatar_id\"    uuid UNIQUE        NULL\r\n);\r\n\r\nCREATE TABLE \"media\"\r\n(\r\n    \"media_id\"         uuid PRIMARY KEY,\r\n    \"media_created_at\" TIMESTAMP NOT NULL DEFAULT (NOW()),\r\n    \"media_hash\"       TEXT      NOT NULL,\r\n    \"media_directory\"  TEXT      NOT NULL,\r\n    \"media_author_id\"  uuid      NOT NULL,\r\n    \"media_width\"      INT       NOT NULL,\r\n    \"media_height\"     INT       NOT NULL\r\n);\r\n\r\nALTER TABLE \"users\"\r\n    ADD FOREIGN KEY (\"user_avatar_id\") REFERENCES \"media\" (\"media_id\") ON DELETE SET DEFAULT ON UPDATE CASCADE;\n```\n\n\n### SQL queries\n\n```sql\nCREATE TABLE \"users\"\r\n(\r\n    \"user_id\"           uuid PRIMARY KEY,\r\n    \"user_nickname\"     VARCHAR(30) UNIQUE NOT NULL,\r\n    \"user_email\"        TEXT UNIQUE        NOT NULL,\r\n    \"user_display_name\" TEXT               NOT NULL,\r\n    \"user_password\"     TEXT               NULL,\r\n    \"user_google_id\"    TEXT UNIQUE        NULL,\r\n    \"user_apple_id\"     TEXT UNIQUE        NULL,\r\n    \"user_bio\"          VARCHAR(160)       NOT NULL DEFAULT '',\r\n    \"user_created_at\"   TIMESTAMP          NOT NULL DEFAULT (NOW()),\r\n    \"user_avatar_id\"    uuid UNIQUE        NULL\r\n);\r\n\r\nCREATE TABLE \"media\"\r\n(\r\n    \"media_id\"         uuid PRIMARY KEY,\r\n    \"media_created_at\" TIMESTAMP NOT NULL DEFAULT (NOW()),\r\n    \"media_hash\"       TEXT      NOT NULL,\r\n    \"media_directory\"  TEXT      NOT NULL,\r\n    \"media_author_id\"  uuid      NOT NULL,\r\n    \"media_width\"      INT       NOT NULL,\r\n    \"media_height\"     INT       NOT NULL\r\n);\r\n\r\nALTER TABLE \"users\"\r\n    ADD FOREIGN KEY (\"user_avatar_id\") REFERENCES \"media\" (\"media_id\") ON DELETE SET DEFAULT ON UPDATE CASCADE;\r\n\r\n-- name: GetSuggestedUsersByID :many\r\nSELECT DISTINCT u.*, m.*\r\nFROM users u\r\n         LEFT JOIN media m ON u.user_avatar_id = m.media_id\r\nWHERE u.user_id != @user_id\r\nLIMIT @user_imit;\n```\n\n\n### Configuration\n\n```yaml\n{\r\n  \"version\": \"1\",\r\n  \"packages\": [\r\n    {\r\n      \"path\": \"db\",\r\n      \"engine\": \"postgresql\",\r\n      \"schema\": \"query.sql\",\r\n      \"queries\": \"query.sql\"\r\n    }\r\n  ]\r\n}\n```\n\n\n### Playground URL\n\n_No response_\n\n### What operating system are you using?\n\nmacOS\n\n### What database engines are you using?\n\nPostgreSQL\n\n### What type of code are you generating?\n\nGo","author":{"url":"https://github.com/fr3fou","@type":"Person","name":"fr3fou"},"datePublished":"2021-12-08T17:34:29.000Z","interactionStatistic":{"@type":"InteractionCounter","interactionType":"https://schema.org/CommentAction","userInteractionCount":11},"url":"https://github.com/1334/sqlc/issues/1334"}