sqlc does not correctly generate psql when `ANY`/`SOME` is used

[cosban]

Version

1.13.0 / 1.14.0

What happened?

See the postgresql docs for more information about ANY/SOME.

Current State

field_id	field_name	description
19ea00a0-85dc-434e-a273-74f61b65e4cb	FIELD_ONE	A field for testing
2fcc8dc2-0fb3-4754-b66c-0070139f70c5	FIELD_TWO	Another field for testing

when ListFilteredEntries is called, it produces the following error:

&pq.Error{Severity:"ERROR", Code:"22P02", Message:"malformed array literal: \"19ea00a0-85dc-434e-a273-74f61b65e4cb\"", Detail:"Array value must start with \"{\" or dimension information.", Hint:"", Position:"", InternalPosition:"", InternalQuery:"", Where:"unnamed portal parameter $1 = '...'", Schema:"", Table:"", Column:"", DataTypeName:"", Constraint:"", File:"arrayfuncs.c", Line:"316", Routine:"array_in"}

Expectation

I would expect something similar to the following to be generated (which works when manually written out)

// take note of slice of UUIDs
func (q *Store) ListFilteredEntries(ctx context.Context, fieldID []uuid.UUID) ([]MpField, error) {
	rows, err := q.db.QueryContext(ctx, listFilteredMPFields, pq.Array(fieldID)) // take note of pq.Array usage
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var items []MpField
	for rows.Next() {
		var i MpField
		if err := rows.Scan(&i.FieldID, &i.FieldName, &i.Description); err != nil {
			return nil, err
		}
		items = append(items, i)
	}
	if err := rows.Close(); err != nil {
		return nil, err
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}

Relevant log output

#16 5.674               Error:          Expected nil (error), but got: &pq.Error{Severity:"ERROR", Code:"22P02", Message:"malformed array literal: \"19ea00a0-85dc-434e-a273-74f61b65e4cb\"", Detail:"Array value must start with \"{\" or dimension information.", Hint:"", Position:"", InternalPosition:"", InternalQuery:"", Where:"unnamed portal parameter $1 = '...'", Schema:"", Table:"", Column:"", DataTypeName:"", Constraint:"", File:"arrayfuncs.c", Line:"316", Routine:"array_in"}
#16 5.674               Test:           TestCustomQuery/Create_MP_Fields
#16 5.674               Messages:       pq: malformed array literal: "19ea00a0-85dc-434e-a273-74f61b65e4cb"

Database schema

CREATE TABLE entries
(
    field_id    UUID          NOT NULL,
    field_name  VARCHAR(255)  NOT NULL UNIQUE,
    description VARCHAR(1025) NOT NULL,
    PRIMARY KEY (field_id)
);

SQL queries

-- name: ListFilteredEntries :many
SELECT field_id, field_name, description
FROM entries
WHERE field_id = ANY($1)

Configuration

version: "1"
packages:
  - name: "db"
    path: "./db/gen"
    queries: "./db/query/"
    schema: "./db/schema/"
    engine: "postgresql"
    emit_json_tags: true
    emit_prepared_queries: false
    emit_interface: false
    emit_exact_table_names: false
    emit_empty_slices: true
    emit_exported_queries: false
    emit_result_struct_pointers: false
    emit_params_struct_pointers: false
    emit_methods_with_db_argument: false
    json_tags_case_style: "camel"
    output_db_file_name: "db.go"
    output_models_file_name: "models.go"
    output_querier_file_name: "querier.go"

Playground URL

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

What operating system are you using?

Linux

What database engines are you using?

PostgreSQL

What type of code are you generating?

Go

[cosban]

The playground verifies that this occurs on 1.14.0 as well

[fr3fou]

A quick fix would be to use a named parameter + a type cast:

...
WHERE field_id = ANY(@ids::uuid[]);
...
-- $1::uuid[] also works, but results in code with a param named `dollar_1` which is ugly

[kyleconroy]

@cosban The expectation right now is that ANY requires the type cast. I know it's not ideal.