Where clause in nested queries does not return expected results.

[abbashus]

The condition in WHERE clause for nested queries does not return expected result.

===== Mappings =====
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",
                  "ignore_above": 256
                }
              }
              
            }
          }
        },
        "title": {
          "type": "text",
          "fields": {
            "keyword": {
              "type": "keyword",
              "ignore_above": 256
            }
          }
        }
      
    }
  }
}

===== Data =====
PUT employees_nested/_doc/1
{
  "id": 3,
  "name": "Bob Smith",
  "title": null,
  "projects": [
    {
      "name": "AWS Redshift Spectrum querying"
    },
        {
      "name": "AWS Redshift security"
    },
        {
      "name": "AWS Aurora security"
    }
  ]
}
 
 
PUT employees_nested/_doc/2
{
  "id": 4,
  "name": "Susan Smith",
  "title": "Dev Mgr",
  "projects": []
}
 

This query

POST _opendistro/_sql/?format=jdbc
{
"query" : "SELECT e.name AS employeeName, e.title AS title, p.name AS projectName FROM employees_nested AS e,  e.projects AS p WHERE p.name = 'AWS Redshift Spectrum querying' "
 
}

should return

...
...
"datarows": [
    [
      "Bob Smith",
      null,
      "AWS Redshift Spectrum querying"
    ],
...
...

but returns this

...
"datarows": [],
...

From the _explain

...
...
                "nested": {
                  "query": {
                    "term": {
                      "projects.name.keyword": {  <======
                        "value": "AWS Redshift Spectrum querying",
                        "boost": 1
                      }
                    }
                  },
...
...

the field for the term query should be projects.name.keyword instead of projects.name to get correct results.

[FreCap]

I tried the query above is generating the right result but it is using the legacy engine. Is this thread specific to the new engine?

Fallback message:

[2021-06-01T18:27:42,061][INFO ][c.a.o.s.l.p.RestSqlAction] [0847b650-f46f-4224-bbd0-63b99111c38c] Incoming request /_opendistro/_sql?pretty=true: ( SELECT identifier AS employeeName, identifier AS title FROM table e, table p WHERE identifier = 'string_literal' )
[2021-06-01T18:27:42,069][WARN ][stderr                   ]  line 1:8 missing '(' at '.'