Same parameter with two different types should error

[kevinburke1]

If I do something like

CREATE TABLE authors (
  id   BIGSERIAL PRIMARY KEY,
  name text      NOT NULL
);

-- name: UpdateAuthor :exec
UPDATE authors
SET name = $1
WHERE id = $1;

In theory this should error because $1 must either be a number or a text. PostgreSQL returns the following error when executing this query:

ERROR:  invalid input syntax for type bigint: "foo"

In practice sqlc is happy to allow it, generating the following code:

// Code generated by sqlc. DO NOT EDIT.
// source: query.sql

package querytest

import (
	"context"
)

const updateAuthor = `-- name: UpdateAuthor :exec
UPDATE authors
SET name = $1
WHERE id = $1
`

func (q *Queries) UpdateAuthor(ctx context.Context, name string) error {
	_, err := q.db.ExecContext(ctx, updateAuthor, name)
	return err
}

[kevinburke1]

In Postgres, not sure whether MySQL has the same problem.

[mightyguava]

oh i think #306 is a step towards this, as it no longer drops the second $1 in the initial parsing step

[kyleconroy]

This is fixed in v1.23.0 by enabling the database-backed query analyzer. We added a test case for this issue so it won’t break in the future.

You can play around with the working example on the playground