Parameters with Array type have incorrect names

[ErrorBoi]

What happened?

Hello, I'm trying to generate Go Code from SQL Query using ANY method. When I run sqlc generate, variable with array method gets unexpected name.

Actual result: Variable with []int type gets dollar_1 name Expected result: Variable gets IDs name

Note: variable might get Column + Number name (e.g. Column4) when sql query contains several ANY methods in it.

Database schema

CREATE TABLE pilots (
  id BIGSERIAL PRIMARY KEY
);

SQL queries

-- name: ListPilotsByIDs :many
SELECT * FROM pilots
WHERE id = ANY($1::int[]);

Configuration

Playground URL

https://play.sqlc.dev/p/455e276514a8ecf33f4d86175da22a6965058d86d08a9e127c59e3a3a9acbad4

[kyleconroy]

Agreed that this is a bug. In the meantime, you can use named parameters to get the correct parameter name:

-- name: ListPilotsByIDs :many
SELECT * FROM pilots
WHERE id = ANY(sqlc.arg(ids)::int[]);

Or using the @ shortcut:

-- name: ListPilotsByIDs :many
SELECT * FROM pilots
WHERE id = ANY(@ids::int[]);

[MinSomai]

Below doesn't work. any solution to this? (ANSWERED)

-- name: ListTransfers :many
SELECT * FROM transfers
WHERE
    from_account_id = ANY(sqlc.arg(from_account_ids)::int[]) OR
    to_account_id = ANY(sqlc.arg(to_account_ids)::int[])
ORDER BY id
LIMIT $1
OFFSET $2;

error

query mixes positional parameters ($1) and named parameters (sqlc.arg or @arg)

Update: seems I can use sqlc.arg with limit but I cannot name it limit like this

LIMIT sqlc.arg(limit)

Update: this works.

LIMIT sqlc.arg('limit')

[theenoahmason]

@kyleconroy How can I accomplish the above example in MySQL?

-- name: ListPilotsByIDs :many
SELECT * FROM pilots
WHERE id = ANY(@ids::int[]);

sqlc.arg('ids')::int[] results in a syntax error.

[xeoncross]

Looks like sqlc needs to support passing slices to MySQL queries #695 first

[kyleconroy]

This can be accomplished in MySQL and SQLite using sqlc.slice.

As for the parameter name, sqlc.arg Anand named parameters solve this issue nicely.