SQL: Max() function doesn't work with Interval subtraction and addition

[musteaf]

Elasticsearch version (bin/elasticsearch --version): 8.0.0

JVM version (java -version): 13.0.2

OS version (uname -a if on a Unix-like system): MAC 10.14.3

Description of the problem including expected versus actual behavior:

Expected max() function needs to work with "intervals" when using arithmetic operations such that "sub" and "add".

Steps to reproduce:

1. inserted docs like

{
    "birth_date":"1993-06-02T00:00:00Z",
    "emp_no":"10020",
    "first_name":"Zera",
    "gender":"F",
    "hire_date":"1999-07-26T00:00:00Z",
    "languages":7,
    "last_name":"LTP",
    "salary":"23523567"
}

2. executed

{
    "query": "SELECT gender, max(hire_date) + INTERVAL 1 YEARS AS dt FROM test_emp GROUP BY gender HAVING max(hire_date) + INTERVAL 1 YEARS >= '1997-01-01T00:00:00.000Z'::timestamp ORDER BY 1"
}

and also

{
    "query": "SELECT gender, max(hire_date) AS dt FROM test_emp GROUP BY gender HAVING max(hire_date) + INTERVAL 1 YEARS >= '1997-01-01T00:00:00.000Z'::timestamp ORDER BY 1"
}

neither of them is working

Provide logs (if relevant):
for sub I get

"org.elasticsearch.xpack.sql.expression.predicate.operator.arithmetic.SqlBinaryArithmeticOperation.lambda$static$1(SqlBinaryArithmeticOperation.java:60)",
                "org.elasticsearch.xpack.sql.expression.predicate.operator.arithmetic.SqlBinaryArithmeticOperation.doApply(SqlBinaryArithmeticOperation.java:129)",
                "org.elasticsearch.xpack.ql.expression.predicate.PredicateBiFunction.apply(PredicateBiFunction.java:24)",
                "org.elasticsearch.xpack.sql.expression.function.scalar.whitelist.InternalSqlScriptUtils.sub(InternalSqlScriptUtils.java:141)",
                "InternalSqlScriptUtils.nullSafeFilter(InternalSqlScriptUtils.gte(InternalSqlScriptUtils.sub(params.a0,InternalSqlScriptUtils.intervalYearMonth(params.v0,params.v1)),InternalSqlScriptUtils.asDateTime(params.v2)))"

for add I get

"org.elasticsearch.xpack.sql.expression.predicate.operator.arithmetic.SqlBinaryArithmeticOperation.lambda$static$0(SqlBinaryArithmeticOperation.java:32)",
                "org.elasticsearch.xpack.sql.expression.predicate.operator.arithmetic.SqlBinaryArithmeticOperation.doApply(SqlBinaryArithmeticOperation.java:129)",
                "org.elasticsearch.xpack.ql.expression.predicate.PredicateBiFunction.apply(PredicateBiFunction.java:24)",
                "org.elasticsearch.xpack.sql.expression.function.scalar.whitelist.InternalSqlScriptUtils.add(InternalSqlScriptUtils.java:121)",
                "InternalSqlScriptUtils.nullSafeFilter(InternalSqlScriptUtils.gte(InternalSqlScriptUtils.add(params.a0,InternalSqlScriptUtils.intervalYearMonth(params.v0,params.v1)),InternalSqlScriptUtils.asDateTime(params.v2)))"

Also, I want to work on this issue if it is possible.

[elasticmachine]

Pinging @elastic/es-search (:Search/SQL)

[matriv]

@elastic/es-ql

[bpintea]

This no longer reproduces in latest stable (7.12).