René's URL Explorer Experiment

{"@context":"https://schema.org","@type":"DiscussionForumPosting","headline":"When upgrade to 1.11.0, nullable fields not correct in left join","articleBody":"### Version\r\n\r\n1.11.0\r\n\r\n### What happened?\r\n\r\nWhen work with v1.10, left join is run correctly. \r\n\r\nWhen the code was generated with **v1.10,** the left join struct was as follows :\r\n\r\n```\r\ntype GetItemByUniqueIdentifierRow struct {\r\n\tID               int64\r\n\tUniqueIdentifier string\r\n\tTitle            string\r\n\tContent          string\r\n\tMetadataID       sql.NullInt64\r\n\tMetadataRate     sql.NullInt64\r\n\tID_2             sql.NullInt64\r\n\tRate             sql.NullInt64\r\n\tName             sql.NullString\r\n\tItemID           sql.NullInt64\r\n}\r\n```\r\n\r\nWhen the code was generated with **v1.11** \r\n\r\n```\r\ntype GetItemByUniqueIdentifierRow struct {\r\n\tID               int64\r\n\tUniqueIdentifier string\r\n\tTitle            string\r\n\tContent          string\r\n\tMetadataID       sql.NullInt64\r\n\tMetadataRate     sql.NullInt64\r\n\tID_2             int64\r\n\tRate             int64\r\n\tName             string\r\n\tItemID           int64\r\n}\r\n```\r\nYou can see that the joined table fields (Rate, Name, ItemID..), should have Null* types but when used with v1.11 this fields seem like non-nullable(int64 instead of  NullInt64).\r\n\r\nIn fact, when doing left join, if we use alias, it detects nullable fields correctly, if we do not use it and we say that it takes all columns like c.* without using alias, it shows the fields of the join table incorrectly.\r\n\r\nIf we look at the join query, the alias was used for the id and rate fields of the joined table, and these fields became nullable when the code was generated. It is true. \r\nWhen we use c.* to get all columns of the joined table, all fields are generated as non-nullable.  There seems to be a bug here about using c.*\r\n\r\n**SQL tables and go models are below.**\r\n\r\n\r\n### Relevant log output\r\n\r\n_No response_\r\n\r\n### Database schema\r\n\r\n```sql\r\nCREATE TABLE item_metadata  (\r\n          id bigserial PRIMARY KEY,\r\n          name TEXT NOT NULL DEFAULT '',\r\n          rate BIGINT NOT NULL,\r\n          item_id BIGINT REFERENCES items (id) ON DELETE CASCADE\r\n);\r\n\r\nCREATE TABLE item  (\r\n          id bigserial PRIMARY KEY,\r\n          title TEXT NOT NULL DEFAULT '',\r\n          content TEXT NOT NULL DEFAULT '',\r\n          unique_identifier TEXT NOT NULL,\r\n  UNIQUE(unique_identifier)\r\n);\r\n\r\n\r\n-- name: GetItemByUniqueIdentifier :one\r\nSELECT t.*,\r\n    c.id as metadata_id,\r\n    c.rate as metada_rate,\r\n    c.*\r\nFROM item t\r\n    LEFT JOIN item_metadata c ON c.item_id = t.id\r\nWHERE unique_identifier = $1;\r\n\r\n\r\nGo models : \r\n\r\ntype item struct {\r\n\tUniqueIdentifier string\r\n\tTitle         string\r\n\tContent       string\r\n\r\n\tItemMetadata    *metadata.ItemMetadata\r\n\r\n}\r\n\r\ntype ItemMetadata struct {\r\n\tName              string \r\n        Rate              int\r\n\r\n}\r\n```\r\n\r\n\r\n### SQL queries\r\n\r\n```sql\r\n-- name: GetItemByUniqueIdentifier :one\r\nSELECT t.*,\r\n    c.id as metadata_id,\r\n    c.rate as metadata_rate,\r\n    c.*\r\nFROM item t\r\n    LEFT JOIN item_metadata c ON c.item_id = t.id\r\nWHERE unique_identifier = $1;\r\n```\r\n\r\n\r\n### Configuration\r\n\r\n_No response_\r\n\r\n### Playground URL\r\n\r\n_No response_\r\n\r\n### What operating system are you using?\r\n\r\nmacOS\r\n\r\n### What database engines are you using?\r\n\r\nPostgreSQL\r\n\r\n### What type of code are you generating?\r\n\r\nGo","author":{"url":"https://github.com/damla-unal","@type":"Person","name":"damla-unal"},"datePublished":"2021-12-20T16:11:28.000Z","interactionStatistic":{"@type":"InteractionCounter","interactionType":"https://schema.org/CommentAction","userInteractionCount":2},"url":"https://github.com/1349/sqlc/issues/1349"}