SQL: CAST and CONVERT sporadically generate errors when grouped by

[astefan]

sql> SELECT CONVERT(ABS(EXTRACT(YEAR FROM "birth_date")), SQL_BIGINT) FROM test_emp GROUP BY CONVERT(ABS(EXTRACT(YEAR FROM "birth_date")), SQL_BIGINT);
Server error [Server sent bad type [folding_exception]. Original type was [line 1:8: Cannot find grouping for 'CONVERT(ABS(EXTRACT(YEAR FROM "birth_date")), SQL_BIGINT)']. [FoldingException[line 1:8: Cannot find grouping for 'CONVERT(ABS(EXTRACT(YEAR FROM "birth_date")), SQL_BIGINT)']
        at org.elasticsearch.xpack.sql.planner.QueryFolder$FoldAggregate.rule(QueryFolder.java:321)
        at org.elasticsearch.xpack.sql.planner.QueryFolder$FoldAggregate.rule(QueryFolder.java:206)
        at org.elasticsearch.xpack.sql.tree.Node.lambda$transformUp$11(Node.java:196)
        at org.elasticsearch.xpack.sql.tree.Node.transformUp(Node.java:190)
        at org.elasticsearch.xpack.sql.tree.Node.transformUp(Node.java:196)

And same goes for CAST to the same data type:

sql> SELECT CAST(ABS(EXTRACT(YEAR FROM "birth_date")) AS BIGINT) FROM test_emp GROUP BY CAST(ABS(EXTRACT(YEAR FROM "birth_date")) AS BIGINT);
Server error [Server sent bad type [folding_exception]. Original type was [line 1:8: Cannot find grouping for 'CAST(ABS(EXTRACT(YEAR FROM "birth_date")) AS BIGINT)']. [FoldingException[line 1:8: Cannot find grouping for 'CAST(ABS(EXTRACT(YEAR FROM "birth_date")) AS BIGINT)']
        at org.elasticsearch.xpack.sql.planner.QueryFolder$FoldAggregate.rule(QueryFolder.java:321)
        at org.elasticsearch.xpack.sql.planner.QueryFolder$FoldAggregate.rule(QueryFolder.java:206)
        at org.elasticsearch.xpack.sql.tree.Node.lambda$transformUp$11(Node.java:196)
        at org.elasticsearch.xpack.sql.tree.Node.transformUp(Node.java:190)

BUT, same query with a different type to be casted to works:

sql> SELECT CAST(ABS(EXTRACT(YEAR FROM "birth_date")) AS INTEGER) FROM test_emp GROUP BY CAST(ABS(EXTRACT(YEAR FROM "birth_date")) AS INTEGER);
ABS(EXTRACT(YEAR FROM "birth_date"))                                                                                                           
------------------------------------                                                                                                           
null                                                                                                                                           
1952                                                                                                                                           
1953                                                                                                                                           
1954                                                                                                                                           
1955                                                                                                                                           
1956                                                                                                                                           

[elasticmachine]

Pinging @elastic/es-search

[costin]

The issue here is related to function identity and the reuse of ExpressionId:

1. since CAST is a keyword, the parser creates the function for it directly without having to resolve it. Since there are two declarations, there are two different functions ids which differ causing the folder to not find the match.
   A potential solution here is to find the duplicate functions and replace it however this needs to take into account any possible reference to either id.

2. the second query works since the cast is removed/pruned since it has no effect (it's an int being casted to another int).