Error in Translate API when connecting to Elasticsearch v8.x

[sarjak]

NEST/Elasticsearch.Net version: 7.17.5

Elasticsearch version: 8.6.0

.NET runtime version: .Net 6

Operating system version: Windows 10

Description of the problem including expected versus actual behavior:
Throws error when executing SQL Translate API against ElasticSearch v8.x. Also as per documentation have enabled: EnableApiVersioningHeader when creating connection.
It works fine with ElasticSearch v7.x.

Error Stacktrace:

# FailureReason: Unrecoverable/Unexpected BadResponse while attempting POST on https://XXX.es.centralus.azure.elastic-cloud.com/_sql/translate [1] BadResponse: Node: https://XXX.es.centralus.azure.elastic-cloud.com/ Exception: JsonParsingException Took: 00:00:00.3597194 Audit exception in step 1 BadResponse: Elasticsearch.Net.Utf8Json.JsonParsingException: expected:'true | false', actual:'-1', at offset:1547 at Elasticsearch.Net.Utf8Json.JsonReader.ReadBoolean() at Elasticsearch.Net.Utf8Json.Formatters.NullableBooleanFormatter.Deserialize(JsonReader& reader, IJsonFormatterResolver formatterResolver) at Deserialize(Object[] , JsonReader& , IJsonFormatterResolver ) at Nest.ReadAsFormatter2.Deserialize(JsonReader& reader, IJsonFormatterResolver formatterResolver)
at Elasticsearch.Net.Utf8Json.JsonSerializer.Deserialize[T](Byte[] bytes, Int32 offset, IJsonFormatterResolver resolver)
at Elasticsearch.Net.Utf8Json.JsonSerializer.DeserializeAsync[T](Stream stream, IJsonFormatterResolver resolver)
at Nest.TranslateSqlResponseBuilder.DeserializeResponseAsync(IElasticsearchSerializer builtInSerializer, IApiCallDetails response, Stream stream, CancellationToken ctx)
at Elasticsearch.Net.ResponseBuilder.SetBodyAsync[TResponse](ApiCallDetails details, RequestData requestData, Stream responseStream, String mimeType, CancellationToken cancellationToken)
at Elasticsearch.Net.ResponseBuilder.ToResponseAsync[TResponse](RequestData requestData, Exception ex, Nullable1 statusCode, IEnumerable1 warnings, Stream responseStream, String productName, String mimeType, CancellationToken cancellationToken)
at Elasticsearch.Net.HttpConnection.RequestAsync[TResponse](RequestData requestData, CancellationToken cancellationToken)
at Elasticsearch.Net.RequestPipeline.CallElasticsearchAsync[TResponse](RequestData requestData, CancellationToken cancellationToken)
at Elasticsearch.Net.Transport1.RequestAsync[TResponse](HttpMethod method, String path, CancellationToken cancellationToken, PostData data, IRequestParameters requestParameters) Inner Exception: expected:'true | false', actual:'-1', at offset:1547 Elasticsearch.Net.Utf8Json.JsonParsingException: expected:'true | false', actual:'-1', at offset:1547 at Elasticsearch.Net.Utf8Json.JsonReader.ReadBoolean() at Elasticsearch.Net.Utf8Json.Formatters.NullableBooleanFormatter.Deserialize(JsonReader& reader, IJsonFormatterResolver formatterResolver) at Deserialize(Object[] , JsonReader& , IJsonFormatterResolver ) at Nest.ReadAsFormatter2.Deserialize(JsonReader& reader, IJsonFormatterResolver formatterResolver)
at Elasticsearch.Net.Utf8Json.JsonSerializer.Deserialize[T](Byte[] bytes, Int32 offset, IJsonFormatterResolver resolver)
at Elasticsearch.Net.Utf8Json.JsonSerializer.DeserializeAsync[T](Stream stream, IJsonFormatterResolver resolver)
at Nest.TranslateSqlResponseBuilder.DeserializeResponseAsync(IElasticsearchSerializer builtInSerializer, IApiCallDetails response, Stream stream, CancellationToken ctx)
at Elasticsearch.Net.ResponseBuilder.SetBodyAsync[TResponse](ApiCallDetails details, RequestData requestData, Stream responseStream, String mimeType, CancellationToken cancellationToken)
at Elasticsearch.Net.ResponseBuilder.ToResponseAsync[TResponse](RequestData requestData, Exception ex, Nullable1 statusCode, IEnumerable1 warnings, Stream responseStream, String productName, String mimeType, CancellationToken cancellationToken)
at Elasticsearch.Net.HttpConnection.RequestAsync[TResponse](RequestData requestData, CancellationToken cancellationToken)
at Elasticsearch.Net.RequestPipeline.CallElasticsearchAsync[TResponse](RequestData requestData, CancellationToken cancellationToken)
at Elasticsearch.Net.Transport1.RequestAsync[TResponse](HttpMethod method, String path, CancellationToken cancellationToken, PostData data, IRequestParameters requestParameters) Exception: Elasticsearch.Net.UnexpectedElasticsearchClientException: expected:'true | false', actual:'-1', at offset:1547 ---> Elasticsearch.Net.Utf8Json.JsonParsingException: expected:'true | false', actual:'-1', at offset:1547 at Elasticsearch.Net.Utf8Json.JsonReader.ReadBoolean() at Elasticsearch.Net.Utf8Json.Formatters.NullableBooleanFormatter.Deserialize(JsonReader& reader, IJsonFormatterResolver formatterResolver) at Deserialize(Object[] , JsonReader& , IJsonFormatterResolver ) at Nest.ReadAsFormatter2.Deserialize(JsonReader& reader, IJsonFormatterResolver formatterResolver)
at Elasticsearch.Net.Utf8Json.JsonSerializer.Deserialize[T](Byte[] bytes, Int32 offset, IJsonFormatterResolver resolver)
at Elasticsearch.Net.Utf8Json.JsonSerializer.DeserializeAsync[T](Stream stream, IJsonFormatterResolver resolver)
at Nest.TranslateSqlResponseBuilder.DeserializeResponseAsync(IElasticsearchSerializer builtInSerializer, IApiCallDetails response, Stream stream, CancellationToken ctx)
at Elasticsearch.Net.ResponseBuilder.SetBodyAsync[TResponse](ApiCallDetails details, RequestData requestData, Stream responseStream, String mimeType, CancellationToken cancellationToken)
at Elasticsearch.Net.ResponseBuilder.ToResponseAsync[TResponse](RequestData requestData, Exception ex, Nullable1 statusCode, IEnumerable1 warnings, Stream responseStream, String productName, String mimeType, CancellationToken cancellationToken)
at Elasticsearch.Net.HttpConnection.RequestAsync[TResponse](RequestData requestData, CancellationToken cancellationToken)
at Elasticsearch.Net.RequestPipeline.CallElasticsearchAsync[TResponse](RequestData requestData, CancellationToken cancellationToken)
at Elasticsearch.Net.Transport1.RequestAsync[TResponse](HttpMethod method, String path, CancellationToken cancellationToken, PostData data, IRequestParameters requestParameters) --- End of inner exception stack trace --- at Elasticsearch.Net.Transport1.RequestAsync[TResponse](HttpMethod method, String path, CancellationToken cancellationToken, PostData data, IRequestParameters requestParameters)
at daemonConsole.Program.Main(String[] args) in D:\POC\daemonConsole\daemonConsole\Program.cs:line 73`

Steps to reproduce:

1. Install Nest package mentioned above
2. Create client and call SQL Translate API as below
   var sqltranslate = client.Sql.Translate(t => t.Query("select top 10 * from kibana_sample_data_ecommerce"));

Expected behavior
It should return the translated query for the SQL query.

[stevejgordon]

@sarjak Thanks for highlighting this. I've checked, and this is likely caused by this endpoint not yet supporting compatibility mode. There is an issue that tracks this in the Elasticsearch repository. I'll need to verify the differences, but right now, you may unfortunately need to drop to the low-level client for this endpoint until the server implements compatibility mode.

[stevejgordon]

7.x server response:

{
  "size" : 10,
  "_source" : false,
  "sort" : [
    {
      "_doc" : {
        "order" : "asc"
      }
    }
  ]
}

8.x server response:

{
  "size": 10,
  "_source": false,
  "sort": [
    {
      "_doc": {
        "order": "asc"
      }
    }
  ],
  "track_total_hits": -1
}

The issue is track_total_hits, which in 7.x spec was specified only as a boolean, when it can also be an integer. As per this issue it would be a breaking change to change this type in the 7.x client. This is fixed in the new Elasticsearch specification for 8.x code generation.

The issue here is that the 8.x response includes this and uses the integer value which the 7.x client cannot deserialize. The assumption is that compatibility mode would not send this value when the value is false (-1) to mirror the 7.x response. Ideally that change would occur in the server.

[sarjak]

@stevejgordon Thanks for the response. Confirming that it works when downgraded the elastic deployment from 8.x to 7.x with 7.x client.

Wanted to know if there is any roadmap for this fix in the response from 8.x. Also, any ETA for making Translate API available in 8.x client. This will help us in planning the development and creating proper deployment of elastic. Thanks.

[stevejgordon]

@sarjak No ETA I can provide at the moment. I think the server fix is the key thing here since we can't really take the breaking change to the type in NEST at this point. I'll keep this open to help remind me to keep on top of server-side progress.