Jacky Yin created CALCITE-4645:
----------------------------------
Summary: Search In Range is not supported in elastic search adapter
Key: CALCITE-4645
URL: https://issues.apache.org/jira/browse/CALCITE-4645
Project: Calcite
Issue Type: Bug
Components: elasticsearch-adapter
Affects Versions: 1.27.0
Reporter: Jacky Yin
SQL patterns like below will be converted to Search in range which is not
supported in elastic search adapter.
{code:java}
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 {code}
Here is one example of the query plan change history for SQL
{code:java}
select count(*) from view where val1 >= 10 and val1 <=20{code}
*Original:*
{noformat}
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{noformat}
*Simplified*:
{noformat}
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
{noformat}
*Optimized by the rules in es adapter:*
{noformat}
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
{noformat}
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
{noformat}
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{noformat}
Based on the query types in elastic search, search in range should be
translated to range query of ES.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
