René's URL Explorer Experiment

{"@context":"https://schema.org","@type":"DiscussionForumPosting","headline":"Select from views and `array_agg`","articleBody":"### Version\n\n1.24.0\n\n### What happened?\n\nWe have a number of views that are created by joining tables, grouping, and `array_agg`ing some columns. Sqlc generated the right code previously, but if you turn on database-backed query analysis then while the model for the view is still correct, select queries from the view don't return that model and instead return a different and also otherwise incorrect type.\r\n\r\nI.e. in the playground example below, \r\n```\r\ntype BooksWithAuthor struct {\r\n\tBooks []string\r\n\tID    int64\r\n\tName  string\r\n\tBio   sql.NullString\r\n}\r\n```\r\nis correct, but the select query instead returns\r\n```\r\ntype GetAuthorBooksRow struct {\r\n\tBooks string\r\n\tID    int64\r\n\tName  string\r\n\tBio   sql.NullString\r\n}\r\n```\r\nwhich is not only different, but also has an incorrect type for `Books`.\n\n### Relevant log output\n\n_No response_\n\n### Database schema\n\n```sql\nCREATE TABLE authors (\r\n  id   BIGSERIAL PRIMARY KEY,\r\n  name text      NOT NULL,\r\n  bio  text\r\n);\r\n\r\ncreate table books (\r\n  id BIGSERIAL PRIMARY KEY,\r\n  name text NOT NULL,\r\n  author BIGSERIAL references authors(id)\r\n );\r\n \r\n create view books_with_author as \r\n \tselect array_agg(books.name)::text[] as books, authors.* from books\r\n    join authors on authors.id = books.author\r\n    group by authors.id;\n```\n\n\n### SQL queries\n\n```sql\n-- name: GetAuthorBooks :one\r\nSELECT * FROM books_with_author;\n```\n\n\n### Configuration\n\n```yaml\n{\r\n  \"version\": \"2\",\r\n  \"cloud\": {\r\n  \t\"project\":\"\u003cPROJECT_ID\u003e\"\r\n  },\r\n  \"sql\": [{\r\n    \"schema\": \"schema.sql\",\r\n    \"queries\": \"query.sql\",\r\n    \"engine\": \"postgresql\",\r\n    \"database\" :{\r\n    \t\"managed\": true\r\n    },\r\n    \"gen\": {\r\n      \"go\": {\r\n        \"out\": \"db\"\r\n      }\r\n    }\r\n  }]\r\n}\n```\n\n\n### Playground URL\n\nhttps://play.sqlc.dev/p/9d855f57e257cbd5acd956f1bc9d5b1f55ada9604602b5acd1742dd49337e289\n\n### What operating system are you using?\n\nLinux\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/mariusavos","@type":"Person","name":"mariusavos"},"datePublished":"2023-12-19T15:18:24.000Z","interactionStatistic":{"@type":"InteractionCounter","interactionType":"https://schema.org/CommentAction","userInteractionCount":2},"url":"https://github.com/3071/sqlc/issues/3071"}