Set operation improvement

[dai-chen]

Is your feature request related to a problem? Please describe.
Improvements on set operations such as union and intersection by migrating to the new SQL engine.

Here are the feature requests from community:

1. Multi-query doesn't work for default format opendistro-for-elasticsearch/sql#388: Old set operation only works with JSON format.
2. UNION query on more than 2 indexes opendistro-for-elasticsearch/sql#371: Union multi indices
3. "_explain" for SQL query that contains union is not valid json opendistro-for-elasticsearch/sql#114: Explain issue

Describe the solution you'd like
N/A

Describe alternatives you've considered
N/A

Additional context
Add any other context or screenshots about the feature request here.

[deepak-agendaboa]

Any update for issue opendistro-for-elasticsearch/sql#388
I have lots of data in indexes. JSON format is not able to return all the data. So I need to use JDBC format only. But it is throwing the NPE.

[acarbonetto]

I have lots of data in indexes. JSON format is not able to return all the data. So I need to use JDBC format only. But it is throwing the NPE.

@deepak-agendaboa
Is there a specific usage of UNION or MINUS that you need implemented? Multi-index queries are not supported in SQL with JDBC for multiple reasons.

[deepak-agendaboa]

@acarbonetto I have accounting data in open search. And In the front end, the requirement is to display the aggregated data. Such as accounting summary of particular period (last year, last month, etc).
The accounting data is in various indexes. As the data is large so JSON format is not able to return all the data. So I need to use JDBC format to get the aggregated data.
With union, I want to combine the data from multiple indexes.

[acarbonetto]

What's the reasoning behind putting the accounting data in separate indexes? You can consider loading the data into a single index. Or use the index patterns if your indexes have similar names: https://opensearch.org/docs/latest/search-plugins/sql/sql/basic/#from (note: the data should have the same mapping for this to work effectively).

This seems related to: #1441