Aggregation subquery in FROM clause runs into wrong code path

[dai-chen]

Subquery with aggregate function or GROUP BY is being handled by recently added post processing logic unexpectedly. This only happens for default JDBC format:

Example copied from SubqueryIT:

POST _opendistro/_sql
{
  "query": """
    SELECT t.TEMP1 as count, t.TEMP2 as balance
    FROM (SELECT COUNT(*) as TEMP1, SUM(balance) as TEMP2
          FROM accounts) t
  """
}

{
  "error": {
    "reason": "There was internal problem at backend",
    "details": "unsupported expr: t.TEMP1",
    "type": "RuntimeException"
  },
  "status": 503
}

Exception in ES log:

[2020-03-05T13:31:36,187][ERROR][c.a.o.s.p.RestSqlAction  ] [186590df9563.ant.amazon.com] 4e34c9da-f63f-4de8-bcd7-ffc22641d630 Server side error during query execution
java.lang.RuntimeException: unsupported expr: t.TEMP1
	at com.amazon.opendistroforelasticsearch.sql.query.planner.converter.SQLExprToExpressionConverter.convert(SQLExprToExpressionConverter.java:96) ~[opendistro_sql-1.4.0.0.jar:1.4.0.0]
	at com.amazon.opendistroforelasticsearch.sql.query.planner.converter.SQLAggregationParser.parseExprInSelectList(SQLAggregationParser.java:155) ~[opendistro_sql-1.4.0.0.jar:1.4.0.0]
	at com.amazon.opendistroforelasticsearch.sql.query.planner.converter.SQLAggregationParser.parse(SQLAggregationParser.java:66) ~[opendistro_sql-1.4.0.0.jar:1.4.0.0]
	at com.amazon.opendistroforelasticsearch.sql.query.planner.converter.SQLToOperatorConverter.visit(SQLToOperatorConverter.java:61) ~[opendistro_sql-1.4.0.0.jar:1.4.0.0]
	at com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlSelectQueryBlock.accept0(MySqlSelectQueryBlock.java:255) ~[druid-1.0.15.jar:1.0.15]
	at com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlSelectQueryBlock.accept0(MySqlSelectQueryBlock.java:246) ~[druid-1.0.15.jar:1.0.15]
	at com.alibaba.druid.sql.ast.SQLObjectImpl.accept(SQLObjectImpl.java:40) ~[druid-1.0.15.jar:1.0.15]
	at com.alibaba.druid.sql.ast.SQLObjectImpl.acceptChild(SQLObjectImpl.java:62) ~[druid-1.0.15.jar:1.0.15]
	at com.alibaba.druid.sql.ast.statement.SQLSelect.accept0(SQLSelect.java:85) ~[druid-1.0.15.jar:1.0.15]
	at com.alibaba.druid.sql.ast.SQLObjectImpl.accept(SQLObjectImpl.java:40) ~[druid-1.0.15.jar:1.0.15]
	at com.alibaba.druid.sql.ast.SQLObjectImpl.acceptChild(SQLObjectImpl.java:62) ~[druid-1.0.15.jar:1.0.15]
	at com.alibaba.druid.sql.ast.expr.SQLQueryExpr.accept0(SQLQueryExpr.java:55) ~[druid-1.0.15.jar:1.0.15]
	at com.alibaba.druid.sql.ast.SQLObjectImpl.accept(SQLObjectImpl.java:40) ~[druid-1.0.15.jar:1.0.15]
	at com.amazon.opendistroforelasticsearch.sql.query.planner.core.BindingTupleQueryPlanner.<init>(BindingTupleQueryPlanner.java:40) ~[opendistro_sql-1.4.0.0.jar:1.4.0.0]
	at com.amazon.opendistroforelasticsearch.sql.query.ESActionFactory.create(ESActionFactory.java:126) ~[opendistro_sql-1.4.0.0.jar:1.4.0.0]
	at com.amazon.opendistroforelasticsearch.sql.plugin.SearchDao.explain(SearchDao.java:61) ~[opendistro_sql-1.4.0.0.jar:1.4.0.0]
	at com.amazon.opendistroforelasticsearch.sql.plugin.RestSqlAction.explainRequest(RestSqlAction.java:151) [opendistro_sql-1.4.0.0.jar:1.4.0.0]
	at com.amazon.opendistroforelasticsearch.sql.plugin.RestSqlAction.prepareRequest(RestSqlAction.java:120) [opendistro_sql-1.4.0.0.jar:1.4.0.0]
	at org.elasticsearch.rest.BaseRestHandler.handleRequest(BaseRestHandler.java:87) [elasticsearch-7.4.2.jar:7.4.2]

[dai-chen]

This is due to our QueryPlannerScopeDecider doesn't rule out this kind of subquery. And this case is not covered by current SubqueryIT which requests by JSON format rather than default JDBC.

    public static boolean shouldMigrateToQueryPlan(SQLQueryExpr expr, Format format) {
        // The JSON format will return the Elasticsearch aggregation result, which is not supported by the QueryPlanner.
        if (format == Format.JSON) {
            return false;
        }
        QueryPlannerScopeDecider decider = new QueryPlannerScopeDecider();
        return decider.isInScope(expr);
    }