SQLite: parameter in LIKE ... ESCAPE ? is dropped from generated code #4668

Version

1.31.1

What happened?

When the ESCAPE clause of a LIKE expression is a parameter, sqlc leaves that parameter out of the generated Go method. The SQL constant still includes all placeholders, but the method passes one fewer argument, so the query either fails at runtime or quietly returns incorrect results.

For example:

-- name: SearchName :many
SELECT id FROM t WHERE name LIKE ? ESCAPE ?;

generates:

func (q *Queries) SearchName(ctx context.Context, name string) ([]int64, error) {
rows, err := q.db.QueryContext(ctx, searchName, name)

This happens with every parameter style:

• ? placeholders: the call fails with sql: expected 2 arguments, got 1.
• sqlc.arg(esc) / @esc: the generated SQL uses ?1/?2. The driver doesn't check the argument count, so ?2 is bound as NULL. LIKE … ESCAPE NULL evaluates to NULL, and the query returns no rows and no error.
• LIKE ? ESCAPE ? AND score > ?: the generated method takes Name and Score and passes arg.Score in the ESCAPE position, with nothing for score > ?. With a driver that doesn't count arguments, the query would run with the wrong values.
• LIKE 'a%' ESCAPE ?: the method has no parameters at all.

A literal escape (ESCAPE '') works correctly.

I ran the generated code against github.com/ncruces/go-sqlite3 v0.35.6. The table held the rows '100%' and '100 apples', and each call searched for the prefix 100% using ! as the escape character:

SearchName: ids=[] err=sql: expected 2 arguments, got 1
SearchNameNamed: ids=[] err=
SearchThenWhere: ids=[] err=sql: expected 3 arguments, got 2
hand-written: ids=[1] err= -- db.Query("... LIKE ? ESCAPE ?", "100!%%", "!")

Expected: each method takes and passes the escape character as a string parameter. For example, SearchName(ctx, arg SearchNameParams) should pass both the pattern and the escape value.

Relevant log output

sql: expected 2 arguments, got 1

Database schema

CREATE TABLE t (id INTEGER PRIMARY KEY, name TEXT NOT NULL, score INTEGER NOT NULL, created TEXT);

SQL queries

-- name: SearchName :many
SELECT id FROM t WHERE name LIKE ? ESCAPE ?;

-- name: SearchNameNamed :many
SELECT id FROM t WHERE name LIKE sqlc.arg(pattern) ESCAPE sqlc.arg(esc);

-- name: SearchNameAt :many
SELECT id FROM t WHERE name LIKE @pattern ESCAPE @esc;

-- name: SearchEscapeOnly :many
SELECT id FROM t WHERE name LIKE 'a%' ESCAPE ?;

-- name: SearchLiteralEscape :many
SELECT id FROM t WHERE name LIKE ? ESCAPE '\';

-- name: SearchThenWhere :many
SELECT id FROM t WHERE name LIKE ? ESCAPE ? AND score > ?;

Configuration

version: "2"

sql:
  - engine: "sqlite"
    schema: "schema.sql"
    queries: "query.sql"
    gen:
      go:
        package: "db"
        out: "db"

Playground URL

No response

What operating system are you using?

Linux

What database engines are you using?

SQLite

What type of code are you generating?

Go