[BUG] [SQL] Group by on a subquery returns an error

[joshuali925]

Describe the bug
from ODFE repo opendistro-for-elasticsearch/sql#662:

While applying a basic filter in PBID with direct query mode, the following query is generated

select `OriginCountry`
from 
(
    select `OriginCountry`
    from `kibana_sample_data_flights`
    where `OriginCountry` in ('AE', 'CA')
) as `ITBL`
group by `OriginCountry`
order by `OriginCountry`
limit 501

which returns an error as

{
  "error": {
    "reason": "There was internal problem at backend",
    "details": "class com.alibaba.druid.sql.ast.statement.SQLSubqueryTableSource cannot be cast to class com.alibaba.druid.sql.ast.statement.SQLJoinTableSource (com.alibaba.druid.sql.ast.statement.SQLSubqueryTableSource and com.alibaba.druid.sql.ast.statement.SQLJoinTableSource are in unnamed module of loader java.net.FactoryURLClassLoader @d5556bf)",
    "type": "ClassCastException"
  },
  "status": 503
}

[penghuo]

Currently, OpenSearch SQL doesn't support IN operator. https://github.com/opensearch-project/sql/blob/main/docs/user/dql/expressions.rst#comparison-operators

One workaround should be.

POST /_plugins/_sql
{
  "query": """select `OriginCountry` from (
    select `OriginCountry`
    from `opensearch_dashboards_sample_data_flights`
    where `OriginCountry` = 'AE' or  `OriginCountry` = 'CA') as `ITBL` group by `OriginCountry` order by `OriginCountry` limit 501"""
}