SQL: grouping by a CASTed value doesn't work

[astefan]

The following query (where hire_date is of type DATE) will throw an exception - SELECT CAST(YEAR(hire_date) AS VARCHAR) FROM test_emp GROUP BY CAST(YEAR(hire_date) AS VARCHAR) LIMIT 1:

Server error [Server sent bad type [folding_exception]. Original type was [line 1:8: Cannot find grouping for 'CAST(YEAR(hire_date [UTC]))']. [FoldingException[line 1:8: Cannot find grouping for 'CAST(YEAR(hire_date [UTC]))']
        at org.elasticsearch.xpack.sql.planner.QueryFolder$FoldAggregate.rule(QueryFolder.java:310)
        at org.elasticsearch.xpack.sql.planner.QueryFolder$FoldAggregate.rule(QueryFolder.java:195)
        at org.elasticsearch.xpack.sql.tree.Node.lambda$transformUp$11(Node.java:188)
        at org.elasticsearch.xpack.sql.tree.Node.transformUp(Node.java:182)
        at org.elasticsearch.xpack.sql.tree.Node.lambda$transformUp$10(Node.java:180)
        at org.elasticsearch.xpack.sql.tree.Node.transformChildren(Node.java:201)
        at org.elasticsearch.xpack.sql.tree.Node.transformUp(Node.java:180)
        at org.elasticsearch.xpack.sql.tree.Node.transformUp(Node.java:188)
        at org.elasticsearch.xpack.sql.planner.QueryFolder$FoldingRule.apply(QueryFolder.java:548)
        at org.elasticsearch.xpack.sql.planner.QueryFolder$FoldingRule.apply(QueryFolder.java:544)
        at org.elasticsearch.xpack.sql.rule.RuleExecutor$Transformation.<init>(RuleExecutor.java:82)
        at org.elasticsearch.xpack.sql.rule.RuleExecutor.executeWithInfo(RuleExecutor.java:155)
        at org.elasticsearch.xpack.sql.rule.RuleExecutor.execute(RuleExecutor.java:130)
        at org.elasticsearch.xpack.sql.planner.QueryFolder.fold(QueryFolder.java:81)
        at org.elasticsearch.xpack.sql.planner.Planner.foldPlan(Planner.java:38)
        at org.elasticsearch.xpack.sql.planner.Planner.plan(Planner.java:28)
        at org.elasticsearch.xpack.sql.session.SqlSession.lambda$physicalPlan$4(SqlSession.java:147)
        at org.elasticsearch.action.ActionListener$1.onResponse(ActionListener.java:60)
        at org.elasticsearch.xpack.sql.session.SqlSession.lambda$optimizedPlan$3(SqlSession.java:143)
        at org.elasticsearch.action.ActionListener$1.onResponse(ActionListener.java:60)
        at org.elasticsearch.xpack.sql.session.SqlSession.lambda$preAnalyze$2(SqlSession.java:131)
        at org.elasticsearch.action.ActionListener$1.onResponse(ActionListener.java:60)
        at org.elasticsearch.xpack.sql.analysis.index.IndexResolver.lambda$resolveWithSameMapping$3(IndexResolver.java:239)
        at org.elasticsearch.action.ActionListener$1.onResponse(ActionListener.java:60)
        at org.elasticsearch.action.support.TransportAction$1.onResponse(TransportAction.java:66)
        at org.elasticsearch.action.support.TransportAction$1.onResponse(TransportAction.java:62)
        at org.elasticsearch.action.support.master.TransportMasterNodeAction$AsyncSingleAction$1.onResponse(TransportMasterNodeAction.java:172)
        at org.elasticsearch.action.support.master.TransportMasterNodeAction$AsyncSingleAction$1.onResponse(TransportMasterNodeAction.java:169)
        at org.elasticsearch.action.admin.indices.get.TransportGetIndexAction.doMasterOperation(TransportGetIndexAction.java:142)
        at org.elasticsearch.action.admin.indices.get.TransportGetIndexAction.doMasterOperation(TransportGetIndexAction.java:50)
        at org.elasticsearch.action.support.master.info.TransportClusterInfoAction.masterOperation(TransportClusterInfoAction.java:52)
        at org.elasticsearch.action.support.master.info.TransportClusterInfoAction.masterOperation(TransportClusterInfoAction.java:34)
        at org.elasticsearch.action.support.master.TransportMasterNodeAction.masterOperation(TransportMasterNodeAction.java:109)
        at org.elasticsearch.action.support.master.TransportMasterNodeAction$AsyncSingleAction$2.doRun(TransportMasterNodeAction.java:189)
        at org.elasticsearch.common.util.concurrent.AbstractRunnable.run(AbstractRunnable.java:37)
        at org.elasticsearch.common.util.concurrent.EsExecutors$1.execute(EsExecutors.java:135)

whereas aliasing the CAST and using the alias inside the GROUP by works - SELECT CAST(YEAR(hire_date) AS VARCHAR) c FROM test_emp GROUP BY c LIMIT 1;

Trying the failing query from above with the translate API gives the following:

{
    "error": {
        "root_cause": [
            {
                "type": "folding_exception",
                "reason": "line 1:8: Cannot find grouping for 'CAST(YEAR(hire_date [UTC]))'"
            }
        ],
        "type": "folding_exception",
        "reason": "line 1:8: Cannot find grouping for 'CAST(YEAR(hire_date [UTC]))'"
    },
    "status": 400
}

[elasticmachine]

Pinging @elastic/es-search-aggs

[astefan]

This one does work, though: SELECT CAST(YEAR(hire_date) AS VARCHAR) AS year FROM test_emp GROUP BY year LIMIT 10;

[matriv]

Issue comes from the fact that functions are rewritten in the Optimizer and they end up with different ExpressionId. We try to find the fields in the GROUP BY inside a Map that's created from the fields in the SELECT clause. This Map though has as keys the ExpressionId and therefore the attempt to match the fields is failing.