Type Inference Problem for Array Comparisons in SQL Queries

[cmkqwerty]

Version

1.24.0

What happened?

Bug Description In array comparison operations using ANY/SOME/ALL operators, type inference occurs incorrectly in the auto-generated Go code.

https://www.postgresql.org/docs/current/functions-comparisons.html#FUNCTIONS-COMPARISONS-ANY-SOME

As can be seen in the documentation above, the syntax format of these operators is <value> = ANY/SOME/ALL ( <array> ).

Expected Behaviour Let's consider the following example sql block:

-- name: ListUsersByDepartment :many
SELECT * FROM "Users"
WHERE $1 = ANY(department_id)
ORDER BY user_id
LIMIT $2
OFFSET $3;

The query here should retrieve all rows with the value given in the department_id array column. The auto-generated function should accept int64 type data for $1, but it accepts []int64. Since the SQL query expects a single value, the query returns error. Here is the auto-generated go code for this example:

type ListUsersByDepartmentParams struct {
DepartmentID []int64 `json:"department_id"` // should be type int64
Limit int32 `json:"limit"`
Offset int32 `json:"offset"`
}

func (q *Queries) ListUsersByDepartment(ctx context.Context, arg ListUsersByDepartmentParams) ([]LegalNotice, error) {
rows, err := q.db.QueryContext(ctx, listLegalNoticesByDepartment, pq.Array(arg.DepartmentID), arg.Limit, arg.Offset) // arg.DepartmentID shouldn't capsulated by pq.Array()
...

Relevant log output

No response

Database schema

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

SQL queries

-- name: GetBug :many
SELECT * FROM authors
WHERE $1 = ANY(alert) 
LIMIT 1;

Configuration

{
  "version": "2",
  "sql": [{
    "schema": "schema.sql",
    "queries": "query.sql",
    "engine": "postgresql",
    "gen": {
      "go": {
        "out": "db"
      }
    }
  }]
}

Playground URL

https://play.sqlc.dev/p/c24b11453e8a3ab2aae1cd2c97b184962c14d73eb688b35865f9f2cd447add0f

What operating system are you using?

macOS

What database engines are you using?

PostgreSQL

What type of code are you generating?

Go

[cmkqwerty]

#3084

[ulope]

I'm running into the same problem.

As a workaround until a fix is merged the following beautiful abomination can be used (using OP's schema as an example):

-- name: GetBug :many
SELECT * FROM authors
WHERE ($1::BIGINT[])[1] = ANY(alert) 
LIMIT 1;

You then need to pass the query parameter as a 1-element slice.