Feature request: support SELECT DISTINCT

[seraphjiang]

Use case

We want to be able to use SELECT DISTINCT to find out list of unique item of specific field without duplicate data

Example SQL

SELECT DISTINCT(DestAirportID) FROM kibana_sample_data_flights order by DestAirportID

OD-SQL

GET _opendistro/_sql?format=csv
{
  "query": "select distinct(DestAirportID) from  kibana_sample_data_flights order by DestAirportID"
}

actual result(Partial)
you could see, there is duplicate data in result

DestAirportID
ABQ
ABQ
ADL
AICM
AICM
AICM
AICM
AICM
AICM
AICM
...

Expected result
no duplicate data

DestAirportID
ABQ
ADL
AICM
AMS
ARN

[chloe-zh]

@seraphjiang Thanks for reporting. We've reproduced this issue:

POST _opendistro/_sql?format=csv
{
  "query": "select distinct(DestAirportID) from  kibana_sample_data_flights order by DestAirportID"
}

Output:

DestAirportID
ABQ
ABQ
ADL
AICM
AICM
AICM
AICM
...

We will investigate this problem and explore the solution to support the DISTINCT feature soon. Thanks!

[dai-chen]

For now we only support COUNT(DISTINCT) because we rely on Elasticsearch aggregation to de-duplicate. Basically SELECT DISTINCT is equivalent to GROUP BY without aggregate function, so one option is to translate distinct to aggregation. In other database, DISTINCT and GROUP BY generates different physical plan, so it makes more sense to do this translation when generating DSL rather than by rewriter.