Issue with functions in WHERE clause

[torrayne]

The code below generates without any parameters making it impossible to supply the WHERE clause with parameters.

/* name: GetConversions :many */
SELECT *
FROM `conversions`
WHERE isnull(`archived_at`) = ?
ORDER BY created_at DESC
LIMIT 25;

Generated Code

const listConversions = `-- name: ListConversions :many
select id, registration_id, code, value, notes, created_by, created_at, confirmed_by, confirmed_at, archived_by, archived_at from conversions where isnull(archived_at) = :v1 order by created_at desc limit 25
`

func (q *Queries) ListConversions(ctx context.Context) ([]Conversion, error) {
	rows, err := q.query(ctx, q.listConversionsStmt, listConversions)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	items := []Conversion{}
	for rows.Next() {
		var i Conversion
		if err := rows.Scan(
			&i.ID,
			&i.RegistrationID,
			&i.Code,
			&i.Value,
			&i.Notes,
			&i.CreatedBy,
			&i.CreatedAt,
			&i.ConfirmedBy,
			&i.ConfirmedAt,
			&i.ArchivedBy,
			&i.ArchivedAt,
		); err != nil {
			return nil, err
		}
		items = append(items, i)
	}
	if err := rows.Close(); err != nil {
		return nil, err
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}

[kyleconroy]

The mysql package is deprecated (#633)

However, the replacement package, engine/dolphin, fails to generate the correct code. Note that it does find the parameter in the WHERE clause, but assigns it the wrong type. I need to add support for function return types. Here's what it currently spits out:

CREATE TABLE conversions (
  body text,
  archived_at datetime NOT NULL DEFAULT NOW(),
  created_at  datetime NOT NULL DEFAULT NOW()
);

/* name: GetConversions :many */
SELECT *
FROM conversions
WHERE isnull(archived_at) = ?
ORDER BY created_at DESC
LIMIT 25;

{
  "version": "1",
  "packages": [
    {
      "engine": "_dolphin",
      "path": "go",
      "name": "querytest",
      "schema": "query.sql",
      "queries": "query.sql"
    }
  ]
}

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

package querytest

import (
	"context"
	"time"
)

const getConversions = `-- name: GetConversions :many
SELECT body, archived_at, created_at
FROM conversions
WHERE isnull(archived_at) = ?
ORDER BY created_at DESC
LIMIT 25
`

func (q *Queries) GetConversions(ctx context.Context, archivedAt time.Time) ([]Conversion, error) {
	rows, err := q.db.QueryContext(ctx, getConversions, archivedAt)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var items []Conversion
	for rows.Next() {
		var i Conversion
		if err := rows.Scan(&i.Body, &i.ArchivedAt, &i.CreatedAt); err != nil {
			return nil, err
		}
		items = append(items, i)
	}
	if err := rows.Close(); err != nil {
		return nil, err
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}

[kyleconroy]

Here's a reproduction of the issue on the playground.

[kyleconroy]

Closed in favor of #657