Union query result should work in JDBC format

[LantaoJin]

Description

Union query only works with csv format, but without the '?format=csv' query parameter at the end of the POST call will throw NullPointerException. Reproduce:

PUT index001/_doc/1
{
  "query_id" : "1"
}

PUT index002/_doc/1
{
  "query_id" : "1"
}

POST /_plugins/_sql
{
  "query": "select query_id from index001 UNION select query_id from index002"
}

Currently, the UNION statement is implemented in legacy engine(v1). It's a bug in legacy engine(v1). This PR fixes this issue in legacy engine.

Issues Resolved

Resolve https://github.com/opensearch-project/sql/issues/2540

Check List

• [x] New functionality includes testing.
  • [x] All tests pass, including unit test, integration test and doctest
• ~~[ ] New functionality has been documented.~~
  • ~~[ ] New functionality has javadoc added~~
  • ~~[ ] New functionality has user manual doc added~~
• [x] Commits are signed per the DCO using --signoff

By submitting this pull request, I confirm that my contribution is made under the terms of the Apache 2.0 license. For more information on following Developer Certificate of Origin and signing off your commits, please check here.

[vamsimanohar]

Can you add little more details on where exactly is the bug?

[LantaoJin]

Can you add little more details on where exactly is the bug?

The NPE issue caused by calling loadFromEsState for both SELECT clauses of UNION children is missing. The fixing is adding https://github.com/opensearch-project/sql/pull/2757/files#diff-744a18b0fa02b144d9aaf91192a6594175eb0bafdca6e01adb841093c36854f5R105-R108 and https://github.com/opensearch-project/sql/pull/2757/files#diff-d81d74cf75806ee8450a8a706d7e71d21787d2cabfcb2c4f2c0e83176e711940R26

[opensearch-trigger-bot[bot]]

This PR is stalled because it has been open for 30 days with no activity.

[RyanL1997]

@LantaoJin , do we still need this change?

[LantaoJin]

the UNION statement is implemented in legacy engine(v1)

Since the UNION statement is implemented in legacy engine(v1), let me close this PR now. The issue should be addressed when we enable Calcite for SQL.