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

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.

Start by reading #3367 and inventorying the legacy IT test cases that use the _plugins/_sql?format=json endpoint and OpenSearch DSL response checks. Compare the affected queries with v2 behavior and document which tests to retain, update, adapt, or remove; done means the legacy test coverage has an agreed direction and the affected cases reflect it.