opensearch-project
Cannot use the IN clause within the SUM method
Describe the bug Hello everyone, Is there anyone here familiar with using SQL on OpenSearch? We are encountering errors when running our queries and would greatly appreciate any assistance or insights you could provide.
To Reproduce Specifically, we are facing issues with queries of type :
this works:
SELECT count(*) AS fps_count
FROM `my-alias-db`
WHERE status IN ('AA','BB')
but this does not (needed for creating new metrics in superset)
SELECT SUM(CASE WHEN status IN ('AA', 'BB') THEN 1 ELSE 0 END) AS status_sum
FROM 'my-alias-db'
Expected behavior
I'm expecting to have same results but I'm getting this error:
odelasticsearch error: Error (NullPointerException): {'error': {'reason': 'Invalid SQL query', 'details': '', 'type': 'NullPointerException'}, 'status': 400}
Host/Environment (please complete the following information):
- OS: [ opensearch on aws]
Additional context need to use my query for creating new metrics on superset
@LantaoJin could you check whether this will be supported with the Calcite work
@LantaoJin could you check whether this will be supported with the Calcite work
@seankao-az it works w/wo Calcite
POST /_plugins/_ppl
{
"query" : """
source=state_country | stats sum(case(country in ('USA', 'England'), 1 else 0)) as status_sum
"""
}
returns
{
"schema": [
{
"name": "status_sum",
"type": "int"
}
],
"datarows": [
[
3
]
],
"total": 1,
"size": 1
}
Even I cannot reproduce in SQL with main branch.
POST /_plugins/_sql
{
"query" : """
SELECT SUM(CASE WHEN country IN ('USA', 'England') THEN 1 ELSE 0 END) AS status_sum
FROM state_country
"""
}
returns
{
"schema": [
{
"name": "SUM(CASE WHEN country IN ('USA', 'England') THEN 1 ELSE 0 END)",
"alias": "status_sum",
"type": "integer"
}
],
"datarows": [
[
3
]
],
"total": 1,
"size": 1,
"status": 200
}
