Couchbase parameterized query with numeric values

Question

I'm trying to add numeric values to parameterized AnalyticsQuery but keep getting errors when the query runs. The java creating the query looks like this:

  private ParameterizedAnalyticsQuery aggregateQuery(String userId, Long from, Long to) {
    return AnalyticsQuery.parameterized(
        "select d.field1,"
            + " d.field2"
            + " from data d"
            + " where d.userId = $userId"
            + " and d.timestamp between $from and $to",
        JsonObject.create()
            .put("userId", userId)
            .put("from", from)
            .put("to", to)
    );
  }

When the query is run the following error is returned:

<< Encountered \"from\" at column 213. ","code":24000}]

If I change the query to the following then it works and returns rows:

    return AnalyticsQuery.parameterized(
        "select d.field1,"
            + " d.field2"
            + " from data d"
            + " where d.userId = $userId"
            + " and d.timestamp between " + from
            + " and " + to,
        JsonObject.create()
            .put("userId", userId)
    );

Why is there a problem when the parameters are not Strings? Is there a way to use parameterized queries with numeric values?

Answer 1

FROM and TO are reserved keywords in N1QL for Analytics and therefore must be put in backquotes when used as parameter names:

... and d.timestamp between $`from` and $`to`

For a list of all reserved keywords please see: https://docs.couchbase.com/server/current/analytics/appendix_1_keywords.html