[BUG] SUM(const) fails with IllegalStateException #799
Description
User scenario:
- Install Tableau
- Install ODBC driver
- Configure driver
- Connect to it (without using OpenSearch connector)
- Perform data refresh from a table
Under the hood, Tableau calls the following query:
SELECT SUM(number) AS alias FROM table HAVING COUNT(number) > numberFor 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(*)...