Difficulty using sqlc.narg in a WHERE clause

[danielbprice]

Version

Other

What happened?

I am so excited for nullable params!!

I was trying to test out the new sqlc.narg() functionality on main, and I am having trouble with it. I was hoping to use this support more flexible WHERE clauses. What I am seeing is that the parameter that I am using is being dropped and the compiler is emitting an error, but, confusingly the whole thing runs to completion.

I am running sqlc compiled from edb9560

CC @skabbes

Relevant log output

I get, from the compiler:

$ sqlc generate
unsupported reference type: <nil>$ echo $?
0

Here is the generated code.  You can see that it ran to completion, but dropped the parameter, thereby creating nonsense code:

const listAuthors = `-- name: ListAuthors :many
SELECT id, name, bio FROM authors
WHERE $1 IS NULL OR $1 = name
ORDER BY name
`

func (q *Queries) ListAuthors(ctx context.Context) ([]Author, error) {
        rows, err := q.db.Query(ctx, listAuthors)
        if err != nil {
                return nil, err
        }
        defer rows.Close()
        var items []Author
        for rows.Next() {
                var i Author
                if err := rows.Scan(&i.ID, &i.Name, &i.Bio); err != nil {
                        return nil, err
                }
                items = append(items, i)
        }
        if err := rows.Err(); err != nil {
                return nil, err
        }
        return items, nil
}

Database schema

-- Example queries for sqlc
CREATE TABLE authors (
  id   BIGSERIAL PRIMARY KEY,
  name text      NOT NULL,
  bio  text
);

SQL queries

-- name: ListAuthors :many
SELECT * FROM authors
WHERE sqlc.narg('name') IS NULL OR sqlc.narg('name') = name
ORDER BY name;

Configuration

version: "1"
project:
    id: ""
packages:
  - name: "test"
    path: "test/"
    sql_package: "pgx/v4"
    engine: "postgresql"
    emit_interface: true
    # Filenames are organized to support make rule
    schema: "test/test.sql"
    queries: "test/test.sql"
    output_db_file_name: "test-db.go"
    output_models_file_name: "test-gen-models.go"
    output_querier_file_name: "test-gen-querier.go"

Playground URL

No response

What operating system are you using?

Linux

What database engines are you using?

PostgreSQL

What type of code are you generating?

Go

[skabbes]

Thanks for the great report, I'll take a look after my $DAYJOB is done.

[skabbes]

@danielbprice this was a bug that predated sqlc.narg, is has to do with sqlc's handling of IS NULL and IS NOT NULL expressions. I can't help fix the immediate bug, but I can offer a workaround. If you cast the sqlc.narg call to the same type as the column, the code generation will succeed. Hope that unblocks you from using sqlc.narg more broadly!

-- name: ListAuthors :many
SELECT * FROM authors
WHERE sqlc.narg('name')::text IS NULL OR sqlc.narg('name') = name
ORDER BY name;

@kyleconroy this bug is that *ast.NullTest is not handled in compiler/resolve. Though even if if were implemented, it looks like there needs to be a typechecking pass which can make sense of the all the uses of a named argument.

[danielbprice]

That's an awesome workaround, thanks!