BETWEEN keyword in CTE generates duplicate params

[penmanglewood]

Version

1.23.0

What happened?

I have a query using a CTE with a WHERE clause filtering a date range using BETWEEN, and sqlc generated the params struct with duplicate fields for the from and to dates.

const listAuthorsWithCTE = `-- name: ListAuthorsWithCTE :many
WITH page AS (SELECT id FROM authors WHERE authors.created_at BETWEEN ? AND ?)
SELECT authors.id, name, bio, created_at, page.id FROM authors
INNER JOIN page ON page.id = authors.id
ORDER BY name
`

type ListAuthorsWithCTEParams struct {
	FromCreatedAt   time.Time
	FromCreatedAt_2 time.Time
	ToCreatedAt     time.Time
	ToCreatedAt_2   time.Time
}

And the database is queried with all of them.

rows, err := q.db.QueryContext(ctx, listAuthorsWithCTE,
	arg.FromCreatedAt,
	arg.FromCreatedAt_2,
	arg.ToCreatedAt,
	arg.ToCreatedAt_2,
)

Note that changing the WHERE clause to use >= and < outputs the correct number of parameters.

Relevant log output

No response

Database schema

CREATE TABLE authors (
  id   INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255)      NOT NULL,
  bio  VARCHAR(255),
  created_at DATETIME NOT NULL
);

SQL queries

-- name: ListAuthorsWithCTE :many
WITH page AS (SELECT id FROM authors WHERE authors.created_at BETWEEN ? AND ?)
SELECT * FROM authors
INNER JOIN page ON page.id = authors.id
ORDER BY name;

Configuration

{
  "version": "2",
  "sql": [{
    "schema": "schema.sql",
    "queries": "query.sql",
    "engine": "mysql",
    "gen": {
      "go": {
        "out": "db"
      }
    }
  }]
}

Playground URL

https://play.sqlc.dev/p/f475a847db9fef33de461eadc8638ca289c85e2cd7ab40df344454ccb721c836

What operating system are you using?

macOS

What database engines are you using?

MySQL

What type of code are you generating?

Go

[AdrienHorgnies]

Hi, there's a similar, most likely related issue on sqlite. No golang parameters are generated for the related queries (but sql parameters are in the generated template SQL).

What happened?

Golang parameters are missing from the function signature when the SQL query uses the operator BETWEEN.

generated signature (notice the absence of parameters beside ctx):

func (q *Queries) SearchIntersectingWorklogs(ctx context.Context) ([]Worklog, error) {
    ...
}

By comparison, if instead I specify the following query:

-- name: SearchIntersectingWorklogs :many
SELECT *
FROM worklogs
WHERE started_at <= sqlc.arg(start) AND sqlc.arg(start) <= finished_at;

I get a signature with a param (notice the parameter start next to ctx):

func (q *Queries) SearchIntersectingWorklogs(ctx context.Context, start time.Time) ([]Worklog, error) {
    ...
}

Version

v1.26.0

Database Schema

CREATE TABLE worklogs (
    id INTEGER PRIMARY KEY,
    started_at TIMESTAMP NOT NULL,
    finished_at TIMESTAMP NOT NULL
);

SQL queries

- name: SearchIntersectingWorklogs :many
SELECT *
FROM worklogs
WHERE sqlc.arg(start) BETWEEN started_at AND finished_at;

Playground:

https://play.sqlc.dev/p/366240428f2d8ea7ff1d2dfd6d1fdb5499b3e539c0ef7138a273942c86e7a3f3

What operating system are you using?

Ubuntu

What database engines are you using?

sqlite

What type of code are you generating?

Go