[
https://issues.apache.org/jira/browse/CALCITE-4069?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17138874#comment-17138874
]
Andrei Sereda commented on CALCITE-4069:
----------------------------------------
What would happen in the case below ?
{code:sql}
select count(*), sum(field1), min(field2), count(*) from foo
{code}
> Elasticsearch the result of count(*) will be added to the result list twice
> when use multiple aggregations without group by
> ---------------------------------------------------------------------------------------------------------------------------
>
> Key: CALCITE-4069
> URL: https://issues.apache.org/jira/browse/CALCITE-4069
> Project: Calcite
> Issue Type: Improvement
> Components: elasticsearch-adapter
> Reporter: yeyonghao
> Priority: Major
> Time Spent: 10m
> Remaining Estimate: 0h
>
> when you write a sql:
> {code:java}
> select count(*) from mytable
> {code}
> in:
> {code:java}
> org.apache.calcite.adapter.elasticsearch.ElasticsearchTable#aggregate
> {code}
> As an optimization, totalHit will be the result of the query for count *:
> {code:java}
> // elastic exposes total number of documents matching a query in
> "/hits/total" path
> // this can be used for simple "select count(*) from table"
> final long total = res.searchHits().total().value();
> {code}
> It is then added to the result set:
> {code:java}
> if (groupBy.isEmpty()) {
> // put totals automatically for count(*) expression(s), unless they contain
> group by
> for (String expr : countAll) {
> result.forEach(m -> m.put(expr, total));
> }
> }
> {code}
> This is fine if there is only one count * aggregate function in SQL.
> But when you write multiple aggregate functions in your SQL:
> {code:java}
> select count(*),sum(field1) from mytable{code}
> You'll notice that the result of count * is repeatedly added to the result
> set (it doesn't affect the final result, but I think it can be optimized).
> The result of count * is first added to the result set along with other
> aggregate function result values in the following code:
> {code:java}
> final List<Map<String, Object>> result = new ArrayList<>();
> if (res.aggregations() != null) {
> // collect values
> ElasticsearchJson.visitValueNodes(res.aggregations(), m -> {
> // using 'Collectors.toMap' will trigger Java 8 bug here
> Map<String, Object> newMap = new LinkedHashMap<>();
> for (String key: m.keySet()) {
> newMap.put(fieldMap.getOrDefault(key, key), m.get(key));
> }
> result.add(newMap);
> });
> } else {
> // probably no group by. add single result
> result.add(new LinkedHashMap<>());
> }
> {code}
> The second time is added again in the following code:
> {code:java}
> // elastic exposes total number of documents matching a query in
> "/hits/total" path
> // this can be used for simple "select count(*) from table"
> final long total = res.searchHits().total().value();
> if (groupBy.isEmpty()) {
> // put totals automatically for count(*) expression(s), unless they contain
> group by
> for (String expr : countAll) {
> result.forEach(m -> m.put(expr, total));
> }
> }
> {code}
> Although such operations have no effect on the final result when there are
> multiple aggregate functions, it is obvious that they can be
> optimized.TotalHit is added to the result set only when groupby. isEmpty()
> and aggregation. size() == 1 simultaneously satisfy:
> {code:java}
> if (groupBy.isEmpty() && aggregations.size() == 1) {
> ...
> }
> {code}
> This avoids repeatedly adding count * to the result set.
>
>
>
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
