You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@phoenix.apache.org by ma...@apache.org on 2015/04/16 16:31:57 UTC
[16/50] [abbrv] phoenix git commit: PHOENIX-1712 Add INSTR function
PHOENIX-1712 Add INSTR function
Add method for detecting a substring within another string.
Signed-off-by: Gabriel Reid <ga...@ngdata.com>
Project: http://git-wip-us.apache.org/repos/asf/phoenix/repo
Commit: http://git-wip-us.apache.org/repos/asf/phoenix/commit/1f942b1f
Tree: http://git-wip-us.apache.org/repos/asf/phoenix/tree/1f942b1f
Diff: http://git-wip-us.apache.org/repos/asf/phoenix/diff/1f942b1f
Branch: refs/heads/calcite
Commit: 1f942b1f0e815674f1917c18167d848769435148
Parents: f766a78
Author: NAVEEN MADHIRE <vm...@indiana.edu>
Authored: Mon Mar 16 23:11:45 2015 -0400
Committer: Gabriel Reid <ga...@ngdata.com>
Committed: Thu Apr 2 21:07:55 2015 +0200
----------------------------------------------------------------------
.../apache/phoenix/end2end/InstrFunctionIT.java | 126 +++++++++++++++++++
.../phoenix/expression/ExpressionType.java | 4 +-
.../expression/function/InstrFunction.java | 105 ++++++++++++++++
.../expression/function/InstrFunctionTest.java | 108 ++++++++++++++++
4 files changed, 342 insertions(+), 1 deletion(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/phoenix/blob/1f942b1f/phoenix-core/src/it/java/org/apache/phoenix/end2end/InstrFunctionIT.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/InstrFunctionIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/InstrFunctionIT.java
new file mode 100644
index 0000000..57c0661
--- /dev/null
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/InstrFunctionIT.java
@@ -0,0 +1,126 @@
+/*
+ * 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.phoenix.end2end;
+
+import static org.junit.Assert.assertEquals;
+import static org.junit.Assert.assertFalse;
+import static org.junit.Assert.assertTrue;
+
+import java.sql.Connection;
+import java.sql.DriverManager;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+
+import org.junit.Test;
+
+public class InstrFunctionIT extends BaseHBaseManagedTimeIT {
+ private void initTable(Connection conn, String sortOrder, String s, String subStr) throws Exception {
+ String ddl = "CREATE TABLE SAMPLE (name VARCHAR NOT NULL PRIMARY KEY " + sortOrder + ", substr VARCHAR)";
+ conn.createStatement().execute(ddl);
+ String dml = "UPSERT INTO SAMPLE VALUES(?,?)";
+ PreparedStatement stmt = conn.prepareStatement(dml);
+ stmt.setString(1, s);
+ stmt.setString(2, subStr);
+ stmt.execute();
+ conn.commit();
+ }
+
+ private void testInstr(Connection conn, String queryToExecute, Integer expValue) throws Exception {
+ ResultSet rs;
+ rs = conn.createStatement().executeQuery(queryToExecute);
+ assertTrue(rs.next());
+ assertEquals(expValue.intValue(), rs.getInt(1));
+ assertFalse(rs.next());
+
+ }
+
+ private void testInstrFilter(Connection conn, String queryToExecute, String expected) throws Exception {
+ ResultSet rs;
+ PreparedStatement stmt = conn.prepareStatement(queryToExecute);
+ rs = stmt.executeQuery();
+ assertTrue(rs.next());
+ assertEquals(expected, rs.getString(1));
+
+ }
+
+ @Test
+ public void testSingleByteInstrAscending() throws Exception {
+ Connection conn = DriverManager.getConnection(getUrl());
+ initTable(conn, "ASC", "abcdefghijkl","fgh");
+ String queryToExecute = "SELECT INSTR(name, 'fgh') FROM SAMPLE";
+ testInstr(conn, queryToExecute, 5);
+ }
+
+ @Test
+ public void testSingleByteInstrDescending() throws Exception {
+ Connection conn = DriverManager.getConnection(getUrl());
+ initTable(conn, "DESC", "abcdefghijkl","fgh");
+ String queryToExecute = "SELECT INSTR(name, 'fgh') FROM SAMPLE";
+ testInstr(conn, queryToExecute, 5);
+ }
+
+ @Test
+ public void testSingleByteInstrAscendingNoString() throws Exception {
+ Connection conn = DriverManager.getConnection(getUrl());
+ initTable(conn, "ASC", "abcde fghijkl","lmn");
+ String queryToExecute = "SELECT INSTR(name, 'lmn') FROM SAMPLE";
+ testInstr(conn, queryToExecute, -1);
+ }
+
+ @Test
+ public void testSingleByteInstrDescendingNoString() throws Exception {
+ Connection conn = DriverManager.getConnection(getUrl());
+ initTable(conn, "DESC", "abcde fghijkl","lmn");
+ String queryToExecute = "SELECT INSTR(name, 'lmn') FROM SAMPLE";
+ testInstr(conn, queryToExecute, -1);
+ }
+
+ @Test
+ public void testMultiByteInstrAscending() throws Exception {
+ Connection conn = DriverManager.getConnection(getUrl());
+ initTable(conn, "ASC", "AɚɦFGH","ɚɦ");
+ String queryToExecute = "SELECT INSTR(name, 'ɚɦ') FROM SAMPLE";
+ testInstr(conn, queryToExecute, 1);
+ }
+
+ @Test
+ public void testMultiByteInstrDecending() throws Exception {
+ Connection conn = DriverManager.getConnection(getUrl());
+ initTable(conn, "DESC", "AɚɦFGH","ɚɦ");
+ String queryToExecute = "SELECT INSTR(name, 'ɚɦ') FROM SAMPLE";
+ testInstr(conn, queryToExecute, 1);
+ }
+
+ @Test
+ public void testByteInstrAscendingFilter() throws Exception {
+ Connection conn = DriverManager.getConnection(getUrl());
+ initTable(conn, "ASC", "abcdefghijkl","fgh");
+ String queryToExecute = "select NAME from sample where instr(name, 'fgh') > 0";
+ testInstrFilter(conn, queryToExecute,"abcdefghijkl");
+ }
+
+
+ @Test
+ public void testByteInstrDecendingFilter() throws Exception {
+ Connection conn = DriverManager.getConnection(getUrl());
+ initTable(conn, "DESC", "abcdefghijkl","fgh");
+ String queryToExecute = "select NAME from sample where instr(name, 'fgh') > 0";
+ testInstrFilter(conn, queryToExecute,"abcdefghijkl");
+ }
+
+}
http://git-wip-us.apache.org/repos/asf/phoenix/blob/1f942b1f/phoenix-core/src/main/java/org/apache/phoenix/expression/ExpressionType.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/main/java/org/apache/phoenix/expression/ExpressionType.java b/phoenix-core/src/main/java/org/apache/phoenix/expression/ExpressionType.java
index a7f8b4f..c25b1cc 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/expression/ExpressionType.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/expression/ExpressionType.java
@@ -42,6 +42,7 @@ import org.apache.phoenix.expression.function.FloorDecimalExpression;
import org.apache.phoenix.expression.function.FloorFunction;
import org.apache.phoenix.expression.function.HourFunction;
import org.apache.phoenix.expression.function.IndexStateNameFunction;
+import org.apache.phoenix.expression.function.InstrFunction;
import org.apache.phoenix.expression.function.InvertFunction;
import org.apache.phoenix.expression.function.LTrimFunction;
import org.apache.phoenix.expression.function.LastValueFunction;
@@ -205,7 +206,8 @@ public enum ExpressionType {
SecondFunction(SecondFunction.class),
WeekFunction(WeekFunction.class),
HourFunction(HourFunction.class),
- NowFunction(NowFunction.class)
+ NowFunction(NowFunction.class),
+ InstrFunction(InstrFunction.class)
;
ExpressionType(Class<? extends Expression> clazz) {
http://git-wip-us.apache.org/repos/asf/phoenix/blob/1f942b1f/phoenix-core/src/main/java/org/apache/phoenix/expression/function/InstrFunction.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/main/java/org/apache/phoenix/expression/function/InstrFunction.java b/phoenix-core/src/main/java/org/apache/phoenix/expression/function/InstrFunction.java
new file mode 100644
index 0000000..317d4b3
--- /dev/null
+++ b/phoenix-core/src/main/java/org/apache/phoenix/expression/function/InstrFunction.java
@@ -0,0 +1,105 @@
+/*
+ * 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.phoenix.expression.function;
+
+import java.io.DataInput;
+import java.io.IOException;
+import java.util.List;
+
+import org.apache.hadoop.hbase.io.ImmutableBytesWritable;
+import org.apache.phoenix.expression.Expression;
+import org.apache.phoenix.expression.LiteralExpression;
+import org.apache.phoenix.parse.FunctionParseNode.Argument;
+import org.apache.phoenix.parse.FunctionParseNode.BuiltInFunction;
+import org.apache.phoenix.schema.tuple.Tuple;
+import org.apache.phoenix.schema.types.PDataType;
+import org.apache.phoenix.schema.types.PInteger;
+import org.apache.phoenix.schema.types.PVarchar;
+import org.apache.phoenix.util.ByteUtil;
+
+@BuiltInFunction(name=InstrFunction.NAME, args={
+ @Argument(allowedTypes={ PVarchar.class }),
+ @Argument(allowedTypes={ PVarchar.class })})
+public class InstrFunction extends ScalarFunction{
+
+ public static final String NAME = "INSTR";
+
+ private String strToSearch = null;
+
+ public InstrFunction() { }
+
+ public InstrFunction(List<Expression> children) {
+ super(children);
+ init();
+ }
+
+ private void init() {
+ Expression strToSearchExpression = getChildren().get(1);
+ if (strToSearchExpression instanceof LiteralExpression) {
+ Object strToSearchValue = ((LiteralExpression) strToSearchExpression).getValue();
+ if (strToSearchValue != null) {
+ this.strToSearch = strToSearchValue.toString();
+ }
+ }
+ }
+
+
+ @Override
+ public boolean evaluate(Tuple tuple, ImmutableBytesWritable ptr) {
+ Expression child = getChildren().get(0);
+
+ if (!child.evaluate(tuple, ptr)) {
+ return false;
+ }
+
+ if (ptr.getLength() == 0) {
+ ptr.set(ByteUtil.EMPTY_BYTE_ARRAY);
+ return true;
+ }
+
+ int position;
+ //Logic for Empty string search
+ if (strToSearch == null){
+ position = 0;
+ ptr.set(PInteger.INSTANCE.toBytes(position));
+ return true;
+ }
+
+ String sourceStr = (String) PVarchar.INSTANCE.toObject(ptr, getChildren().get(0).getSortOrder());
+
+ position = sourceStr.indexOf(strToSearch);
+ ptr.set(PInteger.INSTANCE.toBytes(position));
+ return true;
+ }
+
+ @Override
+ public PDataType getDataType() {
+ return PInteger.INSTANCE;
+ }
+
+ @Override
+ public String getName() {
+ return NAME;
+ }
+
+ @Override
+ public void readFields(DataInput input) throws IOException {
+ super.readFields(input);
+ init();
+ }
+}
http://git-wip-us.apache.org/repos/asf/phoenix/blob/1f942b1f/phoenix-core/src/test/java/org/apache/phoenix/expression/function/InstrFunctionTest.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/test/java/org/apache/phoenix/expression/function/InstrFunctionTest.java b/phoenix-core/src/test/java/org/apache/phoenix/expression/function/InstrFunctionTest.java
new file mode 100644
index 0000000..603ad39
--- /dev/null
+++ b/phoenix-core/src/test/java/org/apache/phoenix/expression/function/InstrFunctionTest.java
@@ -0,0 +1,108 @@
+/*
+ * 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.phoenix.expression.function;
+
+import static org.junit.Assert.assertTrue;
+
+import java.sql.SQLException;
+import java.util.Arrays;
+import java.util.List;
+
+import org.apache.hadoop.hbase.io.ImmutableBytesWritable;
+import org.apache.phoenix.expression.Expression;
+import org.apache.phoenix.expression.LiteralExpression;
+import org.apache.phoenix.schema.SortOrder;
+import org.apache.phoenix.schema.types.PDataType;
+import org.apache.phoenix.schema.types.PVarchar;
+import org.junit.Test;
+
+public class InstrFunctionTest {
+
+ public static void inputExpression(String value, PDataType dataType, String strToSearch,Integer expected, SortOrder order) throws SQLException{
+ Expression inputArg = LiteralExpression.newConstant(value,dataType,order);
+
+ Expression strToSearchExp = LiteralExpression.newConstant(strToSearch,dataType);
+ List<Expression> expressions = Arrays.<Expression>asList(inputArg,strToSearchExp);
+ Expression instrFunction = new InstrFunction(expressions);
+ ImmutableBytesWritable ptr = new ImmutableBytesWritable();
+ instrFunction.evaluate(null,ptr);
+ Integer result = (Integer) instrFunction.getDataType().toObject(ptr);
+ assertTrue(result.compareTo(expected) == 0);
+
+ }
+
+
+ @Test
+ public void testInstrFunction() throws SQLException {
+ inputExpression("abcdefghijkl",PVarchar.INSTANCE, "fgh", 5, SortOrder.ASC);
+
+ inputExpression("abcdefghijkl",PVarchar.INSTANCE, "fgh", 5, SortOrder.DESC);
+
+ inputExpression("abcde fghijkl",PVarchar.INSTANCE, " fgh", 5, SortOrder.ASC);
+
+ inputExpression("abcde fghijkl",PVarchar.INSTANCE, " fgh", 5, SortOrder.DESC);
+
+ inputExpression("abcde fghijkl",PVarchar.INSTANCE, "lmn", -1, SortOrder.DESC);
+
+ inputExpression("abcde fghijkl",PVarchar.INSTANCE, "lmn", -1, SortOrder.ASC);
+
+ inputExpression("ABCDEFGHIJKL",PVarchar.INSTANCE, "FGH", 5, SortOrder.ASC);
+
+ inputExpression("ABCDEFGHIJKL",PVarchar.INSTANCE, "FGH", 5, SortOrder.DESC);
+
+ inputExpression("ABCDEFGHiJKL",PVarchar.INSTANCE, "iJKL", 8, SortOrder.ASC);
+
+ inputExpression("ABCDEFGHiJKL",PVarchar.INSTANCE, "iJKL", 8, SortOrder.DESC);
+
+ inputExpression("ABCDE FGHiJKL",PVarchar.INSTANCE, " ", 5, SortOrder.ASC);
+
+ inputExpression("ABCDE FGHiJKL",PVarchar.INSTANCE, " ", 5, SortOrder.DESC);
+
+ inputExpression("ABCDE FGHiJKL",PVarchar.INSTANCE, "", 0, SortOrder.ASC);
+
+ inputExpression("ABCDE FGHiJKL",PVarchar.INSTANCE, "", 0, SortOrder.DESC);
+
+ inputExpression("ABCDEABC",PVarchar.INSTANCE, "ABC", 0, SortOrder.ASC);
+
+ inputExpression("ABCDEABC",PVarchar.INSTANCE, "ABC", 0, SortOrder.DESC);
+
+ inputExpression("AB01CDEABC",PVarchar.INSTANCE, "01C", 2, SortOrder.ASC);
+
+ inputExpression("AB01CDEABC",PVarchar.INSTANCE, "01C", 2, SortOrder.DESC);
+
+ inputExpression("ABCD%EFGH",PVarchar.INSTANCE, "%", 4, SortOrder.ASC);
+
+ inputExpression("ABCD%EFGH",PVarchar.INSTANCE, "%", 4, SortOrder.DESC);
+
+ //Tests for MultiByte Characters
+
+ inputExpression("AɚɦFGH",PVarchar.INSTANCE, "ɚɦ", 1, SortOrder.ASC);
+
+ inputExpression("AɚɦFGH",PVarchar.INSTANCE, "ɚɦ", 1, SortOrder.DESC);
+
+ inputExpression("AɚɦFGH",PVarchar.INSTANCE, "ɦFGH", 2, SortOrder.ASC);
+
+ inputExpression("AɚɦFGH",PVarchar.INSTANCE, "ɦFGH", 2, SortOrder.DESC);
+
+ inputExpression("AɚɦF/GH",PVarchar.INSTANCE, "ɦF/GH", 2, SortOrder.ASC);
+
+ inputExpression("AɚɦF/GH",PVarchar.INSTANCE, "ɦF/GH", 2, SortOrder.DESC);
+ }
+
+
+}