README.md

sqlc generator bug

There is a query generation bug in github.com/sqlc-dev/sqlc. It exists at least in v1.29.0 and v1.30.0 where I tested it.

Summary

Description

This code demonstrates a bug in the github.com/sqlc-dev/sqlc query generator. The bug happens when using slices and using sqlc.arg() or @ to name parameters, as in this example

-- name: BrokenQuery :many
SELECT sqlc.embed(mytable)
FROM mytable
WHERE
    typ IN (sqlc.slice(types))
    AND (sqlc.arg(allnames) OR (name IN (sqlc.slice(names))));

When running q.BrokenQuery(ctx, []int64{1, 2}, false, []string{"name1"}), sqlc generates:

-- name: BrokenQuery :many
SELECT mytable.id, mytable.typ, mytable.name, mytable.val
FROM mytable
WHERE
    typ IN (?,?)
    AND (?2 OR (name IN (?)))

-- ARGS: [1 2 false name1]

Because the sqlc.arg(allnames) was translated into the fixed string ?2, but the generator generates two variables for typ IN (?,?) (the number of types passed in), the positional value ?2 is incorrect and should be changed to ?3 here.

Workaround

This problem can be avoided by avoiding named parameters:

-- name: FixedQuery :many
SELECT sqlc.embed(mytable)
FROM mytable
WHERE
    typ IN (sqlc.slice(types))
    AND (? OR (name IN (sqlc.slice(names))));

When running q.FixedQuery(ctx, []int64{1, 2}, false, []string{"name1"}), sqlc generates:

-- name: FixedQuery :many
SELECT mytable.id, mytable.typ, mytable.name, mytable.val
FROM mytable
WHERE
    typ IN (?,?)
    AND (? OR (name IN (?)))

-- ARGS: [1 2 false name1]

and the parameters are placed where they belong.

Running

To run the example code:

   $ go generate ./...
   $ go run ./cmd