You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@drill.apache.org by sm...@apache.org on 2015/04/15 22:34:25 UTC
[4/4] drill git commit: DRILL-2658: Add ilike and substring functions
DRILL-2658: Add ilike and substring functions
Project: http://git-wip-us.apache.org/repos/asf/drill/repo
Commit: http://git-wip-us.apache.org/repos/asf/drill/commit/033d0dfd
Tree: http://git-wip-us.apache.org/repos/asf/drill/tree/033d0dfd
Diff: http://git-wip-us.apache.org/repos/asf/drill/diff/033d0dfd
Branch: refs/heads/master
Commit: 033d0dfd6b7a5fa0eeaf4b337955b3870c2d680a
Parents: bf3db31
Author: Steven Phillips <sp...@maprtech.com>
Authored: Mon Mar 23 18:17:26 2015 -0700
Committer: Steven Phillips <sm...@apache.org>
Committed: Wed Apr 15 12:44:34 2015 -0700
----------------------------------------------------------------------
.../exec/expr/fn/impl/StringFunctions.java | 103 +++++++++++++++++++
.../exec/expr/fn/impl/TestStringFunctions.java | 59 +++++++++++
2 files changed, 162 insertions(+)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/drill/blob/033d0dfd/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 a47bf87..49f581f 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
@@ -35,6 +35,7 @@ import org.apache.drill.exec.expr.annotations.Workspace;
import org.apache.drill.exec.expr.holders.BigIntHolder;
import org.apache.drill.exec.expr.holders.BitHolder;
import org.apache.drill.exec.expr.holders.IntHolder;
+import org.apache.drill.exec.expr.holders.NullableIntHolder;
import org.apache.drill.exec.expr.holders.NullableVarCharHolder;
import org.apache.drill.exec.expr.holders.VarBinaryHolder;
import org.apache.drill.exec.expr.holders.VarCharHolder;
@@ -90,6 +91,50 @@ public class StringFunctions{
}
}
+ @FunctionTemplate(name = "ilike", scope = FunctionScope.SIMPLE, nulls = NullHandling.NULL_IF_NULL)
+ public static class ILike implements DrillSimpleFunc{
+
+ @Param VarCharHolder input;
+ @Param(constant=true) VarCharHolder pattern;
+ @Output BitHolder out;
+ @Workspace java.util.regex.Matcher matcher;
+
+ public void setup() {
+ 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)),
+ java.util.regex.Pattern.CASE_INSENSITIVE).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(name = "ilike", scope = FunctionScope.SIMPLE, nulls = NullHandling.NULL_IF_NULL)
+ public static class ILikeWithEscape 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() {
+ 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)),
+ java.util.regex.Pattern.CASE_INSENSITIVE).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;
@@ -409,6 +454,64 @@ public class StringFunctions{
}
+ @FunctionTemplate(names = {"substring", "substr" }, scope = FunctionScope.SIMPLE, nulls = NullHandling.INTERNAL)
+ public static class SubstringRegex implements DrillSimpleFunc{
+
+ @Param VarCharHolder input;
+ @Param(constant=true) VarCharHolder pattern;
+ @Output NullableVarCharHolder out;
+ @Workspace java.util.regex.Matcher matcher;
+
+ public void setup() {
+ matcher = java.util.regex.Pattern.compile(
+ org.apache.drill.exec.expr.fn.impl.StringFunctionHelpers.toStringFromUTF8(pattern.start, pattern.end, pattern.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);
+ if (matcher.find()) {
+ out.isSet = 1;
+ out.buffer = input.buffer;
+ out.start = org.apache.drill.exec.expr.fn.impl.StringFunctionUtil.getUTF8CharPosition(input.buffer, input.start, input.end, matcher.start());
+ out.end = org.apache.drill.exec.expr.fn.impl.StringFunctionUtil.getUTF8CharPosition(input.buffer, input.start, input.end, matcher.end());
+ }
+ }
+ }
+
+ @FunctionTemplate(names = {"substring", "substr" }, scope = FunctionScope.SIMPLE, nulls = NullHandling.INTERNAL)
+ public static class SubstringRegexNullable implements DrillSimpleFunc{
+
+ @Param NullableVarCharHolder input;
+ @Param(constant=true) VarCharHolder pattern;
+ @Output NullableVarCharHolder out;
+ @Workspace java.util.regex.Matcher matcher;
+
+ public void setup() {
+ matcher = java.util.regex.Pattern.compile(
+ org.apache.drill.exec.expr.fn.impl.StringFunctionHelpers.toStringFromUTF8(pattern.start, pattern.end, pattern.buffer))
+ .matcher("");
+ }
+
+ public void eval() {
+ if (input.isSet == 0) {
+ out.isSet = 0;
+ } else {
+ String i = org.apache.drill.exec.expr.fn.impl.StringFunctionHelpers.toStringFromUTF8(input.start, input.end, input.buffer);
+ matcher.reset(i);
+ if (matcher.find()) {
+ out.isSet = 1;
+ out.buffer = input.buffer;
+ out.start = org.apache.drill.exec.expr.fn.impl.StringFunctionUtil.getUTF8CharPosition(input.buffer, input.start, input.end, matcher.start());
+ out.end = org.apache.drill.exec.expr.fn.impl.StringFunctionUtil.getUTF8CharPosition(input.buffer, input.start, input.end, matcher.end());
+ } else {
+ out.isSet = 0;
+ }
+ }
+ }
+ }
+
// Return first length characters in the string. When length is negative, return all but last |length| characters.
// If length > total charcounts, return the whole string.
// If length = 0, return empty
http://git-wip-us.apache.org/repos/asf/drill/blob/033d0dfd/exec/java-exec/src/test/java/org/apache/drill/exec/expr/fn/impl/TestStringFunctions.java
----------------------------------------------------------------------
diff --git a/exec/java-exec/src/test/java/org/apache/drill/exec/expr/fn/impl/TestStringFunctions.java b/exec/java-exec/src/test/java/org/apache/drill/exec/expr/fn/impl/TestStringFunctions.java
new file mode 100644
index 0000000..e1ef7c9
--- /dev/null
+++ b/exec/java-exec/src/test/java/org/apache/drill/exec/expr/fn/impl/TestStringFunctions.java
@@ -0,0 +1,59 @@
+/**
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.apache.drill.exec.expr.fn.impl;
+
+import org.apache.drill.BaseTestQuery;
+import org.junit.Test;
+
+public class TestStringFunctions extends BaseTestQuery {
+
+ @Test
+ public void testILike() throws Exception {
+ testBuilder()
+ .sqlQuery("select n_name from cp.`tpch/nation.parquet` where ilike(n_name, '%united%') = true")
+ .unOrdered()
+ .baselineColumns("n_name")
+ .baselineValues("UNITED STATES")
+ .baselineValues("UNITED KINGDOM")
+ .build()
+ .run();
+ }
+
+ @Test
+ public void testILikeEscape() throws Exception {
+ testBuilder()
+ .sqlQuery("select a from (select concat(r_name , '_region') a from cp.`tpch/region.parquet`) where ilike(a, 'asia#_region', '#') = true")
+ .unOrdered()
+ .baselineColumns("a")
+ .baselineValues("ASIA_region")
+ .build()
+ .run();
+ }
+
+ @Test
+ public void testSubstr() throws Exception {
+ testBuilder()
+ .sqlQuery("select substr(n_name, 'UN.TE.') a from cp.`tpch/nation.parquet` where ilike(n_name, 'united%') = true")
+ .unOrdered()
+ .baselineColumns("a")
+ .baselineValues("UNITED")
+ .baselineValues("UNITED")
+ .build()
+ .run();
+ }
+}