Select from views and `array_agg`

[mariusavos]

trafficstars

Version

1.24.0

What happened?

We have a number of views that are created by joining tables, grouping, and array_agging 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.

I.e. in the playground example below,

type BooksWithAuthor struct {
	Books []string
	ID    int64
	Name  string
	Bio   sql.NullString
}

is correct, but the select query instead returns

type GetAuthorBooksRow struct {
	Books string
	ID    int64
	Name  string
	Bio   sql.NullString
}

which is not only different, but also has an incorrect type for Books.

Relevant log output

No response

Database schema

CREATE TABLE authors (
  id   BIGSERIAL PRIMARY KEY,
  name text      NOT NULL,
  bio  text
);

create table books (
  id BIGSERIAL PRIMARY KEY,
  name text NOT NULL,
  author BIGSERIAL references authors(id)
 );
 
 create view books_with_author as 
 	select array_agg(books.name)::text[] as books, authors.* from books
    join authors on authors.id = books.author
    group by authors.id;

SQL queries

-- name: GetAuthorBooks :one
SELECT * FROM books_with_author;

Configuration

{
  "version": "2",
  "cloud": {
  	"project":"<PROJECT_ID>"
  },
  "sql": [{
    "schema": "schema.sql",
    "queries": "query.sql",
    "engine": "postgresql",
    "database" :{
    	"managed": true
    },
    "gen": {
      "go": {
        "out": "db"
      }
    }
  }]
}

Playground URL

https://play.sqlc.dev/p/9d855f57e257cbd5acd956f1bc9d5b1f55ada9604602b5acd1742dd49337e289

What operating system are you using?

Linux

What database engines are you using?

PostgreSQL

What type of code are you generating?

Go

[andrewmbenton]

When you look at the playground example, the correct struct for the view is in models.go but the query doesn't use it. So there's something going wrong with the type analysis for array_agg(books.name)::text[] where it's not figuring out the array part.

[mariusavos]

Having looked at it myself for a bit, it seems to me that this was fixed by https://github.com/sqlc-dev/sqlc/pull/3032 If someone else can confirm this then I think the issue can be closed.