SQL: Exception thrown for HAVING on aggregate with nested cast

[matriv]

SELECT gender
FROM test_emp
GROUP BY 1
HAVING (SUM({fn CONVERT(emp_no, SQL_BIGINT)}) > 1000);

throws:

java.sql.SQLException: Server encountered an error [Does not know how to convert argument Cast[LONG] for function Sum[]]. [SqlIllegalArgumentException[Does not know how to convert argument Cast[LONG] for function Sum[]]
	at org.elasticsearch.xpack.sql.planner.QueryTranslator.field(QueryTranslator.java:396)
	at org.elasticsearch.xpack.sql.planner.QueryTranslator$Sums.toAgg(QueryTranslator.java:702)
	at org.elasticsearch.xpack.sql.planner.QueryTranslator$Sums.toAgg(QueryTranslator.java:698)
	at org.elasticsearch.xpack.sql.planner.QueryTranslator$SingleValueAggTranslator.asAgg(QueryTranslator.java:794)
	at org.elasticsearch.xpack.sql.planner.QueryTranslator$AggTranslator.apply(QueryTranslator.java:784)
	at org.elasticsearch.xpack.sql.planner.QueryTranslator.toAgg(QueryTranslator.java:170)
	at org.elasticsearch.xpack.sql.planner.QueryFolder$FoldAggregate.addAggFunction(QueryFolder.java:410)
	at org.elasticsearch.xpack.sql.planner.QueryFolder$FoldAggregate.rule(QueryFolder.java:343)
	at org.elasticsearch.xpack.sql.planner.QueryFolder$FoldAggregate.rule(QueryFolder.java:196)
	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.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:554)
	at org.elasticsearch.xpack.sql.planner.QueryFolder$FoldingRule.apply(QueryFolder.java:550)
	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:82)
	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:153)
	at org.elasticsearch.action.ActionListener$1.onResponse(ActionListener.java:60)
	at org.elasticsearch.xpack.sql.session.SqlSession.lambda$optimizedPlan$3(SqlSession.java:149)
	at org.elasticsearch.action.ActionListener$1.onResponse(ActionListener.java:60)
	at org.elasticsearch.xpack.sql.session.SqlSession.lambda$preAnalyze$2(SqlSession.java:137)
	at org.elasticsearch.action.ActionListener$1.onResponse(ActionListener.java:60)
	at org.elasticsearch.xpack.sql.analysis.index.IndexResolver.lambda$resolveAsMergedMapping$3(IndexResolver.java:246)
	at org.elasticsearch.action.ActionListener$1.onResponse(ActionListener.java:60)
	at org.elasticsearch.action.support.ThreadedActionListener$1.doRun(ThreadedActionListener.java:97)
	at org.elasticsearch.common.util.concurrent.ThreadContext$ContextPreservingAbstractRunnable.doRun(ThreadContext.java:759)
	at org.elasticsearch.common.util.concurrent.AbstractRunnable.run(AbstractRunnable.java:37)
	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
	at java.base/java.lang.Thread.run(Thread.java:834)

[elasticmachine]

Pinging @elastic/es-search

[matriv]

We currently don't support aggregate functions on top of scalar functions

[matriv]

@elastic/es-ql

[matriv]

Closing as this has been solved over time.