case statement is not working properly

[dheerajravindranath]

Elasticsearch Verison: 7.4

{
"query": "select distinct(case when event='test' then user_id end) as testing from events_testing where event='test' and team_id=xxxxx"
}

{
"error": {
"reason": "Invalid SQL query",
"details": "",
"type": "NullPointerException"
},
"status": 400
}

The same query is working fine in other DB. Could someone help me to resolve this?

[dai-chen]

@dheerajravindranath Thanks for reporting the issue! I think we only support simple DISTINCT(* ) and DISTINCT(field). Will let you know if we can fix this or workaround found for your case.

[dheerajravindranath]

Thanks, @dai-chen It would be great if we get this resolved asap!

[dai-chen]

This issue has been resolved in our new SQL engine. Please find more details here: https://github.com/opendistro-for-elasticsearch/sql#experimental.

Test queries verified in PR above: https://github.com/opendistro-for-elasticsearch/sql/blob/develop/integ-test/src/test/resources/correctness/bugfixes/582.txt

SELECT DISTINCT CASE WHEN Carrier = 'ES-Air' Then FlightDelayMin END AS delay FROM kibana_sample_data_flights
SELECT DISTINCT CASE WHEN FlightDelay = true Then Carrier END AS name FROM kibana_sample_data_flights
SELECT DISTINCT CASE WHEN FlightDelay = true Then Carrier WHEN FlightDelay = false THEN NULL END AS name FROM kibana_sample_data_flights