Feature request: support select columns from right index in left join query

[seraphjiang]

Use case

i have two index patterns
index1 [account, col1]
index2* [account, col2]

they share same key account.

when I run follow query, I'm not able to get columns from index2*.

GET _opendistro/_sql?format=csv
{
  "query": """
  SELECT *, i2.*
  FROM index1 i1 
  LEFT JOIN  [index2*] as i2
    ON i1.account.keyword = i2.account.keyword 
  LIMIT 1000
"""
}

# only columns from left index are selected
account, col1
...
...

version

1.4.0.0

[penghuo]

Test with sample data.

PUT /index1/_doc/1
{
  "account": "a-1",
  "region": "us-east-1"
}

PUT /index1/_doc/2
{
  "account": "b-1",
  "region": "us-east-1"
}

PUT /index21/_doc/1
{
  "account": "a-1",
  "size": "10"
}

PUT /index22/_doc/2
{
  "account": "a-1",
  "size": "15"
}

POST _opendistro/_sql?format=csv
{
  "query": """
  SELECT *, i2.*
  FROM index1 i1 
  LEFT JOIN  [index2*] as i2
    ON i1.account.keyword = i2.account.keyword 
  LIMIT 1000
"""
}

The result is

i1.account,i2.size,i1.region,i2.account
a-1,10,us-east-1,a-1
a-1,15,us-east-1,a-1
b-1,,us-east-1,

@seraphjiang, could you provide sample data for your failure cases?