Issue with functions in WHERE clause

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
}

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
}

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

Closed in favor of #657

dolphin: Add ISNULL function signature

dolphin: Add function signatures (#796)

Navigation Menu

Repository navigation

Issue with functions in WHERE clause #630

Generated Code

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue with functions in WHERE clause #630

Description

Generated Code

Activity

kyleconroy commented on Jul 28, 2020

kyleconroy commented on Oct 26, 2020

kyleconroy commented on Oct 26, 2020

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions