Title: LEFT JOIN (SELECT ...) can cause incorrect type being generated. · Issue #3667 · sqlc-dev/sqlc · GitHub
Open Graph Title: LEFT JOIN (SELECT ...) can cause incorrect type being generated. · Issue #3667 · sqlc-dev/sqlc
X Title: LEFT JOIN (SELECT ...) can cause incorrect type being generated. · Issue #3667 · sqlc-dev/sqlc
Description: Version 1.27.0 What happened? Related: #983 When doing a LEFT JOIN (SELECT ...) sqlc doesn't detect that the columns can be nullable. Example (I've made this PoC available in the playground): -- name: BadQueryRow :many SELECT ad.name AS ...
Open Graph Description: Version 1.27.0 What happened? Related: #983 When doing a LEFT JOIN (SELECT ...) sqlc doesn't detect that the columns can be nullable. Example (I've made this PoC available in the playground): -- na...
X Description: Version 1.27.0 What happened? Related: #983 When doing a LEFT JOIN (SELECT ...) sqlc doesn't detect that the columns can be nullable. Example (I've made this PoC available in the playground...
Opengraph URL: https://github.com/sqlc-dev/sqlc/issues/3667
X: @github
Domain: github.com
{"@context":"https://schema.org","@type":"DiscussionForumPosting","headline":"LEFT JOIN (SELECT ...) can cause incorrect type being generated.","articleBody":"### Version\r\n\r\n1.27.0\r\n\r\n### What happened?\r\n\r\nRelated: https://github.com/sqlc-dev/sqlc/pull/983\r\n\r\nWhen doing a LEFT JOIN (SELECT ...) sqlc doesn't detect that the columns can be nullable. \r\n\r\nExample (I've made this PoC available [in the playground](https://play.sqlc.dev/p/0f833be306744cb1854171be2660d45f96905012a1f0e3db421b56b4c761f333)):\r\n\r\n```sql\r\n-- name: BadQueryRow :many\r\nSELECT\r\n ad.name AS app_directory_name, -- ad.name is nullable\r\n ad.logo AS app_directory_logo,\r\n ad.uuid AS app_directory_uuid\r\nFROM applications AS a\r\nLEFT JOIN ( -- instead of a normal left join\r\n SELECT name, logo, uuid\r\n FROM application_directory.applications\r\n WHERE archived_at IS NULL\r\n) ad ON ad.uuid = a.app_directory_id\r\nGROUP BY\r\n ad.name,\r\n ad.logo,\r\n ad.uuid\r\n```\r\n\r\nThis will return a Row with strings:\r\n\r\n```go\r\ntype BadQueryRow struct {\r\n\tAppDirectoryName string -- incorrect\r\n\tAppDirectoryLogo string\r\n\tAppDirectoryUuid uuid.UUID\r\n}\r\n```\r\n\r\nIf we use a basic LEFT JOIN:\r\n\r\n```sql\r\n-- name: GoodQuery :many\r\nSELECT\r\n\tad.name AS app_directory_name,\r\n ad.logo AS app_directory_logo,\r\n ad.uuid AS app_directory_uuid\r\nFROM applications AS a\r\nLEFT JOIN application_directory.applications ad ON ad.uuid = a.app_directory_id\r\nGROUP BY\r\n ad.name,\r\n ad.logo,\r\n ad.uuid;\r\n```\r\n\r\nWe will get a correct struct:\r\n\r\n```go\r\ntype GoodQueryRow struct {\r\n\tAppDirectoryName sql.NullString\r\n\tAppDirectoryLogo sql.NullString\r\n\tAppDirectoryUuid uuid.NullUUID\r\n}\r\n```\r\n\r\nExpected behavior is for BadQueryRow to be identical to GoodQueryRow.\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_No response_\r\n\r\n### SQL queries\r\n\r\n_No response_\r\n\r\n### Configuration\r\n\r\n_No response_\r\n\r\n### Playground URL\r\n\r\nhttps://play.sqlc.dev/p/0f833be306744cb1854171be2660d45f96905012a1f0e3db421b56b4c761f333\r\n\r\n### What operating system are you using?\r\n\r\nLinux\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/ignassew","@type":"Person","name":"ignassew"},"datePublished":"2024-10-21T10:34:15Z","url":"https://github.com/sqlc-dev/sqlc/issues/3667"}
| react-profiling | 0 |
| react-import-map | react,react-dom,react-dom/client,react-dom/profiling,react-is,react/compiler-runtime,react/jsx-dev-runtime,react/jsx-runtime,scheduler@b8488ee2c322 |
| route-pattern | /_view_fragments/issues/show/:user_id/:repository/:id/issue_layout(.:format) |
| route-controller | voltron_issues_fragments |
| route-action | issue_layout |
| fetch-nonce | v2:8f657c84-15f5-a784-e7f9-d2153d455442 |
| current-catalog-service-hash | 81bb79d38c15960b92d99bca9288a9108c7a47b18f2423d0f6438c5b7bcd2114 |
| request-id | 8AF4:154D88:15F56C6:1C023A3:6B0174CE |
| html-safe-nonce | 489feadfa33c358cbfda48ad7351b93fde752fed5634c3de7163e50beef9840b |
| visitor-payload | eyJyZWZlcnJlciI6IiIsInJlcXVlc3RfaWQiOiI4QUY0OjE1NEQ4ODoxNUY1NkM2OjFDMDIzQTM6NkIwMTc0Q0UiLCJ2aXNpdG9yX2lkIjoiNjA3MTkzMTM2Mjc3OTE2NTkwMiIsInJlZ2lvbl9lZGdlIjoiaWFkIiwicmVnaW9uX3JlbmRlciI6ImlhZCJ9 |
| visitor-hmac | 9f961af9177864b40727e7129ca14e1dd1c650c9fca2928e3677c1f2e4defddb |
| hovercard-subject-tag | issue:2602100938 |
| github-keyboard-shortcuts | repository,issues,copilot |
| google-site-verification | Apib7-x98H0j5cPqHWwSMm6dNU4GmODRoqxLiDzdx9I |
| octolytics-url | https://collector.github.com/github/collect |
| analytics-location | / |
| fb:app_id | 1401488693436528 |
| apple-itunes-app | app-id=1477376905, app-argument=https://github.com/_view_fragments/issues/show/sqlc-dev/sqlc/3667/issue_layout |
| twitter:image | https://opengraph.githubassets.com/2f5f457f6fd73b751b4c146bb444c581786a209ce8554182dfb5e580076909b1/sqlc-dev/sqlc/issues/3667 |
| twitter:card | summary_large_image |
| og:image | https://opengraph.githubassets.com/2f5f457f6fd73b751b4c146bb444c581786a209ce8554182dfb5e580076909b1/sqlc-dev/sqlc/issues/3667 |
| og:image:alt | Version 1.27.0 What happened? Related: #983 When doing a LEFT JOIN (SELECT ...) sqlc doesn't detect that the columns can be nullable. Example (I've made this PoC available in the playground): -- na... |
| og:image:width | 1200 |
| og:image:height | 600 |
| og:site_name | GitHub |
| og:type | object |
| og:author:username | ignassew |
| hostname | github.com |
| expected-hostname | github.com |
| None | cc5dfdd32f62ebd3223cab3af3aa2b268d3605218237326a48a99d1efd89495c |
| turbo-cache-control | no-preview |
| go-import | github.com/sqlc-dev/sqlc git https://github.com/sqlc-dev/sqlc.git |
| octolytics-dimension-user_id | 136738596 |
| octolytics-dimension-user_login | sqlc-dev |
| octolytics-dimension-repository_id | 193160679 |
| octolytics-dimension-repository_nwo | sqlc-dev/sqlc |
| octolytics-dimension-repository_public | true |
| octolytics-dimension-repository_is_fork | false |
| octolytics-dimension-repository_network_root_id | 193160679 |
| octolytics-dimension-repository_network_root_nwo | sqlc-dev/sqlc |
| turbo-body-classes | logged-out env-production page-responsive |
| disable-turbo | false |
| browser-stats-url | https://api.github.com/_private/browser/stats |
| browser-errors-url | https://api.github.com/_private/browser/errors |
| release | 8c77c9e8439031ad54c75a250694842892f693e1 |
| ui-target | full |
| theme-color | #1e2327 |
| color-scheme | light dark |
| react-app-name | issues |
Links:
Viewport: width=device-width