Use of functions on group by [Couchbase 4.5]

why it has been dropped? I gave up the migration (4.1.1 -> 4.5) because i think that the use of functions can be very helpful, although they are not used in production

The new behavior is more precise. You can still use functions. Please post your query and we can take a look. The important thing is that the function should be in the GROUP BY as well.

Error message shown is: “Expression must be a group key or aggregate”

The query is:
SELECT timeIntervalStart, SUM(count) as value FROM metric
where type = “BGroup” and STR_TO_MILLIS(“2015-10-15”) <= STR_TO_MILLIS(timeIntervalStart) and STR_TO_MILLIS(timeIntervalStart) < STR_TO_MILLIS(“2015-11-01”)
group by SUBSTR(timeIntervalStart, 0, 10)

  • timeIntervalStart have the ISO8601 format

I’m filtering by data range and grouping by date.

The query below should work. You should also have an index on timeIntervalStart.

SELECT SUBSTR(timeIntervalStart, 0, 10) AS start, SUM(count) AS value
FROM metric
WHERE type = “BGroup” “2015-10-15” <= timeIntervalStart AND timeIntervalStart < "2015-11-01"
GROUP BY SUBSTR(timeIntervalStart, 0, 10)

Works !
Previous query did not conform to the SQL standard but it worked anyway on Couchbase 4.1!
Now it is correct and compliant !
Thank you

1 Like

i created 2 index:

  • CREATE INDEX type_idx ON metric(type) /* document type of metric bucket */
  • CREATE INDEX timeIntervalStart_bgroup_idx ON metric(timeIntervalStart) WHERE (type = BGroup)

but this query perform a bit slow: Execution: 3.25s with 25,253 fetched documents.
Probably my server has poor hardware features ? Or the document key is too long?

The second index should have WHERE type=“BGroup”, not type=BGroup.

You should remove the first index, type_idx, or else use an index hint with only the second index.