PUT employees_nested
{
  "mappings": {
    "properties": {
      "id": {
        "type": "long"
      },
      "name": {
        "type": "text",
        "fields": {
          "keyword": {
            "type": "keyword",
            "ignore_above": 256
          }
        }
      },
      "projects": {
        "type": "nested",
        "properties": {
          "name": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword"
              }
            },
            "fielddata": true
          },
          "started_year": {
            "type": "long"
          }
        }
      },
      "title": {
        "type": "text",
        "fields": {
          "keyword": {
            "type": "keyword",
            "ignore_above": 256
          }
        }
      }
    }
  }
}

POST employees_nested/_bulk
{"index":{"_id":"1"}}
{"id":3,"name":"Bob Smith","title":null,"projects":[{"name":"AWS Redshift Spectrum querying","started_year":1990},{"name":"AWS Redshift security","started_year":1999},{"name":"AWS Aurora security","started_year":2015}]}
{"index":{"_id":"2"}}
{"id":4,"name":"Susan Smith","title":"Dev Mgr","projects":[]}
{"index":{"_id":"3"}}
{"id":6,"name":"Jane Smith","title":"Software Eng 2","projects":[{"name":"AWS Redshift security","started_year":1998},{"name":"AWS Hello security","started_year":2015,"address":[{"city":"Dallas","state":"TX"}]}]}

POST _opendistro/_sql
{
  "query":
    """
    SELECT e.id AS id,
           e.name AS employeeName,
           e.title AS title,
           p.name AS projectName
    FROM employees_nested AS e
    LEFT JOIN e.projects AS p
    """
}

{
  "error": {
    "reason": "There was internal problem at backend",
    "details": "Index 0 out of bounds for length 0",
    "type": "ArrayIndexOutOfBoundsException"
  },
  "status": 500
}

[2020-03-26T15:26:25,114][WARN ][c.a.o.s.e.f.PrettyFormatRestExecutor] [186590df9563.ant.amazon.com] Error happened in pretty formatter
java.lang.ArrayIndexOutOfBoundsException: Index 0 out of bounds for length 0
	at com.amazon.opendistroforelasticsearch.sql.executor.format.SelectResultSet.doFlatNestedFieldName(SelectResultSet.java:751) ~[opendistro_sql-1.6.0.0.jar:1.6.0.0]
	at com.amazon.opendistroforelasticsearch.sql.executor.format.SelectResultSet.flatNestedField(SelectResultSet.java:743) ~[opendistro_sql-1.6.0.0.jar:1.6.0.0]
	at com.amazon.opendistroforelasticsearch.sql.executor.format.SelectResultSet.populateRows(SelectResultSet.java:560) ~[opendistro_sql-1.6.0.0.jar:1.6.0.0]
	at com.amazon.opendistroforelasticsearch.sql.executor.format.SelectResultSet.extractData(SelectResultSet.java:526) ~[opendistro_sql-1.6.0.0.jar:1.6.0.0]
	at com.amazon.opendistroforelasticsearch.sql.executor.format.SelectResultSet.<init>(SelectResultSet.java:108) ~[opendistro_sql-1.6.0.0.jar:1.6.0.0]
	at com.amazon.opendistroforelasticsearch.sql.executor.format.Protocol.loadResultSet(Protocol.java:83) ~[opendistro_sql-1.6.0.0.jar:1.6.0.0]
	at com.amazon.opendistroforelasticsearch.sql.executor.format.Protocol.<init>(Protocol.java:65) ~[opendistro_sql-1.6.0.0.jar:1.6.0.0]
	at com.amazon.opendistroforelasticsearch.sql.executor.format.PrettyFormatRestExecutor.execute(PrettyFormatRestExecutor.java:71) [opendistro_sql-1.6.0.0.jar:1.6.0.0]
	at com.amazon.opendistroforelasticsearch.sql.executor.format.PrettyFormatRestExecutor.execute(PrettyFormatRestExecutor.java:47) [opendistro_sql-1.6.0.0.jar:1.6.0.0]
	at com.amazon.opendistroforelasticsearch.sql.executor.AsyncRestExecutor.doExecuteWithTimeMeasured(AsyncRestExecutor.java:161) [opendistro_sql-1.6.0.0.jar:1.6.0.0]
	at com.amazon.opendistroforelasticsearch.sql.executor.AsyncRestExecutor.lambda$async$1(AsyncRestExecutor.java:121) [opendistro_sql-1.6.0.0.jar:1.6.0.0]
	at com.amazon.opendistroforelasticsearch.sql.utils.LogUtils.lambda$withCurrentContext$0(LogUtils.java:72) [opendistro_sql-1.6.0.0.jar:1.6.0.0]
	at org.elasticsearch.common.util.concurrent.ThreadContext$ContextPreservingRunnable.run(ThreadContext.java:633) [elasticsearch-7.6.1.jar:7.6.1]
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) [?:?]
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) [?:?]
	at java.lang.Thread.run(Thread.java:830) [?:?]