Use of functions on group by [Couchbase 4.5]

SQL++
1

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

2

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.

3

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.

4

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)
;

5

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
6

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?

7

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.