[BUG] JOIN query with object field in joined condition return nothing

[dai-chen]

What is the bug?

If a JOIN query uses object field in ON clause as joined condition, it will return empty.

How can one reproduce the bug?

PUT join-object-test/_doc/1
{ 
  "account.name.first": "John"
}

POST _plugins/_sql
{
  "query" : """
    SELECT
      p.account.name.first
    FROM join-object-test p
    JOIN join-object-test y  
    ON p.account.name.first = y.account.name.first
  """
}

What is the expected behavior?

{
  "schema": [{
    "name": "p.account.name.first",
    "type": "text"
  }],
  "total": 1,
  "datarows": [["John"]],
  "size": 1,
  "status": 200
}

What is your host/environment?

• Version: 2.x

Do you have any additional context?

Code: https://github.com/opensearch-project/sql/blob/2.x/legacy/src/main/java/org/opensearch/sql/legacy/query/planner/physical/node/scroll/SearchHitRow.java#L128

The getValueOfPath() finds object field value in source recursively. However, the source is already flatten as below:

source = {HashMap@17079}  size = 1
 "account.name.first" -> "John"

With a simple check, the problematic query can work. But not sure when the source format is changed. Or this function never work for object field?

[dai-chen]

After more test, I found out that the root cause is OpenSearch has different response format for object field. For the join-object-test index above, it returns:

{
   ...
    "hits": [
      {
        "_index": "join-object-test",
        "_id": "As03gIUBc6yO9RmmbOSb",
        "_score": 1.0,
        "_source": {
          "account": {
            "name": "John"
          }
        }
      }
    ]
  }
}

But if the doc with object field is created in the way as follows:

POST http://localhost:9200/join-object-test2/_doc/
{
  "account.name": "John"
}

OpenSearch returns:

POST localhost:9200/join-object-test2/_search
{
  "took": 2,
   ...
    "hits": [
      {
        "_index": "join-object-test2",
        "_id": "Ac00gIUBc6yO9RmmMeTE",
        "_score": 1.0,
        "_source": {
          "account.name": "John"
        }
      }
    ]
  }
}

Although both indexes have the exactly same mapping:

{
  "join-object-test": {
    "mappings": {
      "properties": {
        "account": {
          "properties": {
            "name": {
              "type": "text",
              "fields": {
                "keyword": {
                  "type": "keyword",
                  "ignore_above": 256
                }
              }
            }
          }
        }
      }
    }
  }
}

So the issue seems not only for JOIN in legacy code. V2 code also assumes the first response format. Need more investigation to confirm.

[dai-chen]

More tests for different response format:

POST http://localhost:9200/jointest3/_doc/
{
  "account": {
    "name.first": "John"
  }
}

"hits": [
  {
    "_index": "jointest3",
    "_id": "rhEAg4UBBxPx0bCIdbYG",
    "_score": 1.0,
    "_source": {
      "account": {
        "name.first": "John"
      }
    }
  }
]

POST http://localhost:9200/jointest4/_doc/
{
  "account.name": {
    "first": "John"
  }
}

"hits": [
  {
    "_index": "jointest4",
    "_id": "rxEBg4UBBxPx0bCITLby",
    "_score": 1.0,
    "_source": {
      "account.name": {
        "first": "John"
      }
    }
  }
]