[BUG] SUM(const) fails with IllegalStateException

[Yury-Fridlyand]

User scenario:

1. Install Tableau
2. Install ODBC driver
3. Configure driver
4. Connect to it (without using OpenSearch connector)
5. Perform data refresh from a table

Under the hood, Tableau calls the following query:

SELECT SUM(number) AS alias FROM table  HAVING COUNT(number) > number

For example:

SELECT SUM(1) AS `cnt_` FROM logs  HAVING COUNT(2) > 1;

The query fails:

Server side error during query execution
java.lang.IllegalStateException: metric aggregation doesn't support expression 1
        at org.opensearch.sql.opensearch.storage.script.aggregation.dsl.AggregationBuilderHelper.build(AggregationBuilderHelper.java:46) ~[opensearch-2.2.0.0-SNAPSHOT.jar:?]
        at org.opensearch.sql.opensearch.storage.script.aggregation.dsl.MetricAggregationBuilder.make(MetricAggregationBuilder.java:170) ~[opensearch-2.2.0.0-SNAPSHOT.jar:?]
        at org.opensearch.sql.opensearch.storage.script.aggregation.dsl.MetricAggregationBuilder.visitNamedAggregator(MetricAggregationBuilder.java:102) ~[opensearch-2.2.0.0-SNAPSHOT.jar:?]
        at org.opensearch.sql.opensearch.storage.script.aggregation.dsl.MetricAggregationBuilder.visitNamedAggregator(MetricAggregationBuilder.java:37) ~[opensearch-2.2.0.0-SNAPSHOT.jar:?]
        at org.opensearch.sql.expression.aggregation.NamedAggregator.accept(NamedAggregator.java:71) ~[core-2.2.0.0-SNAPSHOT.jar:?]
        at org.opensearch.sql.opensearch.storage.script.aggregation.dsl.MetricAggregationBuilder.build(MetricAggregationBuilder.java:62) ~[opensearch-2.2.0.0-SNAPSHOT.jar:?]
        at org.opensearch.sql.opensearch.storage.script.aggregation.AggregationQueryBuilder.buildAggregationBuilder(AggregationQueryBuilder.java:80) ~[opensearch-2.2.0.0-SNAPSHOT.jar:?]
        at org.opensearch.sql.opensearch.storage.OpenSearchIndex$OpenSearchDefaultImplementor.visitIndexAggregation(OpenSearchIndex.java:183) ~[opensearch-2.2.0.0-SNAPSHOT.jar:?]
        at org.opensearch.sql.opensearch.storage.OpenSearchIndex$OpenSearchDefaultImplementor.visitNode(OpenSearchIndex.java:134) ~[opensearch-2.2.0.0-SNAPSHOT.jar:?]
        at org.opensearch.sql.opensearch.storage.OpenSearchIndex$OpenSearchDefaultImplementor.visitNode(OpenSearchIndex.java:121) ~[opensearch-2.2.0.0-SNAPSHOT.jar:?]
        at org.opensearch.sql.opensearch.planner.logical.OpenSearchLogicalIndexAgg.accept(OpenSearchLogicalIndexAgg.java:78) ~[opensearch-2.2.0.0-SNAPSHOT.jar:?]
        at org.opensearch.sql.planner.DefaultImplementor.visitChild(DefaultImplementor.java:134) ~[core-2.2.0.0-SNAPSHOT.jar:?]
        at org.opensearch.sql.planner.DefaultImplementor.visitProject(DefaultImplementor.java:73) ~[core-2.2.0.0-SNAPSHOT.jar:?]
        at org.opensearch.sql.planner.DefaultImplementor.visitProject(DefaultImplementor.java:48) ~[core-2.2.0.0-SNAPSHOT.jar:?]
        at org.opensearch.sql.planner.logical.LogicalProject.accept(LogicalProject.java:42) ~[core-2.2.0.0-SNAPSHOT.jar:?]
        at org.opensearch.sql.opensearch.storage.OpenSearchIndex.implement(OpenSearchIndex.java:113) ~[opensearch-2.2.0.0-SNAPSHOT.jar:?]
        at org.opensearch.sql.planner.Planner.plan(Planner.java:49) ~[core-2.2.0.0-SNAPSHOT.jar:?]
        at org.opensearch.sql.sql.SQLService.plan(SQLService.java:107) ~[sql-2.2.0.0-SNAPSHOT.jar:?]
        at org.opensearch.sql.legacy.plugin.RestSQLQueryAction.prepareRequest(RestSQLQueryAction.java:104) ~[legacy-2.2.0.0-SNAPSHOT.jar:?]
        at org.opensearch.sql.legacy.plugin.RestSqlAction.lambda$prepareRequest$1(RestSqlAction.java:156) [legacy-2.2.0.0-SNAPSHOT.jar:?]
        at org.opensearch.sql.opensearch.executor.Scheduler.lambda$withCurrentContext$0(Scheduler.java:30) [opensearch-2.2.0.0-SNAPSHOT.jar:?]
        at org.opensearch.common.util.concurrent.ThreadContext$ContextPreservingRunnable.run(ThreadContext.java:747) [opensearch-2.2.0.jar:2.2.0]
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) [?:?]
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) [?:?]
        at java.lang.Thread.run(Thread.java:829) [?:?]

Actually, it fails on SUM(1).
MySQL:

mysql> SELECT SUM(1) AS `cnt_` FROM user HAVING COUNT(2) > 1;
+------+
| cnt_ |
+------+
|    5 |
+------+
1 row in set (0.00 sec)

No idea why Tableau doesn't use SELECT COUNT(*)...

[Yury-Fridlyand]

Please, label as TDVT/Tableau.

[Yury-Fridlyand]

Fixed by #1288