[Bug] Support Multi-fields in WHERE Conditions

[rupal-bq]

What is the bug?
There is limitation on Multi-fields in WHERE Conditions

If a field is defined with multiple types, then SQL engine treat it as text_keyword.

How can one reproduce the bug?
For example if the field defined as

"id1" : {
          "type" : "keyword",
          "fields" : {
            "id2" : {
              "type" : "integer"
            }
          }
        },

then following query

SELECT id1 
FROM sample_data 
WHERE n1 IN ('19') AND n2 IN ('299')
ORDER BY id1.id2 ASC LIMIT 10 OFFSET 0

will result in error

{
  "error": {
    "reason": "Invalid SQL query",
    "details": "can't resolve Symbol(namespace=FIELD_NAME, name=id1.id2) in type env",
    "type": "SemanticCheckException"
  },
  "status": 400
}

What is the expected behavior?
For multi-field (a text field with another keyword field inside), there should be no assumption that the keyword field name is always "keyword".

Query with above format should return expected result.

What is your host/environment?

• OS: [e.g. iOS]
• Version [e.g. 22]
• Plugins

Do you have any screenshots?
If applicable, add screenshots to help explain your problem.

Do you have any additional context?
Add any other context about the problem.

[Yury-Fridlyand]

Related or duplicated by #1038

[acarbonetto]

Related or duplicated by #1038

Related, but not a duplicate. #1038 is specific to text fields and requires that text fields get treated properly by JDBC engine. Any field can be defined with multi-fields. This would also benefit text fields. JDBC, for example, might treat the field myText as a field of SQL-type text (according to #1038), but also treat the multi-field myText.keyword as a field of SQL-type varchar (from this issue).

@rupal-bq this is a FEATURE, not a BUG. There only very limited existing support for multi-fields in the existing engines (using the like function kind of does this... but very poorly).