SQL:Field name with "." returns null

[adamquan]

To reproduce:

PUT /test/blah/1
{
  "name": "Shane",
  "full.name": "Shane Connelly"
}

PUT /test/blah/2
{
  "name": "Shane",
  "full": {
    "name": "Shane Connelly"
  }
}

POST /_xpack/sql?format=txt
{
  "query": "DESCRIBE test"
}

POST /_xpack/sql?format=txt
{
  "query": "select * from test"
}

this is the result. As you can see, full.name for the first row is null.

   full.name   |     name      
---------------+---------------
null           |Shane          
Shane Connelly |Shane

[elasticmachine]

Pinging @elastic/es-search

[astefan]

This issue is about the way ES SQL handles the results it gets back from ES. In this particular case, because full.name is a text field (as opposed to keywords where it gets the values from docvalue_fields), SQL creates a query that will ask for

"_source": {
    "includes": [
      "full.name",
      "name"
    ],
    "excludes": []
  }

When it gets back the results, it will parse the source and try to extract a parent field called full and its sub-field called name. In JSON this is represented as "full": { "name": "....."}. But, in the case of document with id 1, the source is "full.name": "Shane Connelly". SQL will not know what field is "full.name" because it won't split the fields following dots, so it will put null as the value.

Shortly put, SQL knows how to handle "field": {"sub_field1":"", "sub_field2":....} replies and no dot notations (field.sub_field1.X.Y....).
We should "normalize" what we get back from ES and transform the result in one form only (the curly brackets one probably) and handle that forward.