Aggregation support for JOIN on multiple indices

[pradeepthota07]

I have tried joining two indices similar to sql. Here are the observations below.

1. I see it returns only max of 200 results and not all the results.
2. Joins does not support the aggregate functions like count, max, min etc

PS: I am using opendistro 0.9.0

Any help is appreciated. Thanks

[dai-chen]

I have tried joining two indices similar to sql. Here are the observations below.

1. I see it returns only max of 200 results and not all the results.
2. Joins does not support the aggregate functions like count, max, min etc

PS: I am using opendistro 0.9.0

Any help is appreciated. Thanks

Thanks for reporting the issue. Please find the answer below.

1. Yes, we use 200 by default on the safe side because we don't support cursor for now. We may remove it in future. The workaround is to use LIMIT in your query with a large number.
2. Yes, we generate query plan for JOIN like other SQL query engine. For now we only support limited post-processing operator in the plan. (if I recall right probably only Sort operator for ORDER BY and Project for column projection: https://github.com/opendistro-for-elasticsearch/sql/tree/master/src/main/java/com/amazon/opendistroforelasticsearch/sql/query/planner/physical/node)