DRILL-1544 : Support escape character in "like" or "similar to" SQL operator.
Conflicts:
exec/java-exec/src/test/java/org/apache/drill/TestExampleQueries.java
Project: http://git-wip-us.apache.org/repos/asf/incubator-drill/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-drill/commit/f7570d9a
Tree: http://git-wip-us.apache.org/repos/asf/incubator-drill/tree/f7570d9a
Diff: http://git-wip-us.apache.org/repos/asf/incubator-drill/diff/f7570d9a
Branch: refs/heads/master
Commit: f7570d9a30cefbec9b8ef42db9f395ad6527bd00
Parents: 797b1bc
Author: Jinfeng Ni <[email protected]>
Authored: Thu Oct 16 13:58:51 2014 -0700
Committer: Jinfeng Ni <[email protected]>
Committed: Fri Nov 7 10:52:57 2014 -0800
----------------------------------------------------------------------
.../exec/expr/fn/impl/StringFunctions.java | 44 ++++++++++++++++++++
.../org/apache/drill/TestExampleQueries.java | 18 ++++++++
.../test/resources/jsoninput/specialchar.json | 3 ++
3 files changed, 65 insertions(+)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-drill/blob/f7570d9a/exec/java-exec/src/main/java/org/apache/drill/exec/expr/fn/impl/StringFunctions.java
----------------------------------------------------------------------
diff --git
a/exec/java-exec/src/main/java/org/apache/drill/exec/expr/fn/impl/StringFunctions.java
b/exec/java-exec/src/main/java/org/apache/drill/exec/expr/fn/impl/StringFunctions.java
index 9883f25..d6afa6a 100644
---
a/exec/java-exec/src/main/java/org/apache/drill/exec/expr/fn/impl/StringFunctions.java
+++
b/exec/java-exec/src/main/java/org/apache/drill/exec/expr/fn/impl/StringFunctions.java
@@ -69,6 +69,28 @@ public class StringFunctions{
}
}
+ @FunctionTemplate(name = "like", scope = FunctionScope.SIMPLE, nulls =
NullHandling.NULL_IF_NULL)
+ public static class LikeWithEscape implements DrillSimpleFunc{
+
+ @Param VarCharHolder input;
+ @Param(constant=true) VarCharHolder pattern;
+ @Param(constant=true) VarCharHolder escape;
+ @Output BitHolder out;
+ @Workspace java.util.regex.Matcher matcher;
+
+ public void setup(RecordBatch incoming) {
+ matcher =
java.util.regex.Pattern.compile(org.apache.drill.exec.expr.fn.impl.RegexpUtil.sqlToRegexLike(
//
+
org.apache.drill.exec.expr.fn.impl.StringFunctionHelpers.toStringFromUTF8(pattern.start,
pattern.end, pattern.buffer),
+
org.apache.drill.exec.expr.fn.impl.StringFunctionHelpers.toStringFromUTF8(escape.start,
escape.end, escape.buffer))).matcher("");
+ }
+
+ public void eval() {
+ String i =
org.apache.drill.exec.expr.fn.impl.StringFunctionHelpers.toStringFromUTF8(input.start,
input.end, input.buffer);
+ matcher.reset(i);
+ out.value = matcher.matches()? 1:0;
+ }
+ }
+
@FunctionTemplate(names = {"similar", "similar to"}, scope =
FunctionScope.SIMPLE, nulls = NullHandling.NULL_IF_NULL)
public static class Similar implements DrillSimpleFunc{
@Param VarCharHolder input;
@@ -88,6 +110,28 @@ public class StringFunctions{
}
}
+ @FunctionTemplate(names = {"similar", "similar to"}, scope =
FunctionScope.SIMPLE, nulls = NullHandling.NULL_IF_NULL)
+ public static class SimilarWithEscape implements DrillSimpleFunc{
+ @Param VarCharHolder input;
+ @Param(constant=true) VarCharHolder pattern;
+ @Param(constant=true) VarCharHolder escape;
+ @Output BitHolder out;
+ @Workspace java.util.regex.Matcher matcher;
+
+ public void setup(RecordBatch incoming) {
+
+ matcher =
java.util.regex.Pattern.compile(org.apache.drill.exec.expr.fn.impl.RegexpUtil.sqlToRegexSimilar(
+
org.apache.drill.exec.expr.fn.impl.StringFunctionHelpers.toStringFromUTF8(pattern.start,
pattern.end, pattern.buffer),
+
org.apache.drill.exec.expr.fn.impl.StringFunctionHelpers.toStringFromUTF8(escape.start,
escape.end, escape.buffer))).matcher("");
+ }
+
+ public void eval() {
+ String i =
org.apache.drill.exec.expr.fn.impl.StringFunctionHelpers.toStringFromUTF8(input.start,
input.end, input.buffer);
+ matcher.reset(i);
+ out.value = matcher.matches()? 1:0;
+ }
+ }
+
/*
* Replace all substring that match the regular expression with replacement.
*/
http://git-wip-us.apache.org/repos/asf/incubator-drill/blob/f7570d9a/exec/java-exec/src/test/java/org/apache/drill/TestExampleQueries.java
----------------------------------------------------------------------
diff --git
a/exec/java-exec/src/test/java/org/apache/drill/TestExampleQueries.java
b/exec/java-exec/src/test/java/org/apache/drill/TestExampleQueries.java
index 5a4cdd0..9eaa20e 100644
--- a/exec/java-exec/src/test/java/org/apache/drill/TestExampleQueries.java
+++ b/exec/java-exec/src/test/java/org/apache/drill/TestExampleQueries.java
@@ -512,4 +512,22 @@ public class TestExampleQueries extends BaseTestQuery{
public void testTextQueries() throws Exception {
test("select cast('285572516' as int) from cp.`tpch/nation.parquet` limit
1");
}
+
+ @Test // DRILL-1544
+ public void testLikeEscape() throws Exception {
+ int actualRecordCount = testSql("select id, name from
cp.`jsoninput/specialchar.json` where name like '%#_%' ESCAPE '#'");
+ int expectedRecordCount = 1;
+ assertEquals(String.format("Received unexepcted number of rows in output:
expected=%d, received=%s",
+ expectedRecordCount, actualRecordCount), expectedRecordCount,
actualRecordCount);
+
+ }
+
+ @Test
+ public void testSimilarEscape() throws Exception {
+ int actualRecordCount = testSql("select id, name from
cp.`jsoninput/specialchar.json` where name similar to '(N|S)%#_%' ESCAPE '#'");
+ int expectedRecordCount = 1;
+ assertEquals(String.format("Received unexepcted number of rows in output:
expected=%d, received=%s",
+ expectedRecordCount, actualRecordCount), expectedRecordCount,
actualRecordCount);
+ }
+
}
http://git-wip-us.apache.org/repos/asf/incubator-drill/blob/f7570d9a/exec/java-exec/src/test/resources/jsoninput/specialchar.json
----------------------------------------------------------------------
diff --git a/exec/java-exec/src/test/resources/jsoninput/specialchar.json
b/exec/java-exec/src/test/resources/jsoninput/specialchar.json
new file mode 100644
index 0000000..0e32237
--- /dev/null
+++ b/exec/java-exec/src/test/resources/jsoninput/specialchar.json
@@ -0,0 +1,3 @@
+{"id": 0,"name": "AFRICA"}
+{"id": 1, "name": "AMERICA"}
+{"id": 5, "name": "NORTH_SOUTH_AMERICA"}
