Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-4292

Wrong results in ElasticSearch when query contains NOT EQUAL

    XMLWordPrintableJSON

Details

    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

      Attachments

        Issue Links

          Activity

            People

              bill neil Bill Neil
              shlok7296 Shlok Srivastava
              Votes:
              9 Vote for this issue
              Watchers:
              8 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - 0h
                  0h
                  Logged:
                  Time Spent - 0.5h
                  0.5h