Add support for 'COLLATE' for sqlite WHERE clause

[epes]

What do you want to change?

Add support for COLLATE in WHERE clause. Currently if I add COLLATE NOCASE after a WHERE expression, it omits the parameter from the generated query.

Use case

A table has a unique string constraint on a column with COLLATE NOCASE. I want to write a query to use COLLATE NOCASE to ignore case.

Table:

CREATE TABLE accounts (
    id TEXT NOT NULL PRIMARY KEY,
    name TEXT NOT NULL UNIQUE,

    UNIQUE (name COLLATE NOCASE)
);

.sql:

-- name: GetAccountByName :one
SELECT * FROM accounts
WHERE name = ? COLLATE NOCASE
LIMIT 1;

produces:

const getAccountByName = `-- name: GetAccountByName :one
SELECT id, name FROM accounts
WHERE name = ? COLLATE NOCASE
LIMIT 1
`

func (q *Queries) GetAccountByName(ctx context.Context) (Account, error) {
	row := q.db.QueryRowContext(ctx, getAccountByName)
	var i Account
	err := row.Scan(
		&i.ID,
		&i.Name,
	)
	return i, err
}

Which omits the name parameter in the query. When I don't include COLLATE NOCASE then the query works as expected and includes the name parameter.

Expected:

func (q *Queries) GetAccountByName(ctx context.Context, name string) (Account, error) {
	row := q.db.QueryRowContext(ctx, getAccountByName, name)
	var i Account
	err := row.Scan(
		&i.ID,
		&i.Name,
	)
	return i, err
}

What database engines need to be changed?

sqlite

What programming language backends need to be changed?

Go