LEFT JOIN with WHERE doesn't filter the result as expected

[penghuo]

Issue
The WHERE clause doesn't filter out the null value from right table after LEFT JOIN.

LEFT JOIN query

GET /_opendistro/_sql/?
{
  "query": "SELECT * FROM dog D LEFT JOIN account A ON D.holdersName = A.firstname WHERE A.firstname = 'Hattie'"
}

Sample Data

// dog index
{"index":{"_type": "dog", "_id":"1"}}
{"dog_name":"rex","holdersName":"Daenerys","age":2}
{"index":{"_type": "dog", "_id":"6"}}
{"dog_name":"snoopy","holdersName":"Hattie","age":4}

// account index
{"index":{"_type": "account", "_id":"6"}}
{"account_number":6,"balance":5686,"firstname":"Hattie","lastname":"Bond","age":36}

Actual Result

    "hits": [
      {
        "_type": "dog|account",
        "_id": "6|6",
        "_score": "-Infinity",
        "_source": {
          "D.age": 4,
          "D.holdersName": "Hattie",
          "A.lastname": "Bond",
          "D.dog_name": "snoopy",
          "A.balance": 5686,
          "A.firstname": "Hattie",
          "A.age": 36,
        }
      },
      {
        "_type": "dog|null",
        "_id": "1|0",
        "_score": "-Infinity",
        "_source": {
          "D.dog_name": "rex",
          "D.age": 2,
          "D.holdersName": "Daenerys"
        }
      }
    ]

Expected Result

    "hits": [
      {
        "_type": "dog|account",
        "_id": "6|6",
        "_score": "-Infinity",
        "_source": {
          "D.age": 4,
          "D.holdersName": "Hattie",
          "A.lastname": "Bond",
          "D.dog_name": "snoopy",
          "A.balance": 5686,
          "A.firstname": "Hattie",
          "A.age": 36,
        }
      }
    ]

[dai-chen]

Thanks for raising the issue! The root cause is there is no physical operator for WHERE conditions on top of Join operator to perform the post-processing for now. We missed this semantic error in the testing because we used and assumed existing Nested Loop join is correct (which actually has the same issue) and didn't realized the difference in other RDBMS.

To fix the issue, a new operator should be included in physical plan in which WHERE conditions be translated into in-memory operation in Java code. To make it simple, we can start with support for very basic operation such as =, <, > etc.