[CALCITE-4645] In Elasticsearch adapter, a range predicate should be translated to a range query

• Type: Bug
• Status: Open
• Priority: Critical
• Resolution: Unresolved
• Affects Version/s: 1.27.0
• Component/s: elasticsearch-adapter
• Labels: pull-request-available
• Assignee: Unassigned
• Reporter: Jacky Yin
• Created: 10/Jun/21 00:58
• Updated: 28/Feb/24 06:03

Description

SQL patterns like below will be converted to Search in range which is not supported in the Elasticsearch adapter. 

select count(*) from view where (f1 > 10 and f1 < 20) or (f1 > 30 and f1 < 40))
select * from view where f1 > 10 and f1 < 20
select * from view where f1 < 10 or f1 > 20 

Here is one example of the query plan change history for SQL

 select count(*) from view where val1 >= 10 and val1 <=20

Original:

 LogicalAggregate(group=[{}], EXPR$0=[COUNT()]): rowcount = 1.0, cumulative cost = {136.125 rows, 810.1 cpu, 0.0 io}, id = 14
  LogicalFilter(condition=[AND(>=($5, 10), <=($5, 20))]): rowcount = 25.0, cumulative cost = {135.0 rows, 810.1 cpu, 0.0 io}, id = 12
    LogicalProject(cat1=[ITEM($0, 'cat1')], cat2=[ITEM($0, 'cat2')], cat3=[ITEM($0, 'cat3')], cat4=[ITEM($0, 'cat4')], cat5=[ITEM($0, 'cat5')], val1=[ITEM($0, 'val1')], val2=[ITEM($0, 'val2')]): rowcount = 100.0, cumulative cost = {110.0 rows, 710.1 cpu, 0.0 io}, id = 11
      ElasticsearchTableScan(table=[[elastic, aggs]]): rowcount = 100.0, cumulative cost = {10.0 rows, 10.100000000000001 cpu, 0.0 io}, id = 10

 

Simplified:

LogicalAggregate(group=[{}], EXPR$0=[COUNT()]): rowcount = 2.5, cumulative cost = {2.8125 rows, 0.0 cpu, 0.0 io}, id = 32
  LogicalFilter(subset=[rel#31:RelSubset#2.NONE.[]], condition=[SEARCH($0, Sarg[[10..20]])]): rowcount = 25.0, cumulative cost = {25.0 rows, 100.0 cpu, 0.0 io}, id = 30
    LogicalProject(subset=[rel#29:RelSubset#1.NONE.[]], val1=[ITEM($0, 'val1')]): rowcount = 100.0, cumulative cost = {100.0 rows, 100.0 cpu, 0.0 io}, id = 28
      ElasticsearchTableScan(subset=[rel#27:RelSubset#0.ELASTICSEARCH.[]], table=[[elastic, aggs]]): rowcount = 100.0, cumulative cost = {10.0 rows, 10.100000000000001 cpu, 0.0 io}, id = 10
 

 

Optimized by the rules in es adapter:

ElasticsearchToEnumerableConverter: rowcount = 1.0, cumulative cost = {12.7125 rows, 20.200000000000003 cpu, 0.0 io}, id = 84
  ElasticsearchAggregate(group=[{}], EXPR$0=[COUNT()]): rowcount = 1.0, cumulative cost = {12.6125 rows, 20.1 cpu, 0.0 io}, id = 83
    ElasticsearchFilter(condition=[SEARCH(ITEM($0, 'val1'), Sarg[[10..20]])]): rowcount = 25.0, cumulative cost = {12.5 rows, 20.1 cpu, 0.0 io}, id = 82
      ElasticsearchTableScan(table=[[elastic, aggs]]): rowcount = 100.0, cumulative cost = {10.0 rows, 10.100000000000001 cpu, 0.0 io}, id = 10
 

 

The LogicalFilter(condition=[AND(>=($5, 10), <=($5, 20))]) is converted to LogicalFilter(subset=rel#31:RelSubset#2.NONE.[], condition=[SEARCH($0, Sarg[[10..20]])]) which is not supported in the predicate analyzer of elastic search adapter.

 

Below is the exception output

 org.apache.calcite.adapter.elasticsearch.PredicateAnalyzer$PredicateAnalyzerException: Unsupported call: [SEARCH(ITEM($0, 'val1'), Sarg[[10..20]])]org.apache.calcite.adapter.elasticsearch.PredicateAnalyzer$PredicateAnalyzerException: Unsupported call: [SEARCH(ITEM($0, 'val1'), Sarg[[10..20]])] at org.apache.calcite.adapter.elasticsearch.PredicateAnalyzer$Visitor.visitCall(PredicateAnalyzer.java:238) at org.apache.calcite.adapter.elasticsearch.PredicateAnalyzer$Visitor.visitCall(PredicateAnalyzer.java:127) at org.apache.calcite.rex.RexCall.accept(RexCall.java:189) at org.apache.calcite.adapter.elasticsearch.PredicateAnalyzer.analyze(PredicateAnalyzer.java:112) at org.apache.calcite.adapter.elasticsearch.ElasticsearchFilter$PredicateAnalyzerTranslator.translateMatch(ElasticsearchFilter.java:102) at org.apache.calcite.adapter.elasticsearch.ElasticsearchFilter.implement(ElasticsearchFilter.java:67) at org.apache.calcite.adapter.elasticsearch.ElasticsearchRel$Implementor.visitChild(ElasticsearchRel.java:129) at org.apache.calcite.adapter.elasticsearch.ElasticsearchAggregate.implement(ElasticsearchAggregate.java:128) at org.apache.calcite.adapter.elasticsearch.ElasticsearchRel$Implementor.visitChild(ElasticsearchRel.java:129) at org.apache.calcite.adapter.elasticsearch.ElasticsearchToEnumerableConverter.implement(ElasticsearchToEnumerableConverter.java:67) at org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.implementRoot(EnumerableRelImplementor.java:111) ... 77 more

 

Based on the query types in elastic search, search in range should be translated to range query of ES. 

 

Issue Links

• is duplicated by: CALCITE-4606 Test case "countStar" throws exception in AggregationTest of Elasticsearch adapter
• links to: GitHub Pull Request #2434