This is an automated email from the ASF dual-hosted git repository.
xuzifu666 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 0211999427 [CALCITE-7673] Add support for the LIKE operator to the
MongoDB adapter
0211999427 is described below
commit 0211999427e294114db8f6b58cad95870cc13ccb
Author: Yu Xu <[email protected]>
AuthorDate: Mon Jul 27 17:31:12 2026 +0800
[CALCITE-7673] Add support for the LIKE operator to the MongoDB adapter
---
.../calcite/adapter/mongodb/MongoFilter.java | 182 +++++++++++++++++++++
.../calcite/adapter/mongodb/MongoAdapterTest.java | 164 +++++++++++++++++++
2 files changed, 346 insertions(+)
diff --git
a/mongodb/src/main/java/org/apache/calcite/adapter/mongodb/MongoFilter.java
b/mongodb/src/main/java/org/apache/calcite/adapter/mongodb/MongoFilter.java
index 00a26c5fe3..c3d12a84be 100644
--- a/mongodb/src/main/java/org/apache/calcite/adapter/mongodb/MongoFilter.java
+++ b/mongodb/src/main/java/org/apache/calcite/adapter/mongodb/MongoFilter.java
@@ -31,6 +31,7 @@
import org.apache.calcite.rex.RexLiteral;
import org.apache.calcite.rex.RexNode;
import org.apache.calcite.rex.RexUtil;
+import org.apache.calcite.sql.SqlKind;
import org.apache.calcite.sql.type.SqlTypeName;
import org.apache.calcite.util.JsonBuilder;
import org.apache.calcite.util.Pair;
@@ -222,6 +223,10 @@ private Void translateMatch2(RexNode node,
List<Map<String, Object>> orMapList,
return translateUnary("$ne", (RexCall) node, multimap, eqMap);
case IS_NULL:
return translateUnary("$eq", (RexCall) node, multimap, eqMap);
+ case LIKE:
+ return translateLike((RexCall) node, multimap);
+ case NOT:
+ return translateNot((RexCall) node, orMapList);
default:
throw new AssertionError("cannot translate " + node);
}
@@ -302,5 +307,182 @@ private Void translateUnary(String op, RexCall call,
translateBinary2(op, left, right, multimap, eqMap);
return null;
}
+
+ /** Translates LIKE to {$regex: ...}. */
+ private Void translateLike(RexCall call,
+ Multimap<String, Pair<String, RexLiteral>> multimap) {
+ final RexNode left = stripCast(call.operands.get(0));
+ final RexNode right = call.operands.get(1);
+
+ // LIKE must have a literal on the right side
+ if (right.getKind() != SqlKind.LITERAL) {
+ throw new AssertionError("cannot translate LIKE with non-literal
pattern: " + call);
+ }
+ final RexLiteral patternLiteral = (RexLiteral) right;
+ final String sqlPattern = patternLiteral.getValue2().toString();
+
+ final @Nullable Character escapeChar = escapeChar(call);
+ final String finalRegex = sqlLikeToMongoRegex(sqlPattern, escapeChar);
+
+ switch (left.getKind()) {
+ case INPUT_REF:
+ final RexInputRef left1 = (RexInputRef) left;
+ String name = fieldNames.get(left1.getIndex());
+ multimap.put(name, Pair.of("$regex",
rexBuilder.makeLiteral(finalRegex)));
+ return null;
+ case ITEM:
+ String itemName = MongoRules.isItem((RexCall) left);
+ if (itemName != null) {
+ multimap.put(itemName, Pair.of("$regex",
rexBuilder.makeLiteral(finalRegex)));
+ return null;
+ }
+ // fall through
+ default:
+ throw new AssertionError("cannot translate LIKE " + call);
+ }
+ }
+
+ /** Translates NOT to a MongoDB $nor expression. */
+ private Void translateNot(RexCall call, List<Map<String, Object>>
orMapList) {
+ final RexNode operand = call.operands.get(0);
+ switch (operand.getKind()) {
+ case LIKE:
+ return translateNotLike((RexCall) operand, orMapList);
+ default:
+ throw new AssertionError("cannot translate NOT " + call);
+ }
+ }
+
+ /** Translates NOT LIKE to {$nor: [{field: {$regex: ...}}]}. */
+ private Void translateNotLike(RexCall call, List<Map<String, Object>>
orMapList) {
+ final RexNode left = stripCast(call.operands.get(0));
+ final RexNode right = call.operands.get(1);
+
+ if (right.getKind() != SqlKind.LITERAL) {
+ throw new AssertionError("cannot translate NOT LIKE with non-literal
pattern: " + call);
+ }
+ final RexLiteral patternLiteral = (RexLiteral) right;
+ final String sqlPattern = patternLiteral.getValue2().toString();
+
+ final @Nullable Character escapeChar = escapeChar(call);
+ final String finalRegex = sqlLikeToMongoRegex(sqlPattern, escapeChar);
+
+ final String name;
+ switch (left.getKind()) {
+ case INPUT_REF:
+ final RexInputRef left1 = (RexInputRef) left;
+ name = fieldNames.get(left1.getIndex());
+ break;
+ case ITEM:
+ String itemName = MongoRules.isItem((RexCall) left);
+ if (itemName != null) {
+ name = itemName;
+ break;
+ }
+ // fall through
+ default:
+ throw new AssertionError("cannot translate NOT LIKE " + call);
+ }
+
+ Map<String, Object> regexMap = builder.map();
+ Map<String, Object> regexOp = builder.map();
+ regexOp.put("$regex", finalRegex);
+ regexMap.put(name, regexOp);
+ List<Object> norList = builder.list();
+ norList.add(regexMap);
+ Map<String, Object> norMap = builder.map();
+ norMap.put("$nor", norList);
+ orMapList.add(norMap);
+ return null;
+ }
+
+ /** Strips a leading CAST, if any. MongoDB is implicitly typed. */
+ private static RexNode stripCast(RexNode node) {
+ if (node.getKind() == SqlKind.CAST) {
+ return ((RexCall) node).operands.get(0);
+ }
+ return node;
+ }
+
+ /** Returns the escape character declared in a LIKE expression, or null. */
+ private static @Nullable Character escapeChar(RexCall call) {
+ if (call.operands.size() != 3) {
+ return null;
+ }
+ final RexNode escapeNode = call.operands.get(2);
+ if (escapeNode.getKind() != SqlKind.LITERAL) {
+ throw new AssertionError("cannot translate LIKE with non-literal
escape: " + call);
+ }
+ final String escape = ((RexLiteral) escapeNode).getValue2().toString();
+ if (escape.length() != 1) {
+ throw new AssertionError("cannot translate LIKE with multi-character
escape: " + call);
+ }
+ return escape.charAt(0);
+ }
+
+ /**
+ * Converts SQL LIKE pattern to MongoDB regex pattern.
+ *
+ * <p>SQL: {@code %} matches zero or more characters, {@code _} matches a
single
+ * character. MongoDB: {@code .*} matches zero or more characters, {@code
.}
+ * matches a single character.
+ *
+ * <p>We add {@code ^} and {@code $} anchors so that the entire string
matches
+ * the pattern, just as SQL LIKE does.
+ */
+ private static String sqlLikeToMongoRegex(String sqlPattern, @Nullable
Character escapeChar) {
+ final StringBuilder regex = new StringBuilder(sqlPattern.length() * 2);
+ regex.append("^");
+ for (int i = 0; i < sqlPattern.length(); i++) {
+ char c = sqlPattern.charAt(i);
+ if (escapeChar != null && c == escapeChar) {
+ if (i == sqlPattern.length() - 1) {
+ throw new AssertionError("Invalid escape sequence at end of LIKE
pattern: "
+ + sqlPattern);
+ }
+ final char nextChar = sqlPattern.charAt(i + 1);
+ if (nextChar == '%' || nextChar == '_' || nextChar == escapeChar) {
+ regex.append(escapeRegexChar(nextChar));
+ i++;
+ } else {
+ throw new AssertionError("Invalid escape sequence in LIKE pattern:
" + sqlPattern);
+ }
+ } else if (c == '%') {
+ regex.append(".*");
+ } else if (c == '_') {
+ regex.append('.');
+ } else {
+ regex.append(escapeRegexChar(c));
+ }
+ }
+ regex.append("$");
+ return regex.toString();
+ }
+
+ /**
+ * Escapes a character for use in a MongoDB regex if it's a special regex
character.
+ */
+ private static String escapeRegexChar(char c) {
+ // MongoDB regex special characters that need escaping
+ switch (c) {
+ case '\\':
+ case '^':
+ case '$':
+ case '.':
+ case '|':
+ case '?':
+ case '*':
+ case '+':
+ case '(':
+ case ')':
+ case '[':
+ case ']':
+ case '{':
+ case '}':
+ return "\\" + c;
+ default:
+ return String.valueOf(c);
+ }
+ }
}
}
diff --git
a/mongodb/src/test/java/org/apache/calcite/adapter/mongodb/MongoAdapterTest.java
b/mongodb/src/test/java/org/apache/calcite/adapter/mongodb/MongoAdapterTest.java
index c4fef104f8..aa5dc299ca 100644
---
a/mongodb/src/test/java/org/apache/calcite/adapter/mongodb/MongoAdapterTest.java
+++
b/mongodb/src/test/java/org/apache/calcite/adapter/mongodb/MongoAdapterTest.java
@@ -1211,4 +1211,168 @@ private static Consumer<List> mongoChecker(final
String... expected) {
"CITY_SUBSTRING=RA",
"CITY_SUBSTRING=UT");
}
+
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7673";>[CALCITE-7673]
+ * MongoDB Adapter can not support LIKE operator</a>. */
+ @Test void testLikePrefix() {
+ // Test LIKE on state field - matches states starting with 'A'
+ assertModel(MODEL)
+ .query("select state, city from zips where state like 'A%' order by
state")
+ .returnsUnordered(
+ "STATE=AK; CITY=ANCHORAGE",
+ "STATE=AK; CITY=FAIRBANKS",
+ "STATE=AK; CITY=JUNEAU",
+ "STATE=AL; CITY=CENTER POINT",
+ "STATE=AL; CITY=TUSCALOOSA",
+ "STATE=AL; CITY=SOUTHSIDE",
+ "STATE=AR; CITY=CONWAY",
+ "STATE=AR; CITY=GRAVEL RIDGE",
+ "STATE=AR; CITY=JONESBORO",
+ "STATE=AZ; CITY=MESA",
+ "STATE=AZ; CITY=PHOENIX",
+ "STATE=AZ; CITY=YUMA");
+ }
+
+ /** Test case for LIKE operator with suffix pattern (ends with). */
+ @Test void testLikeSuffix() {
+ assertModel(MODEL)
+ .query("select state, city from zips where city like '%TON' order by
state, city")
+ .limit(5)
+ .returnsOrdered(
+ "STATE=DC; CITY=WASHINGTON",
+ "STATE=KY; CITY=HATTON",
+ "STATE=MA; CITY=BROCKTON",
+ "STATE=ME; CITY=LEWISTON",
+ "STATE=MN; CITY=NEW BRIGHTON");
+ }
+
+ /** Test case for LIKE operator with contains pattern. */
+ @Test void testLikeContains() {
+ assertModel(MODEL)
+ .query("select state, city from zips where city like '%ING%' order by
state")
+ .limit(5)
+ .returnsOrdered(
+ "STATE=DC; CITY=WASHINGTON",
+ "STATE=MA; CITY=FRAMINGHAM",
+ "STATE=MO; CITY=JENNINGS",
+ "STATE=MT; CITY=BILLINGS",
+ "STATE=NC; CITY=LEXINGTON");
+ }
+
+ /** Test case for LIKE operator with single character wildcard. */
+ @Test void testLikeSingleChar() {
+ // Pattern 'NEW ______' matches cities starting with 'NEW ' followed by
exactly 6 characters
+ // NEW IBERIA: "NEW " + "IBERIA" (6 chars) = matches
+ // NEW ORLEANS: "NEW " + "ORLEANS" (7 chars) = does not match
+ // NEW YORK: "NEW " + "YORK" (4 chars) = does not match
+ assertModel(MODEL)
+ .query("select city, state from zips where city like 'NEW ______'
order by city")
+ .returnsOrdered(
+ "CITY=NEW IBERIA; STATE=LA");
+ }
+
+ /** Test case for LIKE operator combined with other filters. */
+ @Test void testLikeCombinedWithOtherFilters() {
+ assertModel(MODEL)
+ .query("select city, state from zips where city like 'L%' and state =
'CA' order by city")
+ .returnsOrdered(
+ "CITY=LOS ANGELES; STATE=CA");
+ }
+
+ /** Test case for LIKE operator verifying the generated MongoDB regex. */
+ @Test void testLikeGeneratedRegex() {
+ assertModel(MODEL)
+ .query("select state from zips where city like 'A%'")
+ .queryContains(
+ mongoChecker(
+ "{$match: {city: {$regex: '^A.*$'}}}",
+ "{$project: {STATE: '$state'}}"))
+ .returnsUnordered(
+ "STATE=AK",
+ "STATE=IA",
+ "STATE=SC",
+ "STATE=SD",
+ "STATE=TX");
+ }
+
+ /** Test case for LIKE operator with escape character on underscore. */
+ @Test void testLikeEscapeUnderscore() {
+ // Without escape, '_' matches a single character.
+ assertModel(MODEL)
+ .query("select city from zips where city like 'BROOKLY_'")
+ .returnsUnordered("CITY=BROOKLYN");
+ // With escape, '_' is a literal character and does not match BROOKLYN.
+ assertModel(MODEL)
+ .query("select city from zips where city like 'BROOKLY\\_' ESCAPE
'\\'")
+ .returnsUnordered();
+ }
+
+ /** Test case for LIKE operator with escape character on percent. */
+ @Test void testLikeEscapePercent() {
+ // Without escape, '%' matches zero or more characters.
+ assertModel(MODEL)
+ .query("select city from zips where city like 'BROOKLYN%'")
+ .returnsUnordered("CITY=BROOKLYN");
+ // With escape, '%' is a literal character and does not match BROOKLYN.
+ assertModel(MODEL)
+ .query("select city from zips where city like 'BROOKLYN\\%' ESCAPE
'\\'")
+ .returnsUnordered();
+ }
+
+ /** Test case for LIKE operator without a default escape character. */
+ @Test void testLikeNoDefaultEscape() {
+ // Without ESCAPE, '\' is an ordinary character; 'A\%' matches cities
+ // starting with 'A%' and should return nothing.
+ assertModel(MODEL)
+ .query("select city from zips where city like 'A\\%'")
+ .returnsUnordered();
+ }
+
+ /** Test case for LIKE operator escaping regex special characters. */
+ @Test void testLikeRegexSpecialChar() {
+ // '.' is an ordinary SQL LIKE character and must be escaped in the
+ // generated MongoDB regex; otherwise it would match arbitrary characters.
+ assertModel(MODEL)
+ .query("select city from zips where city like 'A.B%'")
+ .returnsUnordered();
+ }
+
+ /** Test case for LIKE operator with a custom escape character. */
+ @Test void testLikeCustomEscapeChar() {
+ // Use '!' as the escape character. Here '%' is a wildcard.
+ assertModel(MODEL)
+ .query("select city from zips where city like 'BROOKLYN%' ESCAPE '!'")
+ .returnsUnordered("CITY=BROOKLYN");
+ // '!%' makes '%' a literal character, so it does not match BROOKLYN.
+ assertModel(MODEL)
+ .query("select city from zips where city like 'BROOKLYN!%' ESCAPE '!'")
+ .returnsUnordered();
+ }
+
+ /** Test case for LIKE operator verifying the generated regex with escapes.
*/
+ @Test void testLikeGeneratedRegexWithEscape() {
+ assertModel(MODEL)
+ .query("select state from zips where city like 'A\\_B\\%C%' ESCAPE
'\\'")
+ .queryContains(
+ mongoChecker(
+ "{$match: {city: {$regex: '^A_B%C.*$'}}}",
+ "{$project: {STATE: '$state'}}"))
+ .returnsUnordered();
+ }
+
+ /** Test case for NOT LIKE operator. */
+ @Test void testNotLike() {
+ assertModel(MODEL)
+ .query("select city from zips where city not like 'A%'")
+ .returnsCount(144);
+ }
+
+ /** Test case for LIKE operator on ITEM (_MAP) access. */
+ @Test void testLikeItem() {
+ assertModel(MODEL)
+ .query("select cast(_MAP['city'] as varchar) from
\"mongo_raw\".\"zips\" "
+ + "where _MAP['city'] like 'A%'")
+ .returnsCount(5);
+ }
}
