Support for Arithmetic Operations in SQL SELECT Clause During SQL to DSL Conversion

[xinghui]

Issue Description:

I'm currently working on a feature that involves converting SQL to DSL using Cadence. However, I noticed that the following SQL query is not supported:

SELECT field2 + field3 FROM index234
When attempting to execute this query, I receive the following error:

esql: *sqlparser.AliasedExpr not supported in select body

It would be highly beneficial if Cadence could support arithmetic operations (such as addition, subtraction, multiplication, and division) within the SELECT clause of SQL queries. This enhancement would greatly expand the flexibility and functionality of SQL to DSL conversions.

Thank you for considering this feature request!

[demirkayaender]

Where do you use this query in Cadence?

[ibarrajo]

Hi @xinghui !

Cadence uses a database (usually Cassandra or MySQL) internally as the persistance layer for workflows. I'm not aware of a use case were Cadence ingests and processes SQL queries. We do surface Elastic Search syntax for use in advanced visibility queries. It uses https://github.com/xwb1989/sqlparser within the ESQL module:
https://github.com/uber/cadence/blob/811ffe70505f8410473471f0465cac1cf706d869/common/elasticsearch/esql/README.md

From the docs, it looks like the query should be supported, could you provide an example of your query that caused the error? Because from the error it sounds like you might be aliasing a field in the select statement