Queries with "null" in JSON results cannot be deserialized

[rGunti]

NEST/Elasticsearch.Net version: 7.0.0-alpha2

Elasticsearch version: 7.0.1

Description of the problem including expected versus actual behavior:
I am using client.QuerySql() to fetch data (configured externally). When the resulting data contains a null token, the deserialization fails with the following stack trace.

Elasticsearch.Net.UnexpectedElasticsearchClientException: expected:',', actual:'null', at offset:4450 ---> Elasticsearch.Net.JsonParsingException: expected:',', actual:'null', at offset:4450
   at Elasticsearch.Net.JsonReader.ReadIsValueSeparatorWithVerify()
   at Elasticsearch.Net.JsonReader.ReadIsEndArrayWithSkipValueSeparator(Int32& count)
   at Elasticsearch.Net.CollectionFormatterBase`4.Deserialize(JsonReader& reader, IJsonFormatterResolver formatterResolver)
   at Nest.SqlRowFormatter.Deserialize(JsonReader& reader, IJsonFormatterResolver formatterResolver)
   at Elasticsearch.Net.CollectionFormatterBase`4.Deserialize(JsonReader& reader, IJsonFormatterResolver formatterResolver)
   at Deserialize(Object[] , JsonReader& , IJsonFormatterResolver )
   at Elasticsearch.Net.JsonSerializer.Deserialize[T](Byte[] bytes, Int32 offset, IJsonFormatterResolver resolver)
   at Elasticsearch.Net.JsonSerializer.Deserialize[T](Stream stream, IJsonFormatterResolver resolver)
   at Nest.InternalSerializer.Deserialize[T](Stream stream)
   at Elasticsearch.Net.ResponseBuilder.SetBody[TResponse](ApiCallDetails details, RequestData requestData, Stream responseStream, String mimeType)
   at Elasticsearch.Net.ResponseBuilder.ToResponse[TResponse](RequestData requestData, Exception ex, Nullable`1 statusCode, IEnumerable`1 warnings, Stream responseStream, String mimeType)
   at Elasticsearch.Net.HttpConnection.Request[TResponse](RequestData requestData)
   at Elasticsearch.Net.RequestPipeline.CallElasticsearch[TResponse](RequestData requestData)
   at Elasticsearch.Net.Transport`1.Request[TResponse](HttpMethod method, String path, PostData data, IRequestParameters requestParameters)
   --- End of inner exception stack trace ---
   at Elasticsearch.Net.Transport`1.Request[TResponse](HttpMethod method, String path, PostData data, IRequestParameters requestParameters)
   at Nest.ElasticClient.QuerySql(Func`2 selector)
   at ElasticActor.Core.Worker.ExecuteTask(ActivatedTask task)

Am I missing a config setting or is this an oversight in development?

Steps to reproduce:

1. Query data using SQL with ElasticClient.QuerySql(...)
2. Exception gets thrown

[Mpdreamz]

Are you able to set .EnableDebugMode() on ConnectionSettings and paste the contents of the .DebugInformation on the exception?

This should make it a bit easier to debug on our end. Make sure the request/response contain no sensitive information before sharing though.

[rGunti]

The debug info on the Exception contains this:

# FailureReason: Unrecoverable/Unexpected BadResponse while attempting POST on http://host:9200/_sql?pretty=true&error_trace=true
 - [1] BadResponse: Node: http://host:9200/ Exception: JsonParsingException Took: 00:00:00.0400000
# Audit exception in step 1 BadResponse:
Elasticsearch.Net.JsonParsingException: expected:',', actual:'null', at offset:4450
   at Elasticsearch.Net.JsonReader.ReadIsValueSeparatorWithVerify()
   at Elasticsearch.Net.JsonReader.ReadIsEndArrayWithSkipValueSeparator(Int32& count)
   at Elasticsearch.Net.CollectionFormatterBase`4.Deserialize(JsonReader& reader, IJsonFormatterResolver formatterResolver)
   at Nest.SqlRowFormatter.Deserialize(JsonReader& reader, IJsonFormatterResolver formatterResolver)
   at Elasticsearch.Net.CollectionFormatterBase`4.Deserialize(JsonReader& reader, IJsonFormatterResolver formatterResolver)
   at Deserialize(Object[] , JsonReader& , IJsonFormatterResolver )
   at Elasticsearch.Net.JsonSerializer.Deserialize[T](Byte[] bytes, Int32 offset, IJsonFormatterResolver resolver)
   at Elasticsearch.Net.JsonSerializer.Deserialize[T](Stream stream, IJsonFormatterResolver resolver)
   at Nest.InternalSerializer.Deserialize[T](Stream stream)
   at Elasticsearch.Net.ResponseBuilder.SetBody[TResponse](ApiCallDetails details, RequestData requestData, Stream responseStream, String mimeType)
   at Elasticsearch.Net.ResponseBuilder.ToResponse[TResponse](RequestData requestData, Exception ex, Nullable`1 statusCode, IEnumerable`1 warnings, Stream responseStream, String mimeType)
   at Elasticsearch.Net.HttpConnection.Request[TResponse](RequestData requestData)
   at Elasticsearch.Net.RequestPipeline.CallElasticsearch[TResponse](RequestData requestData)
   at Elasticsearch.Net.Transport`1.Request[TResponse](HttpMethod method, String path, PostData data, IRequestParameters requestParameters)
# Inner Exception: expected:',', actual:'null', at offset:4450
Elasticsearch.Net.JsonParsingException: expected:',', actual:'null', at offset:4450
   at Elasticsearch.Net.JsonReader.ReadIsValueSeparatorWithVerify()
   at Elasticsearch.Net.JsonReader.ReadIsEndArrayWithSkipValueSeparator(Int32& count)
   at Elasticsearch.Net.CollectionFormatterBase`4.Deserialize(JsonReader& reader, IJsonFormatterResolver formatterResolver)
   at Nest.SqlRowFormatter.Deserialize(JsonReader& reader, IJsonFormatterResolver formatterResolver)
   at Elasticsearch.Net.CollectionFormatterBase`4.Deserialize(JsonReader& reader, IJsonFormatterResolver formatterResolver)
   at Deserialize(Object[] , JsonReader& , IJsonFormatterResolver )
   at Elasticsearch.Net.JsonSerializer.Deserialize[T](Byte[] bytes, Int32 offset, IJsonFormatterResolver resolver)
   at Elasticsearch.Net.JsonSerializer.Deserialize[T](Stream stream, IJsonFormatterResolver resolver)
   at Nest.InternalSerializer.Deserialize[T](Stream stream)
   at Elasticsearch.Net.ResponseBuilder.SetBody[TResponse](ApiCallDetails details, RequestData requestData, Stream responseStream, String mimeType)
   at Elasticsearch.Net.ResponseBuilder.ToResponse[TResponse](RequestData requestData, Exception ex, Nullable`1 statusCode, IEnumerable`1 warnings, Stream responseStream, String mimeType)
   at Elasticsearch.Net.HttpConnection.Request[TResponse](RequestData requestData)
   at Elasticsearch.Net.RequestPipeline.CallElasticsearch[TResponse](RequestData requestData)
   at Elasticsearch.Net.Transport`1.Request[TResponse](HttpMethod method, String path, PostData data, IRequestParameters requestParameters)
# Exception:
Elasticsearch.Net.UnexpectedElasticsearchClientException: expected:',', actual:'null', at offset:4450 ---> Elasticsearch.Net.JsonParsingException: expected:',', actual:'null', at offset:4450
   at Elasticsearch.Net.JsonReader.ReadIsValueSeparatorWithVerify()
   at Elasticsearch.Net.JsonReader.ReadIsEndArrayWithSkipValueSeparator(Int32& count)
   at Elasticsearch.Net.CollectionFormatterBase`4.Deserialize(JsonReader& reader, IJsonFormatterResolver formatterResolver)
   at Nest.SqlRowFormatter.Deserialize(JsonReader& reader, IJsonFormatterResolver formatterResolver)
   at Elasticsearch.Net.CollectionFormatterBase`4.Deserialize(JsonReader& reader, IJsonFormatterResolver formatterResolver)
   at Deserialize(Object[] , JsonReader& , IJsonFormatterResolver )
   at Elasticsearch.Net.JsonSerializer.Deserialize[T](Byte[] bytes, Int32 offset, IJsonFormatterResolver resolver)
   at Elasticsearch.Net.JsonSerializer.Deserialize[T](Stream stream, IJsonFormatterResolver resolver)
   at Nest.InternalSerializer.Deserialize[T](Stream stream)
   at Elasticsearch.Net.ResponseBuilder.SetBody[TResponse](ApiCallDetails details, RequestData requestData, Stream responseStream, String mimeType)
   at Elasticsearch.Net.ResponseBuilder.ToResponse[TResponse](RequestData requestData, Exception ex, Nullable`1 statusCode, IEnumerable`1 warnings, Stream responseStream, String mimeType)
   at Elasticsearch.Net.HttpConnection.Request[TResponse](RequestData requestData)
   at Elasticsearch.Net.RequestPipeline.CallElasticsearch[TResponse](RequestData requestData)
   at Elasticsearch.Net.Transport`1.Request[TResponse](HttpMethod method, String path, PostData data, IRequestParameters requestParameters)
   --- End of inner exception stack trace ---
   at Elasticsearch.Net.Transport`1.Request[TResponse](HttpMethod method, String path, PostData data, IRequestParameters requestParameters)
   at Nest.ElasticClient.QuerySql(Func`2 selector)
   at ElasticActor.Core.Worker.ExecuteTask(ActivatedTask task) in C:\Users\myuser\source\repos\ElasticActor\ElasticActor.Core\Worker.cs:line 106

[rGunti]

And here is a JSON sample that I grabbed through Wireshark:
sample.json.txt

[codebrain]

Thanks @rGunti - I was able to reproduce and have opened a PR to resolve this,

[codebrain]

Merged