Parsing error with named parameter shorthand

[tschaub]

I've come across a case where the @ parameter naming shorthand doesn't seem to work, while the sqlc.arg() syntax does work. I'm happy to stick with the sqlc.arg() syntax, but it tripped me up for a while before I discovered that the @ shorthand was causing trouble.

Using v1.7.0, the following works:

-- name: ListSubscriptionsForProcessing :many
SELECT * FROM subscriptions
WHERE
  subscriptions.enabled AND
  NOT subscriptions.processing AND
  subscriptions.id NOT IN (SELECT subscription_id from jobs WHERE jobs.status = 'failed') AND
  subscriptions.start_time <= sqlc.arg(day_start)::timestamptz - make_interval(days => subscriptions.latency) AND
  subscriptions.end_time >= sqlc.arg(day_start)::timestamptz - make_interval(days => subscriptions.latency) AND
  sqlc.arg(now)::timestamptz >= sqlc.arg(acquired)::timestamptz - ('1 hour'::INTERVAL * ST_XMax(subscriptions.geometry) / 15) AND
  (
    subscriptions.last_processed IS NULL OR
    subscriptions.last_processed + '1 day'::INTERVAL * subscriptions.interval <= sqlc.arg(day_start)::timestamptz - make_interval(days => subscriptions.latency)
  );

Here is a complete working example: https://play.sqlc.dev/p/a282065c4fa4a38e9c6b384b016052ac35eeb209ef677ed2d6a01ba0c87a2aef

If I instead use the @ shorthand syntax, the query fails to parse.

-- name: ListSubscriptionsForProcessing :many
SELECT * FROM subscriptions
WHERE
  subscriptions.enabled AND
  NOT subscriptions.processing AND
  subscriptions.id NOT IN (SELECT subscription_id from jobs WHERE jobs.status = 'failed') AND
  subscriptions.start_time <= @day_start::timestamptz - make_interval(days => subscriptions.latency) AND
  subscriptions.end_time >= @day_start::timestamptz - make_interval(days => subscriptions.latency) AND
  @now::timestamptz >= @acquired::timestamptz - ('1 hour'::INTERVAL * ST_XMax(subscriptions.geometry) / 15) AND
  (
    subscriptions.last_processed IS NULL OR
    subscriptions.last_processed + '1 day'::INTERVAL * subscriptions.interval <= @day_start::timestamptz - make_interval(days => subscriptions.latency)
  );

go run github.com/kyleconroy/sqlc/cmd/sqlc generate

Results in

# package queries
sql/queries/subscriptions.sql:29:1: edited query syntax is invalid: syntax error at or near "ptions"
exit status 1
main.go:3: running "go": exit status 1

Here is an example of the version that doesn't work: https://play.sqlc.dev/p/0ff9be7d534c0b5a6b7fbd4b3c3c36ce3e1c3b26c324fba5b300ea623295cbaa

This package has been a pleasure to use. Thank you for all the continued work on it!

[kyleconroy]

Thanks for the report. We've had a few of these issues pop up. I sadly think the only fix is removing support for the shorthand, since it's non-standard. I'd suggest using sqlc.arg going forward.

[kyleconroy]

Duplicate of #605