SQL: a query touching an empty index shouldn't return an error message

[astefan]

Following the change in #52967, a SELECT * FROM test where test index doesn't have a mapping will return an error message. It should return no columns and no error message.

[elasticmachine]

Pinging @elastic/es-search (:Search/SQL)

[Luegg]

The same (or a similar) problem occurs when querying empty nested fields:

PUT http://localhost:9200/test
Content-Type: application/json

{
  "mappings": {
    "properties": {
      "user": {
        "type": "nested"
      }
    }
  }
}

###

http://localhost:9200/_sql?format=txt
Content-Type: application/json

{
    "query": "select user.* from test"
}

results in

{
  "error": {
    "root_cause": [
      {
        "type": "verification_exception",
        "reason": "Found 1 problem\nline 1:8: Unknown column [user]"
      }
    ],
    "type": "verification_exception",
    "reason": "Found 1 problem\nline 1:8: Unknown column [user]"
  },
  "status": 400
}

but the query succeeds as soon as there are fields in user:

POST http://localhost:9200/test/_doc
Content-Type: application/json

{
    "user": [
        {"name": "p"},
        {"name": "q"}
    ]
}

###

http://localhost:9200/_sql?format=txt
Content-Type: application/json

{
    "query": "select user.* from test"
}

now retrieves:

   user.name   
---------------
p              
q              

[austinjoyal]

The same (or a similar) problem occurs when querying empty nested fields:

PUT http://localhost:9200/test
Content-Type: application/json

{
  "mappings": {
    "properties": {
      "user": {
        "type": "nested"
      }
    }
  }
}

###

http://localhost:9200/_sql?format=txt
Content-Type: application/json

{
    "query": "select user.* from test"
}

results in

{
  "error": {
    "root_cause": [
      {
        "type": "verification_exception",
        "reason": "Found 1 problem\nline 1:8: Unknown column [user]"
      }
    ],
    "type": "verification_exception",
    "reason": "Found 1 problem\nline 1:8: Unknown column [user]"
  },
  "status": 400
}

but the query succeeds as soon as there are fields in user:

POST http://localhost:9200/test/_doc
Content-Type: application/json

{
    "user": [
        {"name": "p"},
        {"name": "q"}
    ]
}

###

http://localhost:9200/_sql?format=txt
Content-Type: application/json

{
    "query": "select user.* from test"
}

now retrieves:

   user.name   
---------------
p              
q              

Hello, I am getting the same "Unknown column" error on a non-nested field if the value is null. I am using python client for elasticsearch and querying using sql.query(), but I suppose the curl will also throw the same error. The query is successful as soon as I have a value in the field.
Can anyone confirm if it is still an issue and if so, what is the solution in the meantime?