This is an automated email from the ASF dual-hosted git repository.
mbudiu pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/main by this push:
new 07852af44c [CALCITE-7068] ElasticSearch adapter support LIKE operator
07852af44c is described below
commit 07852af44c0eb8a98ccf786f845e1ea2766fc5e4
Author: xuzifu666 <[email protected]>
AuthorDate: Mon Jun 23 16:15:27 2025 +0800
[CALCITE-7068] ElasticSearch adapter support LIKE operator
---
.../adapter/elasticsearch/PredicateAnalyzer.java | 21 +++-
.../adapter/elasticsearch/QueryBuilders.java | 85 ++++++++++++-
.../elasticsearch/ElasticSearchAdapterTest.java | 139 ++++++++++++++++++++-
3 files changed, 239 insertions(+), 6 deletions(-)
diff --git
a/elasticsearch/src/main/java/org/apache/calcite/adapter/elasticsearch/PredicateAnalyzer.java
b/elasticsearch/src/main/java/org/apache/calcite/adapter/elasticsearch/PredicateAnalyzer.java
index 897da25e26..cfc8f20d82 100644
---
a/elasticsearch/src/main/java/org/apache/calcite/adapter/elasticsearch/PredicateAnalyzer.java
+++
b/elasticsearch/src/main/java/org/apache/calcite/adapter/elasticsearch/PredicateAnalyzer.java
@@ -348,7 +348,7 @@ private QueryExpression binary(RexCall call) {
checkForIncompatibleDateTimeOperands(call);
- checkState(call.getOperands().size() == 2);
+ checkState(call.getOperands().size() == 2 || call.isA(SqlKind.LIKE));
final Expression a = call.getOperands().get(0).accept(this);
final Expression b = call.getOperands().get(1).accept(this);
@@ -373,7 +373,12 @@ private QueryExpression binary(RexCall call) {
case CONTAINS:
return QueryExpression.create(pair.getKey()).contains(pair.getValue());
case LIKE:
- throw new UnsupportedOperationException("LIKE not yet supported");
+ if (call.getOperands().size() == 3) {
+ final Expression e = call.getOperands().get(2).accept(this);
+ LiteralExpression escape = expressAsLiteral(e);
+ return QueryExpression.create(pair.getKey()).like(pair.getValue(),
escape);
+ }
+ return QueryExpression.create(pair.getKey()).like(pair.getValue());
case EQUALS:
return QueryExpression.create(pair.getKey()).equals(pair.getValue());
case NOT_EQUALS:
@@ -586,6 +591,8 @@ public boolean isPartial() {
public abstract QueryExpression like(LiteralExpression literal);
+ public abstract QueryExpression like(LiteralExpression literal,
LiteralExpression escape);
+
public abstract QueryExpression notLike(LiteralExpression literal);
public abstract QueryExpression equals(LiteralExpression literal);
@@ -698,6 +705,11 @@ private CompoundQueryExpression(boolean partial,
BoolQueryBuilder builder) {
+ "cannot be applied to a compound expression");
}
+ @Override public QueryExpression like(LiteralExpression literal,
LiteralExpression escape) {
+ throw new PredicateAnalyzerException("SqlOperatorImpl ['like'] "
+ + "cannot be applied to a compound expression");
+ }
+
@Override public QueryExpression notLike(LiteralExpression literal) {
throw new PredicateAnalyzerException("SqlOperatorImpl ['notLike'] "
+ "cannot be applied to a compound expression");
@@ -796,6 +808,11 @@ private SimpleQueryExpression(NamedFieldExpression rel) {
return this;
}
+ @Override public QueryExpression like(LiteralExpression literal,
LiteralExpression escape) {
+ builder = regexpQuery(getFieldReference(), literal.stringValue(),
escape.stringValue());
+ return this;
+ }
+
@Override public QueryExpression contains(LiteralExpression literal) {
builder = QueryBuilders.matchQuery(getFieldReference(), literal.value());
return this;
diff --git
a/elasticsearch/src/main/java/org/apache/calcite/adapter/elasticsearch/QueryBuilders.java
b/elasticsearch/src/main/java/org/apache/calcite/adapter/elasticsearch/QueryBuilders.java
index 7b5459e445..17d289499b 100644
---
a/elasticsearch/src/main/java/org/apache/calcite/adapter/elasticsearch/QueryBuilders.java
+++
b/elasticsearch/src/main/java/org/apache/calcite/adapter/elasticsearch/QueryBuilders.java
@@ -20,7 +20,9 @@
import java.io.IOException;
import java.util.ArrayList;
+import java.util.HashMap;
import java.util.List;
+import java.util.Map;
import static java.util.Objects.requireNonNull;
@@ -169,6 +171,17 @@ static RegexpQueryBuilder regexpQuery(String name, String
regexp) {
return new RegexpQueryBuilder(name, regexp);
}
+ /**
+ * A Query that matches documents containing terms with a specified regular
expression.
+ *
+ * @param name The name of the field
+ * @param regexp The regular expression
+ * @param escape The regular escape
+ */
+ static RegexpQueryBuilder regexpQuery(String name, String regexp, String
escape) {
+ return new RegexpQueryBuilder(name, regexp, escape);
+ }
+
/**
* A Query that matches documents matching boolean combinations of other
queries.
@@ -497,14 +510,80 @@ static class RegexpQueryBuilder extends QueryBuilder {
private final String fieldName;
@SuppressWarnings("unused")
private final String value;
+ @SuppressWarnings("unused")
+ private String escape;
RegexpQueryBuilder(final String fieldName, final String value) {
+ this(fieldName, value, "\\");
+ }
+
+ RegexpQueryBuilder(final String fieldName, final String value, final
String escape) {
+ requireNonNull(fieldName, "fieldName");
+ requireNonNull(value, "value");
+ requireNonNull(escape, "escape");
this.fieldName = fieldName;
- this.value = value;
+ this.escape = escape;
+ // replace % to * and _ to ? for sql with like operator
+ HashMap<String, String> kv = new HashMap<>();
+ kv.put("%", "*");
+ kv.put("_", "?");
+ this.value = replaceWildcard(value, kv, escape);
+ }
+
+ public static String replaceWildcard(String value, Map<String, String> kv,
String escape) {
+ ArrayList<String> ret = new ArrayList<>();
+ int escapeCount = 0;
+ for (int index = 0; index < value.length(); index++) {
+ String current = value.substring(index, index + 1);
+ if (index == 0) {
+ if (!current.equals(escape)) {
+ current = kv.keySet().contains(current) ? kv.get(current) :
current;
+ ret.add(current);
+ } else {
+ escapeCount++;
+ }
+ continue;
+ }
+
+ if (!kv.keySet().contains(current) && !current.equals(escape)) {
+ ret.add(current);
+ escapeCount = 0;
+ continue;
+ }
+
+ if (current.equals(escape)) {
+ escapeCount++;
+ if (escapeCount % 2 == 0) {
+ ret.add(current);
+ }
+ continue;
+ }
+
+ String last = value.substring(index - 1, index);
+ if (kv.keySet().contains(current)) {
+ if (!last.equals(escape)) {
+ ret.add(kv.get(current));
+ } else {
+ if (escapeCount % 2 == 0) {
+ ret.add(kv.get(current));
+ } else {
+ ret.add(current);
+ }
+ }
+ escapeCount = 0;
+ }
+ }
+ return String.join("", ret);
}
- @Override void writeJson(final JsonGenerator generator) {
- throw new UnsupportedOperationException();
+ @Override void writeJson(final JsonGenerator generator) throws IOException
{
+ generator.writeStartObject();
+ generator.writeFieldName("wildcard");
+ generator.writeStartObject();
+ generator.writeFieldName(fieldName);
+ writeObject(generator, value);
+ generator.writeEndObject();
+ generator.writeEndObject();
}
}
diff --git
a/elasticsearch/src/test/java/org/apache/calcite/adapter/elasticsearch/ElasticSearchAdapterTest.java
b/elasticsearch/src/test/java/org/apache/calcite/adapter/elasticsearch/ElasticSearchAdapterTest.java
index b3e769accf..eb112e5138 100644
---
a/elasticsearch/src/test/java/org/apache/calcite/adapter/elasticsearch/ElasticSearchAdapterTest.java
+++
b/elasticsearch/src/test/java/org/apache/calcite/adapter/elasticsearch/ElasticSearchAdapterTest.java
@@ -188,6 +188,8 @@ private CalciteAssert.AssertThat calciteAssert() {
assertNotNull(esSchmea);
}
+ /** Test for <a
href="https://issues.apache.org/jira/browse/CALCITE-7068";>[CALCITE-7068]
+ * ElasticSearch adapter support LIKE operator</a>. */
@Test void basic() {
calciteAssert()
// by default elastic returns max 10 records
@@ -214,11 +216,146 @@ private CalciteAssert.AssertThat calciteAssert() {
.query("select * from elastic.zips where _MAP['CITY'] = 'BROOKLYN'")
.returnsCount(0);
-
// limit 0
calciteAssert()
.query("select * from elastic.zips limit 0")
.returnsCount(0);
+
+ // test with ESCAPE
+ calciteAssert()
+ // this case would covnert to like 'BRO%IK*', match no one, count is 0
+ .query(
+ "select * from elastic.zips where _MAP['city'] like 'BRO\\%OK%'
ESCAPE '\\' limit 10")
+ .returnsOrdered("")
+ .returnsCount(0);
+
+ calciteAssert()
+ // this case would covnert to like 'BRO*IK*', match one result, count
is 1
+ .query(
+ "select * from elastic.zips where _MAP['city'] like 'B\\R\\O%OK%'
ESCAPE '\\' limit 10")
+ .returnsOrdered(
+
+ "_MAP={id=11226, city=BROOKLYN, loc=[-73.956985, 40.646694],
pop=111396, state=NY}")
+ .returnsCount(1);
+
+
+ calciteAssert()
+ // this case would covnert to like 'BROIK*', match one result, count
is 1
+ .query(
+ "select * from elastic.zips where _MAP['city'] like 'BRO!OK%'
ESCAPE '!' limit 10")
+ .returnsOrdered(
+
+ "_MAP={id=11226, city=BROOKLYN, loc=[-73.956985, 40.646694],
pop=111396, state=NY}")
+ .returnsCount(1);
+
+ calciteAssert()
+ // this case would covnert to like 'BR!OIK*', match no one, count is 0
+ .query(
+ "select * from elastic.zips where _MAP['city'] like 'BRO!!OK%'
ESCAPE '!' limit 10")
+ .returnsOrdered("")
+ .returnsCount(0);
+
+ // test with %
+ calciteAssert()
+ .query("select * from elastic.zips where _MAP['city'] like 'BROOK%'
limit 10")
+ .returnsOrdered(
+ "_MAP={id=11226, city=BROOKLYN, loc=[-73.956985, 40.646694],
pop=111396, state=NY}")
+ .returnsCount(1);
+
+ calciteAssert()
+ .query("select * from elastic.zips where _MAP['city'] like '%ROOK%'
limit 10")
+ .returnsOrdered(
+ "_MAP={id=11226, city=BROOKLYN, loc=[-73.956985, 40.646694],
pop=111396, state=NY}")
+ .returnsCount(1);
+
+ // test with _
+ calciteAssert()
+ .query("select * from elastic.zips where _MAP['city'] like 'BROOKLY_'
limit 10")
+ .returnsOrdered(
+ "_MAP={id=11226, city=BROOKLYN, loc=[-73.956985, 40.646694],
pop=111396, state=NY}")
+ .returnsCount(1);
+
+ calciteAssert()
+ .query("select * from elastic.zips where _MAP['city'] like 'BROOKL_'
limit 10")
+ .returnsOrdered("")
+ .returnsCount(0);
+
+ calciteAssert()
+ .query("select * from elastic.zips where _MAP['city'] like 'BROOKL__'
limit 10")
+ .returnsOrdered(
+ "_MAP={id=11226, city=BROOKLYN, loc=[-73.956985, 40.646694],
pop=111396, state=NY}")
+ .returnsCount(1);
+
+ calciteAssert()
+ .query("select * from elastic.zips where _MAP['city'] like '_ROOKLY_'
limit 10")
+ .returnsOrdered(
+ "_MAP={id=11226, city=BROOKLYN, loc=[-73.956985, 40.646694],
pop=111396, state=NY}")
+ .returnsCount(1);
+
+ calciteAssert()
+ .query("select * from elastic.zips where _MAP['city'] like '_ROO*Y_'
limit 10")
+ .returnsOrdered(
+ "_MAP={id=11226, city=BROOKLYN, loc=[-73.956985, 40.646694],
pop=111396, state=NY}")
+ .returnsCount(1);
+ }
+
+
+ /**
+ * A test for ReplaceWildcard with escape.
+ */
+ @Test void testReplaceWildcard() {
+ HashMap<String, String> kv = new HashMap<>();
+ kv.put("%", "*");
+ kv.put("_", "?");
+
+ String value = "aa\\%b%";
+ String source = "%";
+ String target = "*";
+ String escape = "\\";
+ assertEquals(QueryBuilders.RegexpQueryBuilder.replaceWildcard(value, kv,
escape),
+ "aa%b*");
+
+ value = "aa\\\\%b%";
+ source = "%";
+ target = "*";
+ escape = "\\";
+ assertEquals(QueryBuilders.RegexpQueryBuilder.replaceWildcard(value, kv,
escape),
+ "aa\\*b*");
+
+ value = "aa\\\\\\%b%";
+ source = "%";
+ target = "*";
+ escape = "\\";
+ assertEquals(QueryBuilders.RegexpQueryBuilder.replaceWildcard(value, kv,
escape),
+ "aa\\%b*");
+
+ value = "aa!%b%";
+ source = "%";
+ target = "*";
+ escape = "!";
+ assertEquals(QueryBuilders.RegexpQueryBuilder.replaceWildcard(value, kv,
escape),
+ "aa%b*");
+
+ value = "aa!!%b%";
+ source = "%";
+ target = "*";
+ escape = "!";
+ assertEquals(QueryBuilders.RegexpQueryBuilder.replaceWildcard(value, kv,
escape),
+ "aa!*b*");
+
+ value = "aa!!!!%b%";
+ source = "%";
+ target = "*";
+ escape = "!";
+ assertEquals(QueryBuilders.RegexpQueryBuilder.replaceWildcard(value, kv,
escape),
+ "aa!!*b*");
+
+ value = "aa!!!%b%";
+ source = "%";
+ target = "*";
+ escape = "!";
+ assertEquals(QueryBuilders.RegexpQueryBuilder.replaceWildcard(value, kv,
escape),
+ "aa!%b*");
}
@Test void testAlias() {
