[BUG]Aggregate functions in PowerBI fail

[ghost]

Describe the bug Visualizations with Minimum, Maximum, Standard deviation, Variance, or Median options selected fail to load.

To Reproduce Steps to reproduce the behavior:

1. Start PowerBI
2. Connect to OpenSearch
3. Drag a numerical column into the workspace
4. Select one of the aggregate functions listed above
5. See visualization error in the workspace

Expected behavior The result should be have the expected value of the corresponding aggregate function.

Screenshots

Additional context Using OpenSearch version 1.2.0

[Yury-Fridlyand]

Min, max, count, avg work. Standard deviation and Median aggregation fails. PBI generates the same query for both of them:

select sum(`num3`) as `C1`,  count(`num3`) as `C2`,  sum(`C1`) as `C3` from  (  select `num3`,   { fn power(`num3`, 2) } as `C1`  from `calcs` ) as `ITBL`

SQL plugin falls to legacy engine (V2 doesn't support subquery), and it fails to parse block in curly brackets.

[2023-01-04T09:13:15,263][WARN ][stderr                   ] [dbg] line 22:8 extraneous input '{' expecting {'CASE', 'CAST', 'DATETIME', 'FALSE', 'FIRST', 'LAST', 'LEFT', 'MATCH', 'NOT', 'NULL', 'RIGHT', 'TRUE', 'AVG', 'COUNT', 'MAX', 'MIN', 'SUM', 'VAR_POP', 'VAR_SAMP', 'VARIANCE', 'STD', 'STDDEV', 'STDDEV_POP', 'STDDEV_SAMP', 'SUBSTRING', 'TRIM', 'FULL', 'INTERVAL', 'MICROSECOND', 'SECOND', 'MINUTE', 'HOUR', 'DAY', 'WEEK', 'MONTH', 'QUARTER', 'YEAR', 'ABS', 'ACOS', 'ASCII', 'ASIN', 'ATAN', 'ATAN2', 'CEIL', 'CEILING', 'CONCAT', 'CONCAT_WS', 'CONV', 'CONVERT_TZ', 'COS', 'COT', 'CRC32', 'CURDATE', 'CURTIME', 'CURRENT_DATE', 'CURRENT_TIME', 'CURRENT_TIMESTAMP', 'DATE', 'DATE_ADD', 'DATE_FORMAT', 'DATE_SUB', 'DAYNAME', 'DAYOFMONTH', 'DAYOFWEEK', 'DAYOFYEAR', 'DEGREES', 'E', 'EXP', 'FLOOR', 'FROM_DAYS', 'FROM_UNIXTIME', 'IF', 'IFNULL', 'ISNULL', 'LENGTH', 'LN', 'LOCALTIME', 'LOCALTIMESTAMP', 'LOCATE', 'LOG', 'LOG10', 'LOG2', 'LOWER', 'LTRIM', 'MAKEDATE', 'MAKETIME', 'MONTHNAME', 'NOW', 'NULLIF', 'PERIOD_ADD', 'PERIOD_DIFF', 'PI', 'POW', 'POWER', 'RADIANS', 'RAND', 'REPLACE', 'ROUND', 'RTRIM', 'SIGN', 'SIN', 'SQRT', 'SUBDATE', 'SYSDATE', 'TAN', 'TIME', 'TIME_TO_SEC', 'TIMESTAMP', 'TRUNCATE', 'TO_DAYS', 'UTC_DATE', 'UNIX_TIMESTAMP', 'UPPER', 'UTC_TIME', 'UTC_TIMESTAMP', 'D', 'T', 'TS', 'DENSE_RANK', 'RANK', 'ROW_NUMBER', 'FIELD', 'MATCHPHRASE', 'MATCH_PHRASE', 'SIMPLE_QUERY_STRING', 'QUERY_STRING', 'MATCH_PHRASE_PREFIX', 'MULTI_MATCH', 'QUERY', 'TYPEOF', 'SUBSTR', 'STRCMP', 'ADDDATE', 'HIGHLIGHT', 'MATCH_BOOL_PREFIX', '+', '-', 'MOD', '.', '(', '0', '1', '2', STRING_LITERAL, DECIMAL_LITERAL, REAL_LITERAL, ID, DOUBLE_QUOTE_ID, BACKTICK_QUOTE_ID}
[2023-01-04T09:13:15,265][ERROR][o.o.s.l.p.RestSqlAction  ] [dbg] 2848a904-ef0f-4666-926c-7405dfaf3e1b Client side error during query execution com.alibaba.druid.sql.parser.ParserException: Error. Unable to parse ODBC Literal Timestamp
        at org.opensearch.sql.legacy.parser.ElasticSqlExprParser.primary(ElasticSqlExprParser.java:142)
        at com.alibaba.druid.sql.parser.SQLExprParser.expr(SQLExprParser.java:121)
        at com.alibaba.druid.sql.parser.SQLExprParser.parseSelectItem(SQLExprParser.java:1787)
        at com.alibaba.druid.sql.parser.SQLSelectParser.parseSelectList(SQLSelectParser.java:275)
        at org.opensearch.sql.legacy.parser.ElasticSqlSelectParser.query(ElasticSqlSelectParser.java:115)
        at com.alibaba.druid.sql.parser.SQLSelectParser.select(SQLSelectParser.java:60)
        at com.alibaba.druid.sql.parser.SQLSelectParser.parseTableSource(SQLSelectParser.java:302)
...

[Yury-Fridlyand]

@dai-chen, I think it should be moved back to SQL repo. I guess a fix should be done in PBI Connector too - in SQL capabilities list. Docs.