Cannot sort by children sub-aggregation in terms aggregation

[gaobinlong]

Elasticsearch version: master

Plugins installed: []

JVM version (java -version): 13.0.2

OS version (uname -a if on a Unix-like system): Mac OS 10.13.6

Description of the problem including expected versus actual behavior:

Today we cannot sort by children sub-aggregation in a terms aggregation, it returns an error with message: "Buckets can only be sorted on a sub-aggregator path that is built out of zero or more single-bucket aggregations within the path and a final single-bucket or a metrics aggregation at the path end".

Steps to reproduce:

1. PUT my_index

{
  "mappings": {
    "properties": {
      "a": {
        "type": "keyword"
      },
      "b": {
        "type": "keyword"
      },
      "my_join_field": {
        "type": "join",
        "relations": {
          "question": "answer"
        }
      }
    }
  }
}

2. POST my_index/_doc/1?refresh

{
  "a": "1",
  "my_join_field": {
    "name": "question"
  }
}

3. POST my_index/_doc/2?routing=1&refresh

{
  "b": "2",
  "my_join_field": {
    "name": "answer",
    "parent": "1"
  }
}

4. GET my_index/_search

{
  "size": 0,
  "aggs": {
    "x": {
      "terms": {
        "field": "a",
        "order": {
          "y.doc_count": "desc"
        }
      },
      "aggs": {
        "y": {
          "children": {
            "type": "answer"
          }
        }
      }
    }
  }
}

This query works fine in 6.4.3, but cannot execute in 6.8 and later, it throws an error:

{
  "error": {
    "root_cause": [
      {
        "type": "aggregation_execution_exception",
        "reason": "Invalid aggregation order path [y.doc_count]. Buckets can only be sorted on a sub-aggregator path that is built out of zero or more single-bucket aggregations within the path and a final single-bucket or a metrics aggregation at the path end."
      }
    ],
    "type": "search_phase_execution_exception",
    "reason": "all shards failed",
    "phase": "query",
    "grouped": true,
    "failed_shards": [
      {
        "shard": 0,
        "index": "my_index",
        "node": "WwA-ZFZ2QF2gHs962ZMXLw",
        "reason": {
          "type": "aggregation_execution_exception",
          "reason": "Invalid aggregation order path [y.doc_count]. Buckets can only be sorted on a sub-aggregator path that is built out of zero or more single-bucket aggregations within the path and a final single-bucket or a metrics aggregation at the path end.",
          "caused_by": {
            "type": "illegal_argument_exception",
            "reason": "Buckets can only be sorted on a sub-aggregator path that is built out of zero or more single-bucket aggregations within the path and a final single-bucket or a metrics aggregation at the path end."
          }
        }
      }
    ]
  },
  "status": 500
}

Provide logs (if relevant):

[2020-03-14T15:28:56,308][WARN ][r.suppressed             ] [node1] path: /my_index/_search, params: {index=my_index}
org.elasticsearch.action.search.SearchPhaseExecutionException: all shards failed
	at org.elasticsearch.action.search.AbstractSearchAsyncAction.onPhaseFailure(AbstractSearchAsyncAction.java:549) [main/:?]
	at org.elasticsearch.action.search.AbstractSearchAsyncAction.executeNextPhase(AbstractSearchAsyncAction.java:309) [main/:?]
	at org.elasticsearch.action.search.AbstractSearchAsyncAction.onPhaseDone(AbstractSearchAsyncAction.java:578) [main/:?]
	at org.elasticsearch.action.search.AbstractSearchAsyncAction.onShardFailure(AbstractSearchAsyncAction.java:389) [main/:?]
	at org.elasticsearch.action.search.AbstractSearchAsyncAction$1.onFailure(AbstractSearchAsyncAction.java:245) [main/:?]
	at org.elasticsearch.action.search.SearchExecutionStatsCollector.onFailure(SearchExecutionStatsCollector.java:73) [main/:?]
	at org.elasticsearch.action.ActionListenerResponseHandler.handleException(ActionListenerResponseHandler.java:59) [main/:?]
	at org.elasticsearch.action.search.SearchTransportService$ConnectionCountingHandler.handleException(SearchTransportService.java:402) [main/:?]
	at org.elasticsearch.transport.TransportService$ContextRestoreResponseHandler.handleException(TransportService.java:1067) [main/:?]
	at org.elasticsearch.transport.TransportService$DirectResponseChannel.processException(TransportService.java:1176) [main/:?]
	at org.elasticsearch.transport.TransportService$DirectResponseChannel.sendResponse(TransportService.java:1150) [main/:?]
	at org.elasticsearch.transport.TaskTransportChannel.sendResponse(TaskTransportChannel.java:60) [main/:?]
	at org.elasticsearch.action.support.ChannelActionListener.onFailure(ChannelActionListener.java:56) [main/:?]
	at org.elasticsearch.search.SearchService.lambda$executeQueryPhase$2(SearchService.java:389) [main/:?]
	at org.elasticsearch.common.util.concurrent.TimedRunnable.doRun(TimedRunnable.java:44) [main/:?]
	at org.elasticsearch.common.util.concurrent.ThreadContext$ContextPreservingAbstractRunnable.doRun(ThreadContext.java:747) [main/:?]
	at org.elasticsearch.common.util.concurrent.AbstractRunnable.run(AbstractRunnable.java:37) [main/:?]
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) [?:?]
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) [?:?]
	at java.lang.Thread.run(Thread.java:830) [?:?]
Caused by: org.elasticsearch.search.aggregations.AggregationExecutionException: Invalid aggregation order path [y.doc_count]. Buckets can only be sorted on a sub-aggregator path that is built out of zero or more single-bucket aggregations within the path and a final single-bucket or a metrics aggregation at the path end.
	at org.elasticsearch.search.aggregations.InternalOrder$Aggregation.partiallyBuiltBucketComparator(InternalOrder.java:80) ~[main/:?]
	at org.elasticsearch.search.aggregations.InternalOrder$CompoundOrder.lambda$partiallyBuiltBucketComparator$0(InternalOrder.java:185) ~[main/:?]
	at java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:195) ~[?:?]
	at java.util.LinkedList$LLSpliterator.forEachRemaining(LinkedList.java:1239) ~[?:?]
	at java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:484) ~[?:?]
	at java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:474) ~[?:?]
	at java.util.stream.ReduceOps$ReduceOp.evaluateSequential(ReduceOps.java:913) ~[?:?]
	at java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234) ~[?:?]
	at java.util.stream.ReferencePipeline.collect(ReferencePipeline.java:578) ~[?:?]
	at org.elasticsearch.search.aggregations.InternalOrder$CompoundOrder.partiallyBuiltBucketComparator(InternalOrder.java:186) ~[main/:?]
	at org.elasticsearch.search.aggregations.bucket.terms.TermsAggregator.<init>(TermsAggregator.java:187) ~[main/:?]
	at org.elasticsearch.search.aggregations.bucket.terms.AbstractStringTermsAggregator.<init>(AbstractStringTermsAggregator.java:43) ~[main/:?]
	at org.elasticsearch.search.aggregations.bucket.terms.GlobalOrdinalsStringTermsAggregator.<init>(GlobalOrdinalsStringTermsAggregator.java:93) ~[main/:?]
	at org.elasticsearch.search.aggregations.bucket.terms.TermsAggregatorFactory$ExecutionMode$2.create(TermsAggregatorFactory.java:313) ~[main/:?]
	at org.elasticsearch.search.aggregations.bucket.terms.TermsAggregatorFactory.doCreateInternal(TermsAggregatorFactory.java:159) ~[main/:?]
	at org.elasticsearch.search.aggregations.support.ValuesSourceAggregatorFactory.createInternal(ValuesSourceAggregatorFactory.java:50) ~[main/:?]
	at org.elasticsearch.search.aggregations.AggregatorFactory.create(AggregatorFactory.java:225) ~[main/:?]
	at org.elasticsearch.search.aggregations.AggregatorFactories.createTopLevelAggregators(AggregatorFactories.java:203) ~[main/:?]
	at org.elasticsearch.search.aggregations.AggregationPhase.preProcess(AggregationPhase.java:55) ~[main/:?]
	at org.elasticsearch.search.query.QueryPhase.execute(QueryPhase.java:150) ~[main/:?]
	at org.elasticsearch.indices.IndicesService.lambda$loadIntoContext$21(IndicesService.java:1340) ~[main/:?]
	at org.elasticsearch.indices.IndicesService.lambda$cacheShardLevelResult$22(IndicesService.java:1397) ~[main/:?]
	at org.elasticsearch.indices.IndicesRequestCache$Loader.load(IndicesRequestCache.java:174) ~[main/:?]
	at org.elasticsearch.indices.IndicesRequestCache$Loader.load(IndicesRequestCache.java:157) ~[main/:?]
	at org.elasticsearch.common.cache.Cache.computeIfAbsent(Cache.java:434) ~[main/:?]
	at org.elasticsearch.indices.IndicesRequestCache.getOrCompute(IndicesRequestCache.java:123) ~[main/:?]
	at org.elasticsearch.indices.IndicesService.cacheShardLevelResult(IndicesService.java:1403) ~[main/:?]
	at org.elasticsearch.indices.IndicesService.loadIntoContext(IndicesService.java:1337) ~[main/:?]
	at org.elasticsearch.search.SearchService.loadOrExecuteQueryPhase(SearchService.java:363) ~[main/:?]
	at org.elasticsearch.search.SearchService.executeQueryPhase(SearchService.java:430) ~[main/:?]
	at org.elasticsearch.search.SearchService.lambda$executeQueryPhase$2(SearchService.java:387) ~[main/:?]
	... 6 more
Caused by: java.lang.IllegalArgumentException: Buckets can only be sorted on a sub-aggregator path that is built out of zero or more single-bucket aggregations within the path and a final single-bucket or a metrics aggregation at the path end.
	at org.elasticsearch.search.aggregations.Aggregator.bucketComparator(Aggregator.java:139) ~[main/:?]
	at org.elasticsearch.search.aggregations.support.AggregationPath.bucketComparator(AggregationPath.java:224) ~[main/:?]
	at org.elasticsearch.search.aggregations.InternalOrder$Aggregation.partiallyBuiltBucketComparator(InternalOrder.java:77) ~[main/:?]
	at org.elasticsearch.search.aggregations.InternalOrder$CompoundOrder.lambda$partiallyBuiltBucketComparator$0(InternalOrder.java:185) ~[main/:?]
	at java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:195) ~[?:?]
	at java.util.LinkedList$LLSpliterator.forEachRemaining(LinkedList.java:1239) ~[?:?]
	at java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:484) ~[?:?]
	at java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:474) ~[?:?]
	at java.util.stream.ReduceOps$ReduceOp.evaluateSequential(ReduceOps.java:913) ~[?:?]
	at java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234) ~[?:?]
	at java.util.stream.ReferencePipeline.collect(ReferencePipeline.java:578) ~[?:?]
	at org.elasticsearch.search.aggregations.InternalOrder$CompoundOrder.partiallyBuiltBucketComparator(InternalOrder.java:186) ~[main/:?]
	at org.elasticsearch.search.aggregations.bucket.terms.TermsAggregator.<init>(TermsAggregator.java:187) ~[main/:?]
	at org.elasticsearch.search.aggregations.bucket.terms.AbstractStringTermsAggregator.<init>(AbstractStringTermsAggregator.java:43) ~[main/:?]
	at org.elasticsearch.search.aggregations.bucket.terms.GlobalOrdinalsStringTermsAggregator.<init>(GlobalOrdinalsStringTermsAggregator.java:93) ~[main/:?]
	at org.elasticsearch.search.aggregations.bucket.terms.TermsAggregatorFactory$ExecutionMode$2.create(TermsAggregatorFactory.java:313) ~[main/:?]
	at org.elasticsearch.search.aggregations.bucket.terms.TermsAggregatorFactory.doCreateInternal(TermsAggregatorFactory.java:159) ~[main/:?]
	at org.elasticsearch.search.aggregations.support.ValuesSourceAggregatorFactory.createInternal(ValuesSourceAggregatorFactory.java:50) ~[main/:?]
	at org.elasticsearch.search.aggregations.AggregatorFactory.create(AggregatorFactory.java:225) ~[main/:?]
	at org.elasticsearch.search.aggregations.AggregatorFactories.createTopLevelAggregators(AggregatorFactories.java:203) ~[main/:?]
	at org.elasticsearch.search.aggregations.AggregationPhase.preProcess(AggregationPhase.java:55) ~[main/:?]
	at org.elasticsearch.search.query.QueryPhase.execute(QueryPhase.java:150) ~[main/:?]
	at org.elasticsearch.indices.IndicesService.lambda$loadIntoContext$21(IndicesService.java:1340) ~[main/:?]
	at org.elasticsearch.indices.IndicesService.lambda$cacheShardLevelResult$22(IndicesService.java:1397) ~[main/:?]
	at org.elasticsearch.indices.IndicesRequestCache$Loader.load(IndicesRequestCache.java:174) ~[main/:?]
	at org.elasticsearch.indices.IndicesRequestCache$Loader.load(IndicesRequestCache.java:157) ~[main/:?]
	at org.elasticsearch.common.cache.Cache.computeIfAbsent(Cache.java:434) ~[main/:?]
	at org.elasticsearch.indices.IndicesRequestCache.getOrCompute(IndicesRequestCache.java:123) ~[main/:?]
	at org.elasticsearch.indices.IndicesService.cacheShardLevelResult(IndicesService.java:1403) ~[main/:?]
	at org.elasticsearch.indices.IndicesService.loadIntoContext(IndicesService.java:1337) ~[main/:?]
	at org.elasticsearch.search.SearchService.loadOrExecuteQueryPhase(SearchService.java:363) ~[main/:?]
	at org.elasticsearch.search.SearchService.executeQueryPhase(SearchService.java:430) ~[main/:?]
	at org.elasticsearch.search.SearchService.lambda$executeQueryPhase$2(SearchService.java:387) ~[main/:?]

[gaobinlong]

I found a change in ChildrenAggregatorFactory, now in master it is:

elasticsearch/modules/parent-join/src/main/java/org/elasticsearch/join/aggregations/ChildrenAggregatorFactory.java

Line 77 in 89d6873

if (collectsFromSingleBucket) {

,but in 6.4.3 it is:

protected Aggregator doCreateInternal(WithOrdinals valuesSource, Aggregator parent,
            boolean collectsFromSingleBucket, List<PipelineAggregator> pipelineAggregators, Map<String, Object> metaData)
                    throws IOException {
        long maxOrd = valuesSource.globalMaxOrd(context.searcher());
        return new ParentToChildrenAggregator(name, factories, context, parent, childFilter,
            parentFilter, valuesSource, maxOrd, pipelineAggregators, metaData);
    }

[elasticmachine]

Pinging @elastic/es-analytics-geo (:Analytics/Aggregations)

[polyfractal]

Thanks for the report @gaobinlong. I believe you're correct, and that particular change came from #34845

It introduces an optimization for when the agg has parents, but that optimization also breaks how our sorting logic works because it no longer "looks" like a single-bucket agg due to being wrapped with the MultiBucketAggregatorWrapper. Not entirely clear how to solve this regression but I'm going to mark this as a bug since I believe it is one.

[vlastv]

Similar problem #53534

[nik9000]

See #66876.