Support `filter` query parameter in the new engine

[MaxKsyunz]

Support requests such as this:

>> curl -H 'Content-Type: application/json' -X POST localhost:9200/_plugins/_sql -d '{
  "query" : "SELECT firstname, lastname, balance FROM accounts",
  "filter" : {
    "range" : {
      "balance" : {
        "lt" : 10000
      }
    }
  }
}'

filter property is OpenSearch DSL that gets added to the query generated from the SQL expression.

[ctellechea2001]

@MaxKsyunz Do you have any update on that? We saw that the same issue still happening in the latest versions of opensearch.

The new information that we discovered is that the issue happened with the default output format(jdbc) POST _plugins/_sql/format=jdbc. With any other format it is working fine. (json,csv).

I discovered today that the issue related to non-zero fetch_size was resolved in the latest version of opensearch v2.11.1 (keep happened in 2.7.0)
But the issue related to filter keep happens in the latest version.

POST _plugins/_sql/
{
"query": "select * from transitions",
"fetch_size": 1,
"filter": {
"term": {
"reason": "bad ip"
}
}
}

{ "error": { "reason": "There was internal problem at backend", "details": "Index type [transitions] does not exist", "type": "IllegalArgumentException" }, "status": 500 }

always doing that queries to the index ALIAS. if you go directly everything is fine.

[kohlir2020]

Asking for an update on this bug. I hoped it was fixed in the above PR as mentioned, but I'm still running into the same issue with version 2.19.0 (DSL style top-level filters are not respected in JDBC format output - but somehow they work with json/csv format).

POST /_plugins/_sql?format=jdbc
{
"query": "SELECT field FROM index-name",
"filter": {
"bool": {
"must": [
{"query_string": {"query": "(metadata:(Value1))"}}
]
}
}
}

Above query gives different results for "format=json" vs "format=jdbc". Would very much appreciate the fix in JDBC format.