[CALCITE-4232] Elasticsearch IN Query is not supported

• Type: Improvement
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Component/s: elasticsearch-adapter
• Labels: IN calcite elasticsearch pull-request-available query
• Assignee: Unassigned
• Reporter: Shlok Srivastava
• Created: 06/Sep/20 10:28
• Updated: 04/Oct/21 09:42

Description

IN clause is required in multiple queries to fetch  documents that contain more than one exact terms in a provided field.

SQL Query

name IN ("kimchy", "james")

 RelNode for same-

rexb.makeCall(SqlStdOperatorTable.IN, builder.field("name"), rexb.makeLiteral("kimchy"), rexb.makeLiteral("james"))

 

  Problem : Calcite for above RelNode throws

java.sql.SQLException : Error while preparing statement [null]

Suppressed: java.lang.UnsupportedOperationException: Can't handle partial QueryExpression: org.apache.calcite.adapter.elasticsearch.PredicateAnalyzer$CompoundQueryExpression@3e782f91
at org.apache.calcite.adapter.elasticsearch.PredicateAnalyzer.analyze(PredicateAnalyzer.java:114)
at org.apache.calcite.adapter.elasticsearch.ElasticsearchFilter$PredicateAnalyzerTranslator.translateMatch(ElasticsearchFilter.java:99)
at org.apache.calcite.adapter.elasticsearch.ElasticsearchFilter.implement(ElasticsearchFilter.java:64)
at org.apache.calcite.adapter.elasticsearch.ElasticsearchRel$Implementor.visitChild(ElasticsearchRel.java:140)
at org.apache.calcite.adapter.elasticsearch.ElasticsearchProject.implement(ElasticsearchProject.java:59)

Expected Elasticsearch Query - 

GET /_search
{
  "query": {
    "terms": {
      "name": [
        "kimchy",
        "james"
      ]
    }
  }
}

Solution: Extend IN SQLTableOperator support in elasticsearch adapters to use Terms Query Builder which is already present in elasticsearch adapters.

 

Issue Links

• links to: GitHub Pull Request #2201
• links to: GitHub Pull Request #2551