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
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
WHERE type = “BGroup” “2015-10-15” <= timeIntervalStart AND timeIntervalStart < "2015-11-01"
GROUP BY SUBSTR(timeIntervalStart, 0, 10)
Previous query did not conform to the SQL standard but it worked anyway on Couchbase 4.1!
Now it is correct and compliant !
i created 2 index:
- CREATE INDEX
type) /* document type of metric bucket */
- CREATE INDEX
timeIntervalStart) WHERE (
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.