[CALCITE-4292] Wrong results in ElasticSearch when query contains NOT EQUAL

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Not A Bug
• Fix Version/s: 1.30.0
• Component/s: elasticsearch-adapter
• Labels: ElasticSearch NotEquals QueryBuilder calcite pull-request-available
• Assignee: Bill Neil
• Reporter: Shlok Srivastava
• Created: 28/Sep/20 08:49
• Updated: 19/Mar/22 09:21
• Resolved: 04/Jan/22 14:30

Description

Currently elasticsearch adapters ignore records which do not have the field specified in NotEqual clause, which is ideally included by elasticsearch.

Query:

SELECT* from zips WHERE name <> "NMAX"

The elasticsearch query formed  for above  is this : 

{
  "query": {
    "constant_score": {
      "filter": {
        "bool": {
          "must": {
            "exists": {
              "field": "Name"
            }
          },
          "must_not": {
            "term": {
              "Name": "NMQAX"
            }
          }
        }
      }
    }
  }
}

Problem : The above query ignores document which do not have Name field which is ideally included by elasticsearch but ignored due to must exists condition. 

Data:

{ "_id" : "01701", "name" : "NMAX", "loc" : [ -71.42548600000001, 42.300665 ], "pop" : 65046, "state" : "MA" }
{ "_id" : "02154", "name" : "NORTH WALTHAM", "loc" : [ -71.236497, 42.382492 ], "pop" : 57871, "state" : "MA" }
{ "_id" : "02401 , "loc" : [ -71.03434799999999, 42.081571 ], "pop" : 59498, "state" : "MA" }

 

 
Expected result: 

{ "_id" : "02154", "name" : "NORTH WALTHAM", "loc" : [ -71.236497, 42.382492 ], "pop" : 57871, "state" : "MA" }
{ "_id" : "02401", "loc" : [ -71.03434799999999, 42.081571 ], "pop" : 59498, "state" : "MA" }

 
Current Result:

 { "_id" : "02154", "name" : "NORTH WALTHAM", "loc" : [ -71.236497, 42.382492 ], "pop" : 57871, "state" : "MA" }

RelNode for same - 

relB.not(relB.equals(relb.literal("Name"),relb.literal"NMQAX"))

 

Solution : Remove the exists condition from Not equals Query Expression. Elasticsearch doesn't put this condition therefore keeping queries in sync. Code

Issue Links

• links to: GitHub Pull Request #2216