Skip to content

Parameters with Array type have incorrect names #243

Closed
@ErrorBoi

Description

@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

Activity

kyleconroy

kyleconroy commented on Feb 11, 2020

@kyleconroy
Collaborator

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

MinSomai commented on Apr 7, 2021

@MinSomai
Contributor

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

theenoahmason commented on Dec 24, 2021

@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

xeoncross commented on Feb 12, 2022

@xeoncross
Contributor

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

kyleconroy

kyleconroy commented on Sep 22, 2023

@kyleconroy
Collaborator

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

      Development

      No branches or pull requests

        Participants

        @kyleconroy@xeoncross@theenoahmason@ErrorBoi@MinSomai

        Issue actions

          Parameters with Array type have incorrect names · Issue #243 · sqlc-dev/sqlc