Affects Version/s: None
Fix Version/s: Search Services 2.0
Sprint:Team Ninja-King - S&I 36, Team Ninja-King - S&I 37, Team Ninja-King - S&I 38, Team Ninja-King - S&I 39
Avg function doesn't work if you are grouping by multiple group by.
The issue is reproducible through an ad hoc integration test in:
The reason is that Calcite parses the avg function as avg when there's just one group by.
When there are multiple group by it is parsed as SUM(field)/COUNT(field).
This is noticeable here: org.alfresco.solr.sql.SolrTable.SolrQueryable#query
Taking a look to List<Pair<String, String>> metricPairs input for both the scenarios, the difference is clear (with the multiple group by you see the DIVISION in the String operations input parameter).
org.alfresco.solr.sql.SolrTable.SolrQueryable#query is kinda the Alfresco custom code entry point, so I suspect the bug may be Calcite side.
I was not able to find an elegant solution with a quick fix, addressing it may require some work (unless we opt for a brute workaround).
Acceptance criteria :
- avg function works when you group by multiple fields
- few new integration tests are added for that specific use case in org.alfresco.solr.query.stream.DistributedGroupBySqlIT
- avg(CAST(cm_sizeCurrent AS DOUBLE)) as AvgSize returns the double average even if cm_sizeCurrent was originally BIGINT