_plugins/_sql/_explain is not working as described in documentation

[atompie]

Description

Endpoint _plugins/_sql/_explain is not working as described in documentation.

Documentation states:

Description

To translate your query, send it to explain endpoint. The explain output is OpenSearch domain specific language (DSL) in JSON format. You can just copy and paste it to your console to run it against OpenSearch directly.

Explain query:

>> curl -H 'Content-Type: application/json' -X POST localhost:9200/_plugins/_sql/_explain -d '{
  "query" : "SELECT firstname, lastname FROM accounts WHERE age > 20"
}'

Result:

{
  "from": 0,
  "size": 200,
  "query": {
    "bool": {
      "filter": [{
        "bool": {
          "must": [{
            "range": {
              "age": {
                "from": 20,
                "to": null,
                "include_lower": false,
                "include_upper": true,
                "boost": 1.0
              }
            }
          }],
          "adjust_pure_negative": true,
          "boost": 1.0
        }
      }],
      "adjust_pure_negative": true,
      "boost": 1.0
    }
  },
  "_source": {
    "includes": [
      "firstname",
      "lastname"
    ],
    "excludes": []
  }
}

But this is actually not true.

To Reproduce

RUN:

POST _plugins/_sql/_explain
{
 "query": "SELECT * FROM tracardi-rule"
}

And you get something like:

{
  "root": {
    "name": "ProjectOperator",
    "description": {
      "fields": "[metadata]"
    },
    "children": [
      {
        "name": "OpenSearchIndexScan",
        "description": {
          "request": """OpenSearchQueryRequest(indexName=tracardi-rule, sourceBuilder={"from":0,"size":200,"timeout":"1m","_source":{"includes":["metadata"],"excludes":[]}}, searchDone=false)"""
        },
        "children": []
      }
    ]
  }
}```

**Expected behavior**
I expect to find legit query that I could run against _search endpoint

[atompie]

Is there other way to translate SQL in opensearch? Thanks.

[chloe-zh]

Hi @atompie last year we built a new query engine that not only run queries in OpenSearch engine but also supports post processing for the complicated queries. Accordingly, the explain is no longer translated to OpenSearch DSL, but to the query planning within the new query engine instead.

For those queries that don't require post processing, you can see a complete DSL in the "OpenSearchIndexScan" or "OpenSearchAggIndexScan" something, for example sourceBuilder={"from":0,"size":200,"timeout":"1m","_source":{"includes":["metadata"],"excludes":[]}} in the example above.

[Yury-Fridlyand]

Please, see documentation update in opensearch-project/documentation-website#830.