phoenix-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From jamestay...@apache.org
Subject [4/4] phoenix git commit: PHOENIX-4643 Implement ARRAY_REMOVE built in function (Xavier Jodoin)
Date Fri, 23 Mar 2018 19:32:39 GMT
PHOENIX-4643 Implement ARRAY_REMOVE built in function (Xavier Jodoin)


Project: http://git-wip-us.apache.org/repos/asf/phoenix/repo
Commit: http://git-wip-us.apache.org/repos/asf/phoenix/commit/cdb77d46
Tree: http://git-wip-us.apache.org/repos/asf/phoenix/tree/cdb77d46
Diff: http://git-wip-us.apache.org/repos/asf/phoenix/diff/cdb77d46

Branch: refs/heads/4.x-cdh5.11.2
Commit: cdb77d468bc5bc8bbba8eef11935ff79620a3106
Parents: 6c1d6c2
Author: James Taylor <jtaylor@salesforce.com>
Authored: Fri Mar 23 12:00:45 2018 -0700
Committer: James Taylor <jtaylor@salesforce.com>
Committed: Fri Mar 23 12:31:22 2018 -0700

----------------------------------------------------------------------
 .../phoenix/end2end/ArrayRemoveFunctionIT.java  | 383 +++++++++++++++++++
 .../phoenix/expression/ExpressionType.java      |  10 +-
 .../function/ArrayRemoveFunction.java           |  88 +++++
 .../expression/ArrayRemoveFunctionTest.java     | 285 ++++++++++++++
 4 files changed, 762 insertions(+), 4 deletions(-)
----------------------------------------------------------------------


http://git-wip-us.apache.org/repos/asf/phoenix/blob/cdb77d46/phoenix-core/src/it/java/org/apache/phoenix/end2end/ArrayRemoveFunctionIT.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/ArrayRemoveFunctionIT.java
b/phoenix-core/src/it/java/org/apache/phoenix/end2end/ArrayRemoveFunctionIT.java
new file mode 100644
index 0000000..b5a468c
--- /dev/null
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/ArrayRemoveFunctionIT.java
@@ -0,0 +1,383 @@
+/*
+ * 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.assertNull;
+import static org.junit.Assert.assertTrue;
+
+import java.sql.Connection;
+import java.sql.DriverManager;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+
+import org.apache.phoenix.schema.TypeMismatchException;
+import org.junit.Before;
+import org.junit.Test;
+
+public class ArrayRemoveFunctionIT extends ParallelStatsDisabledIT {
+
+	private Connection conn;
+	private String tableName;
+
+	@Before
+	public void setup() throws Exception {
+		conn = DriverManager.getConnection(getUrl());
+		tableName = initTables(conn);
+	}
+
+	private String initTables(Connection conn) throws Exception {
+		String tableName = generateUniqueName();
+		String ddl = "CREATE TABLE " + tableName
+				+ " (region_name VARCHAR PRIMARY KEY,varchars VARCHAR[],integers INTEGER[],doubles DOUBLE[],bigints
BIGINT[],"
+				+ "chars CHAR(15)[],double1 DOUBLE,char1 CHAR(17),nullcheck INTEGER,chars2 CHAR(15)[],
nullVarchar VARCHAR[], nullBigInt BIGINT[],double2 DOUBLE,integer1 INTEGER,oneItem VARCHAR[],char2
char(15),varchar1 VARCHAR)";
+		conn.createStatement().execute(ddl);
+		String dml = "UPSERT INTO " + tableName
+				+ "(region_name,varchars,integers,doubles,bigints,chars,double1,char1,nullcheck,chars2,double2,integer1,oneItem,char2,varchar1)
VALUES('SF Bay Area',"
+				+ "ARRAY['2345','46345','23234']," + "ARRAY[2345,46345,23234,456],"
+				+ "ARRAY[10.0,23.45,46.345,23.234,45.6,5.78]," + "ARRAY[12,34,56,78,910],"
+				+ "ARRAY['a','bbbb','c','ddd','e','c']," + "23.45," + "'wert'," + "NULL,"
+				+ "ARRAY['a','bbbb','c','ddd','e','foo']," + "12," + "12," + "ARRAY['alone'],'2345','a')";
+		PreparedStatement stmt = conn.prepareStatement(dml);
+		stmt.execute();
+		conn.commit();
+		return tableName;
+	}
+
+	@Test
+	public void testEmptyArrayModification() throws Exception {
+		ResultSet rs = conn.createStatement()
+				.executeQuery("SELECT ARRAY_REMOVE(nullVarChar,'34567') FROM " + tableName + " LIMIT
1");
+		assertTrue(rs.next());
+
+		assertNull(rs.getArray(1));
+		assertFalse(rs.next());
+	}
+
+	@Test
+	public void testArrayRemoveFunctionVarchar() throws Exception {
+		ResultSet rs = conn.createStatement().executeQuery(
+				"SELECT ARRAY_REMOVE(varchars,'23234') FROM " + tableName + " WHERE region_name = 'SF
Bay Area'");
+		assertTrue(rs.next());
+
+		assertEquals(conn.createArrayOf("VARCHAR", new String[] { "2345", "46345" }), rs.getArray(1));
+		assertFalse(rs.next());
+	}
+
+	@Test
+	public void testArrayRemoveFunctionInteger() throws Exception {
+		ResultSet rs = conn.createStatement().executeQuery(
+				"SELECT ARRAY_REMOVE(integers,456) FROM " + tableName + " WHERE region_name = 'SF Bay
Area'");
+		assertTrue(rs.next());
+
+		assertEquals(conn.createArrayOf("INTEGER", new Integer[] { 2345, 46345, 23234 }), rs.getArray(1));
+		assertFalse(rs.next());
+	}
+
+	@Test
+	public void testArrayRemoveFunctionDouble() throws Exception {
+		ResultSet rs = conn.createStatement().executeQuery(
+				"SELECT ARRAY_REMOVE(doubles,double1) FROM " + tableName + " WHERE region_name = 'SF
Bay Area'");
+		assertTrue(rs.next());
+
+		assertEquals(conn.createArrayOf("DOUBLE", new Double[] { 10.0, 46.345, 23.234, 45.6, 5.78
}), rs.getArray(1));
+		assertFalse(rs.next());
+	}
+
+	@Test
+	public void testArrayRemoveFunctionDoubleWithInt() throws Exception {
+		ResultSet rs = conn.createStatement().executeQuery(
+				"SELECT ARRAY_REMOVE(doubles,10),doubles FROM " + tableName + " WHERE region_name = 'SF
Bay Area'");
+		assertTrue(rs.next());
+
+		assertEquals(conn.createArrayOf("DOUBLE", new Double[] { 23.45, 46.345, 23.234, 45.6, 5.78
}), rs.getArray(1));
+		assertEquals(conn.createArrayOf("DOUBLE", new Double[] { 10.0, 23.45, 46.345, 23.234, 45.6,
5.78 }),
+				rs.getArray(2));
+		assertFalse(rs.next());
+	}
+
+	@Test
+	public void testArrayRemoveFunctionBigint() throws Exception {
+		ResultSet rs = conn.createStatement().executeQuery(
+				"SELECT ARRAY_REMOVE(bigints,56),bigints FROM " + tableName + " WHERE region_name = 'SF
Bay Area'");
+		assertTrue(rs.next());
+
+		assertEquals(conn.createArrayOf("BIGINT", new Long[] { 12l, 34l, 78l, 910l }), rs.getArray(1));
+		assertEquals(conn.createArrayOf("BIGINT", new Long[] { 12l, 34l, 56l, 78l, 910l }), rs.getArray(2));
+		assertFalse(rs.next());
+	}
+
+	@Test
+	public void testArrayRemoveFunctionBigintWithInteger() throws Exception {
+		ResultSet rs = conn.createStatement().executeQuery(
+				"SELECT ARRAY_REMOVE(bigints,integer1) FROM " + tableName + " WHERE region_name = 'SF
Bay Area'");
+		assertTrue(rs.next());
+
+		assertEquals(conn.createArrayOf("BIGINT", new Long[] { 34l, 56l, 78l, 910l }), rs.getArray(1));
+		assertFalse(rs.next());
+	}
+
+	@Test(expected = TypeMismatchException.class)
+	public void testArrayRemoveFunctionBigintWithDouble() throws Exception {
+		ResultSet rs = conn.createStatement().executeQuery(
+				"SELECT ARRAY_REMOVE(bigints,double2) FROM " + tableName + " WHERE region_name = 'SF
Bay Area'");
+		assertTrue(rs.next());
+
+		assertEquals(conn.createArrayOf("BIGINT", new Long[] { 34l, 56l, 78l, 910l }), rs.getArray(1));
+		assertFalse(rs.next());
+	}
+
+	@Test
+	public void testArrayRemoveFunctionChar() throws Exception {
+		ResultSet rs = conn.createStatement().executeQuery(
+				"SELECT ARRAY_REMOVE(chars,'ddd') FROM " + tableName + " WHERE region_name = 'SF Bay
Area'");
+		assertTrue(rs.next());
+
+		assertEquals(conn.createArrayOf("CHAR", new String[] { "a", "bbbb", "c", "e", "c" }), rs.getArray(1));
+		assertFalse(rs.next());
+	}
+
+	@Test(expected = TypeMismatchException.class)
+	public void testArrayRemoveFunctionIntToCharArray() throws Exception {
+		conn.createStatement().executeQuery(
+				"SELECT ARRAY_REMOVE(varchars,234) FROM " + tableName + " WHERE region_name = 'SF Bay
Area'");
+	}
+
+	@Test(expected = TypeMismatchException.class)
+	public void testArrayRemoveFunctionVarcharToIntegerArray() throws Exception {
+		conn.createStatement().executeQuery(
+				"SELECT ARRAY_REMOVE(integers,'234') FROM " + tableName + " WHERE region_name = 'SF Bay
Area'");
+	}
+
+	@Test
+	public void testArrayRemoveFunctionWithNestedFunctions1() throws Exception {
+		ResultSet rs = conn.createStatement().executeQuery("SELECT ARRAY_REMOVE(ARRAY[23,2345],integers[1])
FROM "
+				+ tableName + " WHERE region_name = 'SF Bay Area'");
+		assertTrue(rs.next());
+
+		assertEquals(conn.createArrayOf("INTEGER", new Integer[] { 23 }), rs.getArray(1));
+		assertFalse(rs.next());
+	}
+
+	@Test
+	public void testArrayRemoveFunctionWithNestedFunctions2() throws Exception {
+		ResultSet rs = conn.createStatement()
+				.executeQuery("SELECT ARRAY_REMOVE(integers,ARRAY_ELEM(ARRAY[2345,4],1)) FROM " + tableName
+						+ " WHERE region_name = 'SF Bay Area'");
+		assertTrue(rs.next());
+
+		assertEquals(conn.createArrayOf("INTEGER", new Integer[] { 46345, 23234, 456 }), rs.getArray(1));
+		assertFalse(rs.next());
+	}
+
+	@Test
+	public void testArrayRemoveFunctionWithUpsert1() throws Exception {
+		String uniqueName = generateUniqueName();
+		String ddl = "CREATE TABLE " + uniqueName + " (region_name VARCHAR PRIMARY KEY,varchars
VARCHAR[])";
+		conn.createStatement().execute(ddl);
+
+		String dml = "UPSERT INTO " + uniqueName
+				+ "(region_name,varchars) VALUES('SF Bay Area',ARRAY_REMOVE(ARRAY['hello','world'],'world'))";
+		conn.createStatement().execute(dml);
+		conn.commit();
+
+		ResultSet rs = conn.createStatement()
+				.executeQuery("SELECT varchars FROM " + uniqueName + " WHERE region_name = 'SF Bay Area'");
+		assertTrue(rs.next());
+
+		assertEquals(conn.createArrayOf("VARCHAR", new String[] { "hello" }), rs.getArray(1));
+		assertFalse(rs.next());
+	}
+
+	@Test
+	public void testArrayRemoveFunctionWithUpsert2() throws Exception {
+		String tableName = generateUniqueName();
+		String ddl = "CREATE TABLE " + tableName + " (region_name VARCHAR PRIMARY KEY,integers
INTEGER[])";
+		conn.createStatement().execute(ddl);
+
+		String dml = "UPSERT INTO " + tableName
+				+ "(region_name,integers) VALUES('SF Bay Area',ARRAY_REMOVE(ARRAY[4,5],5))";
+		conn.createStatement().execute(dml);
+		conn.commit();
+
+		ResultSet rs = conn.createStatement()
+				.executeQuery("SELECT integers FROM " + tableName + " WHERE region_name = 'SF Bay Area'");
+		assertTrue(rs.next());
+
+		assertEquals(conn.createArrayOf("INTEGER", new Integer[] { 4 }), rs.getArray(1));
+		assertFalse(rs.next());
+	}
+
+	@Test
+	public void testArrayRemoveFunctionWithUpsertSelect1() throws Exception {
+		String sourceTableName = generateUniqueName();
+		String targetTableName = generateUniqueName();
+
+		String ddl = "CREATE TABLE " + sourceTableName + " (region_name VARCHAR PRIMARY KEY,doubles
DOUBLE[])";
+		conn.createStatement().execute(ddl);
+
+		ddl = "CREATE TABLE " + targetTableName + " (region_name VARCHAR PRIMARY KEY,doubles DOUBLE[])";
+		conn.createStatement().execute(ddl);
+
+		String dml = "UPSERT INTO " + sourceTableName
+				+ "(region_name,doubles) VALUES('SF Bay Area',ARRAY_APPEND(ARRAY[5.67,7.87],9))";
+		conn.createStatement().execute(dml);
+
+		dml = "UPSERT INTO " + sourceTableName
+				+ "(region_name,doubles) VALUES('SF Bay Area2',ARRAY_APPEND(ARRAY[56.7,7.87],9))";
+		conn.createStatement().execute(dml);
+		conn.commit();
+
+		dml = "UPSERT INTO " + targetTableName
+				+ "(region_name, doubles) SELECT region_name, ARRAY_REMOVE(doubles,9) FROM " + sourceTableName;
+		conn.createStatement().execute(dml);
+		conn.commit();
+
+		ResultSet rs = conn.createStatement().executeQuery("SELECT doubles FROM " + targetTableName);
+		assertTrue(rs.next());
+
+		assertEquals(conn.createArrayOf("DOUBLE", new Double[] { 5.67, 7.87 }), rs.getArray(1));
+		assertTrue(rs.next());
+
+		assertEquals(conn.createArrayOf("DOUBLE", new Double[] { 56.7, 7.87 }), rs.getArray(1));
+		assertFalse(rs.next());
+	}
+
+	@Test
+	public void testArrayRemoveFunctionInWhere1() throws Exception {
+		ResultSet rs = conn.createStatement().executeQuery(
+				"SELECT region_name FROM " + tableName + " WHERE ARRAY[2345,46345,23234]=ARRAY_REMOVE(integers,456)");
+		assertTrue(rs.next());
+
+		assertEquals("SF Bay Area", rs.getString(1));
+		assertFalse(rs.next());
+	}
+
+	@Test
+	public void testArrayRemoveFunctionVarcharWithNull() throws Exception {
+		ResultSet rs = conn.createStatement().executeQuery(
+				"SELECT ARRAY_REMOVE(varchars,NULL) FROM " + tableName + " WHERE region_name = 'SF Bay
Area'");
+		assertTrue(rs.next());
+
+		assertEquals(conn.createArrayOf("VARCHAR", new String[] { "2345", "46345", "23234" }),
rs.getArray(1));
+		assertFalse(rs.next());
+	}
+
+	@Test
+	public void testArrayRemoveFunctionDoublesWithNull() throws Exception {
+		ResultSet rs = conn.createStatement().executeQuery(
+				"SELECT ARRAY_REMOVE(doubles,NULL) FROM " + tableName + " WHERE region_name = 'SF Bay
Area'");
+		assertTrue(rs.next());
+
+		assertEquals(conn.createArrayOf("DOUBLE", new Double[] { 10.0, 23.45, 46.345, 23.234, 45.6,
5.78 }),
+				rs.getArray(1));
+		assertFalse(rs.next());
+	}
+
+	@Test
+	public void testArrayRemoveFunctionCharsWithNull() throws Exception {
+		ResultSet rs = conn.createStatement().executeQuery(
+				"SELECT ARRAY_REMOVE(chars,NULL) FROM " + tableName + " WHERE region_name = 'SF Bay Area'");
+		assertTrue(rs.next());
+
+		assertEquals(conn.createArrayOf("CHAR", new String[] { "a", "bbbb", "c", "ddd", "e", "c"
}), rs.getArray(1));
+		assertFalse(rs.next());
+	}
+
+	@Test
+	public void testArrayRemoveFunctionWithNull() throws Exception {
+		ResultSet rs = conn.createStatement().executeQuery(
+				"SELECT ARRAY_REMOVE(integers,nullcheck) FROM " + tableName + " WHERE region_name = 'SF
Bay Area'");
+		assertTrue(rs.next());
+
+		assertEquals(conn.createArrayOf("INTEGER", new Integer[] { 2345, 46345, 23234, 456 }),
rs.getArray(1));
+		assertFalse(rs.next());
+	}
+
+	@Test
+	public void testArrayRemoveFunctionFirstElement() throws Exception {
+		ResultSet rs = conn.createStatement().executeQuery(
+				"SELECT ARRAY_REMOVE(varchars,'2345') FROM " + tableName + " WHERE region_name = 'SF
Bay Area'");
+		assertTrue(rs.next());
+
+		assertEquals(conn.createArrayOf("VARCHAR", new String[] { "46345", "23234" }), rs.getArray(1));
+		assertFalse(rs.next());
+	}
+
+	@Test
+	public void testArrayRemoveFunctionMiddleElement() throws Exception {
+		ResultSet rs = conn.createStatement().executeQuery(
+				"SELECT ARRAY_REMOVE(varchars,'46345') FROM " + tableName + " WHERE region_name = 'SF
Bay Area'");
+		assertTrue(rs.next());
+
+		assertEquals(conn.createArrayOf("VARCHAR", new String[] { "2345", "23234" }), rs.getArray(1));
+		assertFalse(rs.next());
+	}
+
+	@Test
+	public void testArrayRemoveFunctionLastElement() throws Exception {
+		ResultSet rs = conn.createStatement().executeQuery(
+				"SELECT ARRAY_REMOVE(varchars,'23234') FROM " + tableName + " WHERE region_name = 'SF
Bay Area'");
+		assertTrue(rs.next());
+
+		assertEquals(conn.createArrayOf("VARCHAR", new String[] { "2345", "46345" }), rs.getArray(1));
+		assertFalse(rs.next());
+	}
+
+	@Test
+	public void testArrayRemoveFunctionOneElement() throws Exception {
+		ResultSet rs = conn.createStatement().executeQuery(
+				"SELECT ARRAY_REMOVE(oneItem,'alone') FROM " + tableName + " WHERE region_name = 'SF
Bay Area'");
+		assertTrue(rs.next());
+
+		assertEquals(conn.createArrayOf("VARCHAR", new String[0]), rs.getArray(1));
+		assertFalse(rs.next());
+	}
+
+	@Test
+	public void testArrayRemoveFunctionRepeatingElements() throws Exception {
+		ResultSet rs = conn.createStatement().executeQuery(
+				"SELECT ARRAY_REMOVE(chars,'c') FROM " + tableName + " WHERE region_name = 'SF Bay Area'");
+		assertTrue(rs.next());
+
+		assertEquals(conn.createArrayOf("CHAR", new String[] { "a", "bbbb", "ddd", "e" }), rs.getArray(1));
+		assertFalse(rs.next());
+	}
+
+	@Test
+	public void testArrayRemoveFunctionCharFromVarcharArray() throws Exception {
+		ResultSet rs = conn.createStatement().executeQuery(
+				"SELECT ARRAY_REMOVE(varchars,char2) FROM " + tableName + " WHERE region_name = 'SF Bay
Area'");
+		assertTrue(rs.next());
+
+		assertEquals(conn.createArrayOf("VARCHAR", new String[] { "46345", "23234" }), rs.getArray(1));
+		assertFalse(rs.next());
+	}
+
+	@Test
+	public void testArrayRemoveFunctionVarcharFromCharArray() throws Exception {
+		ResultSet rs = conn.createStatement().executeQuery(
+				"SELECT ARRAY_REMOVE(chars,varchar1) FROM " + tableName + " WHERE region_name = 'SF Bay
Area'");
+		assertTrue(rs.next());
+
+		assertEquals(conn.createArrayOf("CHAR", new String[] { "bbbb", "c", "ddd", "e", "c" }),
rs.getArray(1));
+		assertFalse(rs.next());
+	}
+
+}

http://git-wip-us.apache.org/repos/asf/phoenix/blob/cdb77d46/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 9a53eb1..c5065e0 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
@@ -29,6 +29,7 @@ import org.apache.phoenix.expression.function.ArrayFillFunction;
 import org.apache.phoenix.expression.function.ArrayIndexFunction;
 import org.apache.phoenix.expression.function.ArrayLengthFunction;
 import org.apache.phoenix.expression.function.ArrayPrependFunction;
+import org.apache.phoenix.expression.function.ArrayRemoveFunction;
 import org.apache.phoenix.expression.function.ArrayToStringFunction;
 import org.apache.phoenix.expression.function.ByteBasedRegexpReplaceFunction;
 import org.apache.phoenix.expression.function.ByteBasedRegexpSplitFunction;
@@ -46,9 +47,12 @@ import org.apache.phoenix.expression.function.CollationKeyFunction;
 import org.apache.phoenix.expression.function.ConvertTimezoneFunction;
 import org.apache.phoenix.expression.function.CountAggregateFunction;
 import org.apache.phoenix.expression.function.DayOfMonthFunction;
+import org.apache.phoenix.expression.function.DayOfWeekFunction;
+import org.apache.phoenix.expression.function.DayOfYearFunction;
 import org.apache.phoenix.expression.function.DecodeFunction;
 import org.apache.phoenix.expression.function.DefaultValueExpression;
 import org.apache.phoenix.expression.function.DistinctCountAggregateFunction;
+import org.apache.phoenix.expression.function.DistinctCountHyperLogLogAggregateFunction;
 import org.apache.phoenix.expression.function.DistinctValueAggregateFunction;
 import org.apache.phoenix.expression.function.EncodeFunction;
 import org.apache.phoenix.expression.function.ExpFunction;
@@ -129,9 +133,6 @@ import org.apache.phoenix.expression.function.UDFExpression;
 import org.apache.phoenix.expression.function.UpperFunction;
 import org.apache.phoenix.expression.function.WeekFunction;
 import org.apache.phoenix.expression.function.YearFunction;
-import org.apache.phoenix.expression.function.DayOfWeekFunction;
-import org.apache.phoenix.expression.function.DayOfYearFunction;
-import org.apache.phoenix.expression.function.DistinctCountHyperLogLogAggregateFunction;
 
 import com.google.common.collect.Maps;
 
@@ -296,7 +297,8 @@ public enum ExpressionType {
     FirstValuesFunction(FirstValuesFunction.class),
     LastValuesFunction(LastValuesFunction.class),
     DistinctCountHyperLogLogAggregateFunction(DistinctCountHyperLogLogAggregateFunction.class),
-    CollationKeyFunction(CollationKeyFunction.class);
+    CollationKeyFunction(CollationKeyFunction.class),
+    ArrayRemoveFunction(ArrayRemoveFunction.class);
 
     ExpressionType(Class<? extends Expression> clazz) {
         this.clazz = clazz;

http://git-wip-us.apache.org/repos/asf/phoenix/blob/cdb77d46/phoenix-core/src/main/java/org/apache/phoenix/expression/function/ArrayRemoveFunction.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/main/java/org/apache/phoenix/expression/function/ArrayRemoveFunction.java
b/phoenix-core/src/main/java/org/apache/phoenix/expression/function/ArrayRemoveFunction.java
new file mode 100644
index 0000000..d71cc23
--- /dev/null
+++ b/phoenix-core/src/main/java/org/apache/phoenix/expression/function/ArrayRemoveFunction.java
@@ -0,0 +1,88 @@
+/*
+ * 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.util.List;
+
+import org.apache.hadoop.hbase.io.ImmutableBytesWritable;
+import org.apache.phoenix.expression.Expression;
+import org.apache.phoenix.parse.ArrayModifierParseNode;
+import org.apache.phoenix.parse.FunctionParseNode;
+import org.apache.phoenix.schema.SortOrder;
+import org.apache.phoenix.schema.TypeMismatchException;
+import org.apache.phoenix.schema.types.PArrayDataTypeDecoder;
+import org.apache.phoenix.schema.types.PArrayDataTypeEncoder;
+import org.apache.phoenix.schema.types.PBinaryArray;
+import org.apache.phoenix.schema.types.PChar;
+import org.apache.phoenix.schema.types.PDataType;
+import org.apache.phoenix.schema.types.PVarbinary;
+import org.apache.phoenix.schema.types.PVarbinaryArray;
+import org.apache.phoenix.util.StringUtil;
+
+@FunctionParseNode.BuiltInFunction(name = ArrayRemoveFunction.NAME, nodeClass = ArrayModifierParseNode.class,
args = {
+		@FunctionParseNode.Argument(allowedTypes = { PBinaryArray.class, PVarbinaryArray.class
}),
+		@FunctionParseNode.Argument(allowedTypes = { PVarbinary.class }) })
+public class ArrayRemoveFunction extends ArrayModifierFunction {
+
+	public static final String NAME = "ARRAY_REMOVE";
+
+	public ArrayRemoveFunction() {
+	}
+
+	public ArrayRemoveFunction(List<Expression> children) throws TypeMismatchException
{
+		super(children);
+	}
+
+	@Override
+	protected boolean modifierFunction(ImmutableBytesWritable ptr, int length, int offset, byte[]
arrayBytes,
+			PDataType baseType, int arrayLength, Integer maxLength, Expression arrayExp) {
+		SortOrder sortOrder = arrayExp.getSortOrder();
+
+		if (ptr.getLength() == 0 || arrayBytes.length == 0) {
+			ptr.set(arrayBytes, offset, length);
+			return true;
+		}
+
+		PArrayDataTypeEncoder arrayDataTypeEncoder = new PArrayDataTypeEncoder(baseType, sortOrder);
+
+		if (getRHSBaseType().equals(PChar.INSTANCE)) {
+			int unpaddedCharLength = StringUtil.getUnpaddedCharLength(ptr.get(), ptr.getOffset(),
ptr.getLength(),
+					sortOrder);
+			ptr.set(ptr.get(), offset, unpaddedCharLength);
+		}
+
+		for (int arrayIndex = 0; arrayIndex < arrayLength; arrayIndex++) {
+			ImmutableBytesWritable ptr2 = new ImmutableBytesWritable(arrayBytes, offset, length);
+			PArrayDataTypeDecoder.positionAtArrayElement(ptr2, arrayIndex, baseType, maxLength);
+			if (baseType.compareTo(ptr2, sortOrder, ptr, sortOrder, baseType) != 0) {
+				arrayDataTypeEncoder.appendValue(ptr2.get(), ptr2.getOffset(), ptr2.getLength());
+			}
+		}
+
+		ptr.set(arrayDataTypeEncoder.encode());
+
+		return true;
+	}
+
+	@Override
+	public String getName() {
+		return NAME;
+	}
+
+}

http://git-wip-us.apache.org/repos/asf/phoenix/blob/cdb77d46/phoenix-core/src/test/java/org/apache/phoenix/expression/ArrayRemoveFunctionTest.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/test/java/org/apache/phoenix/expression/ArrayRemoveFunctionTest.java
b/phoenix-core/src/test/java/org/apache/phoenix/expression/ArrayRemoveFunctionTest.java
new file mode 100644
index 0000000..eb45eac
--- /dev/null
+++ b/phoenix-core/src/test/java/org/apache/phoenix/expression/ArrayRemoveFunctionTest.java
@@ -0,0 +1,285 @@
+/*
+ * 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;
+
+import static org.junit.Assert.assertEquals;
+
+import java.math.BigDecimal;
+import java.sql.Date;
+import java.sql.SQLException;
+import java.util.Calendar;
+import java.util.List;
+
+import org.apache.hadoop.hbase.io.ImmutableBytesWritable;
+import org.apache.phoenix.expression.function.ArrayRemoveFunction;
+import org.apache.phoenix.schema.SortOrder;
+import org.apache.phoenix.schema.types.PBoolean;
+import org.apache.phoenix.schema.types.PDataType;
+import org.apache.phoenix.schema.types.PDate;
+import org.apache.phoenix.schema.types.PDecimal;
+import org.apache.phoenix.schema.types.PDouble;
+import org.apache.phoenix.schema.types.PFloat;
+import org.apache.phoenix.schema.types.PInteger;
+import org.apache.phoenix.schema.types.PLong;
+import org.apache.phoenix.schema.types.PSmallint;
+import org.apache.phoenix.schema.types.PTime;
+import org.apache.phoenix.schema.types.PTimestamp;
+import org.apache.phoenix.schema.types.PTinyint;
+import org.apache.phoenix.schema.types.PVarbinary;
+import org.apache.phoenix.schema.types.PVarchar;
+import org.apache.phoenix.schema.types.PhoenixArray;
+import org.junit.Test;
+
+import com.google.common.collect.Lists;
+
+public class ArrayRemoveFunctionTest {
+
+	private static void testExpression(LiteralExpression array, LiteralExpression element, PhoenixArray
expected)
+			throws SQLException {
+		List<Expression> expressions = Lists.newArrayList((Expression) element);
+		expressions.add(array);
+
+		Expression arrayRemoveFunction = new ArrayRemoveFunction(expressions);
+		ImmutableBytesWritable ptr = new ImmutableBytesWritable();
+		arrayRemoveFunction.evaluate(null, ptr);
+		PhoenixArray result = (PhoenixArray) arrayRemoveFunction.getDataType().toObject(ptr,
+				expressions.get(1).getSortOrder(), array.getMaxLength(), array.getScale());
+		assertEquals(expected, result);
+	}
+
+	private static void test(PhoenixArray array, Object element, PDataType arrayDataType, Integer
arrMaxLen,
+			Integer arrScale, PDataType elementDataType, Integer elemMaxLen, Integer elemScale, PhoenixArray
expected,
+			SortOrder arraySortOrder, SortOrder elementSortOrder) throws SQLException {
+		LiteralExpression arrayLiteral, elementLiteral;
+		arrayLiteral = LiteralExpression.newConstant(array, arrayDataType, arrMaxLen, arrScale,
arraySortOrder,
+				Determinism.ALWAYS);
+		elementLiteral = LiteralExpression.newConstant(element, elementDataType, elemMaxLen, elemScale,
+				elementSortOrder, Determinism.ALWAYS);
+		testExpression(arrayLiteral, elementLiteral, expected);
+	}
+
+	@Test
+	public void testArrayRemoveFunction1() throws Exception {
+		Object[] o = new Object[] { 1, 2, -3, 4 };
+		Object[] o2 = new Object[] { 1, 2, -3 };
+		Object element = 4;
+		PDataType baseType = PInteger.INSTANCE;
+
+		PhoenixArray arr = new PhoenixArray.PrimitiveIntPhoenixArray(baseType, o);
+		PhoenixArray expected = new PhoenixArray.PrimitiveIntPhoenixArray(baseType, o2);
+		test(arr, element, PDataType.fromTypeId(baseType.getSqlType() + PDataType.ARRAY_TYPE_BASE),
null, null,
+				baseType, null, null, expected, SortOrder.ASC, SortOrder.ASC);
+	}
+
+	@Test
+	public void testArrayRemoveFunction2() throws Exception {
+		Object[] o = new Object[] { "1", "2", "3", "4" };
+		Object[] o2 = new Object[] { "1", "3", "4" };
+		Object element = "2";
+		PDataType baseType = PVarchar.INSTANCE;
+
+		PhoenixArray arr = new PhoenixArray(baseType, o);
+		PhoenixArray expected = new PhoenixArray(baseType, o2);
+		test(arr, element, PDataType.fromTypeId(baseType.getSqlType() + PDataType.ARRAY_TYPE_BASE),
null, null,
+				baseType, null, null, expected, SortOrder.ASC, SortOrder.ASC);
+	}
+
+	@Test
+	public void testArrayRemoveFunction3() throws Exception {
+		Object[] o = new Object[] { "1", "2", "2", "4" };
+		Object[] o2 = new Object[] { "1", "4" };
+		Object element = "2";
+		PDataType baseType = PVarchar.INSTANCE;
+
+		PhoenixArray arr = new PhoenixArray(baseType, o);
+		PhoenixArray expected = new PhoenixArray(baseType, o2);
+		test(arr, element, PDataType.fromTypeId(baseType.getSqlType() + PDataType.ARRAY_TYPE_BASE),
null, null,
+				baseType, null, null, expected, SortOrder.ASC, SortOrder.ASC);
+	}
+
+	@Test
+	public void testArrayRemoveFunction4() throws Exception {
+		Object[] o = new Object[] { "1", "2", "2", "4" };
+		Object[] o2 = new Object[] { "1", "2", "2", "4" };
+		Object element = "5";
+		PDataType baseType = PVarchar.INSTANCE;
+
+		PhoenixArray arr = new PhoenixArray(baseType, o);
+		PhoenixArray expected = new PhoenixArray(baseType, o2);
+		test(arr, element, PDataType.fromTypeId(baseType.getSqlType() + PDataType.ARRAY_TYPE_BASE),
null, null,
+				baseType, null, null, expected, SortOrder.ASC, SortOrder.ASC);
+	}
+
+	@Test
+	public void testArrayRemoveFunctionBoolean() throws Exception {
+		Boolean[] o = new Boolean[] { true, false, false, true };
+		Boolean[] o2 = new Boolean[] { true, true };
+		Boolean element = false;
+		PDataType baseType = PBoolean.INSTANCE;
+
+		PhoenixArray arr = new PhoenixArray.PrimitiveBooleanPhoenixArray(baseType, o);
+		PhoenixArray expected = new PhoenixArray.PrimitiveBooleanPhoenixArray(baseType, o2);
+		test(arr, element, PDataType.fromTypeId(baseType.getSqlType() + PDataType.ARRAY_TYPE_BASE),
null, null,
+				baseType, null, null, expected, SortOrder.ASC, SortOrder.ASC);
+	}
+
+	@Test
+	public void testArrayRemoveFunction6() throws Exception {
+		Object[] o = new Object[] { new Float(2.3), new Float(7.9), new Float(-9.6), new Float(2.3)
};
+		Object[] o2 = new Object[] { new Float(7.9), new Float(-9.6) };
+		Object element = 2.3;
+		PDataType baseType = PFloat.INSTANCE;
+
+		PhoenixArray arr = new PhoenixArray.PrimitiveFloatPhoenixArray(baseType, o);
+		PhoenixArray expected = new PhoenixArray.PrimitiveFloatPhoenixArray(baseType, o2);
+		test(arr, element, PDataType.fromTypeId(baseType.getSqlType() + PDataType.ARRAY_TYPE_BASE),
null, null,
+				baseType, null, null, expected, SortOrder.ASC, SortOrder.ASC);
+	}
+
+	@Test
+	public void testArrayRemoveFunction7() throws Exception {
+		Object[] o = new Object[] { 4.78, 9.54, 2.34, -9.675, Double.MAX_VALUE };
+		Object[] o2 = new Object[] { 9.54, 2.34, -9.675, Double.MAX_VALUE };
+		Object element = 4.78;
+		PDataType baseType = PDouble.INSTANCE;
+
+		PhoenixArray arr = new PhoenixArray.PrimitiveDoublePhoenixArray(baseType, o);
+		PhoenixArray expected = new PhoenixArray.PrimitiveDoublePhoenixArray(baseType, o2);
+		test(arr, element, PDataType.fromTypeId(baseType.getSqlType() + PDataType.ARRAY_TYPE_BASE),
null, null,
+				baseType, null, null, expected, SortOrder.ASC, SortOrder.ASC);
+	}
+
+	@Test
+	public void testArrayRemoveFunction8() throws Exception {
+		Object[] o = new Object[] { 123l, 677l, 98789l, -78989l, 66787l };
+		Object[] o2 = new Object[] { 123l, 677l, -78989l, 66787l };
+		Object element = 98789l;
+		PDataType baseType = PLong.INSTANCE;
+
+		PhoenixArray arr = new PhoenixArray.PrimitiveLongPhoenixArray(baseType, o);
+		PhoenixArray expected = new PhoenixArray.PrimitiveLongPhoenixArray(baseType, o2);
+		test(arr, element, PDataType.fromTypeId(baseType.getSqlType() + PDataType.ARRAY_TYPE_BASE),
null, null,
+				baseType, null, null, expected, SortOrder.ASC, SortOrder.ASC);
+	}
+
+	@Test
+	public void testArrayRemoveFunction9() throws Exception {
+		Object[] o = new Object[] { (short) 34, (short) -89, (short) 999, (short) 34 };
+		Object[] o2 = new Object[] { (short) 34, (short) -89, (short) 999, (short) 34 };
+		Object element = (short) -23;
+		PDataType baseType = PSmallint.INSTANCE;
+
+		PhoenixArray arr = new PhoenixArray.PrimitiveShortPhoenixArray(baseType, o);
+		PhoenixArray expected = new PhoenixArray.PrimitiveShortPhoenixArray(baseType, o2);
+		test(arr, element, PDataType.fromTypeId(baseType.getSqlType() + PDataType.ARRAY_TYPE_BASE),
null, null,
+				baseType, null, null, expected, SortOrder.ASC, SortOrder.ASC);
+	}
+
+	@Test
+	public void testArrayRemoveFunction10() throws Exception {
+		Object[] o = new Object[] { (byte) 4, (byte) 8, (byte) 9 };
+		Object[] o2 = new Object[] { (byte) 8, (byte) 9 };
+		Object element = (byte) 4;
+		PDataType baseType = PTinyint.INSTANCE;
+
+		PhoenixArray arr = new PhoenixArray.PrimitiveBytePhoenixArray(baseType, o);
+		PhoenixArray expected = new PhoenixArray.PrimitiveBytePhoenixArray(baseType, o2);
+		test(arr, element, PDataType.fromTypeId(baseType.getSqlType() + PDataType.ARRAY_TYPE_BASE),
null, null,
+				baseType, null, null, expected, SortOrder.ASC, SortOrder.ASC);
+	}
+
+	@Test
+	public void testArrayRemoveFunction11() throws Exception {
+		Object[] o = new Object[] { BigDecimal.valueOf(2345), BigDecimal.valueOf(-23.45), BigDecimal.valueOf(785)
};
+		Object[] o2 = new Object[] { BigDecimal.valueOf(-23.45), BigDecimal.valueOf(785) };
+		Object element = BigDecimal.valueOf(2345);
+		PDataType baseType = PDecimal.INSTANCE;
+
+		PhoenixArray arr = new PhoenixArray(baseType, o);
+		PhoenixArray expected = new PhoenixArray(baseType, o2);
+		test(arr, element, PDataType.fromTypeId(baseType.getSqlType() + PDataType.ARRAY_TYPE_BASE),
null, null,
+				baseType, null, null, expected, SortOrder.ASC, SortOrder.ASC);
+	}
+
+	@Test
+	public void testArrayRemoveFunction12() throws Exception {
+		Calendar calendar = Calendar.getInstance();
+		java.util.Date currentDate = calendar.getTime();
+		java.sql.Date date = new java.sql.Date(currentDate.getTime());
+		Date date2 = new Date(new java.util.Date().getTime() + 1000);
+
+		Object[] o = new Object[] { date, date, date, date2 };
+		Object[] o2 = new Object[] { date2 };
+		PDataType baseType = PDate.INSTANCE;
+
+		PhoenixArray arr = new PhoenixArray(baseType, o);
+		PhoenixArray expected = new PhoenixArray(baseType, o2);
+		test(arr, date, PDataType.fromTypeId(baseType.getSqlType() + PDataType.ARRAY_TYPE_BASE),
null, null, baseType,
+				null, null, expected, SortOrder.ASC, SortOrder.ASC);
+	}
+
+	@Test
+	public void testArrayRemoveFunction13() throws Exception {
+		Calendar calendar = Calendar.getInstance();
+		java.util.Date currentDate = calendar.getTime();
+		java.sql.Time time = new java.sql.Time(currentDate.getTime());
+		java.sql.Time time2 = new java.sql.Time(new java.util.Date().getTime() + 1000);
+
+		Object[] o = new Object[] { time, time, time, time2 };
+		Object[] o2 = new Object[] { time2 };
+		PDataType baseType = PTime.INSTANCE;
+
+		PhoenixArray arr = new PhoenixArray(baseType, o);
+		PhoenixArray expected = new PhoenixArray(baseType, o2);
+		test(arr, time, PDataType.fromTypeId(baseType.getSqlType() + PDataType.ARRAY_TYPE_BASE),
null, null, baseType,
+				null, null, expected, SortOrder.ASC, SortOrder.ASC);
+	}
+
+	@Test
+	public void testArrayRemoveFunction14() throws Exception {
+		Calendar calendar = Calendar.getInstance();
+		java.util.Date currentDate = calendar.getTime();
+		java.sql.Timestamp timestamp = new java.sql.Timestamp(currentDate.getTime());
+		java.sql.Timestamp timestamp2 = new java.sql.Timestamp(new java.util.Date().getTime() +
1000);
+
+		Object[] o = new Object[] { timestamp, timestamp2, timestamp, timestamp };
+		Object[] o2 = new Object[] { timestamp2 };
+		PDataType baseType = PTimestamp.INSTANCE;
+
+		PhoenixArray arr = new PhoenixArray(baseType, o);
+		PhoenixArray expected = new PhoenixArray(baseType, o2);
+		test(arr, timestamp, PDataType.fromTypeId(baseType.getSqlType() + PDataType.ARRAY_TYPE_BASE),
null, null,
+				baseType, null, null, expected, SortOrder.ASC, SortOrder.ASC);
+	}
+
+	@Test
+	public void testArrayRemoveFunction15() throws Exception {
+		byte[][] o = new byte[][] { new byte[] { 2, 0, 3 }, new byte[] { 42, 3 }, new byte[] {
5, 3 },
+				new byte[] { 6, 3 }, new byte[] { 2, 5 } };
+		byte[][] o2 = new byte[][] { new byte[] { 42, 3 }, new byte[] { 5, 3 }, new byte[] { 6,
3 },
+				new byte[] { 2, 5 } };
+		byte[] element = new byte[] { 2, 0, 3 };
+		PDataType baseType = PVarbinary.INSTANCE;
+
+		PhoenixArray arr = new PhoenixArray(baseType, o);
+		PhoenixArray expected = new PhoenixArray(baseType, o2);
+		test(arr, element, PDataType.fromTypeId(baseType.getSqlType() + PDataType.ARRAY_TYPE_BASE),
null, null,
+				baseType, 1, null, expected, SortOrder.ASC, SortOrder.DESC);
+	}
+
+}


Mime
View raw message