[v3.0.0] Handle legacy test cases relying on OpenSearch DSL format

[seankao-az]

This task is to decide which of the legacy tests using OpenSearch DSL (json) format are worth keeping and what to do with them.

#3367 deprecates OpenSearch DSL format.
However, there are some issues with legacy IT test cases:

• Correctness check is written against the OpenSearch DSL response format. This is less of an issue and can be resolved by either
  • Modify all test cases to expect and check correctness with jdbc response
  • Add an adapting layer to map jdbc response to the json format, so that we don't need to modify multiple hundreds of test cases
• A more troublesome issue is that they rely on using the endpoint _plugins/_sql?format=json to ensure legacy engine is being used, instead of v2 engine. Since the PR deprecates "?format=json", most of those queries in legacy IT will now be run by the v2 engine. However,
  • Some of those queries are considered invalid query for the v2 engine.
  • And for those queries that have no problem running in v2 engine, it's questionable if we still want to test them in legacy engine.

We might want to move away from specifically testing the legacy engine in IT cases, and instead just test the desired queries, regardless of it's legacy or not.