PHOENIX-1712 Add INSTR function Add method for detecting a substring within another string.
Signed-off-by: Gabriel Reid <[email protected]> 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 <[email protected]> Authored: Mon Mar 16 23:11:45 2015 -0400 Committer: Gabriel Reid <[email protected]> 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); + } + + +}
